How to restore the backup with existing DB name?


This article will explain the step by step procedure to restore the backup file with existing DB Name and given sample TSQL scripts for every steps. Normally this steps will be used when we need a SQL job to restore DB in scheduled manner.

Step 1: setting permission with “SINGLE_USER WITH ROLLBACK” since no one should access the existing DB while restoring except who is doing restore.


USE MASTER
GO
ALTER DATABASE Coaching_DWETL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Step 2: Creating temp table to store backup information since we require logical name for data & log files.

IF OBJECT_ID('tempdb..#backupInformation') IS NOT NULL
BEGIN
drop table #backupInformation
END

create table #backupInformation
(
LogicalName varchar(100),
PhysicalName varchar(100),
Type varchar(1),
FileGroupName nvarchar(50) ,
Size varchar(100) ,
MaxSize nvarchar(100),
FileId bigint,
CreateLSN bigint,
DropLSN bigint,
UniqueId uniqueidentifier,
ReadOnlyLSN bigint,
ReadWriteLSN bigint,
BackupSizeInBytes bigint,
SourceBlockSize bigint,
FileGroupId bigint,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN nvarchar(100),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit,
TSEThumbprint varchar(100)
)


Step 3: Inserting Data into temp table

insert into #backupInformation exec('restore filelistonly from disk = ''C:\Data warehouse\backUp\Coaching\Coaching_backup_201006162300.bak''')

Step 4: Get the logical name of data and log file.

declare @LogicalDBData nvarchar(100),
@LogicalDBLog nvarchar(100)
set @LogicalDBData = (select LogicalName from #backupInformation where Type='D')
set @LogicalDBLog = (select LogicalName from #backupInformation where Type='L')


Step 5: Restore the DB and move the data and log files.

RESTORE DATABASE Coaching_DWETL
FROM DISK = 'C:\Data warehouse\backUp\Coaching\Coaching_backup_201006162300.bak'
WITH
REPLACE,
MOVE @LogicalDBData TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.NDWSTG01DB\MSSQL\DATA\Coaching_DWETL18June.MDF',
MOVE @LogicalDBLog TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.NDWSTG01DB\MSSQL\DATA\Coaching_DWETL18june_1.LDF'


Step 6: Once the restore is success, revert back the permission to MULTI_USER

ALTER DATABASE Coaching_DWETL SET MULTI_USER WITH NO_WAIT


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: