How to create a new SQL instance and move databases By Mariette Knap sql, move database This article describes the procedures to install a new SQL instance and restore databases from another server. Pages How to create a new SQL instance and move databasesInstall a new named SQL instanceRestore databases from another SQL server TERMS This document and what comes with it are provided as-is with blunt warning: Use at your own risk, buyer beware. You break your system; you own the resolution as well. We have no liability for what you do, or can't do, or fail to do with this information. Your entire protection is to start over again with a protected backup, or from protected system. If you don't want to accept this idea, please don't use this document. Install a new named SQL instance Put the Premium Technology CD in your CD player and have a look at the first screenshot. Choose 'Install Microsoft SQL Server 2000'. Note that if you run this from SBS 2003 Premium Technologies SP1 integrated media the extract window will show, if you install SQL 2000 from Gold media the SQL installer will start immediately. Click continue and ignore this warning. Click next to continue setup Be sure that local computer is selected and click next. Select to 'Create a new instance of SQL Server, or install Client Tools'. Fill in your name and company name if it does appear in this window. Accept the EULA and click Yes. Choose 'Server and Client Tools' and click Next Uncheck 'Default' and give the instance a name. You can choose to put the databases on another partition or drive. In most cases you should do that. Choose 'Use the Local System account'. In most cases this is the easiest way to setup a new instance. Only in situations where security is a concern you would create a special account that is only used to start the new instance, for example a database that is the back end of a web server but this is outside of the scope of this article. Depending on your needs you will set the database to 'Mixed Mode' or 'Windows Authentication'. In our case we choose Mix Mode because we want to use this database in a web application. You must define a 'sa' password. Be sure to make this a very complex and long password because the 'sa' account has access to everything in this new instance. If you are going to use this instance and its databases in a web application you should never use the 'sa' account in your connection string. Instead of using the 'sa' account you should create a new account that is 'dbowner' of only that database you are going to use. Click finish to complete setup. After the installation of this new SQL instance we need to upgrade it with SQL 2000 SP4. Please, follow the instruction on 'How to install SQL 2000 Service Pack 4'. Restore databases from another SQL server Restore databases from another SQL server - Part 1 'The old server' 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: Restore databases from another SQL server - Part 2 'The new server' 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.