Recovering a SQL database via T-SQL

  • [アーティクル]

So this post is courtesy of a late night database issue. One of my important SQL databases was coming up suspect, so what to do? Well, here's some TSQL that may help, if you need to recover a database:

USE master
GO
ALTER DATABASE YourDatabase SET EMERGENCY
GO
DBCC CHECKDB (YourDatabase)
GO
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DBCC CHECKDB (YourDatabase, REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE YourDatabase SET MULTI_USER
GO

Of course, replace YourDatabase with your database name. This will put your DB into emergency mode (you will be able to query it at that point), and then since you run DBCC the second time, the database hopefully will be repaired. The last step, alter database, will hopefully make it normal to the system and ready for use. I recommend a reboot after it's all said and done.

This works fine (done this before) with suspect databases. If you've experienced a hardware failure, like a disk especially, don't expect these things to work - just hope you have a SQL backup. Hope this helps someone else.

— If you like my blogs, please share it on social media, rate it, and/or leave a comment. —