| Author |
Message |
etsuchiya
Joined: 29 Oct 2012
Posts: 3
|
 Posted: Mon Oct 29, 2012 9:58 pm Post subject: Restore multiple databases in the same job |
 |
|
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 |
|
 |
Marianne
Joined: 03 Oct 2011
Posts: 45
|
| Posted: Tue Oct 30, 2012 10:01 am Post subject: |
|
|
Hi
No, I'm afraid it's not possible to restore more than one database at a time.
Cheers,
Marianne
_________________
Marianne Crowder
SQL Backup 7 Team
Red Gate Software Limited |
|
| Back to top |
|
|
jonstahura
Joined: 28 Sep 2012
Posts: 11
|
| Posted: Tue Oct 30, 2012 1:41 pm Post subject: |
|
|
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 |
|
|
etsuchiya
Joined: 29 Oct 2012
Posts: 3
|
| Posted: Tue Oct 30, 2012 6:36 pm Post subject: |
|
|
Thanks jonstahura
I will try this in our test environment
Regards |
|
| Back to top |
|
|
SQLGator
Joined: 02 Dec 2011
Posts: 10
|
| Posted: Tue Dec 11, 2012 5:48 pm Post subject: |
|
|
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 |
|
|
SQLGator
Joined: 02 Dec 2011
Posts: 10
|
| Posted: Tue Dec 11, 2012 5:49 pm Post subject: |
|
|
Or actually it says
[SQLSTATE 42000] (Error 137) Unclosed quotation mark after the character string ' '' ' |
|
| Back to top |
|
|
petey
Joined: 24 Apr 2005
Posts: 2219
|
| Posted: Wed Dec 12, 2012 1:32 pm Post subject: |
|
|
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 |
|
|
SQLGator
Joined: 02 Dec 2011
Posts: 10
|
| Posted: Mon Dec 17, 2012 9:31 pm Post subject: |
|
|
| The problem with that is @sitename is the database name, how does it look through all of the database names? |
|
| Back to top |
|
|
petey
Joined: 24 Apr 2005
Posts: 2219
|
| Posted: Wed Dec 19, 2012 2:58 am Post subject: |
|
|
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 |
|
|
|
