How to execute this t-sql script that syntax checking does not allow me?

Question

This script fails to execute from PowerShell (sqlcmd) and directly from SQL Management Studio:

IF db_id('BANANATEST') is not null
 BEGIN   
  print 'DB BANANATEST already exists. NO ACTION TAKEN.'
 END
ELSE
 BEGIN
  print 'DB BANANATEST does NOT exist.'
  print 'Restoring a backup of BANANATEST'
  RESTORE DATABASE BANANATEST
   FROM DISK = 'BANANATEST.original.bak' ;

  print 'Creating User BANANATEST'
  USE master
  If Exists (select loginname from master.dbo.syslogins where name ='BANANATEST')
  Begin
   DROP LOGIN [BANANATEST]
  END
  CREATE LOGIN [BANANATEST] WITH PASSWORD=N'BANANATEST', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
  USE BANANATEST
  ALTER USER [BANANATEST] WITH LOGIN = [BANANATEST]
END

The error is Syntax.

Msg 911, Level 16, State 1, Line 20 Database 'BANANATEST' does not exist. Make sure that the name is entered correctly.

While it is accurate, BANANATEST DB doesn't exist in the moment of query "compilation"... SQL Server doesn't realise that database WILL exist when it reaches this line.

How can I make it work? Either by ignoring syntax checks or whatever it make it work...

NOTE: The backup comes with a BANANATEST usr and I want to cover two scenarios:

LOGIN already exists in SQL Server
LOGIN does not exist

Use dynamic SQL so you don't get a compilation error when running this code. — Gordon Linoff
– Gordon Linoff, Commented Apr 25, 2016 at 16:25
Separate the RESTORE and USE into separate batches using a GO — Conrad Frix
– Conrad Frix, Commented Apr 25, 2016 at 16:34

Oscar Foley · Accepted Answer · 2016-04-26 08:16:00Z

1

You can get around the go with #temp tables

CREATE table #temp12 (restore1  bit)
IF db_id('BANANATEST') is not null
 BEGIN   
  print 'DB BANANATEST already exists. NO ACTION TAKEN.'
 END
ELSE
 BEGIN
  print 'DB BANANATEST does NOT exist.'
  print 'Restoring a backup of BANANATEST'
  RESTORE DATABASE BANANATEST
   FROM DISK = 'BANANATEST.original.bak' ;
  INSERT INTO #temp12 VALUES ('1')
  print 'Creating User BANANATEST'
 END 
 GO
  USE master
  IF EXISTS (SELECT 1 from #TEMP12)
      BEGIN
      If Exists (select loginname from master.dbo.syslogins where name ='BANANATEST')
      Begin
       DROP LOGIN [BANANATEST]
      END
      CREATE LOGIN [BANANATEST] WITH PASSWORD=N'BANANATEST', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
      USE BANANATEST
      ALTER USER [BANANATEST] WITH LOGIN = [BANANATEST]
  END
DROP TABLE #temp12

edited Apr 26, 2016 at 8:16

Oscar Foley

7,0659 gold badges62 silver badges94 bronze badges

answered Apr 25, 2016 at 19:57

Kevin Johnson

2181 gold badge3 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Oscar Foley Over a year ago

It works ok. I edited your answer as there usually a #TEMP db in sql server already and restore is a reserverd word...

Collectives™ on Stack Overflow

How to execute this t-sql script that syntax checking does not allow me?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related