Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

How to refurbish all transactions log reserve in SQL Server

In this article, we will be studying to restore the last full backup and all transactions log backup in SQL server.

We will be doing this coding in SQL server 2008 and the complete process in done in several steps. The first step is to create the initial step.

Listing 1: Shows the coding for initial step

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

After creating the initial step, the next step is to create a full backup.

Listing 2: Shows the coding for creating full backup

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

Now we will be adding some more data to our existing data.

Listing 3: Shows the coding for adding more data

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

After adding the data, transaction log is taken.

Listing 4: Shows the coding for taken transaction logs

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

Now some more data is added to the previous data which is as follows.

Listing 5: Shows the coding for the more added data

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

Listing 6: Shows the coding for the transaction log after adding the data

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
Shows the snapshot of screen after taking the backup

Figure 1: Shows the snapshot of screen after taking the backup

After adding data and taking the transactions, a new database is created for the process of restoration.

Listing 7: Shows the coding for creating a new database for restore

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

After all this, the final main script will be as follows.

Listing 8: Shows the coding for the main script

----------------------------------------------------------------------------
-- 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);
Shows the snapshot of screen after executing the script

Figure 2: Shows the snapshot of screen after executing the script

Listing 8: Shows the coding for retrieving physical file name for restore database

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

Listing 9: Shows the output of listing 8

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

Listing 10: Shows the coding for retrieving last FULL backup physical file name and from that get mdf and ldf info from backup file name

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

Listing 11: Shows the coding for restoring last FULL back up and make the database in NORECOVERY mode

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;

Listing 12: Shows the coding for populating all transaction log backup list after full backup

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

Listing 13: Shows the code which will finally restore all transaction log backup and make the database for normal operation

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;

Conclusion

In this article, we have learnt about dynamically restoring full and all transactions log backup for SQL server 2008 in detail.



I am well versed with Computer Programming languages and possess good working knowledge on software languages such as C, Java, PHP, HTML and CSS

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login