Transferring a SQL Server 2000 DB to a New Server

Question

Transferring a SQL Server 2000 DB to a New Server

Don Guillermo des Las Colinas

SSC Veteran

Points: 206
More actions
July 25, 2005 at 9:44 am

#363249

We recently purchased a new DB server and I have some questions about the process of moving the exisitng DB (code, data and latest transactions) over to the new box.
My existing DB is SS2K Enterprise running on Windows 2000 Advanced Server. Since we use none of the advanced features nor have the size that merits these versions our new servers are Windows 2003 Standard and SS2K Standard editions. I've researched moving to less robust versions and it doesn't seem to be a problem for us.
Since our system runs 24x7X365 AND I am having the server login information changed for security purposes, I am leaning toward the Backup Restore transfer method rather than the Copy Database Wizard, SQL Scripts or Attaching/Detaching methods. I don't have very many jobs running on the system nor do I have any User-Defined Error messages that can't be carried over to the new digs via the Backup/Restore transfer method. I have successfully restored the DB, which is perhaps a Gig in size, to a development machine from a backup (.BAK file) using the Restore Database tool available via Enterprise Manager but I have never attempted to restore up to a certain point in the transaction logs, which are backed up every 15 minutes.
Can anyone assist me in determining a viable procedure for making the switch to the new machine? After a couple of days testing the new set up and fixing all the login errors, etc, How will the final switch take place without losing transactions? How does one start using the new DB? Is it just a matter of changing the connection string in the application to point to the new DB?
I'd appreciate from anyone who has performed a similar move in the past as I've never done this and it is a scary prospect for me!

Thanks,
Rich

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

DCPeterson SSCoach Points: 15266 More actions · Answer 1

First off it will require a window of time that your application will be offline. The question really is how long? The simplest method is to shut down the application, make a backup of the database and restore it to the new server, change the application connection properties to point to the new server and you are done.

With a 1GB database that operation should take less than an hour. If that is too long a time to be down then you can use transaction log backups to cut down the time a bit. Shortly before the cutover, make a full backup and restore it to the new server using WITH NORECOVERY. When you are ready to make the switch, shut off the application, make a last transaction log backup and take the database offline (you might want to disable the job that backs up the t-log every 15 minutes so that you don't have to worry about restoring a bunch of t-logs). Then restore any t-log backups that have been performed since you took the full backup, including the last one you ran manually. Make sure that all the t-log restores, except the very last one, are done using the WITH NORECOVERY option. Reconfigure your application and you are done.

There are several variations on this basic theme that you can use depending on your needs, but essentially they all follow the same pattern of turn off access, backup, restore, reconfigure and grant access.

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

Don Guillermo des Las Colinas SSC Veteran Points: 206 More actions · Answer 2

Thanks for the response DC

You mention using the WITH NORECOVERYoption in your response. If I do this via Enterprise Manager, will there be a means for me to select that in the interface or will using this option force me to use an SP?

Thanks again!

Rich

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

You can select this in EM, it's the bottom of the 2nd tab.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 4

Assuming you have multiple databases to migrate and a limited maintenance window, trying to use Enterprise Manager is risky unless you enjoy trying to work quickly under pressure with no mistakes. You are better off trying to script everything you will need in advance.

I went through this a while back, migrating from four servers to two new servers. I created scripts to identify the latest full backup for each user database and generate the required restore statements (including WITH MOVE because of different drive layout). The STANDBY= option was used to allow further RESTORE LOG commands but still allow read-only access so the web guys could test connections to the new dbs. A separate database on the new servers kept track of the timestamp of the last file restored for each database.

A second script looked for log backup files for all databases that were newer than the last file restored for each database, and generated appropriate RESTORE LOG ... WITH MOVE... STANDBY = commands. I ran this manually, but it could run continuously or you could schedule it in sync with the log backups on the source servers.

Up to this point, the original servers are still running without disruption. You will still need a maintenance window to cut over to the new servers, but the constant log syncing minimizes the time required. In my case we used sort of a rolling window, with web developers working on one app at a time while I migrated the dbs.

The third script was used for final migration, one db at a time. The basic steps were:

Put db in single-user mode to lock out connections

Create log backup with '_Final' suffix instead of timestamp in filename

Remove db from log backup maint plan on old server

Put old db in multi-user read-only mode

Run log shipping script on new server. "_Final" suffix tells it (for that db) to use WITH RECOVERY instead of WITH STANDBY.

Don Guillermo des Las Colinas SSC Veteran Points: 206 More actions · Answer 5

I have a single database to migrate and it's pretty small... less that 2 Gigs total. I have very limited TSql experience which is why I am hoping to get by with EM.

Rich

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

One database should be manageable with Enterprise Manager. You could still use my steps for the final migration (single user, final backup, then multi-user readonly) through Enterprise Manager.