I have to periodically migrate a copy of database on a production web server down to a web server used by some developers.  The names of the databases are different, as are the underlying file names and locations. I use Management Studio to do this.  In this situation, we're running 2008 R2.

I get it done, but I feel like I'm probably doing it the wrong way.  I'm hoping that someone here can tell me if that's the case.

I make a backup on the production server, then move the file to the developer server.  On that server, I take the db offline, then restore the file into the proper database.  Before I start the restore I change the files to match the default names for the target database.  Then I do the restore.

The main practical problem I have is that the security ends of breaking for some logins, but not for others, and I don't know why.  I use the same login names for users on the source and destination machines, but sometimes they don't come through cleanly, and I have to recreate them.  It's always the same users that break.  I can fix them, but I'd like to understand why some users come across while others don't.

Also, what's the best way to move a db from one engine to another, with a name change along the way?  And how do you do it if you have different physical environments and different paths for the actual underlying files between the two systems?

Hi Alex,

This sounds like an orphan user situation. When you have the issue again use this query to verify if you have orphan users in your database:

USE <database_name>;
GO
sp_change_users_login @Action='Report';

If it is Orphan User then the resolution is here: https://msdn.microsoft.com/en-us/library/ms175475(v=sql.105).aspx

This process can be done using T-SQL and once you make the script you can automate it. If it is just the data that you need to move then you can consider an SSIS package to move the data from the production database to the developer database.

Hi Alex,

According to your description, you are experiencing the login and user not match issue after moved a database from one server to another server, right?

SQL Server Login is for Authentication and SQL Server User is for Authorization. Authentication can decide if we have permissions to access the server or not and Authorization decides what are different operations we can do in a database. A "Login" grants the principal entry into the SERVER. A "User" grants a login entry into a single DATABASE.

The issue can be caused by that the login for those users exist on original server and it not exist on new server. However you said that "I use the same login names for users on the source and destination machines". It's a strange issue that you use the same login on two servers. Please double check the login on two server and post the error message, so that we can make further analysis.

For the methods to move a database from one server to another server. There a several options for us.

• Detach the database from the old server and attach it in the new server
• Back up the database in the old server and restore it in the destination server
• Use copy database wizard in SQL Server Management Studio
• Generate the create script using Generate Script Wizard (SSMS) and execute it in the destination server.
• Use Transfer Object in SMO

Different options are used in different scenario, please refer to the link below to see the details.
http://blogs.msdn.com/b/sreekarm/archive/2009/09/11/move-a-database-from-one-server-to-another-server-in-sql-server-2008.aspx

Regards,