September 11, 2008 at 7:48 pm
Hello every one.
we are in the process of restoring a database with 1 full backup and about 38 trans log backups. the problem is we have about 180
databases so the process can be very tedious. My manager want's me to come up with either a t-script or batch script so it can just
read the names of the trn files from a folder and plug it in the restore script , he doesnt want me to do it one by one as for 180 databases will take
alot of time. iIknow how to back up and restore but have no idea how to go about make it read it from a file.
i hope every 1 out there will understand by problem . the trn files are sitting in a folder , from there they will be restored.
Thanx
bobby
September 12, 2008 at 1:00 am
the script should go something like this:
-- declare variables
declare @dir varchar(1000), @rowid int, @sqlcmd varchar(1000), @filename varchar(1000)
declare @trn table (rowid int identity(1,1),file_name varchar(1000))
-- set variables
set @dir = 'dir c:\i386\ /TW /OD /B' -- look up /TW /OD and /B yourself
insert into @trn exec xp_cmdshell @dir -- this statement will dump all your .trn files into a memory table
-- do stuff
select @rowid = min(rowid) from @trn
while @rowid is not null
begin
select @filename = file_name from @trn where rowid = @rowid
set @sqlcmd = 'restore database dbname from disk = ''path/to/trn/files/' + @filename + ''''
raiserror(@sqlcmd,0,1) with nowait
select @rowid = min(rowid) from @trn where rowID > 0 and rowid > @rowid
end
that should spit you back what you want, if not, that's like 95% of what you need. Finish up the other 5% and you're golden.
**edit**
I just ran the above script and it does what you want. Just feed it the correct path and you're set
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
September 12, 2008 at 7:28 am
You also need to know when you are at the last file as all restores prior to that will need WITH NORECOVERY and the last RESTORE will be WITH RECOVERY.
If you really want to speed up and simplify the RESTORE process you should be taking some DIFFERENTIAL backups in there somewhere. For example, if you are running nightly Full backups and Log backups every 1/2 hour you could/should do a differential every 4 hours then the max number of restores would be 1 Full, 1 Differential, 7 Logs.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 12, 2008 at 8:58 am
Thanks alot ,
i am looking at the script but nut much is making sense but i will look in to it and try to understand. if any 1 else can help explaining this script a bit i will really be tankfull. sorry gusy kind a new in scripting
Thanks
B
September 12, 2008 at 1:01 pm
what's there not to understand about the script I provided? it creates a memory table and stores all of your .trn file names in it.
Then the while loop loops thru that memory table spitting out the restore statement. As Jack had said, you just need to put in the NO RECOVERY and RECOVERY statement.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
September 14, 2008 at 1:49 pm
Thanx,
ur a great help .
Bobby
September 15, 2008 at 9:47 am
This from a personal email and posted here so others can benefit.
hello jack , thanx for your advise, can u please tell me , in the script , i know in the last trn file i need to put restore with revovery , but how will i know i am at the last file in the script, if u can explain this a bit i really appreciate it.
Thanx
bobby
Assuming the code provided by Oberion retrieves the files from oldest to newest (which it has to in order to work) you just need to get the max row_id and compare row_id to max row_id and adjust your SQL Script appropriately.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 15, 2008 at 10:56 am
I have this in my toolkit, IIRC, it was written by Gregory Larsen. It reads the backup history from the msdb, and sorts out the NoRecovery, recovery for you.
Of course, if you have lost your server, you have lost your ability to restore ...
HTH
Dave J
-- Declare variables used in SP
DECLARE @dbName NVARCHAR (1000)
DECLARE @cmd NVARCHAR (1000)
DECLARE @cmd1 NVARCHAR (1000)
DECLARE @db NVARCHAR(128)
DECLARE @filename NVARCHAR(128)
DECLARE @cnt INT
DECLARE @num_processed INT
DECLARE @name NVARCHAR(128)
DECLARE @physical_device_name NVARCHAR(128)
DECLARE @backup_start_date DATETIME
DECLARE @type CHAR(1)
-- Turn off the row number message
SET NOCOUNT ON
--optionally set this to the name of the database you want to restore
SET @dbName = 'myDatabaseName'
-- SECTION 1 ----------------------------------------------
-- Define cursor to hold all the different databases for the restore script will be built
DECLARE db CURSOR FOR
SELECT name FROM master..sysdatabases
--use the next line for all dbs
WHERE NAME not in ('tempdb')
-- or use this line for a specific one
-- WHERE name = @dbName
-- Create a global temporary table that will hold the name of the backup, the database name, and the type of database backup.
CREATE TABLE ##backupnames (
name NVARCHAR(100),
database_name NVARCHAR(100),
type CHAR(1) )
-- Open cursor containing list of database names.
OPEN db
FETCH NEXT FROM db INTO @db
-- Process until no more databases are left
WHILE @@FETCH_STATUS = 0
BEGIN
-- Subsection 1A --------------------------------------------
-- initialize the physical device name
SET @physical_device_name = ''
-- get the name of the last full database backup
SELECT @physical_device_name = physical_device_name , @backup_start_date = backup_start_date
FROM msdb..backupset a join msdb..backupmediaset b ON a.media_set_id = b.media_set_id
join msdb..backupmediafamily c ON a.media_set_id = c.media_set_id
WHERE type='d' and backup_start_date =
(SELECT TOP 1 backup_start_date FROM msdb..backupset
WHERE @db = database_name and type = 'd'
ORDER BY backup_start_date DESC)
-- Did a full database backup name get found
IF @physical_device_name <> ''
BEGIN
-- Build command to place a record in table that holds backup names
SELECT @cmd = 'insert into ##backupnames values (' + CHAR(39) +
@physical_device_name + CHAR(39) + ',' + CHAR(39) + @db + CHAR(39) + ',' +
CHAR(39) + 'd' + CHAR(39)+ ')'
-- Execute command to place a record in table that holds backup names
EXEC SP_EXECUTESQL @cmd
END
-- Subsection 1B --------------------------------------------
-- Reset the physical device name
SET @physical_device_name = ''
-- Find the last differential database backup
SELECT @physical_device_name = physical_device_name, @backup_start_date = backup_start_date
FROM msdb..backupset a join msdb..backupmediaset b ON a.media_set_id = b.media_set_id
join msdb..backupmediafamily c ON a.media_set_id = c.media_set_id
WHERE type='i' and backup_start_date =
(SELECT TOP 1 backup_start_date FROM msdb..backupset
WHERE @db = database_name and type = 'I' and backup_start_date > @backup_start_date
ORDER BY backup_start_date DESC)
-- Did a differential backup name get found
IF @physical_device_name <> ''
BEGIN
-- Build command to place a record in table that holds backup names
SELECT @cmd = 'insert into ##backupnames values (' + CHAR(39) +
@physical_device_name + CHAR(39) + ',' + CHAR(39) + @db + CHAR(39) + ',' +
CHAR(39) + 'i' + CHAR(39)+ ')'
-- Execute command to place a record in table that holds backup names
EXEC SP_EXECUTESQL @cmd
END
-- Subsection 1C --------------------------------------------
-- Build command to place records in table to hold backup names for all
-- transaction log backups from the last database backup
SET @CMD = 'insert into ##backupnames select physical_device_name,' + CHAR(39) + @db + CHAR(39) +
',' + CHAR(39) + 'l' + CHAR(39) +
'from msdb..backupset a join msdb..backupmediaset b on a.media_set_id = b.media_set_id join ' +
'msdb..backupmediafamily c on a.media_set_id = c.media_set_id ' +
'where type=' + CHAR(39) + 'l' + CHAR(39) + 'and backup_start_date > @backup_start_dat and' +
CHAR(39) + @db + CHAR(39) + ' = database_name'
-- Execute command to place records in table to hold backup names
-- for all transaction log backups from the last database backup
EXEC SP_EXECUTESQL @cmd,@params=N'@backup_start_dat datetime', @backup_start_dat = @backup_start_date
-- get next database to process
FETCH NEXT FROM db INTO @db
END
-- close
CLOSE db
-- Section B ----------------------------------------------
OPEN db
-- Get first recod from database list cursor
FETCH NEXT FROM db INTO @db
-- Generate Heading in Restore script
PRINT '-- Restore All databases'
-- Process all databases
WHILE @@FETCH_STATUS = 0
BEGIN
-- define cursor for all database and log backups for specific database being processed
DECLARE backup_name CURSOR FOR
SELECT name,type FROM ##backupnames WHERE database_name = @DB
-- Open cursor containing list of database backups for specific database being processed
OPEN backup_name
-- Determine the number of different backups available for specific database being processed
SELECT @CNT = COUNT(*) FROM ##backupnames WHERE database_name = @DB
-- Get first database backup for specific database being processed
FETCH NEXT FROM backup_name INTO @physical_device_name, @type
-- Set counter to track the number of backups processed
SET @NUM_PROCESSED = 0
-- Process until no more database backups exist for specific database being processed
WHILE @@FETCH_STATUS = 0
BEGIN
-- Increment the counter to track the number of backups processed
SET @NUM_PROCESSED = @NUM_PROCESSED + 1
-- Is the number of database backup processed the same as the number of different backups
-- available for specific database being processed?
IF @CNT = @NUM_PROCESSED
-- If so, is the type of backup currently being processed a transaction log backup?
IF @TYPE = 'l'
-- build restore command to restore the last transaction log
SELECT @cmd = 'restore log ' + RTRIM(@db) + CHAR(13) +
' from disk = ' + CHAR(39) +
RTRIM(SUBSTRING(@physical_device_name,1,LEN(@physical_device_name))) +
CHAR(39) + CHAR(13) + ' with replace, stats = 3'
ELSE
-- Last backup was not a transaction log backup
-- Build restore command to restore the last database backup
SELECT @cmd = 'restore database ' + RTRIM(@db) + CHAR(13) +
' from disk = ' + CHAR(39) +
RTRIM(SUBSTRING(@physical_device_name,1,LEN(@physical_device_name))) +
CHAR(39) + CHAR(13) + ' with replace, stats = 3'
ELSE
-- Current backup is not the last backup
-- Is the current backup being processed a transaction log backup?
IF @TYPE = 'l'
-- Build restore command to restore the current transaction backup, with no recovery
SELECT @cmd = 'restore log ' + RTRIM(@db) + CHAR(13) +
' from disk = ' + CHAR(39) +
RTRIM(SUBSTRING(@physical_device_name,1,LEN(@physical_device_name))) +
CHAR(39) + CHAR(13) + ' with replace, norecovery, stats = 3'
ELSE
-- Current backup being processed is not a transaction log backup
-- Build restore command to restore the currrent database backup, with no recovery
SELECT @cmd = 'restore database ' + RTRIM(@db) + CHAR(13) +
' from disk = ' + CHAR(39) +
RTRIM(SUBSTRING(@physical_device_name,1,LEN(@physical_device_name))) +
CHAR(39) + CHAR(13) + ' with replace, norecovery, stats = 3'
-- if it is master comment line out
IF @db = 'master'
SET @cmd = '/* ' + CHAR(13) + @cmd + CHAR(13) + '*/'
-- Generate the restore command and other commands for restore script
PRINT @cmd
PRINT 'go'
PRINT ' '
-- Get next database backup to process
FETCH NEXT FROM backup_name INTO @physical_device_name, @type
END
-- Close and deallocate database backup name cursor for current database being processed
CLOSE backup_name
DEALLOCATE backup_name
-- Get next database to process
FETCH NEXT FROM db INTO @db
END
-- Close and deallocate cursor containing list of databases to process
CLOSE db
DEALLOCATE db
-- Drop global temporary table
DROP TABLE ##backupnames
September 15, 2008 at 11:23 am
Couldn't you use that same script after each backup is taken and save the required restore script in a different location (central DB, network drive...).
September 15, 2008 at 11:30 am
Yes, The easiest way would by to use oSQL (or sqlcmd, but I've never used that), and save the output file somewhere other than the DB server. Again, all of the backup files need to be elsewhere too... 😉
Dave J
September 15, 2008 at 11:34 am
That's still ovbious to split all those files.
What's the point of putting the backups on the same drive of the DB. When that drive is gone, everything is gone... and that script will do you little good!
September 15, 2008 at 11:50 am
I agree completely. I said "Again, all of the backup files need to be elsewhere too.", meaning NOT on the DB server. 😛
Here I backup to file on the server, (separate drive), which are copied to a fileserver, and the logs are log shipped to two other servers every 30 mins, one here and one 300 miles away. We then backup the file server to tape, and the tapes are stored off-site. Never can have enough backups! 😀
Dave J
September 15, 2008 at 11:56 am
The only thing your DR plan doesn't seem to cover is an asteroid destroying earth... you need a fileserver on the moon, just to be safe :D.
September 22, 2008 at 8:08 am
Here's another personal email about this issue that I will post so everyone can benefit or contribute to the answer:
"-- declare variables
declare @dir varchar(1000), @rowid int, @sqlcmd varchar(1000), @filename varchar(1000)
declare @trn table (rowid int identity(1,1),file_name varchar(1000))
-- set variables
set @dir = 'dir c:\i386\ /TW /OD /B' -- look up /TW /OD and /B yourself
insert into @trn exec xp_cmdshell @dir -- this statement will dump all your .trn files into a memory table
-- do stuff
select @rowid = min(rowid) from @trn
while @rowid is not null
begin
select @filename = file_name from @trn where rowid = @rowid
set @sqlcmd = 'restore database dbname from disk = ''path/to/trn/files/' + @filename + ''''
raiserror(@sqlcmd,0,1) with nowait
select @rowid = min(rowid) from @trn where rowID > 0 and rowid > @rowid
end"
*********************************************************
"Assuming the code provided by Oberion retrieves the files from oldest to newest (which it has to in order to work) you just need to get the max row_id and compare row_id to max row_id and adjust your SQL Script appropriately. "
*********************************************************
hello Jack Corbertt,
sorry to get it touch with ya again, but i really need help in this, i cant figure out how to add the restore with recovery for the last trn file in the code. i understand u r busy but i am totally new at this and if u can just tell me where do add the code in the scrpit ( step by step guide like a new bi) i will really appreciate that , my boss is up my *** about it and i will forever be greatfull to ya on that .
Thanx
Bobby.
Bobby,
If you post the question on the thread it will save me time and may get you a faster answer since everyone who has posted on the thread will get an email (unless they turn that off explicitly).
Try putting this outside the loop:
Declare @max_row_id Int
Select @max_row_id = Max(rowid) From @trn
Then in the loop:
If @rowid = @max_row_id
Begin
set @sqlcmd = @sqlcmd + ' With Recovery'
End
Else
Begin
set @sqlcmd = @sqlcmd + ' With No Recovery'
End
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 22, 2008 at 8:39 am
Thank you for your advise which i will definetly follow, and I am going to try the code right now , hopefully this will work .
Thanks alotttt.
Bobby
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply