Need to restore Multiple Databases on the same SQL Instance

Compresses, encrypts, secures and monitors SQL Server backups.

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

Need to restore Multiple Databases on the same SQL Instance

Postby pamozer » Fri Mar 21, 2014 11:33 pm

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?
pamozer
 
Posts: 13
Joined: Mon Jan 20, 2014 8:51 pm

Postby Manfred.Castro » Mon Mar 24, 2014 6:08 pm

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/ ... -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/vi ... hp?t=16041

Sincerely,
Manfred
Manfred Castro
Product Support
Red Gate Software
Manfred.Castro
 
Posts: 209
Joined: Mon Apr 23, 2012 2:49 pm

Postby pamozer » Thu Apr 10, 2014 6:01 pm

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]
pamozer
 
Posts: 13
Joined: Mon Jan 20, 2014 8:51 pm

Postby Manfred.Castro » Thu Apr 10, 2014 8:31 pm

I think your missing a comma after the password.

WITH PASSWORD=''fjfelejl'' ,
Manfred Castro
Product Support
Red Gate Software
Manfred.Castro
 
Posts: 209
Joined: Mon Apr 23, 2012 2:49 pm

Postby pamozer » Thu Apr 10, 2014 9:47 pm

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='
pamozer
 
Posts: 13
Joined: Mon Jan 20, 2014 8:51 pm

Postby Manfred.Castro » Thu Apr 10, 2014 9:49 pm

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
Manfred.Castro
 
Posts: 209
Joined: Mon Apr 23, 2012 2:49 pm

Postby pamozer » Thu Apr 10, 2014 9:58 pm

Nope. That didn't work either.
pamozer
 
Posts: 13
Joined: Mon Jan 20, 2014 8:51 pm

Postby Manfred.Castro » Thu Apr 10, 2014 10:06 pm

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
Manfred.Castro
 
Posts: 209
Joined: Mon Apr 23, 2012 2:49 pm

Postby pamozer » Thu Apr 10, 2014 11:35 pm

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 :? :?
pamozer
 
Posts: 13
Joined: Mon Jan 20, 2014 8:51 pm

Postby Manfred.Castro » Fri Apr 11, 2014 12:58 am

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
Manfred.Castro
 
Posts: 209
Joined: Mon Apr 23, 2012 2:49 pm


Return to SQL Backup 7

Who is online

Users browsing this forum: jhboricua and 0 guests