Restore multiple databases in the same job

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: JonathanWatts, Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

Restore multiple databases in the same job

Postby etsuchiya » Mon Oct 29, 2012 9:58 pm

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
etsuchiya
 
Posts: 3
Joined: Mon Oct 29, 2012 9:48 pm

Postby Marianne » Tue Oct 30, 2012 10:01 am

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
Marianne
 
Posts: 45
Joined: Mon Oct 03, 2011 3:24 pm

Postby jonstahura » Tue Oct 30, 2012 1:41 pm

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:

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


my way:
Code: Select all
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
 
Posts: 13
Joined: Fri Sep 28, 2012 2:28 pm

Postby etsuchiya » Tue Oct 30, 2012 6:36 pm

Thanks jonstahura

I will try this in our test environment

Regards
etsuchiya
 
Posts: 3
Joined: Mon Oct 29, 2012 9:48 pm

Postby SQLGator » Tue Dec 11, 2012 5:48 pm

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 = ''ed.watson@swfwmd.state.fl.us',
RECOVERY, DISCONNECT_EXISTING,
MOVE LOGFILES TO ''''G:\\VirtualRestore'''' , REPLACE , CHECKDB = ''ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS'', DROPDB''', @exitcode OUT, @sqlerrorcode OUT" '' '

Any ideas?
SQLGator
 
Posts: 10
Joined: Fri Dec 02, 2011 3:52 pm

Postby SQLGator » Tue Dec 11, 2012 5:49 pm

Or actually it says

[SQLSTATE 42000] (Error 137) Unclosed quotation mark after the character string ' '' '
SQLGator
 
Posts: 10
Joined: Fri Dec 02, 2011 3:52 pm

Postby petey » Wed Dec 12, 2012 1:32 pm

Here's an alternative:

Code: Select all
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.
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
petey
 
Posts: 2234
Joined: Sun Apr 24, 2005 12:34 pm

Postby SQLGator » Mon Dec 17, 2012 9:31 pm

The problem with that is @sitename is the database name, how does it look through all of the database names?
SQLGator
 
Posts: 10
Joined: Fri Dec 02, 2011 3:52 pm

Postby petey » Wed Dec 19, 2012 2:58 am

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 7
petey
 
Posts: 2234
Joined: Sun Apr 24, 2005 12:34 pm


Return to SQL Backup 7

Who is online

Users browsing this forum: No registered users and 0 guests