Change Db name in script automatically

How do I run this against multiple DBs, where "TestDb" has to change dynamically to match the selected DB/s name?

ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT

Tagged:

Best Answer

  • Alex BAlex B Posts: 1,131 Diamond 4
    Hi @Gavin

    Hm, righto, looks like SQL doesn't like using a variable for a database name (or isn't expecting one).

    The answer in this SO article seems to cover almost this specific example (https://stackoverflow.com/questions/727788/how-to-use-a-variable-for-the-database-name-in-t-sql/727815) and I've adapted some of the suggestions from the comments as well.
    <div>DECLARE @DBNAME SYSNAME;</div><div>SET @DBNAME =&nbsp; DB_NAME();</div><div><br></div><div>DECLARE @RECOVERY_TEMPLATE VARCHAR(MAX);&nbsp; --could possibly use 176 as max dbname length is 128</div><div>SET @RECOVERY_TEMPLATE='ALTER DATABASE {DBNAME} SET RECOVERY SIMPLE WITH NO_WAIT';</div><div><br></div><div>DECLARE @SQL_SCRIPT VARCHAR(MAX);--could possibly use 176 as max dbname length is 128</div><div>SET @SQL_SCRIPT = REPLACE(@RECOVERY_TEMPLATE, '{DBNAME}', QUOTENAME(@DBNAME));</div><div><br></div><div>EXECUTE (@SQL_SCRIPT)</div>
    Hope that helps!

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?

Answers

Sign In or Register to comment.