December 19, 2008 at 1:23 am
I have a 2005 database with a full backup that was taken at 12:00 and there have been 3 transaction logs taken since then. We had a problem between 12:00 and 1:00, and now I would like to recover the database. I would just like to recover from the full backup and do not want to apply the logs. What steps should I take and if anyone can provide some code, it would be very much appreciated. thx
December 19, 2008 at 1:31 am
If you just want to restore the database, make sure no users are connected to the database. If they are connected, force them to disconnect...
--step 1 disconnect users....
alter database yourDBName set single_user with rollback immediate
-- step 2 - I suggest you take a full backup of current status of your database before restoring with previous backup
backup database yourdbname to disk='your new backup path and filename'
-- step 3 restore the database
RESTORE DATABASE YourDBNAme FROM DISK='your backup file path'
WITH RECOVERY
If you know the time of failure, you can apply the transaction logs and restore till point in time....
December 19, 2008 at 9:11 am
Do I need to do a tail end backup of the transaction log before I do the restore?
December 19, 2008 at 9:18 am
m3n@ce (12/19/2008)
Do I need to do a tail end backup of the transaction log before I do the restore?
Since you're not planning to restore the logs (why?) no need. Just do the restore with the replace option, otherwise SQL will nag about the log tail.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 19, 2008 at 9:23 am
1. I assumed you database has not crashed. You just needed to restore the full database, the backup of which was already available to you. Methods i wrote was sufficient.
2. Again with the same assumption that your database is fine, i dont find a reason to restore the full backup and apply transaction logs. You cannot apply tail log backup unless you've applied all intermediate transaction log backups since last full backup.
3. Since I took full log backup (for safety) at step 2, just before restoring the database, you dont need to take a tail log backup. Again the assumption that your database is online and is healthy.
4. Assuming your database has crashed and you are able to just connect to the database, i suggest you take tail log backup with no_recovery option (if unsuccessful try taking backup with no_truncate option). This way you'll ensure you have minimal loss of data. In such a scenario, if you wish to restore entire database, you'll need to restore the full backup, then all the transaction log backups (in the same sequence in which they were created) and finally the tail log backup. Remember to use with norecovery clause with every restore. At the end of restoration, you can alter the database to recovery mode, indicating there are no more log backups in queue and this will make the db ready to use.
Refer to BOL and msdn for backup and restore
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply