Restore multiple databases in the same job

etsuchiya

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

Marianne

Hi

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

Cheers,

Marianne

jonstahura

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

etsuchiya

Thanks jonstahura

I will try this in our test environment

Regards

[Deleted User]

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]

Or actually it says

[SQLSTATE 42000] (Error 137) Unclosed quotation mark after the character string ' '' '

petey

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.

[Deleted User]

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

petey

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.