Best way to copy a database (SQL Server 2008)

Dumb question - what's the best way to copy instances in an environment where I want to refresh a development server with instances from a production server?

I've done backup-restore, but I've heard detach-copy-attach and one guy even told me he would just copy the datafiles between the filesystems....

Are these the three (or two, the last one sounds kind of suspect) accepted methods?

My understanding is that the second method is faster but requires downtime on the source because of the detach aspect.

Also, in this situation (wanting an exact copy of production on a dev server) what's the accepted practice for transferring logins,etc.? Should I just backup and restore the user databases + master + msdb?

Answers


The fastest way to copy a database is to detach-copy-attach method, but the production users will not have database access while the prod db is detached. You can do something like this if your production DB is for example a Point of Sale system that nobody uses during the night.

If you cannot detach the production db you should use backup and restore.

You will have to create the logins if they are not in the new instance. I do not recommend you to copy the system databases.

You can use the SQL Server Management Studio to create the scripts that create the logins you need. Right click on the login you need to create and select Script Login As / Create.

This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

Need Your Help

Best way to mask dynamic data (timestamps) using ScalaTest

regex scala scalatest

I'm using ScalaTest for my unit testing. I have a test result (JSON) that might look like below. The actual result is huge and complex. This is an example.

select the top 10 products of a given category

ssas mdx

I am trying to select the top 10 products by internet sales from the Adenture Works cube (the cube I managed to compile with the Analysis Services Tutorial), I managed to do the cube perfectly, now I

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.