Ross Group Inc. Tips and Tricks: Database Migration

Author: Marc Troike

February 2007

Microsoft SQL Server 2000 Enterprise to Microsoft SQL Server 2005 Standard
Marc Troike, Senior DBA, Ross Group Inc

Situation
You have several databases on a SQL Server 2000 installation running Enterprise Edition.  You have been running Enterprise Edition primarily for the expanded memory support.  You need to upgrade to SQL Server 2005, but have chosen to use Standard Edition because the feature set is sufficient for your needs.

Problem
You can’t do an in-place upgrade or a Detach/Attach upgrade because of the Enterprise to Standard downgrade.  Microsoft only supports upgrades from SQL Server 2000 Enterprise Edition directly to SQL Server 2005 Enterprise Edition.  (http://msdn2.microsoft.com/en-us/library/ms143393.aspx)

Solution
You must upgrade the databases using Backup and Restore techniques.

  1. Backup your databases from SQL Server 2000
  2. Create new databases on SQL Server 2005 using data and log file sizes from the SQL 2000 catalogs
  3. Copy backups to SQL 2005 server local drive
  4. Restore databases to SQL 2005 server
  5. Drop and recreate users on SQL 2005 database
  6. Set permissions and roles for users

Tips & Tricks
Use dynamic scripting techniques to generate scripts for you.  The following will generate backup statements for all user databases on the server.

  1. Select 'BACKUP DATABASE ' + RTrim(NAME) + ' TO DISK = ''I:\BACKUPS\' + RTrim(NAME) + '.BAK'''
  2. From master.dbo.sysdatabases
  3. Where STATUS = 16
  4. Order By NAME

Using the current size of the database when creating the new catalog avoids disk fragmentation which may occur if databases are extended during restore.

Create SQL and Windows authenticated users at the server level first.  Then you must drop and recreate local database users using the following syntax.

  1. Use [DBName]
  2. Drop Schema [TestUser]
  3. Drop User [TestUser]
  4. Create User [TestUser] for Login [TestUser]

Ross Group Inc is an IT Services Company headquartered in Dayton, Ohio and an annual sponsor of the Greater IT Alliance.  More information on Ross Group Inc services can be found at www.rossgroupinc.com

Comments (0)Add Comment
Write comment
 
 
smaller | bigger
 

busy
search | login