Change Db name in script automatically

• 
  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
  
  

  Alex B October 25, 2019 11:28

  - 0 +
• 
  Thanks Alex, but the following doesn't work.

  ALTER DATABASE DB_NAME() SET RECOVERY SIMPLE WITH NO_WAIT
  

  What's missing?
  

  Gavin October 25, 2019 12:07

  - 0 +
• 
  Also getting
  "Incorrect syntax near 'DB_NAME'."
  

  for this

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

  
  

  
  
  

  Gavin October 25, 2019 12:39

  - 0 +
• 
  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 October 25, 2019 12:58

  - 0 +
• 
  Thanks Alex, much appreciated.
  

  Gavin October 25, 2019 16:35

  - 0 +

Add comment

Please sign in to leave a comment.