Restoring Multiple databases

jortizvjortizv Posts: 5
edited February 22, 2016 4:47PM in SQL Backup Previous Versions
Hi, i am looking for a way on RedGate backup to restore all the databases on a server into a backup SQL server, my problem is that there is way too many databases and I would like to do it all in one shot, using the Gui or a script, is there a way to do this?, and converting the redgates backup file to .bak is not an option.

Any help will be really appreciated...
Thanks

Comments

  • Hi jortizv,

    I have the following script which may help, it doesn't exactly meet your requirements though.

    The script will go through each database on a server and restore a full backup appending 'Test' to the database name.

    If your backup SQL Server doesn't have the databases already created, you could retrieve the database names from a text file or something similar.

    The backup part of the script will need editing depending on logical/physical filenames etc
    USE master
    DECLARE @dbname NVARCHAR(260)
    DECLARE @newdbname NVARCHAR(260)
    DECLARE cDatabases CURSOR
        FOR SELECT  name
            FROM    sysdatabases
            WHERE   name != 'tempdb'
                    AND name != 'master'
                    AND name != 'msdb'
     --Databases to ignore
    DECLARE @datestamp VARCHAR(30)
    DECLARE @restorepath VARCHAR(500)
    DECLARE @filename VARCHAR(500)
    DECLARE @restorestring VARCHAR(1000)
    DECLARE @exitcode INT
    DECLARE @sqlerrorcode INT
    
    SET @restorepath = 'D:\Backup\'
     --Directory backups are stored
    
    OPEN cDatabases
    FETCH NEXT FROM cDatabases INTO @dbname
    
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @newdbname = @dbname + 'Test'
            SET @filename = @restorepath + 'FULL_SQL2005_' + @dbname + '_*.sqb'
            -- FULL_SQL2005 relates to my template for naming backups - Only 1 full backup for each database can be in the Backup location.
            SET @restorestring = '-SQL "RESTORE DATABASE [' + @newdbname
                + '] FROM DISK = ''' + @filename + ''' WITH RECOVERY, MOVE '''
                + @dbname + ''' TO ''D:\Data\' + @dbname + '.mdf'', MOVE '''
                + @dbname + '_log'' TO ''D:\Data\' + @dbname + '.ldf''" -E'
    
            EXEC master..sqlbackup @restorestring, @exitcode OUTPUT,
                @sqlerrorcode OUTPUT
     
            IF ( @exitcode <> 0 )
                OR ( @sqlerrorcode <> 0 ) 
                BEGIN
                    RAISERROR ( 'SQL Backup job failed with exitcode: %d  SQL error code: %d',
                        16, 1, @exitcode, @sqlerrorcode )
                END
    
            FETCH NEXT FROM cDatabases INTO @dbname
        END
    
    CLOSE cDatabases
    DEALLOCATE cDatabases
    

    I hope this is useful for you.
    Matthew Flatt
    Redgate Foundry
  • I have created another script which more suits your needs.

    The script will read file names from a directory and overwrite existing databases/create new databases on a server.

    If you are interested in this script please e-mail [email protected] and put this forum posting in the subject/content.

    It is a little big to paste here.

    Thanks,
    Matthew Flatt
    Redgate Foundry
  • For future reference, here is the script that Matt is referring to

    /*
    Creation Data : 05/11/2008

    This script will look in a directory for SQL Backup .sqb files and restore the newest backup over current databases.

    The script will also look in a directory for any SQL Backup .sqb files not associated with a current database.

    The database name will then be extracted from the header, checked against the current system databases.

    If this is a new database the script will read the file list and restore to a new database, moving 1 data and 1 log file.

    For more data/log files adjustments will need to be made.

    This script is provided "AS IS".
    */

    USE master
    DECLARE @dbname NVARCHAR(260)
    DECLARE cDatabases CURSOR
    FOR SELECT name
    FROM sysdatabases
    WHERE name != 'tempdb'
    AND name != 'master'
    AND name != 'msdb'
    --Databases to ignore
    DECLARE @restorepath VARCHAR(500)
    DECLARE @filename VARCHAR(500)
    DECLARE @restorestring VARCHAR(1000)
    DECLARE @exitcode INT
    DECLARE @sqlerrorcode INT
    DECLARE @searchstring VARCHAR(500)
    DECLARE @flagg INT
    DECLARE @ldata VARCHAR(200)
    DECLARE @llog VARCHAR(200)
    SET @restorepath = 'C:Backup'
    --Directory backups are stored

    -- Tempory tables to store list of all .sqb files in the @resorepath directory
    CREATE TABLE #sqbdir
    (
    subdirectory VARCHAR(500),
    Files BIT,
    size INT,
    datemodified datetime
    )
    CREATE TABLE #Files
    (
    subdirectory VARCHAR(500)
    )
    -- Get All files/Folder names in @restorepath
    INSERT INTO #sqbdir
    EXEC MASTER..SQBDIR @restorepath
    -- Filter only backup files with .sqb extension
    INSERT INTO #Files
    SELECT subdirectory
    FROM #sqbdir
    WHERE subdirectory LIKE '%.sqb'
    -- Get first database in sysdatabase
    OPEN cDatabases
    FETCH NEXT FROM cDatabases INTO @dbname
    -- While there are more databases
    @FETCH_STATUS = 0
    BEGIN
    SET @filename = NULL
    -- Search for .sqb files in the format of FULL_SQL2005_databasename_2date.sqb
    SET @searchstring = 'Full_SQL2005_' + @dbname + '_2%'
    -- Get the most recent backup file for the current database, only files in above format will be retrieved. Datastamp in filename used to retreive newest
    SELECT @filename = subdirectory
    FROM #Files
    WHERE subdirectory = ( SELECT TOP ( 1 )
    subdirectory
    FROM #Files
    WHERE subdirectory LIKE @searchstring
    ORDER BY subdirectory DESC
    )
    -- Set the backup command for the restore, overwrites current database with backup and moves file to Restored Folder
    SET @restorestring = '-SQL "RESTORE DATABASE [' + @dbname
    + '] FROM DISK = ''' + @restorepath + @filename
    + ''' WITH RECOVERY, REPLACE, MOVETO = ''C:BackupRestored''" -E'
    -- If a backup file exists begin the restore
    IF ( @filename != '' )
    BEGIN
    EXEC master..sqlbackup @restorestring, @exitcode OUTPUT,
    @sqlerrorcode OUTPUT
    -- If an error occurred raise it
    IF ( @exitcode <> 0 )
    OR ( @sqlerrorcode <> 0 )
    BEGIN
    RAISERROR ( 'SQL Backup job failed with exitcode: %d SQL error code: %d',
    16, 1, @exitcode, @sqlerrorcode )
    END
    END


    -- Get next database
    FETCH NEXT FROM cDatabases INTO @dbname
    END
    -- Close cursor and drop temporary tables
    CLOSE cDatabases
    DEALLOCATE cDatabases
    DROP TABLE #Files
    DROP TABLE #sqbdir
    -- Create new tempory tables to store file name information
    CREATE TABLE #sqbdir2
    (
    subdirectory VARCHAR(500),
    Files BIT,
    size INT,
    datemodified datetime
    )
    CREATE TABLE #Files2
    (
    subdirectory VARCHAR(500)
    )

    -- Get any .sqb files still in the @restorepath folder
    INSERT INTO #sqbdir2
    EXEC MASTER..SQBDIR @restorepath
    INSERT INTO #Files2
    SELECT subdirectory
    FROM #sqbdir2
    WHERE subdirectory LIKE '%.sqb'

    -- Create a cursor with list of .sqb files remaining and get first filename
    DECLARE cDatabases CURSOR
    FOR SELECT subdirectory
    FROM #Files2
    OPEN cDatabases
    FETCH NEXT FROM cDatabases INTO @filename
    -- While there are files remaining
    @FETCH_STATUS = 0
    BEGIN
    -- Create a tempory table and read the current backup files header information, inserting it into the table
    CREATE TABLE #header
    (
    id INT IDENTITY,
    header VARCHAR(MAX)
    )
    SET @restorestring = '-SQL "RESTORE SQBHEADERONLY FROM DISK = '''
    + @restorepath + @filename + ''' WITH SINGLERESULTSET" -E'
    INSERT INTO #header
    EXEC MASTER..sqlbackup @restorestring
    -- Extract the database name from the header file
    SELECT @dbname = SUBSTRING(header, 23, 260)
    FROM #HEADER
    WHERE header LIKE 'Database name%'
    -- Check the database name doesn't currently exist on the Server, if it does not restore the file
    SELECT @flagg = COUNT(*)
    FROM sysdatabases
    WHERE name = @dbname
    IF ( @flagg = 0 )
    BEGIN
    -- Create a temporary table to store filelist information and insert the data
    CREATE TABLE #FileList
    (
    ID INT IDENTITY,
    LogicalName VARCHAR(200),
    PhysicalName VARCHAR(200),
    Type CHAR,
    FileGroupName VARCHAR(100),
    Size INT,
    MaxSize BIGINT,
    FileID INT,
    CreateLSN BIGINT,
    DropLSN BIGINT,
    UniqueID VARCHAR(36),
    ReadOnlyLSN INT,
    ReadWriteLSN INT,
    BackupSizeInBytes INT,
    SourceBlockSize INT,
    FileGroupID INT,
    LogGroupGUID VARCHAR(36),
    DifferentialBaseLSN BIGINT,
    DifferentialBaseGUID VARCHAR(36),
    IsReadOnly BIT,
    IsPresent BIT
    )

    SET @restorestring = '-SQL "RESTORE FILELISTONLY FROM DISK = '''
    + @restorepath + @filename + '''" -E'
    INSERT INTO #FileList
    EXEC master..sqlbackup @restorestring
    -- Get the Logical Names for the Data and Log files
    SELECT @ldata = LogicalName
    FROM #FileList
    WHERE TYPE = 'D'
    SELECT @llog = LogicalName
    FROM #FileList
    WHERE TYPE = 'L'
    -- Set the SQL Backup Command, this will create a new database and move the data/log files to D:Data
    -- If your databases generally have more data/log files script will need to be ammended for each extra file
    SET @restorestring = '-SQL "RESTORE DATABASE [' + @dbname
    + '] FROM DISK = ''' + @restorepath + @filename
    + ''' WITH RECOVERY, MOVE ''' + @ldata
    + ''' TO ''D:Data' + @dbname + '_Data.mdf'', MOVE '''
    + @llog + ''' TO ''D:Data' + @dbname
    + '_Log.ldf'', MOVETO = ''C:BackupRestored''" -E'
    -- run the backup command and catch any errors
    EXEC master..sqlbackup @restorestring, @exitcode OUTPUT,
    @sqlerrorcode OUTPUT
    IF ( @exitcode <> 0 )
    OR ( @sqlerrorcode <> 0 )
    BEGIN
    RAISERROR ( 'SQL Backup job failed with exitcode: %d SQL error code: %d',
    16, 1, @exitcode, @sqlerrorcode )
    END
    DROP TABLE #FileList
    END

    DROP TABLE #header


    FETCH NEXT FROM cDatabases INTO @filename
    END

    CLOSE cDatabases
    DEALLOCATE cDatabases

    DROP TABLE #Files2
    DROP TABLE #sqbdir2
    Ricky Ram
    Technical Support
    Red Gate Software Ltd.
Sign In or Register to comment.