Change Db name in script automatically

Gavin

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

Alex B

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

Alex B

Hi @Gavin,

You should just be able to use the built in function DB_NAME()

I've done a small test and this returns the database name correctly:
​

Kind regards,
Alex

Gavin

Thanks Alex, but the following doesn't work.

ALTER DATABASE DB_NAME() SET RECOVERY SIMPLE WITH NO_WAIT

What's missing?

Gavin

Also getting
"Incorrect syntax near 'DB_NAME'."

for this

execute ('ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY SIMPLE WITH NO_WAIT');

Gavin

Thanks Alex, much appreciated.