Need to restore Multiple Databases on the same SQL Instance

pamozer

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?

Manfred.Castro

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

pamozer

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='

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 = [url=''pam.ozer@kareo.com]''pam.ozer@kareo.com[/url]'', 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

Manfred.Castro

I think your missing a comma after the password.

WITH PASSWORD=''fjfelejl'' ,

pamozer

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='

Manfred.Castro

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'''

pamozer

Nope. That didn't work either.

Manfred.Castro

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'''

pamozer

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 :? :?

Manfred.Castro

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'''