| Introduction Moving or copying a SQL Server database from one machine to another requires a lot of preparation in order to ensure a
smooth transfer. When moving from SQL Server 7.0 to SQL Server 2000, there are 4 choices in how to implement the transfer: - Backup and Restore
- sp_detach and sp_attach
- Import and Export Data
- Upgrade the original server to 2000, then transfer the databases to the new server
Each of these has its own issues you will need to review. This article assumes that the SQL Administrator is performing all tasks. The Master Database The first choice to determine is any of the data needed is in the Master database. Login information, Jobs, Alerts, Backups,
and DTS packages are some of the items kept in the master database. If you know that each/all of your databases to move from
SQL Server 7.0 to SQL Server 2000 needs information kept in the master database, you should upgrade the original server to 2000.
After the upgrade is successful, move the databases from the original server to the new server. This is the easiest method if
time and licensing are not considerations. If upgrading the original server is not a choice, the next best choice is to use the Import and Export Data method. This
method is best done by someone that understands T-SQL and is comfortable adding, modifying, or deleting SQL commands. The Backup/Restore and Detach/Attach methods are poor choices if you need the master database, because both of these
methods are best used for databases other than the master.
Business Case I was asked to move a SQL Server 7.0 database to a newly installed SQL Server 2000 server. The database would be the only
database on the new server and the old SQL Server Master database information would either not be necessary or setup by
someone else. Because of the timing, the database would be moved but the original database on the original server would
not be deleted from some time.
Backup and Restore & Sp_detach and Sp_Attach These two methods work best if the destination server is configured exactly like the original server in terms of SQL
configuration including location on the physical hard drive. These two methods can be the quickest transfer choice if
the conditions are right because SQL Server does most of the work and asks very few questions. Since there is little
to choose to configure, these two methods can be quick and easy to perform. Backup and Restore is completed from inside the SQL Enterprise manager. Sp_detach and sp_attach are completing using SQL Query Analyzer (or any tool that will execute SQL code).
Import and Export Data Import and Export data makes a SQL DTS package of instructions for data to export. This becomes a SQL script. If you are a SQL programmer, instead of a SQL IT admin, you may find this method easiest. You can open the SQL script and alter it to add or change any code. Unless both servers are identical in configuration, you will probably have to edit the script in order for it to run without errors. | Method | Best Used | Used By |
|---|
| Backup and Restore | Data Database only - no master data needed | IT admin | | sp_detach and sp_attach | Data Database only - no master data needed | SQL Developer | | Import and Export Data | Master and Data database | IT with T-SQL knowledge or SQL Developer | | Upgrade version, then transfer | Master and Data database | IT admin
Requires more time and possibly additional license |
Business Case - Choosing Backup and Restore I choose backup to begin the process and T-SQL restore because I only needed the database (nothing from the Master) but the file path and database name were changing from the old server to the new server. The rest of this article focuses on this method of moving a database.
Stop All Applications/Connections of Original Database Stopping the applications may involve stopping a web site, closing a port on a firewall, or some other action to make sure
the connections between the calling applications and the database are closed.
Backup of Original SQL Server 7.0 Database Make sure the original database is in read-only mode and allows only 1 user logged on at a time. This ensures that the
database is not changing while the transfer is in progress. In my business case, I knew the original database would
stay online but not be used. Read-only mode was a great way to ensure the applications using the database couldn't
alter the old database after the transfer was completed. In the Enterprise Manager, select the database from the tree-view, right-click and choose Properties. On the Options tab,
check Read-only and check Restrict Access for single user.  The next step on the original server is to make a backup of the database. It's important to backup the entire database
and overwrite existing media (ie, create a new file). If you have naming conventions for nightly backups, you may want
to name the file differently so the file is not easily confused with nightly backups or backup to a different location.  Make a note of the backup file name and location. You may need this in the next step.
Changing File/Path Location During the Move If the old server file path and database name will be different from the new file path and database name, you will
need to determine the exact old server data and log file names. This information is stored in the backup file you
just made. In SQL Query Analyzer, execute: restore filelistonly from disk='filepath\filename.bak'
example: restore filelistonly from disk='c:\sql\backup\mybackup.bak'
 SQL Query Analyzer will return a result set including the PhysicalName of the two files. Make note of these two names,
such as 'test_data.mdf' and 'test_log.ldf'.
Move the Backup File Move the backup file (*.bkf) to a location where the new server can find it, if necessary.
Restore to New SQL Server 2000 Database In order to change the location of the database name and path, I used the T-SQL restore command in SQL Query Analyzer
connected to the new Server: restore database DatabaseName
from disk = 'c:\DatabaseName.bak'
with move 'DatabaseNameData' to 'd:\DatabaseNameData.mdf',
move 'DatabaseNameLog' to 'd:\DatabaseNameLog.ldf',
replace
This changed the location of the database from the old server's path on c:\ to the new server's path on d:\. The database is now restored on the new server. If you need to add anything to the master database on the new server,
now is the time. This includes Logins, re-indexing, Jobs, Alerts, etc. Stop and Start SQL Server.
Restart any Applications Any calling applications will need to change their connection information to point to the new server and new database.
Since the old database on the old server is still in read-only, 1 person mode, it will be easy to tell when an application
has not made the switch.
Changes to Other Applications As an IT Administrator or Developer, you may find some of your other software tools may not work as expected. For example, Microsoft Access may not be able to connect to the new SQL Server by default. This is a compatibility issue and can be resolved by executing T-SQL on the new server: Exec sp_dbcmptlevel 'db',80
Now Microsoft Access should be able to connect to the SQL Server 2000 without any problems.
Summary Moving a database between two databases appears to be an easy task. The complexity of the database and the different versions
of the software can make the job challenging. There are several methods to moving a database. Each should be evaluated for
the specific move you have in mind.
KnowledgeBase Articles There are several Microsoft Knowledgebase articles about moving or copying databases. There are kernels of information
in all the articles. Here are some starter articles:
| 
