Offline, Online and Single-User mode while restoration

Question

Offline, Online and Single-User mode while restoration

skyw

Ten Centuries

Points: 1122
More actions
November 27, 2008 at 10:14 pm

#154961

Hi,
For restoring a database on a production server I am trying to delete the older db. But SQL 2005 doesnt allow the deletion of the db without it being taken offline. I suppose that the Db is being used in some way.
[p]How to check if the db is being used before deleting it[/p].
[p]Does moving the db to Single-User mode help in this scenario[/p].

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

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You can run sp_who or sp_who2 to see if anyone is in the database. Putting in single user mode will work in this case and allow you to drop the DB.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Atif Hall of Fame Points: 3578 More actions · Answer 2

For changing fro example adventureworks in to single user mode

ALTER DATABASE adventureworks SET SINGLE_USER

and for again to multi user mode

ALTER DATABASE adventureworks SET MULTI_USER

DBDigger Microsoft Data Platform Consultancy.

skyw Ten Centuries Points: 1122 More actions · Answer 3

Thanks for fast replies. I'll run sp_who(2) before attempting deletion.

My doubt is -

Is taking the database offline and then deleting it an option at all? (rather than bringing it to the single-user mode and then deleting it.)

Thanks Once Again.

Atif Hall of Fame Points: 3578 More actions · Answer 4

As long as SQL Server is running you can not delete the database. Simply set it to single user mode and restore it with over write option enabled. You will get restored database.

DBDigger Microsoft Data Platform Consultancy.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

skyw (11/27/2008)
Is taking the database offline and then deleting it an option at all? (rather than bringing it to the single-user mode and then deleting it.)

Sure.

ALTER DATABASE SomeDB SET OFFLINE WITH ROLLBACK IMMEDIATE -- kicks users out of db and takes it offline

DROP DATABASE SomeDB

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

skyw Ten Centuries Points: 1122 More actions · Answer 6

skyw

Ten Centuries

Points: 1122

November 30, 2008 at 11:43 pm

#905396

🙂

Thank You.

Vegard Hagen Hall of Fame Points: 3078 More actions · Answer 7

Actually, you do not need to delete the older database in order to restore a new one from production - you can simply restore and overwrite what's already there. (RESTORE WITH REPLACE if you need to, but if the existing database is actually an older copy of the same database from production, you don't even need the "WITH REPLACE" option.)

You still need an exclusive lock on the database to do a restore though, so what you did is fine. 🙂

Kind regards,

Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

It is better to light a candle than to curse the darkness. (Chinese proverb)