December 10, 2008 at 6:52 am
We are moving an SQL2000 2 node cluster to new hardware. I am looking for a way to disable access to the old sql user dabase so that we can do a full backup. Then restore the database to the new MS 2 node cluster running the same version of SQL. Our concern is keeping everyone out of the database while we do a backup.
I am not an SQL person. I know just enough to be dangerous.
Any Help is appreciated.
December 10, 2008 at 9:58 am
To put your database in single user mode immediately, fire this.. Note that all incomplete transactions will be rolled back immediately.
ALTER DATABASE [yourDatabase]
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
If you wish to give time to your users to finish with their transactions, you can give this command...
ALTER DATABASE [yourDatabase] SET SINGLE_USER WITH
ROLLBACK AFTER 60 SECONDS
Please note that only one connection is permitted here(the user issuing this command).
You can start your backup activity after issuing this statement.
December 11, 2008 at 10:55 am
[font="Verdana"]
Backups can be performed while the database is in use, so you need not try and kick users off the database while taking a backup. However, since they are going to use the new database on the cluster after restore; you can use:
ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
You can, detach and attach the database in the cluster after you are finished setting it up. Just a thought.
Interesting Reading:
Backup-Restore Architecture
http://msdn.microsoft.com/hi-in/library/aa174477(en-us).aspx
SQL Server 2000 Backup and Restore
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx%5B/code%5D
Regards,
[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply