InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 17:55:07 GMT20http://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspxHi 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!).KevThu, 21 Mar 2013 03:46:04 GMTkevaburghttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspxI see your point. Thanks for the comment!Wed, 20 Mar 2013 09:16:48 GMTkevaburghttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspxin 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.Wed, 20 Mar 2013 09:03:42 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspx[quote][b]george sibbald (3/20/2013)[/b][hr]to everyone out there, attach with rebuild log is a last resort, please don't replace restores with it.[/quote]Apart that only a restore can replace a damaged mdf file, is there any other reason you wouldn't use it?Wed, 20 Mar 2013 08:48:06 GMTkevaburghttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspxto everyone out there, attach with rebuild log is a last resort, please don't replace restores with it.Wed, 20 Mar 2013 08:06:23 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspxOf 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!Wed, 20 Mar 2013 08:00:13 GMTkevaburghttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspx[quote][b]kevaburg (3/20/2013)[/b][hr]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.[/quote]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.Wed, 20 Mar 2013 07:52:15 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspxActually, 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.....Wed, 20 Mar 2013 06:59:13 GMTkevaburghttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspxas 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.Wed, 20 Mar 2013 06:31:26 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspxHow big is the mdf file? Can you tolerate a little downtime?I ask because I find the simplest way is to copy the mdf to the DEV environment and run the following:create database <db_name>on (filename='<path to mdb>')on attach_rebuild_log;That will create the database as per the orginalWed, 20 Mar 2013 05:03:57 GMTkevaburghttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspxInstead of creating fresh DB if we restore directly it is better and after restoring all files suppose it it not in appropriate drives modify it or attach the database the database files in the dev server.Wed, 20 Mar 2013 01:40:46 GMTnaga.rohitkumarhttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspxI restored the db using interface.Not using the commandThu, 14 Mar 2013 11:46:25 GMTTripzhttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspx[quote][b]Tripz (3/14/2013)[/b][hr]To avoid all the confusions and complications I created a fresh db on dev and restored the backup to this freshly created db.and now things seems to be clean and now the dev website is pointing to new dev db and seems to be fine now.But - I am still not sure how to delete the extra ldf files in the backup file restore to db which has only one mdf and one ldf[/quote]what did you do with the original dev db? The restore would remove the extra .ldf file for you.Thu, 14 Mar 2013 11:25:44 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspxso if the restore worked thats not the problem. What status does the database show as in SSMS?What does 'select state_desc from master.sys.databases' return for your databaseIf the name of the database is the same, and the logical file names are the same for the two files prod has, a straight restore database with no extra clauses should work.Thu, 14 Mar 2013 11:20:09 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspxTo avoid all the confusions and complications I created a fresh db on dev and restored the backup to this freshly created db.and now things seems to be clean and now the dev website is pointing to new dev db and seems to be fine now.But - I am still not sure how to delete the extra ldf files in the backup file restore to db which has only one mdf and one ldfThu, 14 Mar 2013 11:19:09 GMTTripzhttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspxI tried restoring directly by right clicking on the database and selecting the .bak file and choosing the mdf and ldf path's I was able to choose the mdf and only one ldf to restore It restored but the site did not comeup and failing to loadThu, 14 Mar 2013 11:13:52 GMTTripzhttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspxhave you tried to restore? please share the restore command you used and what was the error?Thu, 14 Mar 2013 10:43:01 GMTgeorge sibbaldhttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspxIt's possible a second log file was added because the drive that the original was on ran out of space. My suggestion to minimise your heartache over this would be to detach the old dev database, delete the relevant .mdf and .ldf files, and restore the backup, putting the .mdf and .ldf files where you want to. Now you have a clean restore of the production backup.Thu, 14 Mar 2013 09:53:32 GMTtim.nyland-joneshttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspxHi,Both seems to be logs files but one of them on dev is named as tlog_archive, I am not sure what it is doing.I tried restoring the db from production to dev and attached to the log file named as log.ldf but the dev website did not load what should be my next steps in the scenarioThu, 14 Mar 2013 09:28:00 GMTTripzhttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspxIf your dev environment genuinely does have multiple log files, this is nothing to worry about from a backup point of view. The restore will only let you create a single log file, but you can create the other again afterwards if you need it.Thu, 14 Mar 2013 09:17:48 GMTtim.nyland-joneshttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspxAh, sorry. Still, same question - are they both definitely log files or is one an additional data file that just happens to have the extension .ldf? And are they both definitely attached to the database?Thu, 14 Mar 2013 09:05:53 GMTtim.nyland-joneshttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspxMy production environment is using only ONE ldf and Dev has TWO ldf's and I need to restore from Production to Dev dbThu, 14 Mar 2013 09:01:35 GMTTripzhttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspxAre you sure the production environment is using two ldf files? If so, are they both definitely log files, or is one another data file?Thu, 14 Mar 2013 08:50:54 GMTtim.nyland-joneshttp://www.sqlservercentral.com/Forums/Topic1431025-357-1.aspxWe have Sql Server 2005 database server in both production and development environment.I have the requirement to restore a database from production to Dev.I my production database has one mdf and one ldf files But my dev environment has 1mdf and two ldf files.I am not sure how to restore in this one ldf backup to two ldf instance.How can achieve this ?? Any suggestions/ideas/approach??Thu, 14 Mar 2013 08:48:01 GMTTripz