Auto generate SQL Server database restore scripts (Part 1)
Dynamically restore full and all transaction log backup

Auto generate SQL Server database restore scripts (Part 2)

Model:1 This tutorial will show, how you can restore las full backup and all transaction log backup dynamically.
Create Initial Step

 Collapse | Copy Code

----------------------------------------------------------------------------
-- Create Database
----------------------------------------------------------------------------
USE [master] 
GO 
CREATE DATABASE [DatabaseForLogBackups] ON PRIMARY  
(     NAME = N'DatabaseForLogBackups' 
   , FILENAME = N'D:\SQLData\DatabaseForLogBackups.mdf'  
   , SIZE = 512000KB  
   , FILEGROWTH = 51200KB ) LOG ON  
( NAME = N'DatabaseForLogBackups_log' 
     , FILENAME = N'D:\SQLData\DatabaseForLogBackups_log.ldf'  
   , SIZE = 51200KB  
   , FILEGROWTH = 51200KB ) 
GO 
ALTER DATABASE [DatabaseForLogBackups] SET RECOVERY FULL 
GO

----------------------------------------------------------------------------
-- Create Table
----------------------------------------------------------------------------
USE [DatabaseForLogBackups] 
GO 
 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE TABLE [dbo].[MessageTable1] 
    ( 
      [Message] [nvarchar](100) NOT NULL , 
      [DateTimeStamp] [datetime2] NOT NULL 
    ) 
ON  [PRIMARY] 
GO 
CREATE TABLE [dbo].[MessageTable2] 
    ( 
      [Message] [nvarchar](100) NOT NULL , 
      [DateTimeStamp] [datetime2] NOT NULL 
    ) 
ON  [PRIMARY] 
GO 
CREATE TABLE [dbo].[MessageTable3] 
    ( 
      [Message] [nvarchar](100) NOT NULL , 
      [DateTimeStamp] [datetime2] NOT NULL 
    ) 
ON  [PRIMARY] 
GO

----------------------------------------------------------------------------
-- Load Data in Table
----------------------------------------------------------------------------

USE [DatabaseForLogBackups] 
 
INSERT INTO dbo.MessageTable1  
   VALUES ('This is the initial data for MessageTable1', GETDATE()) 
GO 1000 
 
INSERT INTO dbo.MessageTable2  
   VALUES ('This is the initial data for MessageTable2', GETDATE()) 
GO 1000 
 
INSERT INTO dbo.MessageTable3  
   VALUES ('This is the initial data for MessageTable3', GETDATE()) 
GO 1000

Create Full Backup

 Collapse | Copy Code

DECLARE @name sysname -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

 
-- specify database backup directory
SET @path = 'D:\SQLData\'  
set @name='DatabaseForLogBackups';
 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

 
SET @fileName = @path + @name + '_' + @fileDate + '.bak'  
BACKUP DATABASE @name 
TO DISK = @fileName  
WITH NAME = N'DatabaseForLogBackups-Full Database Backup', STATS = 10, INIT 
GO

Add some more Data

 Collapse | Copy Code

USE [DatabaseForLogBackups] 
GO 
INSERT INTO MessageTable1  
   VALUES ('Second set of data for MessageTable1', GETDATE()) 
GO 50 
INSERT INTO MessageTable2  
   VALUES ('Second set of data for MessageTable2', GETDATE()) 
GO 50 
INSERT INTO MessageTable3  
   VALUES ('Second set of data for MessageTable3', GETDATE()) 
GO 50

Take Transaction Log

 Collapse | Copy Code

DECLARE @name sysname -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

 
-- specify database backup directory
SET @path = 'D:\SQLData\'  
set @name='DatabaseForLogBackups';
 
-- specify filename format
 
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)  
   + '_'  
   + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','') 
 
SET @fileName = @path + @name + '_' + @fileDate + '.trn'  
BACKUP LOG @name 
TO DISK = @fileName  
WITH NAME = N'DatabaseForLogBackups-Log Database Backup', STATS = 10 
GO

Add some more Data

 Collapse | Copy Code

USE [DatabaseForLogBackups] 
GO 
INSERT INTO MessageTable1  
   VALUES ('Second set of data for MessageTable1', GETDATE()) 
GO 150 
INSERT INTO MessageTable2  
   VALUES ('Second set of data for MessageTable2', GETDATE()) 
GO 150 
INSERT INTO MessageTable3  
   VALUES ('Second set of data for MessageTable3', GETDATE()) 
GO 150

Take Another Transaction Log

 Collapse | Copy Code

DECLARE @name sysname -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

 
-- specify database backup directory
SET @path = 'D:\SQLData\'  
set @name='DatabaseForLogBackups';
 
-- specify filename format
 
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)  
   + '_'  
   + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','') 
 
SET @fileName = @path + @name + '_' + @fileDate + '.trn'  
BACKUP LOG @name 
TO DISK = @fileName  
WITH NAME = N'DatabaseForLogBackups-Log Database Backup', STATS = 10 
GO

After Taking Backup

Create New Database for Restore

 Collapse | Copy Code

USE [master] 
GO 
CREATE DATABASE [DatabaseForRestore] ON PRIMARY  
(     NAME = N'DatabaseForRestore' 
   , FILENAME = N'D:\SQLData\DatabaseForRestore.mdf'  
   , SIZE = 512000KB  
   , FILEGROWTH = 51200KB ) LOG ON  
( NAME = N'DatabaseForRestore_log' 
     , FILENAME = N'D:\SQLData\DatabaseForRestore_log.ldf'  
   , SIZE = 51200KB  
   , FILEGROWTH = 51200KB ) 
GO 
ALTER DATABASE [DatabaseForRestore] SET RECOVERY FULL 
GO

Main Script

 Collapse | Copy Code

----------------------------------------------------------------------------
-- Variable
----------------------------------------------------------------------------
DECLARE      
    @BackupFile nvarchar(260),
    @Restore_DatabaseName sysname = NULL,
    @Backup_DatabaseName sysname = NULL,
    @Restore_DataFile nvarchar(260) = NULL,
    @Restore_LogFile nvarchar(260) = NULL,
    @Backup_DataFile nvarchar(260) = NULL,
    @Backup_LogFile nvarchar(260) = NULL,
    @tmp nvarchar(200),
    @physical_device_name varchar(1000),
    @backup_set_id_Full INT;
      
DECLARE @FileList 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
 );
 
 DECLARE @TranBackupList TABLE
 (
    id int identity(1,1) NOT NULL,
    PhysicalName nvarchar(260) NOT NULL
 );      
    
SET @Backup_DatabaseName='DatabaseForLogBackups';
SET @Restore_DatabaseName='DatabaseForRestore';

----------------------------------------------------------------------------
--1. Get Physical File Name from Database Name
----------------------------------------------------------------------------

SELECT 
    @Restore_DataFile=f.filename
FROM master..sysaltfiles f
    INNER JOIN master..sysdatabases d
    ON f.dbid = d.dbid
WHERE d.name = @Restore_DatabaseName
AND f.fileid=1

SELECT 
    @Restore_LogFile=f.filename
FROM master..sysaltfiles f
    INNER JOIN master..sysdatabases d
    ON f.dbid = d.dbid
WHERE d.name = @Restore_DatabaseName
AND f.fileid=2


-- Set Database in Single User Mode
SET @tmp = N'ALTER DATABASE '+ @Restore_DatabaseName +' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;';
EXEC(@tmp);

-- Get the ID and path Name of the most recent full backup for the database
SELECT @physical_device_name=physical_device_name,
@backup_set_id_Full = b.backup_set_id
FROM msdb.dbo.backupset b
    JOIN msdb.dbo.backupmediafamily m 
    ON b.media_set_id = m.media_set_id
WHERE b.backup_set_id=
(
    SELECT MAX(backup_set_id)
    FROM  msdb.dbo.backupset  
    WHERE database_name = @Backup_DatabaseName 
        AND type = 'D' 
)
 
-- get mdf and ldf info from backup file name
SET @tmp = N'RESTORE FILELISTONLY
      FROM DISK=N''' + @physical_device_name + ''' WITH FILE=1' ;
INSERT INTO @FileList
      EXEC(@tmp);
--mdf 
select @Backup_DataFile=LogicalName
from @FileList
where [Type]='D';
--ldf
select @Backup_LogFile=LogicalName
from @FileList
where [Type]='L';


----------------------------------------------------------------------------
--2. Restore Last Full Backup
----------------------------------------------------------------------------

RESTORE DATABASE @Restore_DatabaseName
FROM DISK = @physical_device_name 
WITH MOVE @Backup_DataFile TO @Restore_DataFile ,
MOVE @Backup_LogFile TO @Restore_LogFile,
REPLACE,
NORECOVERY, STATS = 10;

----------------------------------------------------------------------------
--3. Get All transaction Log backup list 
----------------------------------------------------------------------------

insert into @TranBackupList
SELECT  physical_device_name
FROM msdb.dbo.backupset b
    JOIN msdb.dbo.backupmediafamily m 
    ON b.media_set_id = m.media_set_id
WHERE b.backup_set_id>(@backup_set_id_Full)
AND database_name = @Backup_DatabaseName 
AND type = 'L'
ORDER BY b.backup_set_id

----------------------------------------------------------------------------
--4. Restore transaction Log 
----------------------------------------------------------------------------

DECLARE @Flag INT
DECLARE @RowCount INT
select @RowCount=COUNT(*) from @TranBackupList;

SET @Flag = 1
WHILE (@Flag <=@RowCount )
BEGIN
    select @BackupFile=PhysicalName
    from @TranBackupList
    where id=@Flag;
    PRINT @BackupFile
    RESTORE LOG @Restore_DatabaseName
    FROM DISK = @BackupFile
    WITH FILE = 1, NORECOVERY, STATS = 10 ;
    
    SET @Flag = @Flag + 1
END

-- Set Database in Normal mode
RESTORE DATABASE @Restore_DatabaseName
   WITH RECOVERY;

-- Set Database in Multi User Mode
SET @tmp = N'ALTER DATABASE '+ @Restore_DatabaseName +' SET MULTI_USER;';
EXEC(@tmp);

After Executing the Script

Analysis Script

 Collapse | Copy Code

SELECT 
    @Restore_DataFile=f.filename
FROM master..sysaltfiles f
    INNER JOIN master..sysdatabases d
    ON f.dbid = d.dbid
WHERE d.name = @Restore_DatabaseName
AND f.fileid=1

SELECT 
    @Restore_LogFile=f.filename
FROM master..sysaltfiles f
    INNER JOIN master..sysdatabases d
    ON f.dbid = d.dbid
WHERE d.name = @Restore_DatabaseName
AND f.fileid=2

print @Restore_DataFile
print @Restore_LogFile

This will retrive physical file name for restore database and output will be:

 Collapse | Copy Code

D:\SQLData\DatabaseForRestore.mdf
D:\SQLData\DatabaseForRestore_log.ldf

Analysis Script

 Collapse | Copy Code

-- Get the ID and path Name of the most recent full backup for the database
SELECT @physical_device_name=physical_device_name,
@backup_set_id_Full = b.backup_set_id
FROM msdb.dbo.backupset b
    JOIN msdb.dbo.backupmediafamily m 
    ON b.media_set_id = m.media_set_id
WHERE b.backup_set_id=
(
    SELECT MAX(backup_set_id)
    FROM  msdb.dbo.backupset  
    WHERE database_name = @Backup_DatabaseName 
        AND type = 'D' 
)

-- get mdf and ldf info from backup file name
SET @tmp = N'RESTORE FILELISTONLY
      FROM DISK=N''' + @physical_device_name + ''' WITH FILE=1' ;
INSERT INTO @FileList
      EXEC(@tmp);
--mdf 
select @Backup_DataFile=LogicalName
from @FileList
where [Type]='D';
--ldf
select @Backup_LogFile=LogicalName
from @FileList
where [Type]='L';

print @Backup_DataFile
print @Backup_LogFile

This will retrive last FULL backup physical file name and from that get mdf and ldf info from backup file name.

 Collapse | Copy Code

DatabaseForLogBackups
DatabaseForLogBackups_log

Analysis Script

 Collapse | Copy Code

RESTORE DATABASE @Restore_DatabaseName
FROM DISK = @physical_device_name 
WITH MOVE @Backup_DataFile TO @Restore_DataFile ,
MOVE @Backup_LogFile TO @Restore_LogFile,
REPLACE,
NORECOVERY, STATS = 10;

This will restore last FULL backup and make the database in NORECOVERY mode so that logfile restore will be possible.

Analysis Script

 Collapse | Copy Code

insert into @TranBackupList
SELECT  physical_device_name
FROM msdb.dbo.backupset b
    JOIN msdb.dbo.backupmediafamily m 
    ON b.media_set_id = m.media_set_id
WHERE b.backup_set_id>(@backup_set_id_Full)
AND database_name = @Backup_DatabaseName 
AND type = 'L'
ORDER BY b.backup_set_id

This will populate all transaction log backup list after full backup.

Analysis Script

 Collapse | Copy Code

DECLARE @Flag INT
DECLARE @RowCount INT
select @RowCount=COUNT(*) from @TranBackupList;

SET @Flag = 1
WHILE (@Flag <=@RowCount )
BEGIN
    select @BackupFile=PhysicalName
    from @TranBackupList
    where id=@Flag;
    PRINT @BackupFile
    RESTORE LOG @Restore_DatabaseName
    FROM DISK = @BackupFile
    WITH FILE = 1, NORECOVERY, STATS = 10 ;
    
    SET @Flag = @Flag + 1
END

-- Set Database in Normal mode
RESTORE DATABASE @Restore_DatabaseName
   WITH RECOVERY;

This will restore all transaction log backup and make the database for normal operation.