Tutorials

Specify Alternate Text

How to create a new SQL instance and move databases

This article describes the procedures to install a new SQL instance and restore databases from another 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

  1. Put the Premium Technology CD in your CD player and have a look at the first screenshot.
    Install a new named SQL instance
  2. 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.
    Install a new named SQL instance
  3. Click continue and ignore this warning.
    Install a new named SQL instance
  4. Click next to continue setup
    Install a new named SQL instance
  5. Be sure that local computer is selected and click next.
    Install a new named SQL instance
  6. Select to 'Create a new instance of SQL Server, or install Client Tools'.
    Install a new named SQL instance
  7. Fill in your name and company name if it does appear in this window.
    Install a new named SQL instance
  8. Accept the EULA and click Yes.
    Install a new named SQL instance
  9. Choose 'Server and Client Tools' and click Next
    Install a new named SQL instance
  10. Uncheck 'Default' and give the instance a name.
    Install a new named SQL instance
  11. You can choose to put the databases on another partition or drive. In most cases you should do that.
    Install a new named SQL instance
  12. 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.
    Install a new named SQL instance
  13. 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.
    Install a new named SQL instance
  14. Click finish to complete setup.
    Install a new named SQL instance

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'

  1. Open SQL Enterprise Manager from the Start Menu
    Restore databases from another SQL server
  2. 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.
    Restore databases from another SQL server
  3. 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.
    Restore databases from another SQL server
  4. 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.
    Restore databases from another SQL server
  5. 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.
    Restore databases from another SQL server
  6. Choose Add to add a location where the backup file will be located.
    Restore databases from another SQL server
  7. 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.
    Restore databases from another SQL server
  8. 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

Restore databases from another SQL server - Part 2 'The new server'

  1. 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.
    Restore databases from another SQL server
  2. Right click the hive database, choose All Tasks and the Restore Database.
    Restore databases from another SQL server
  3. 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.
    Restore databases from another SQL server
  4. Choose restore from Disk and click add.
    Restore databases from another SQL server
  5. Choose the browse button to browse to your backup file. In my case it was Northwind.bak. Click OK.
    Restore databases from another SQL server
  6. 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.
    Restore databases from another SQL server
  7. If everything went as expected we will see:
    Restore databases from another SQL server
  8. 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.
    Restore databases from another SQL server
  9. 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...
    Restore databases from another SQL server
  10. Here is how we fix this problem. From within Enterprise Manager choose from the menu Tools -> SQL Query Analyzer.
    Restore databases from another SQL server
  11. 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'
    Restore databases from another SQL server
  12. 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.
    Restore databases from another SQL server

About www.server-essentials.com 

www.server-essentials.com is founded by Mariette Knap, a Dutch Microsoft MVP. www.server-essentials.com is a community for IT Consultants and Business Owners who, themselves, take care of the IT infrastructure and Employees who do that little extra in the company to keep things running. Our forum is for discussing all things ‘IT’ and more.  Our documentation is top notch and written by and for the community.

Change your cookie settings


 
Contact Us
(030) 2250455

International: +31302250455

 

Concentrix BV

C. de Rijcklaan 1

3723 PM Bilthoven

The Netherlands

KvK 30202318

VAT Id 814036739B01

This page is intended to be viewed online and may not be printed. You are not allowed to save or print any documentation on www.server-essentials.com. If you save documentation locally or distribute it you are violating the Terms of Service of this website you agreed on when registering an account. You have access to the documentation as long as you have a valid subscription. If you try to download our documentation we will drop Javascript which makes it possible for us to track you.