|
Open SQL Enterprise Manager from the Start Menu
In this example we are going to assume that a Mix Mode login was created on the old database you want to restore. In the screenshot below you see that the Mix Mode loginname was Admin.
You see that the user Admin (is not in the AD because this is a SQL Server account) is listed as one of the users of the Northwind database.
If you double click on user Admin you will notice that this account is 'dbowner' of the database Northing. All our stored procedures and table are owned by Admin. If you restore a database to a different server and probably also to a different instance the Admin login to that database is no longer linked to the Admin login in the Security hive of the instance. This is very important to know if you do a restore to a new server because if you do not run the script further on in this doc access to your database will fail.
Now we are going to make a backup of the SQL database you want to move to a new server. If the database is used in a web application or any other application that users connect to be sure to disconnect all users. You can make a backup when users are attached to the database but I prefer to do that with no connections at all, just to be sure. Right click the Northwind database, choose 'All Tasks' and Backup Database.
Choose Add to add a location where the backup file will be located.
I choose to put the Northwind.bak on my drive E. The extension .bak is the default extension for a SQL backup file. Click OK.
You will be informed if the backup was successful. The backup file should now be located on my drive E:
In this example you will see that the server and instance is the same as the one I made the backup on. The reason for this is that I do not have another server ready to test this but the result and procedure is the same. As you see in the below screenshot we have installed SQL 2000 on a brand new server and created a new instance but there is no Northwind database. We are now going to restore the Northwind database.
Right click the hive database, choose All Tasks and the Restore Database.
Be sure to fill in the box 'Restore as database' with a name and do not use 'master'. Choose to restore from device, that is where your backup file is located. Choose select device.
Choose restore from Disk and click add.
Choose the browse button to browse to your backup file. In my case it was Northwind.bak. Click OK.
We need check our choices. We are going to restore the database as 'Northwind' and we do that from a backup file located on drive E. We choose to do a complete restore. Click OK to continue the restore.
If everything went as expected we will see:
It seems all just fine but you will see something that took me days to figure out. If you look at the users in the database Northwind you see the user Admin is listed but it is missing the Login Name.
If you look in the Logins in the Security hive you see that the Admin is listed because you have added the Login account after you created the new instance. Now what is wrong? Why does this happen? If you try to connect to the database Northwind with, for example a web application, it will fail telling you it cannot connect to the database and some sort of message that the username or password is wrong. Well, this took me days to figure out and I sure hope this article prevents you from searching for days... 
Here is how we fix this problem. From within Enterprise Manager choose from the menu Tools -> SQL Query Analyzer.
In the query window be sure that the Northwind database is the one listed and run:
sp_change_users_login @Action='update_one', @UserNamePattern='Admin', @LoginName='Admin'
If you now open Enterprise manager and double click on your Admin login you should see that Login name is synched with the User name. Problem solved and database is moved from one server to another.
|