Click here to Skip to main content
15,867,488 members
Articles / Database Development / SQL Server
Article

Dynamically restore full and all transaction log backup

Rate me:
Please Sign up or sign in to vote.
5.00/5 (2 votes)
20 Dec 2012BSD1 min read 26.4K   282   9  
Dynamically restore full and all transaction log backup.

Introduction

This tutorial will show, how you can restore las full backup and all transaction log backup dynamically.

Background

When I was trying to restore one of my database, I needed to restore a full backup and several log backup. I was needed to find my database physical location its logical name manually and all transaction log file list. So I needed a process which will do the same thing.

What you Need

This script was tested in SQL Server 2008.

Create Initial Step

SQL
----------------------------------------------------------------------------
-- 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

SQL
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

SQL
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

SQL
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

SQL
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

SQL
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

Image 1

Create New Database for Restore

SQL
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

SQL
----------------------------------------------------------------------------
-- 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

Image 2

Analysis Script

SQL
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:

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

Analysis Script

SQL
-- 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.

DatabaseForLogBackups
DatabaseForLogBackups_log

Analysis Script

SQL
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

SQL
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

SQL
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.

Conclusion

None so far.

References

History

None so far.

License

This article, along with any associated source code and files, is licensed under The BSD License


Written By
Software Developer (Senior) icddr,b
Bangladesh Bangladesh
More than 8 years experience on Programming and Project implementation, I was primarily involved with projects for private organization,Govt.(Bangladesh Army,DG Health,RJSC), NGO (SEDF,WFP). Presently I am working at ICDDR,B and enhancing Hospital Management System developed by Microsoft Dynamic NAV and Windows Mobile Application 5.0

An active supporter of Open Source technology, my interested areas are ERP, IT Audit, Data warehouse, BI etc.

Playing Guitar for 15 years, my interested music style is Blues Rock,Neo Classical.

Certification

70-540:Microsoft® Windows Mobile® 5.0 - Application Development
MB7-514:Microsoft Dynamics™ NAV 5.0 C/SIDE Introduction
MB7-516:Microsoft Dynamics™ NAV 5.0 Solution Development
MB7-517:Microsoft Dynamics™ NAV 5.0 Installation and Configuration
MB7-515:Microsoft Dynamics™ NAV 5.0 Financials
70-432:Microsoft SQL Server 2008 - Implementation and Maintenance
70-450:PRO: Designing, Optimizing and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008
70-448:Microsoft SQL Server 2008, Business Intelligence Development and Maintenance
312-50:Certified Ethical Hacker

Web :http://masudparvezshabuz.appspot.com
Blog :http://masudparvezshabuz.wordpress.com
linkedin :http://www.linkedin.com/in/masudparvez

Comments and Discussions

 
-- There are no messages in this forum --