What are the challenges you face when working across database platforms? Take the survey
Options

Scheduled Restored (with multiple databases) in same job

marmite_dBAmarmite_dBA Posts: 3 Bronze 1
edited June 9, 2015 11:15AM in SQL Backup Previous Versions
Hi all,
I have setup a scheduled restore job within SQL Backup, though would like to know if there is a simpler way of selecting multiple databases within the same job, rather than creating 10 separate jobs for the 10 databases I have.
All test database files are in the same folder and going to the same database the only parameter that is different is the database name.
declare @exitcode int
DECLARE @sqlerrorcode int
EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE b]&lt;dBName&gt;[/b FROM DISK = ''\serverArchive*.sqb'' SOURCE = ''<dBName>'' LATEST_FULL WITH RECOVERY, DISCONNECT_EXISTING, MOVE DATAFILES TO ''F:Temp'', MOVE LOGFILES TO ''F:Temp'', REPLACE, ORPHAN_CHECK, CHECKDB = ''NO_INFOMSGS, ALL_ERRORMSGS''"', @exitcode OUT, @sqlerrorcode OUT
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
END

I have achieved this by copying the above block 10 times and changing the <dBName> to suit, messy I know, is there a way I can set a table variable and pass/iterate the 10 database names into one block or can the GUI have this and I am missing something
Regards
Ian

Comments

  • Options
    peteypetey Posts: 2,358 New member
    Here is one option. It requires that you first create a function that takes a comma delimited string of database names, and returns a table containing one row for each value.
    CREATE FUNCTION dbo.csv2table (@strString varchar(4000))
    RETURNS  @Result TABLE(ID int IDENTITY, dbname varchar(256))
    AS
    BEGIN
        WITH StrCTE(start, stop) AS
        (
          SELECT  1, CHARINDEX(',' , @strString )
          UNION ALL
          SELECT  stop + 1, CHARINDEX(',' ,@strString  , stop + 1)
          FROM StrCTE
          WHERE stop &gt; 0
        )
       
        INSERT INTO @Result
        SELECT LTRIM(SUBSTRING(@strString , start, CASE WHEN stop &gt; 0 THEN stop-start ELSE 4000 END)) AS stringValue
        FROM StrCTE
       
        RETURN
    END
    

    Then, in your job step, you could do this:
    SELECT * INTO #dbnames FROM csv2table('dbrestore001,  dbrestore002') --  change the string value to those of the databases you want to restore
    
    DECLARE @ID int = 1
    DECLARE @maxID int
    
    SELECT @maxID = MAX(ID) FROM #dbnames
    
    DECLARE @dbname varchar(128)
    DECLARE @sqbcmd varchar(1024)
    DECLARE @sqbexitcode int
    DECLARE @sqlerrorcode int
    
    WHILE (@ID &lt;= @MAXID)
    BEGIN
    	SELECT @dbname = dbname FROM #dbnames WHERE ID = @ID
    
    	SET @sqbcmd = '-SQL "RESTORE DATABASE &#91;' + @dbname + '&#93; FROM DISK = &#91;\serverArchive*.sqb&#93; SOURCE = &#91;' + @dbname + '&#93; LATEST_FULL WITH RECOVERY, DISCONNECT_EXISTING, MOVE DATAFILES TO &#91;F:Temp&#93;, MOVE LOGFILES TO &#91;F:Temp&#93;, REPLACE, ORPHAN_CHECK, CHECKDB = &#91;NO_INFOMSGS, ALL_ERRORMSGS&#93;"'
    
    	EXECUTE sqlbackup @sqbcmd, @sqbexitcode OUT, @sqlerrorcode OUT
    
    	IF (@sqbexitcode &gt;= 500) OR (@sqlerrorcode &lt;&gt; 0)
    	BEGIN
    		RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @sqbexitcode, @sqlerrorcode)
    	END
    
    	SET @ID = @ID + 1
    END
    
    DROP TABLE #dbnames
    
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    marmite_dBAmarmite_dBA Posts: 3 Bronze 1
    Peter,
    Thanks for this works a treat!
    Regards
    Ian
Sign In or Register to comment.