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
» 