Restore multiple databases in the same job

etsuchiya · October 29, 2012 4:58PM

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 · October 30, 2012 5:01AM

Hi

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

Cheers,

Marianne

jonstahura · October 30, 2012 8:41AM

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 &#91;pubs&#93; 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 · October 30, 2012 1:36PM

Thanks jonstahura

I will try this in our test environment

Regards

[Deleted User] · December 11, 2012 11:48AM

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:49AM

Or actually it says

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

petey · December 12, 2012 7:32AM

Here's an alternative:

DECLARE @exitcode int
DECLARE @sqlerrorcode int
DECLARE @sitename nvarchar&#40;16&#41;
DECLARE @sql nvarchar&#40;1024&#41;

SET @sitename = '&lt;your value&gt;'

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] · December 17, 2012 3:31PM

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

petey · December 18, 2012 8:58PM

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.

Restore multiple databases in the same job

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk