Restoring a SQL Database - Page 2

Step 3

Between two transaction log backups, try to create some objects in the database, insert some data and try to add new log files and data files to the database "Inventory." You can use the below script

ALTER DATABASE inventory
ADD LOG FILE 
( NAME = test1log2,
  FILENAME = 'd:\Program Files\
    Microsoft SQL Server\MSSQL\
	Data\invent_x.ldf',
  SIZE = 5MB,
  MAXSIZE = 10MB,
  FILEGROWTH = 5MB),
( NAME = test1log3,
  FILENAME = 'd:\Program Files\
    Microsoft SQL Server\MSSQL\
	Data\test3log.ldf',
  SIZE = 5MB,
  MAXSIZE = 10MB,
  FILEGROWTH = 5MB)
Go
use inventory
go
Create table x12322
  (id int, name varchar(200))
go
Insert into x12322 select 1,'a'

Step 4

Create the procedure "USP_RestoreAll" by copying and pasting the SQL code below into query analyzer. You should create this procedure on the SQL Server where you would like to restore the full backup and corresponding transaction log backups. If you want to restore the full backup and corresponding transaction log backups on a different server, then copy all the .BAK and .TRN files to that server or to a shared folder.

SQL Code

set quoted_identifier off
go
use master
go
Create procedure USP_Restoreall
@TargetDatabaseName varchar(128),
@BackupSourcePath varchar(600),
@BackupDatabaseName varchar(128),
@TargetDataPath varchar(600),
@TargetLogPath varchar(600),
@flag varchar(10)
as
--Objective: To restore the latest backup and all the tranlog backups available.
--Created by : MAK
--Created Date: Feb 28, 2004
--Usage:
--Exec USP_Restoreall 'MyInventory',
--  'D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Inventory\','Inventory','D:\','C:\','Exec'

set nocount on
set quoted_identifier off
--drop table #restoretemp
--drop table #restoretemp2
--drop table #temp
--drop table #directory
--drop table #directory2
--declare @TargetDatabaseName varchar(128)
--declare @BackupSourcePath varchar(600)
--declare @BackupDatabaseName varchar(128)
--declare @TargetDataPath varchar(600)
--declare @TargetLogPath varchar(600)
--declare @flag varchar(10)
declare @max1 int
declare @max2 int
declare @count1 int
declare @count2 int
declare @backupname varchar(600)
declare @tempvar varchar(600)
--set @TargetDatabaseName ='MyInventory'
--set @BackupSourcePath ='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Inventory\'
--set @BackupDatabaseName ='Inventory'
--set @TargetDataPath = 'D:\'
--set @TargetLogPath = 'C:\'
--set @flag ='print'
declare @query varchar(2000)
create table #temp (a varchar(2000))
create table #directory (id int identity(1,1),date Datetime, FileName varchar(200))
create table #directory2 (id int identity(1,1),date Datetime, FileName varchar(200))

--Query the directory in descending order date
set @query =  'dir "'+ @BackupSourcePath + @BackupDatabaseName + '*.*" /o-d/A-D'
--print @query
insert into #temp exec master.dbo.xp_cmdshell @query
delete from #temp where isdate(left(a,10))=0
insert into #directory (date,filename) 
  select convert(datetime,left(a,17)) as date,substring(a,40,len(a)) as Filename from #temp

--Find latest backup file in the list
delete from #directory where id > (select id from 
#directory where id = (Select top 1 id from 
#directory where right(Filename,4)='.BAK'))

--store the list in ascending order
insert into #directory2 (date,filename) 
select date,Filename from #directory order by id desc

--select * from #directory
--select * from #temp

select  @backupname = Filename from #directory2 where id=1
--Query the logical filenames from the backup
set @Query = 'restore filelistonly from disk = "'+ @BackupSourcePath +@backupname+'"'
--print @query
Create table #restoretemp (Logicalname varchar(500),Physicalname varchar(500), type varchar(10),
Filegroupname varchar(200),size int,maxsize bigint)
insert #restoretemp exec (@query)
--select * from #restoretemp
select identity(int,1,1) as id,
logicalname,physicalname,type,filegroupname,size,maxsize 
into #restoretemp2 from #restoretemp 


set @query = 'restore database ' + @TargetDataBaseNAme + ' from disk = "' +
@BackupSourcePath + @backupname + '" with norecovery, replace '

select  @max1 = max(id) from #restoretemp2 
set @count1=1
while @count1 <= @max1
begin
select @tempvar = case when type ='D' 
  then '  move "'+logicalname +'" 
  to "' +@TargetDatapath +@TargetDatabaseName +logicalname +right(physicalname,4) +'"'
else '    move "'+logicalname +'" 
  to "' +@Targetlogpath +@TargetDatabaseName +logicalname +right(physicalname,4)+'"' 
  end from #restoretemp2 where id = @count1
set @query = @query + @tempvar
set @count1 = @count1 + 1
end
set @query = replace(@query,'move',',move')

if @flag = 'print' 
begin
print 'set quoted_identifier off'
print 'GO'
print 'use master'
print 'GO'
print @query
end

if @flag = 'Exec' 
begin
print @query
Exec (@query)
end

truncate table #restoretemp2
truncate table #restoretemp

------------------------------------------------------
set @count2=2
select @max2 =max(id) from #directory2 
while @count2 <=@max2
begin
--Query the logical filenames from the Tranlog
select  @backupname = Filename from #directory2 where id=@count2
set @Query = 'restore filelistonly from disk = "'+ @BackupSourcePath +@backupname+'"'
--print @query
insert #restoretemp exec (@query)
--select * from #restoretemp
insert into #restoretemp2 ( logicalname,physicalname,type,filegroupname,size,maxsize )
select * from  #restoretemp 
if @max2 = @count2 
begin
set @query = 'restore log ' + @TargetDataBaseNAme + ' from disk = "' +
@BackupSourcePath + @backupname + '" with recovery, replace '
end
else
begin
set @query = 'restore log ' + @TargetDataBaseNAme + ' from disk = "' +
@BackupSourcePath + @backupname + '" with norecovery, replace '

end
select  @max1 = max(id) from #restoretemp2 
set @count1=1
while @count1 <= @max1
begin
select @tempvar = case when type ='D' then 
  '  move "'+logicalname +'" to 
  "' +@TargetDatapath +@TargetDatabaseName+logicalname +right(physicalname,4) +'"'
else '    move "'+logicalname +'" to 
  "' +@Targetlogpath +@TargetDatabaseName+logicalname +right(physicalname,4)+'"' 
  end from #restoretemp2 where id = @count1
set @query = @query + @tempvar
set @count1 = @count1 + 1
end
set @query = replace(@query,'move',',move')

if @flag = 'print' 
begin
print 'GO'
print @query
end

if @flag = 'Exec' 
begin
print @query
Exec (@query)
end

truncate table #restoretemp2
truncate table #restoretemp
set @count2 = @count2+1
end

if @flag = 'print' 
begin
print 'GO'

end

Step 5

We are going to use this procedure to restore the "Inventory" database as "MyInventory" database. Copy and paste the code below into the query analyzer.

Exec USP_Restoreall 'MyInventory',
  'D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Inventory\',
  'Inventory','D:\','C:\','Print'

Where:

1. 'Myinventory' is the new database name that we want to create

2. 'D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Inventory\' is the path where all the database .BAK and .TRN files are stored.

You can specify UNC path. Make sure SQL Server has sufficient access to the folders.

3. 'Inventory' is the database name

4. 'D:\' is the path where you want to create the data files of the 'MyInventory' database from the .BAK and .TRN

5. 'C:\' is the path where you want to create the log files of the 'MyInventory' database from the .BAK and .TRN

6. 'Print' is the parameter to be passed to the procedure to generate the SQL scripts for restoring the latest full backup and transaction log backups. Instead of 'Print' if you pass 'Exec' as the parameter, it generates the script and restores the .BAK and all the .TRN files.

When you execute the above SQL statement, a SQL script is generated as shown below. As you can see in the below script, it takes only the latest full backup and the corresponding transaction log backups.

set quoted_identifier off
GO
use master
GO
restore database MyInventory from disk = 
  "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Inventory\Inventory_db_200407290022.BAK" 
  with norecovery, 
  replace   ,
  move "Inventory" to "D:\MyInventoryInventory.mdf"    ,
  move "Inventory_log" to "C:\MyInventoryInventory_log.LDF"
GO
restore log MyInventory from disk = 
  "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Inventory\Inventory_tlog_200407290022.TRN" 
  with norecovery, 
  replace   ,
  move "Inventory" to "D:\MyInventoryInventory.mdf"    ,
  move "Inventory_log" to "C:\MyInventoryInventory_log.LDF"
GO
restore log MyInventory from disk = 
  "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Inventory\Inventory_tlog_200407290023.TRN" 
  with norecovery, 
  replace   ,move "Inventory" to "D:\MyInventoryInventory.mdf"    ,
  move "Inventory_log" to "C:\MyInventoryInventory_log.LDF"    ,
  move "test1log2" to "C:\MyInventorytest1log2.ldf"    ,
  move "test1log3" to "C:\MyInventorytest1log3.ldf"
GO
restore log MyInventory from disk = 
  "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Inventory\Inventory_tlog_200407290025.TRN" 
  with norecovery, 
  replace   ,move "Inventory" to "D:\MyInventoryInventory.mdf"    ,
  ove "Inventory_log" to "C:\MyInventoryInventory_log.LDF"    ,
  move "test1log2" to "C:\MyInventorytest1log2.ldf"    ,
  move "test1log3" to "C:\MyInventorytest1log3.ldf"
GO
restore log MyInventory from disk = 
  "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Inventory\Inventory_tlog_200407290030.TRN" 
  with norecovery, 
  replace   ,
  move "Inventory" to "D:\MyInventoryInventory.mdf"    ,
  move "Inventory_log" to "C:\MyInventoryInventory_log.LDF"    ,
  move "test1log2" to "C:\MyInventorytest1log2.ldf"    ,
  move "test1log3" to "C:\MyInventorytest1log3.ldf"
GO
restore log MyInventory from disk = 
  "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Inventory\Inventory_tlog_200407290035.TRN" 
  with norecovery, 
  replace   ,
  move "Inventory" to "D:\MyInventoryInventory.mdf"    ,
  move "Inventory_log" to "C:\MyInventoryInventory_log.LDF"    ,
  move "test1log2" to "C:\MyInventorytest1log2.ldf"    ,
  move "test1log3" to "C:\MyInventorytest1log3.ldf"
GO
restore log MyInventory from disk = 
  "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Inventory\Inventory_tlog_200407290040.TRN" 
  with recovery, 
  replace   ,
  move "Inventory" to "D:\MyInventoryInventory.mdf"    ,
  move "Inventory_log" to "C:\MyInventoryInventory_log.LDF"    ,
  move "test1log2" to "C:\MyInventorytest1log2.ldf"    ,
  move "test1log3" to "C:\MyInventorytest1log3.ldf"
GO

Conclusion

As mentioned earlier in this article, the main intent is to show how to restore a FULL backup of a database and all corresponding transaction log backups to the same server or to a different server, as the same database name or as a different database name.

Download Code here

» See All Articles by Columnist MAK