Restore multiple databases in the same job

• 
  Hi
  
  No, I'm afraid it's not possible to restore more than one database at a time.
  
  Cheers,
  
  Marianne

  Marianne October 30, 2012 05:01

  - 0 +
• 
  you could script it...
  
  i am using dynamic sql to restore 54 databases in a row. I am restoring to another computer and instance, so it is a little more than what you would typically use.
  
  it is pretty easy..
  
  help instructions:
  

  EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [pubs] FROM DISK = ''C:\Backups\pubs_01.sqb'' WITH REPLACE" '
  

  
  my way:

  set @SQL = ' EXECUTE master..sqlbackup ''-SQL "RESTORE DATABASE  ' + @sitename + 
  		' FROM DISK = ''''' + @backupfilepath + ''''' WITH MOVE DATAFILES TO ''''' + @dbdirectory + ''''' , 
  		MOVE LOGFILES TO ''''' + @tlogdirectory +''''' , REPLACE " ''	'
  		
  		exec sp_executesql @sql
  

  jonstahura October 30, 2012 08:41

  - 0 +
• 
  Thanks jonstahura
  
  I will try this in our test environment
  
  Regards

  etsuchiya October 30, 2012 13:36

  - 0 +
• 
  I am trying to script this and it is not working, keeps saying I have an extra '
  

  SET @SQL = ' EXECUTE master..sqlbackup ''-SQL "RESTORE DATABASE  ' + @sitename +
        ' FROM DISK = ''''D:\SQLVMDEV10\' + @sitename + '\FULL\*.sqb'''' WITH MOVE DATAFILES TO ''''G:\VirtualRestore'''' , SOURCE = ''''' + @sitename + ''''' LATEST_FULL WITH MAILTO = [url=''ed.watson@swfwmd.state.fl.us]''ed.watson@swfwmd.state.fl.us[/url]', 
  RECOVERY, DISCONNECT_EXISTING, 
        MOVE LOGFILES TO ''''G:\VirtualRestore'''' , REPLACE , CHECKDB = ''ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS'', DROPDB''', @exitcode OUT, @sqlerrorcode OUT" ''   '
  

  
  Any ideas?

  [Deleted User] December 11, 2012 11:48

  - 0 +
• 
  Or actually it says
  
  [SQLSTATE 42000] (Error 137) Unclosed quotation mark after the character string ' '' '

  [Deleted User] December 11, 2012 11:49

  - 0 +
• 
  Here's an alternative:
  

  DECLARE @exitcode int
  DECLARE @sqlerrorcode int
  DECLARE @sitename nvarchar(16)
  DECLARE @sql nvarchar(1024)
  
  SET @sitename = '<your value>'
  
  SET @sql = '-SQL "RESTORE DATABASE ' + @sitename + ' FROM DISK = ''D:\SQLVMDEV10\' + @sitename + '\FULL\*.sqb'' SOURCE = ''' + @sitename + ''' LATEST_FULL
  	WITH MOVE DATAFILES TO ''G:\VirtualRestore'' , MOVE LOGFILES TO ''G:\VirtualRestore'' ,
  	MAILTO = ''ed.watson@swfwmd.state.fl.us'', RECOVERY, DISCONNECT_EXISTING, 
  	REPLACE , CHECKDB = ''ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS'', DROPDB'
  
  EXEC master..sqlbackup @sql, @exitcode OUT, @sqlerrorcode OUT
  

  
  Note that the SOURCE and LATEST_FULL options must come immediately after the DISK value.

  petey December 12, 2012 07:32

  - 0 +
• 
  The problem with that is @ sitename is the database name, how does it look through all of the database names?

  [Deleted User] December 17, 2012 15:31

  - 0 +
• 
  You could put the list of databases to restore in a table, and use a cursor to iterate through that table to form the script.

  petey December 18, 2012 20:58

  - 0 +

Add comment

Please sign in to leave a comment.