backup question

[jchamp02155]

I have been reading about backups for SQL Server 2000 using Enterprise Manager and have run some successful backups. No where in the documentation does it say anything about bringing the database down to do the backup nor does it use the term "hot backup" which is the term I'm used to describing what I'm doing - backing up the database w/o bringing it down.

Am I missing something or is SQL Server different in this regard?

I'm planning on restoring the db to test the backup but before I do I wanted to confirm this. (It also doesn't say anything about bringing the db down for the restore.)

Thanks.
Joe

[rmiao]

Sql backup doesn't take db down, user can still access the db during backup.

[JBane]

Joe,
There cannot be any other database connections for a restore to occur so your users will need to be out of the system during a restore.


Jeff

[rmiao]

Users can stay in system as long as they don't use db that needs to be restored.

[JBane]

Correct, I probably should have clarified. If you are restoring over a db that users are currently accessing, that will not work.

Jeff

[jchamp02155]

Thanks for the replies.

Next question: how do I keep users out of the database during a restore if the database is up?

Thanks.
Joe

[JBane]

Joe,
You need to kill all connections to the db before you kick off the restore. This can be done with a polite email, or the unpolite but highly effective combination of SP_WHO and KILL. During the restore, if anyone tries to connect, they will get an error like: Database xxxx cannot be opened, it is being restored.

Jeff

[jchamp02155]

Jeff,

Thanks for the info about restores, sp_who and kill. I have a feeling that the kill procedure will come in handy.

I was able to restore the db compltely and to point in time so I feel much better about my backups.

Joe

[Mike John]

If you are using SQL 2000 then ALTER DATABASE SET RESTRICTED_USER is far easier than sp_who and kill. Assuming the users are not connecting as a user with admin or db ownership rights this will let you shut them out of the db very easily.

Mike John

[rmiao]

But it will not disconnect exsiting users. By the way, even db owner should be disconnected from the db before restoring.

[jchamp02155]

Mike and RMIAO,

Thanks for the clarifications.

Joe