March 20, 2013 at 6:31 am
as far as i know this restore is done. ending up with the files you want is all doable via a restore of a bak file, starting to take risks and outages with detaching prod databases and then risking an inconsistent test database is not necessary.
---------------------------------------------------------------------
March 20, 2013 at 6:59 am
Actually, it can be faster and more reliable than a backup and whats more it will always be consistent. The reason for that is in what happens when the database starts up and performs a recovery. All uncommited transactions are rolled back and makes the file consistent together with all the settings and transaction logs of the original.
As you quite rightly mentioned though, if downtime (not outage) is not an option then it isn't a viable option anyway.....
March 20, 2013 at 7:52 am
kevaburg (3/20/2013)
Actually, it can be faster and more reliable than a backup and whats more it will always be consistent. The reason for that is in what happens when the database starts up and performs a recovery. All uncommited transactions are rolled back and makes the file consistent together with all the settings and transaction logs of the original.
sorry, it won't be able to do that without the log file. you will only get away with it with a clean shutdown, so this is a risky way to do this and is unnecessary because a straight restore will take care of it.
---------------------------------------------------------------------
March 20, 2013 at 8:00 am
Of course it will! It doesn't even need a clean shutdown because every time a SQL Server database starts, it performs a recovery, whether we want it to or not!
With the parameter FOR ATTACH_REBUILD_LOG the logs will be rebuilt as the MDF expects them to be! It is clean, very safe, extremely consistent and a great deal faster because you aren't restoring the data, simply attaching the database to the host instance!
Honestly, on the databases I create for testing, it has proven to me that there are options far beyond simple restores that can be multiple times faster and this is one of them!
March 20, 2013 at 8:06 am
to everyone out there, attach with rebuild log is a last resort, please don't replace restores with it.
---------------------------------------------------------------------
March 20, 2013 at 8:48 am
george sibbald (3/20/2013)
to everyone out there, attach with rebuild log is a last resort, please don't replace restores with it.
Apart that only a restore can replace a damaged mdf file, is there any other reason you wouldn't use it?
March 20, 2013 at 9:03 am
in this situation its totally unnecessary, a restore will overwrite the database as you want it.
why start detaching production databases and then not using the log file when you don't have to? No proper database recovery phase can take place without the original log file. Rebuild log is a last resort command if you don't have a log file or a suitable backup, it should be followed by a dbcc checkdb to check no corruptions. This is a method of restoring a database from production to development that is asking for trouble and I would not recommend.
---------------------------------------------------------------------
March 20, 2013 at 9:16 am
I see your point. Thanks for the comment!
March 21, 2013 at 3:46 am
Hi folks,
I just want to point out that although I have been ranting on about the benefits of attach/detach for a database test machine, I have to concede I have missed to many valid points out to be able to validate my arguments anymore. In particular:
When I use FOR ATTACH_REBUILD_LOG I obviously no longer have a logfile. To that end there is no ability to rollback inconsistent or uncommitted transactions on startup, hence an inconsistent state. I was warned of that several times here but didn't think deeply enough.
To that end, there is truly no replacement for a good backup and recovery strategy and my own environment now reflects that.
Many thanks to all for your constructive comments (even if sometimes they were the result of an enormous amount of patience!).
Kev
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply