Restoring Multiple databases

jortizv

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

Matthew Flatt

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

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 support@red-gate.com and put this forum posting in the subject/content.

It is a little big to paste here.

Thanks,

RickyR

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