Monday, August 21, 2006

Restoring a SQL Server Backup from Another Server

I'm going to use my blog as a scratchpad for me to remember something that I normally have to Google for each time that I need it.  Feel free to find the following useful, if that thought appeals to you.

Scenario: In order to migrate a database from one server to another (i.e., in order to create a development version on your laptop that will allow you to work offline or offsite), you restore a backup created on the original server, overwriting the database on the destination server (intentionally). 

Problem: If the database uses SQL Server Logins for security, then the logins specified in the backup will not match up with the logins on your destination server (think ID mismatch).

Solution: sp_change_users_login 'AUTO_FIX', 'theUserName'

tags: ,