What's the difference between Backup/Restore and detach_db/attach_db?

[jsteph]

Hi all,
We've been running this process to update our QA and Sandbox databases with Prod info every week or so. The guy who's doing it is doing a backup of prod, then doing a restore to QA and Sandbox.

Wouldn't it be quicker to detach production (we have a blackout window) and physically move the files to the data-file folders for the QA and sandbox and do a sp_attach_db to those dbs?

What issues might their be?

Thanks,
--Jim

 

[RiverGuy]

Don't you already have full backups that you are performing? You could simply copy a backup file you already have to the development server. One reason that I like restoring to a development server is that I don't have to touch production. Detaching and Attaching production leaves room for error - such as using the incorrect database owner during attach.

 

[jsteph]

Riverguy,
Thanks for responding. Yes, I do have backups but I'm looking at a time standpoint--I have to copy the backups to the dev server, then perform a restore. With detach/attach, I copy the .mdf, et. al. files to dev, then simply do an attach. So it saves that restore time which can be time consuming.

I think I will do the Restore though, because I do have the extra time andI guess I'm just wanting to do a 'best practice' procedure and from your answer it sounds like Restore is the best practice.

For either one however, I still have to deal with that whole "sp_change_users_Logon" mess right? Are there any scenarios where I would copy prod to dev and not have to do that? (We're using sql-server logons).
--Jim

 

[RiverGuy]

For either one however, I still have to deal with that whole "sp_change_users_Logon" mess right? Are there any scenarios where I would copy prod to dev and not have to do that? (We're using sql-server logons)

You can just automate your user fixes instead of calling them one by one. I haven't tried this particular one, as the one I use I got from a friend and don't have access to my server at the moment, but take a look and you'll get the idea. When I restore a database to dev, I just execute a single stored procedure for each restored database to find and fixed the orphaned users.

http://community.spiceworks.com/scripts/show/819-find-and-fix-orphaned-users-in-sql-server