Dell R810 - Manual (Page 42)

Servers: Database consolidation on Dell PowerEdge R810 servers
42
Appendix F. Migrating databases
In this appendix, we give detailed instructions about a basic side-
by-side migration of a user database from a SQL Server 2000
server to a SQL Server 2008 R2 server. There are multiple
methods of accomplishing this task, including using TSQL
commands and automated tools. Here, we discuss performing the
database migration using the graphical interface tools Microsoft
provides with SQL Server 2000 and SQL Server 2008 R2.
1. Log into Windows on the SQL Server 2000 server as either an
administrative user or a user with full rights on SQL Server
2000.
2. Start Query Analyzer by selecting Start | All Programs |
Microsoft SQL Server | Query Analyzer.
3. Enter your server name, and select Windows Authentication.
4. Set the database to single-user mode to ensure no updates
occur.
NOTE: Setting the database to single-user mode
immediately disconnects all users, causing them to lose any
work in progress. You must notify your users well in
advance of setting the database to single-user mode.
5. To set the database to single-user mode, in the query window,
paste the following command, where <DatabaseName> is the
name of the database you are migrating:
USE [master]
GO
ALTER DATABASE [<DatabaseName>]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
6. Press F5 to execute the query.
7. Close Query Analyzer. When Query Analyzer gives you the
chance to save the query, click No.
8. To back up the database and prepare for migration, open
Enterprise Manager by selecting Start | All Programs |
Microsoft SQL Server | Enterprise Manager.
9. In the left pane, expand Microsoft SQL Servers | SQL Server
Group | (local) (Windows NT) | Databases.
10. Right-click the database, and select All Tasks | Backup
Database.
11. Keep the default of complete backup.
12. Click Add… to add a backup device, then the ―…‖ button to
browse to the backup location you want to use. Select a