How to restore all SQL Server database backups from a folder

Sometimes, as a DBA, you will need to restore in bulk hundreds of databases from a server backup to another server.
To do this easily, we created this script that will back up all databases.

This is the “Restore” script at the other end.


CREATE PROCEDURE Restorealldbs

AS

SET nocount ON
— 1 – Variable declaration 
DECLARE @dbName SYSNAME
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE
(
backupfile NVARCHAR(255)
)

DECLARE @lastFullBackup NVARCHAR(500)

DECLARE @lastDiffBackup NVARCHAR(500)

DECLARE @backupFile NVARCHAR(500)

DECLARE @FileListTbl TABLE

(
logicalname          NVARCHAR(128) NOT NULL,
physicalname         NVARCHAR(260) NOT NULL,
type                 CHAR(1) NOT NULL,
filegroupname        NVARCHAR(120) NULL,
size                 NUMERIC(20, 0) NOT NULL,
maxsize              NUMERIC(20, 0) NOT NULL,
fileid               BIGINT NULL,
createlsn            NUMERIC(25, 0) NULL,
droplsn              NUMERIC(25, 0) NULL,
uniqueid             UNIQUEIDENTIFIER NULL,
readonlylsn          NUMERIC(25, 0) NULL,
readwritelsn         NUMERIC(25, 0) NULL,
backupsizeinbytes    BIGINT NULL,
sourceblocksize      INT NULL,
filegroupid          INT NULL,
loggroupguid         UNIQUEIDENTIFIER NULL,
differentialbaselsn  NUMERIC(25, 0) NULL,
differentialbaseguid UNIQUEIDENTIFIER NULL,
isreadonly           BIT NULL,
ispresent            BIT NULL,
tdethumbprint        VARBINARY(32) NULL
);

— 2 – Initialize variables 

SET @dbName = 

SET @backupPath = ‘D:SQLBackup20150306’

— 3 – get list of files 

SET @cmd = ‘DIR /b ‘ + @backupPath

INSERT INTO @fileList

(backupfile)

EXEC master.sys.Xp_cmdshell

@cmd

DECLARE @SQL NVARCHAR(1500)

DECLARE @BiGSQL NVARCHAR(max)

SET @BiGSQL=

DECLARE db_cursor CURSOR FOR
SELECT backupfile
FROM   @fileList
OPEN db_cursor

FETCH next FROM db_cursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @file NVARCHAR(255)

DECLARE @RestoreStatement NVARCHAR(max)

SET @BackupFile = @backupPath + @dbName

SET @RestoreStatement = N’RESTORE FILELISTONLY  FROM DISK=N”’ + @BackupFile + ””

DELETE FROM @FileListTbl
INSERT INTO @FileListTbl
EXEC(@RestoreStatement);

DECLARE @logical_data NVARCHAR(max),

@logical_log  NVARCHAR(max);

SET @logical_data = (SELECT logicalname FROM   @FileListTbl WHERE  type = ‘D’ AND fileid = 1)

SET @logical_log = (SELECT logicalname FROM   @FileListTbl WHERE  type = ‘L’ AND fileid = 2)

SET @dbName = Replace(@DbName, ‘.BAK’, )

SET @sql = N’RESTORE database ‘ + @dbName + ‘ from disk =N”’ + @BackupFile + ”’ with ‘;

SET @sql = @sql + N’ move ”’ + @logical_data + N”’ to ”D:SQL Databases’ + @logical_data + ‘.mdf” ,’ 

SET @sql = @sql + N’ move ”’ + @logical_log + N”’ to ”D:SQL Databases’ + @logical_log + ‘.ldf”  ‘

IF Isnull(@SQL, ) <> 

SET @BiGSQL = @BiGSQL + 
 + @SQL

FETCH next FROM db_cursor INTO @dbName

END

CLOSE db_cursor
DEALLOCATE db_cursor
PRINT @BiGSQL
EXEC Sp_executesql @BiGSQL

Advertisements