Hi,
It's been a long time since I posted something on this blog. Had been working a lot on the IBM DB2 databases for the last couple of years. So it was business as usual in SQL Server. Hence, nothing noteworthy in SQL Server to blog about. But here's one script that saved me several hours yesterday.
On one of my servers we had to recover 10 databases using last night's full backup and all subsequent log backups. So in the middle of the night it was going to be huge challenge to manually pick every log backup file to restore.
So wrote this script to create commands to RESTORE log backup files which can be then run in a query window.
Please follow the instructions below to use the script.
1) Set the database name to the variable @dbname
2) Copy all the transaction log backups to a new directory. You can choose to start copying the files
that were taken a few minutes before the full backup was taken. You can choose to copy the last
file you want to be restored based on the recovery point of time.
3) In the following line set the directory where you copied the log backup files to.
insert into #dir
exec xp_cmdshell 'dir "C:\Backup\Adventure*.trn" /b'
4) In the following line set the directory to where the log backup files have been copied.
SET @cmd='use master; RESTORE LOG ['+@dbname+'] FROM DISK =
N''C:\Backup\'+@filename+''' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10'
select (@cmd)
5) Copy the output which should have the RESTORE commands and run them in a new query
window.
6) Lastly run the following command to bring the database out of restoring state.
RESTORE DATABASE dbname WITH RECOVERY
############################################################################
use master
set nocount on
DECLARE @filename varchar(2000), @cmd varchar(8000), @dbname varchar(100)
-----------------------Enter the Database name in the next line
SET @dbname='New'
IF EXISTS (SELECT name FROM tempdb.sys.tables WHERE name like '#dir%')
begin
DROP table #dir
end
create table #dir (filename varchar(1000))
-----------------------Enter the path to TRN File in the xp_cmdshell line
insert into #dir
exec xp_cmdshell 'dir "C:\Backup\Adventure*.trn" /b'
delete from #dir where filename is null
DECLARE filecursor CURSOR FOR
select * from #dir order by filename asc
OPEN filecursor
FETCH NEXT FROM filecursor INTO @filename
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd='use master; RESTORE LOG ['+@dbname+'] FROM DISK = N''C:\Backup\'+@filename+''' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10'
print @cmd
FETCH NEXT FROM filecursor INTO @filename
END
CLOSE filecursor
DEALLOCATE filecursor
drop table #dir
############################################################################
It's been a long time since I posted something on this blog. Had been working a lot on the IBM DB2 databases for the last couple of years. So it was business as usual in SQL Server. Hence, nothing noteworthy in SQL Server to blog about. But here's one script that saved me several hours yesterday.
On one of my servers we had to recover 10 databases using last night's full backup and all subsequent log backups. So in the middle of the night it was going to be huge challenge to manually pick every log backup file to restore.
So wrote this script to create commands to RESTORE log backup files which can be then run in a query window.
Please follow the instructions below to use the script.
1) Set the database name to the variable @dbname
2) Copy all the transaction log backups to a new directory. You can choose to start copying the files
that were taken a few minutes before the full backup was taken. You can choose to copy the last
file you want to be restored based on the recovery point of time.
3) In the following line set the directory where you copied the log backup files to.
insert into #dir
exec xp_cmdshell 'dir "C:\Backup\Adventure*.trn" /b'
4) In the following line set the directory to where the log backup files have been copied.
SET @cmd='use master; RESTORE LOG ['+@dbname+'] FROM DISK =
N''C:\Backup\'+@filename+''' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10'
select (@cmd)
5) Copy the output which should have the RESTORE commands and run them in a new query
window.
6) Lastly run the following command to bring the database out of restoring state.
RESTORE DATABASE dbname WITH RECOVERY
############################################################################
use master
set nocount on
DECLARE @filename varchar(2000), @cmd varchar(8000), @dbname varchar(100)
-----------------------Enter the Database name in the next line
SET @dbname='New'
IF EXISTS (SELECT name FROM tempdb.sys.tables WHERE name like '#dir%')
begin
DROP table #dir
end
create table #dir (filename varchar(1000))
-----------------------Enter the path to TRN File in the xp_cmdshell line
insert into #dir
exec xp_cmdshell 'dir "C:\Backup\Adventure*.trn" /b'
delete from #dir where filename is null
DECLARE filecursor CURSOR FOR
select * from #dir order by filename asc
OPEN filecursor
FETCH NEXT FROM filecursor INTO @filename
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd='use master; RESTORE LOG ['+@dbname+'] FROM DISK = N''C:\Backup\'+@filename+''' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10'
print @cmd
FETCH NEXT FROM filecursor INTO @filename
END
CLOSE filecursor
DEALLOCATE filecursor
drop table #dir
############################################################################
