October 24, 2009 at 4:55 pm
Hi all
I'm testing the sql server 2008 backup/restore functionality (in case I ever need to to do one). My first backup created a .bak file of 56Mb and a .trn file of 178Kb(thats kilobytes). I thought this was reasonable as my database was created from an Access .mdb of about 50MB. I then added approx 1 years worth of data (test data) and did another backup. The .bak file was 59MB but the .trn file was 188MB ( a massive increase over the first one). I then proceeded to delete the contents of one table (about 35000 records). This was to simulate loss of data.
Then using the SS management studio restore wizard I restored from the 2nd and most recent backup. After entering the restore option to overwrite the existing database, clicked OK and got a dialog almost immediately saying "Restore Successful". However, some 12 hours later my database still says it is "Restoring..." and hence cannot be accessed.
My question is how long should a restore take?
October 25, 2009 at 3:18 am
UtterSQLNewb (10/24/2009)
The .bak file was 59MB but the .trn file was 188MB ( a massive increase over the first one).
Because you didn't take log backups.
Please read through this - Managing Transaction Logs[/url]
After entering the restore option to overwrite the existing database, clicked OK and got a dialog almost immediately saying "Restore Successful". However, some 12 hours later my database still says it is "Restoring..." and hence cannot be accessed.
My question is how long should a restore take?
The restore was complete when management studio said "Restore successful". It sounds like you restored the database WITH NORECOVERY (in the gui, "leave the database inaccessible so further backups can be restored"). When restored with that option, a DB is left in the Restoring state so that differential backups or log backups can be restored.
If you don't want to restore any further backups, run this
RESTORE DATABASE <DB Name> WITH RECOVERY
and it'll come online.
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
October 25, 2009 at 3:10 pm
Gail, many thanks for your reply. The command worked and my database is accessible again. I guess I have a lot to learn but its good to know there is such a wonderful resource to tap in to. cheers, alan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply