Red Gate forums :: View topic - Need to restore Multiple Databases on the same SQL Instance
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

Need to restore Multiple Databases on the same SQL Instance

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



Joined: 20 Jan 2014
Posts: 13

PostPosted: Fri Mar 21, 2014 11:33 pm    Post subject: Need to restore Multiple Databases on the same SQL Instance Reply with quote

For Disaster Recovery purposes I have a directory of full backup files that need to be restored to a single sql instance. Is there any way to either script or use the gui to say restore each of the backup files in this directory?
Back to top
View user's profile Send private message
Manfred.Castro



Joined: 23 Apr 2012
Posts: 209

PostPosted: Mon Mar 24, 2014 6:08 pm    Post subject: Reply with quote

Hi Pam,

Unfortunately there is not currently a way to restore multiple databases using the GUI.
There is a existing feature request you may want to vote for.
https://sqlbackup.uservoice.com/forums/91737-sql-backup/suggestions/5309755-be-able-to-restore-multiple-databases
These forums are actively monitored by our development team and allow our users to request features and vote on them.
If a feature receives a significant amount of votes or is deemed to have merit development may include the feature in a future release.

You can write a dynamic SQL script to restore multiple databases.
See the following thread for some examples.
http://www.red-gate.com/messageboard/viewtopic.php?t=16041

Sincerely,
Manfred
_________________
Manfred Castro
Product Support
Red Gate Software
Back to top
View user's profile Send private message
pamozer



Joined: 20 Jan 2014
Posts: 13

PostPosted: Thu Apr 10, 2014 6:01 pm    Post subject: Reply with quote

I am having some issues with the syntax. Can you point out where I am going wrong?

I am getting the following error Syntax error: 'fjfelej!'' after '\\kprod-nas03\sqlbackups\FULL_DB009_superbill_2039_prod_20140321_112322_encr.sqb WITH PASSWORD='

[code]
Create Table #backupFiles(ID Int Identity(1,1),FileName Varchar(128), depth int, isfile int, databasename varchar(64))
Insert into #backupfiles(FileName, depth, isfile)
EXEC master.sys.xp_dirtree '\\kprod-nas03\ServerUpdates\SQLRestore\',0,1;


Update #backupfiles
set databasename=
substring(FileName,12,len(filename)-charindex('2014',filename)-case when len(filename)=55 then 4 else 3 end)


Create TAble #Restore(ID Int Identity(1,1),FileName Varchar(128), DatabaseName varchar(64))

Insert into #Restore(FileName, DatabaseName)
select FileName,Databasename
from #backupfiles
where filename like '%superbill%'





Declare @DbCount int
Declare @Counter Int
DECLARE @exitcode int
DECLARE @sqlerrorcode int
DECLARE @sitename nvarchar(128)
DECLARE @sql nvarchar(1024)
Declare @DatabaseName varchar(64)

Set @DBCount=(Select Max(ID) from #Restore)
SET @counter=(Select Min(ID) From #Restore)

While @Counter<=@DBCount
Begin

SET @sitename = (Select FileName from #Restore where ID =@Counter)
Set @DatabaseName= (Select DatabaseName from #Restore where ID =@Counter)

SET @sql = '-SQL "RESTORE DATABASE ' + @databasename + ' FROM DISK = ''\\kprod-nas03\sqlbackups\' + @sitename + ' WITH PASSWORD=''fjfelejl'' SOURCE = ''' + @DatabaseName + '''
WITH MOVE DATAFILES TO ''D:\SQLData'' , MOVE LOGFILES TO ''D:\SQLLogs'' ,
MAILTO = ''pam.ozer@kareo.com'', RECOVERY,
CHECKDB = ''ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS'''

EXEC master..sqlbackup @sql, @exitcode OUT, @sqlerrorcode OUT

Set @Counter=@Counter+1
END
----
drop table #backupFiles, #Restore
[/code]
Back to top
View user's profile Send private message
Manfred.Castro



Joined: 23 Apr 2012
Posts: 209

PostPosted: Thu Apr 10, 2014 8:31 pm    Post subject: Reply with quote

I think your missing a comma after the password.

WITH PASSWORD=''fjfelejl'' ,
_________________
Manfred Castro
Product Support
Red Gate Software
Back to top
View user's profile Send private message
pamozer



Joined: 20 Jan 2014
Posts: 13

PostPosted: Thu Apr 10, 2014 9:47 pm    Post subject: Reply with quote

This is the result of the dynameic sql

-SQL "RESTORE DATABASE superbill_2039_prod FROM DISK = '\\kprod-nas03\serverupdates\sqlrestore\FULL_DB009_superbill_2039_prod_20140321_112322_encr.sqb WITH PASSWORD='fjfelej!' ,SOURCE = 'superbill_2039_prod'
WITH MOVE DATAFILES TO 'D:\SQLData' , MOVE LOGFILES TO 'D:\SQLLogs' ,
MAILTO = 'pam.ozer@kareo.com', RECOVERY,
CHECKDB = 'ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS'


I am still getting the same error
Syntax error: 'fjfelej!'' after '\\kprod-nas03\serverupdates\sqlrestore\FULL_DB009_superbill_2039_prod_20140321_112322_encr.sqb WITH PASSWORD='

--Syntax error: 'fjfelej!'' after '\\kprod-nas03\sqlbackups\FULL_DB009_superbill_2039_prod_20140321_112322_encr.sqb WITH PASSWORD='
Back to top
View user's profile Send private message
Manfred.Castro



Joined: 23 Apr 2012
Posts: 209

PostPosted: Thu Apr 10, 2014 9:49 pm    Post subject: Reply with quote

Looks like your command has two "WITH" statements

TRY

SET @sql = '-SQL "RESTORE DATABASE ' + @databasename + ' FROM DISK = ''\\kprod-nas03\sqlbackups\' + @sitename + ' WITH PASSWORD=''fjfelejl'' SOURCE = ''' + @DatabaseName + ''', MOVE DATAFILES TO ''D:\SQLData'' , MOVE LOGFILES TO ''D:\SQLLogs'' ,
MAILTO = ''pam.ozer@kareo.com'', RECOVERY,
CHECKDB = ''ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS'''
_________________
Manfred Castro
Product Support
Red Gate Software
Back to top
View user's profile Send private message
pamozer



Joined: 20 Jan 2014
Posts: 13

PostPosted: Thu Apr 10, 2014 9:58 pm    Post subject: Reply with quote

Nope. That didn't work either.
Back to top
View user's profile Send private message
Manfred.Castro



Joined: 23 Apr 2012
Posts: 209

PostPosted: Thu Apr 10, 2014 10:06 pm    Post subject: Reply with quote

What is the statement generated?

I think you are missing some single quotes.

Try

SET @sql = '-SQL "RESTORE DATABASE ' + @databasename + ' FROM DISK = ''''\\kprod-nas03\sqlbackups\' + @sitename + ''''' WITH PASSWORD=''''fjfelejl'''' SOURCE = '''' + @DatabaseName + '''', MOVE DATAFILES TO ''''D:\SQLData'''' , MOVE LOGFILES TO ''''D:\SQLLogs'''' ,
MAILTO = ''''pam.ozer@kareo.com'''', RECOVERY,
CHECKDB = ''ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS'''
_________________
Manfred Castro
Product Support
Red Gate Software
Back to top
View user's profile Send private message
pamozer



Joined: 20 Jan 2014
Posts: 13

PostPosted: Thu Apr 10, 2014 11:35 pm    Post subject: Reply with quote

Now I get this error
Syntax error: '\\kprod-nas03\sqlbackups\FULL_DB009_superbill_2039_prod_20140321_112322_encr.sqb''' after ''


-SQL "RESTORE DATABASE superbill_2039_prod FROM DISK = ''\\kprod-nas03\sqlbackups\FULL_DB009_superbill_2039_prod_20140321_112322_encr.sqb'' WITH PASSWORD=''fjfelejl'' SOURCE = '' + @DatabaseName + '', MOVE DATAFILES TO ''D:\SQLData'' , MOVE LOGFILES TO ''D:\SQLLogs'' ,
MAILTO = ''pam.ozer@kareo.com'', RECOVERY,
CHECKDB = 'ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS'

Thanks for helping me with this. I'm at my wits end with this Confused Confused
Back to top
View user's profile Send private message
Manfred.Castro



Joined: 23 Apr 2012
Posts: 209

PostPosted: Fri Apr 11, 2014 12:58 am    Post subject: Reply with quote

Try

SET @sql = '-SQL "RESTORE DATABASE ' + @databasename + ' FROM DISK = ''''\\kprod-nas03\sqlbackups\' + @sitename + ''''' WITH PASSWORD=''''fjfelejl'''' SOURCE = ''''' + @DatabaseName + ''''', MOVE DATAFILES TO ''''D:\SQLData'''' , MOVE LOGFILES TO ''''D:\SQLLogs'''' ,
MAILTO = ''''pam.ozer@kareo.com'''', RECOVERY,
CHECKDB = ''ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS'''
_________________
Manfred Castro
Product Support
Red Gate Software
Back to top
View user's profile Send private message
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