SQL Restore

[rebel4k]

I am using SQL 2000 and am trying to do a database restore. I have killed all SpID's on the database and I have placed the database in single user mode. I have also temporarily removed all permissions for other users apart from myself. This included me removing access to the database from the public role. Whenever I start the restore I am getting the following error message "Exclusive access could not be obtained because the database is in use. Restore Database is terminating abnormally". Please can anyone advise me of anything I might have missed or need to do. Any help will be appreciated.

Thanks

 

[SQLBill]

How are you trying to restore the database? Did you open Enterprise Manager (first connection) and from there open Query Analyzer (second connection)?

Remember, single user mode is not truly single USER mode. It is single CONNECTION mode.

Also, make sure you have disabled any jobs that run on that database. If you KILL the SPID for a job, it might just start up again.

Are you using the WITH REPLACE command? Since your database still exists, you need to do WITH REPLACE to overwrite the existing one.

Another option is to detach the original database, move the .mdf/.ldf files and then do the RESTORE.

-SQLBill

Posting advice: FAQ481-4875

 

[rebel4k]

I have opened enterprise manager to check which Spid's are running. I have also opened query analyzer to switch the database to single user mode. I am indeed using the replace command. At the time of doing the restore both query analyzer and enterprise manager are open at the same time.

 

[SQLBill]

Well, that's TWO connections (users). You aren't in Single User mode. Close Enterprise Manager and use JUST Query Analyzer. You can find the SPIDs by running sp_who or sp_who2.

-SQLBill

Posting advice: FAQ481-4875