July 20, 2006 at 8:28 am
I have a backup from last night and about 50 transaction logs which are needed to applied to restore the database to a point in time before it crashed. is it possible to apply these transaction logs in one GO?
thanks
July 20, 2006 at 9:34 am
well yes and no - you can select them all through EM I believe, or just write a loop in T SQL. You have to apply each log in turn but can do it in one statement.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 20, 2006 at 9:47 am
If You have created BackupDevice then you Restore log command and specify backupDevice name or else copy paste t-sql for all log and change last one with recovery.
RESTORE LOG MyNwind
FROM MyNwind_log1
WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'
GO
RESTORE DATABASE MyNwind
FROM MyNwind_1, MyNwind_2
WITH NORECOVERY
RESTORE LOG MyNwind
FROM MyNwindLog1
WITH NORECOVERY
RESTORE LOG MyNwind
FROM MyNwindLog2
WITH RECOVERY, STOPAT = 'Apr 15, 1998 12:00 AM'
otherwise specify the disk location and use File option of restore
use file option
RESTORE LOG pubs
FROM Pubs1
WITH FILE = 1 with Norecovery
;;;
RESTORE LOG pubs
FROM Pubs1
WITH FILE = 4 with recovery
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
July 24, 2006 at 8:18 am
well if I am writing to a backup device, how will identify the transaction logs to be copied to a remote location?
July 24, 2006 at 8:25 am
I'm not sure I udnerstand what you're asking - the tlogs should have datetimes as part of their filename, failing that look at the file create date. If it's in a backup device then opening the device will show you the available logs.
You must have the preceeding full backup before you can apply logs. Copy your backup devices to your new location if you're building this server somewhere else.
I personally don't like backup devices and would suggest you create tran logs as files. Note that you need to backup your backups and tlogs to keep them safe.
BOL is pretty clear about restoring logs and databases.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 24, 2006 at 9:19 am
Well perhaps I wasnt clear enough. Lets see if the following clears up what I am doing or want to do.
When I do a full database backup to a e.g.. c:\ and not use backup device but actual the disk itself and perform transaction log backups to disk as well, SQL Server doesnt allow me to recover the database in one shot. Even though if I had written the backup and transaction log backup to the same file. It would not let me choose the full backup and the all transaction logs at the same time.
All I want to do is to recover my full backup and transaction logs in one or at most two steps.
How can I achieve that?
July 24, 2006 at 9:42 am
hmm bad news! I hope you appended the files?
I think .. as I don't do things this way, if you select the file from within EM you should see the backups and logs - then it's just a case of ticking which ones you want.
If you chose overwrite when you created your backups then you don't have a recoverable position.
If I get a chance I try backup up this way and let you know.
I'd suggest that you create seperate files for each backup.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 24, 2006 at 9:45 am
Shahab,
Check this very good article in Database Journal by MAK http://www.databasejournal.com/features/mssql/article.php/10894_3389831_1
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'
I agree with collins that BOL has clear explanation on Restoring logs and this is not very complex issue.
Hopefully SP from MAK is close call to what you are looking for.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
July 24, 2006 at 9:49 am
You may find this proc useful. http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1505
If you pass the dataset it will create the restore statements needed to restore the full backup and all transaction logs. You can take the ouput and just run it or modify to restore to a particular time. The script allos the statements to be generated for you which may be faster depending on you circumstances
Francis
July 24, 2006 at 9:49 am
no sweat - select the file ( open device ) in EM for restore and click the button which says view contents, then check which backups/logs you want to restore.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply