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