Restore multiple databases in the same job

etsuchiyaetsuchiya Posts: 5 Bronze 1
edited December 18, 2012 8:58PM in SQL Backup Previous Versions
Good day, is there a way to restore multiple database backups using one scheduled job?

The GUI tells me that only one database at a time can be restored and have a lot of databases.

Thanks in advanced

Comments

  • Hi

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

    Cheers,

    Marianne
    Marianne Crowder
    Red Gate Software Limited
  • 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
    
  • Thanks jonstahura

    I will try this in our test environment

    Regards
  • [Deleted User][Deleted User] Posts: 8
    edited August 1, 2017 9:08AM
    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=''[email protected]]''[email protected][/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?
  • Or actually it says

    [SQLSTATE 42000] (Error 137) Unclosed quotation mark after the character string ' '' '
  • peteypetey Posts: 2,358 New member
    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 = ''[email protected]'', 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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • [Deleted User][Deleted User] Posts: 8
    edited August 1, 2017 9:11AM
    The problem with that is @ sitename is the database name, how does it look through all of the database names?
  • peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.