Need to restore Multiple Databases on the same SQL Instance

pamozerpamozer Posts: 16 New member
edited April 10, 2014 7:58PM in SQL Backup Previous Versions
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?

Comments

  • 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
  • pamozerpamozer Posts: 16 New member
    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
    
  • I think your missing a comma after the password.

    WITH PASSWORD=''fjfelejl'' ,
    Manfred Castro
    Product Support
    Red Gate Software
  • pamozerpamozer Posts: 16 New member
    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='
  • 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
  • pamozerpamozer Posts: 16 New member
    Nope. That didn't work either.
  • 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
  • pamozerpamozer Posts: 16 New member
    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 :? :?
  • 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
Sign In or Register to comment.