Need to restore Multiple Databases on the same SQL Instance

• 
  Hi Pam,
  
  Unfortunately there is not currently a way to restore multiple databases using the GUI.
  There is a existing feature request you may want to vote for.
  https://sqlbackup.uservoice.com/forums/ ... -databases
  These forums are actively monitored by our development team and allow our users to request features and vote on them.
  If a feature receives a significant amount of votes or is deemed to have merit development may include the feature in a future release.
  
  You can write a dynamic SQL script to restore multiple databases.
  See the following thread for some examples.
  http://www.red-gate.com/messageboard/vi ... hp?t=16041
  
  Sincerely,
  Manfred

  Manfred.Castro March 24, 2014 13:08

  - 0 +
• 
  I am having some issues with the syntax. Can you point out where I am going wrong?
  
  I am getting the following error Syntax error: 'fjfelej!'' after '\\kprod-nas03\sqlbackups\FULL_DB009_superbill_2039_prod_20140321_112322_encr.sqb WITH PASSWORD='
  

  Create Table #backupFiles(ID Int Identity(1,1),FileName Varchar(128), depth int, isfile int, databasename varchar(64))
  Insert into #backupfiles(FileName, depth, isfile)
  EXEC master.sys.xp_dirtree '\\kprod-nas03\ServerUpdates\SQLRestore\',0,1;
  
  
  Update #backupfiles
  set databasename=
  substring(FileName,12,len(filename)-charindex('2014',filename)-case when len(filename)=55 then 4 else 3 end)
  
  
  Create TAble #Restore(ID Int Identity(1,1),FileName Varchar(128), DatabaseName varchar(64))
  
  Insert into #Restore(FileName, DatabaseName)
  select FileName,Databasename
   from #backupfiles
  where filename like '%superbill%'
  
  
  
  
  
  Declare @DbCount int
  Declare @Counter Int
  DECLARE @exitcode int 
  DECLARE @sqlerrorcode int 
  DECLARE @sitename nvarchar(128) 
  DECLARE @sql nvarchar(1024) 
  Declare @DatabaseName varchar(64)
  
  Set @DBCount=(Select Max(ID) from #Restore)
  SET @counter=(Select Min(ID) From #Restore)
  
  While @Counter<=@DBCount
  Begin
  
  SET @sitename = (Select FileName from #Restore where ID =@Counter)
  Set @DatabaseName= (Select DatabaseName from #Restore where ID =@Counter)
  
  SET @sql = '-SQL "RESTORE DATABASE ' + @databasename + ' FROM DISK = ''\\kprod-nas03\sqlbackups\' + @sitename + ' WITH PASSWORD=''fjfelejl'' SOURCE = ''' + @DatabaseName + ''' 
     WITH MOVE DATAFILES TO ''D:\SQLData'' , MOVE LOGFILES TO ''D:\SQLLogs'' , 
     MAILTO = [url=''pam.ozer@kareo.com]''pam.ozer@kareo.com[/url]'', RECOVERY, 
      CHECKDB = ''ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS''' 
  
  EXEC master..sqlbackup @sql, @exitcode OUT, @sqlerrorcode OUT
  
  Set @Counter=@Counter+1
  END
  ----
  drop table #backupFiles, #Restore
  

  pamozer April 10, 2014 13:01

  - 0 +
• 
  I think your missing a comma after the password.
  
  WITH PASSWORD=''fjfelejl'' ,

  Manfred.Castro April 10, 2014 15:31

  - 0 +
• 
  This is the result of the dynameic sql
  
  -SQL "RESTORE DATABASE superbill_2039_prod FROM DISK = '\\kprod-nas03\serverupdates\sqlrestore\FULL_DB009_superbill_2039_prod_20140321_112322_encr.sqb WITH PASSWORD='fjfelej!' ,SOURCE = 'superbill_2039_prod'
  WITH MOVE DATAFILES TO 'D:\SQLData' , MOVE LOGFILES TO 'D:\SQLLogs' ,
  MAILTO = 'pam.ozer@kareo.com', RECOVERY,
  CHECKDB = 'ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS'
  
  
  I am still getting the same error
  Syntax error: 'fjfelej!'' after '\\kprod-nas03\serverupdates\sqlrestore\FULL_DB009_superbill_2039_prod_20140321_112322_encr.sqb WITH PASSWORD='
  
  --Syntax error: 'fjfelej!'' after '\\kprod-nas03\sqlbackups\FULL_DB009_superbill_2039_prod_20140321_112322_encr.sqb WITH PASSWORD='

  pamozer April 10, 2014 16:47

  - 0 +
• 
  Looks like your command has two "WITH" statements
  
  TRY
  
  SET @... = '-SQL "RESTORE DATABASE ' + @databasename + ' FROM DISK = ''\\kprod-nas03\sqlbackups\' + @sitename + ' WITH PASSWORD=''fjfelejl'' SOURCE = ''' + @DatabaseName + ''', MOVE DATAFILES TO ''D:\SQLData'' , MOVE LOGFILES TO ''D:\SQLLogs'' ,
  MAILTO = ''pam.ozer@kareo.com'', RECOVERY,
  CHECKDB = ''ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS'''

  Manfred.Castro April 10, 2014 16:49

  - 0 +
• 
  Nope. That didn't work either.

  pamozer April 10, 2014 16:58

  - 0 +
• 
  What is the statement generated?
  
  I think you are missing some single quotes.
  
  Try
  
  SET @... = '-SQL "RESTORE DATABASE ' + @databasename + ' FROM DISK = ''''\\kprod-nas03\sqlbackups\' + @sitename + ''''' WITH PASSWORD=''''fjfelejl'''' SOURCE = '''' + @DatabaseName + '''', MOVE DATAFILES TO ''''D:\SQLData'''' , MOVE LOGFILES TO ''''D:\SQLLogs'''' ,
  MAILTO = ''''pam.ozer@kareo.com'''', RECOVERY,
  CHECKDB = ''ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS'''

  Manfred.Castro April 10, 2014 17:06

  - 0 +
• 
  Now I get this error
  Syntax error: '\\kprod-nas03\sqlbackups\FULL_DB009_superbill_2039_prod_20140321_112322_encr.sqb''' after ''
  
  
  -SQL "RESTORE DATABASE superbill_2039_prod FROM DISK = ''\\kprod-nas03\sqlbackups\FULL_DB009_superbill_2039_prod_20140321_112322_encr.sqb'' WITH PASSWORD=''fjfelejl'' SOURCE = '' + @DatabaseName + '', MOVE DATAFILES TO ''D:\SQLData'' , MOVE LOGFILES TO ''D:\SQLLogs'' ,
  MAILTO = ''pam.ozer@kareo.com'', RECOVERY,
  CHECKDB = 'ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS'
  
  Thanks for helping me with this. I'm at my wits end with this :? :?

  pamozer April 10, 2014 18:35

  - 0 +
• 
  Try
  
  SET @... = '-SQL "RESTORE DATABASE ' + @databasename + ' FROM DISK = ''''\\kprod-nas03\sqlbackups\' + @sitename + ''''' WITH PASSWORD=''''fjfelejl'''' SOURCE = ''''' + @DatabaseName + ''''', MOVE DATAFILES TO ''''D:\SQLData'''' , MOVE LOGFILES TO ''''D:\SQLLogs'''' ,
  MAILTO = ''''pam.ozer@kareo.com'''', RECOVERY,
  CHECKDB = ''ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS'''

  Manfred.Castro April 10, 2014 19:58

  - 0 +

Add comment

Please sign in to leave a comment.