Red Gate forums :: View topic - Restore multiple databases in the same job
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 7
SQL Backup 7 forum

Restore multiple databases in the same job

Search in SQL Backup 7 forum
Post new topic   Reply to topic
Jump to:  
Author Message
etsuchiya



Joined: 29 Oct 2012
Posts: 3

PostPosted: Mon Oct 29, 2012 9:58 pm    Post subject: Restore multiple databases in the same job Reply with quote

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
Back to top
View user's profile Send private message
Marianne



Joined: 03 Oct 2011
Posts: 45

PostPosted: Tue Oct 30, 2012 10:01 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
jonstahura



Joined: 28 Sep 2012
Posts: 13

PostPosted: Tue Oct 30, 2012 1:41 pm    Post subject: Reply with quote

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:

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


my way:
Code:

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
Back to top
View user's profile Send private message
etsuchiya



Joined: 29 Oct 2012
Posts: 3

PostPosted: Tue Oct 30, 2012 6:36 pm    Post subject: Reply with quote

Thanks jonstahura

I will try this in our test environment

Regards
Back to top
View user's profile Send private message
SQLGator



Joined: 02 Dec 2011
Posts: 10

PostPosted: Tue Dec 11, 2012 5:48 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
SQLGator



Joined: 02 Dec 2011
Posts: 10

PostPosted: Tue Dec 11, 2012 5:49 pm    Post subject: Reply with quote

Or actually it says

[SQLSTATE 42000] (Error 137) Unclosed quotation mark after the character string ' '' '
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2306

PostPosted: Wed Dec 12, 2012 1:32 pm    Post subject: Reply with quote

Here's an alternative:

Code:
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
Back to top
View user's profile Send private message Send e-mail
SQLGator



Joined: 02 Dec 2011
Posts: 10

PostPosted: Mon Dec 17, 2012 9:31 pm    Post subject: Reply with quote

The problem with that is @sitename is the database name, how does it look through all of the database names?
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2306

PostPosted: Wed Dec 19, 2012 2:58 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group