SQL Server 2000 need to prevent logons whilst performing a backup for a side by side migration

There are a number of ways to do this, some more elegant than others:

1. Detach, copy to new server, attach on new server, re-attach on current server

2. Put the DB in single user mode and back it up

3. Disconnect the network cable on the server

4. Block incoming connections to port 1433

putting the database into single user mode will work a treat. You'll need to take the backup right away in the current connection, to prevent other connections getting back in if you shut the connection & try to use Enterprise Manager to do the backup. You'd also want to take the database offline when you're done. Here's a sample using northwind.

use northwind
alter database northwind set single_user with rollback immediate 
backup database northwind to disk='c:\northwind.bak' with init
use master
alter database northwind set offline

detach/copy/attach would work. (Don't leave the LDF file(s) behind. They are important, even though some people don't think so, especially if you are forcing the database down quickly. You could lose data if the log is not correctly attached to the new server.).

I have another alternative which uses the much-overlooked "Pause" feature of SQL Server.

I find that setting a database to single user can be a problem when you have lots of users who are jumping into that database. They get to be the "single user" before my connection does.

My method works best if you are handy with a query window, rather than using the SSMS GUI. This method might not work well if you are using a cluster (it's been years since I would have tried it, and I don't remember the outcome. You don't want to accidentally trigger a failover.)

Also, this method might not work well if there are other databases for other apps that are in production on the same SQL Server.

1. Log in to a query window as an administrator. You will use this connection to kill spids (in a moment) and then backup the database for the last time.

2. Pause the SQL Server Service. (In SSMS, right-click on the Server icon or use the control panel applet or any similar service-control program.)

This will prevent any new logins to this instance, including administrator connections, db_owner connections and SQL Agent connections, but existing connections will not be dropped. While the server is paused, attempts to connect will fail and get an error message. You might want to warn your users or support team.

3. Gracefully shut the user apps down and/or kick out the existing connections of regular users using KILL. This will write the kill commands for you, just change/USE to the database to be migrated first, then run this: select 'kill ' + convert(varchar, spid) from master.dbo.sysprocesses where spid != @@SPID and dbid = DB_ID()

Then copy the output into your query window and run it. It will kill every connection in the current database but yours.

4. Lastly, run your backup command, just like you were going to.

You can use "Resume", on the same menu as "Pause", to allow logins when you are done, if you want. Set the old database to read-only or offline (preferably) before letting people back in. (Offline is better than read-only because you want people to get error messages in the event that you have overlooked something when pointing connection strings to the new server. Dropping the database would also get you error messages, but offline isn't as permanent. If you have to go back to the old server, it's easy to just alter the database online, but you would have to go through a restore if you dropped the database. You can always drop the offline database tomorrow, after you are sure you have a successful migration.)

Of course, you need to restore your backup to the server you are migrating to, fix up any SQL logins, then fix the connection strings for the apps or change the DNS entry for the server, depending on how you are handling that, turn on the database checksum feature, run a full reindex on all of the tables, update the database compatibility level if that is appropriate to do, etc., etc.

If you are new at this or if this is particularly important or has demanding users, I would suggest trying at least one dry run first. You wouldn't need to kick everyone out for a dry run, just take a backup and restore it on the new server. Doing a restore forces you to work out where the files go (drive letters and paths can be different on different servers) and it gives you an idea of how long it will take to do the restore. (When going for real, if you already have a copy of the database on the new server, it's usually a little faster if you are restoring "with replace" than if you are going from scratch. This gives you some wiggle room when giving an estimate to the users of how long the system will be down for the migration.)

Unless you are only using windows logins, you want to test that the SQL logins work before doing it for "real". It's best if you have a test system that mimics production, where you can work out your "WTF?" moments first, but not everyone has that luxury.