SQL Server database corrupt causing problems

jonahconrad · 24 Jul 2015 at 17:30

We use SQL Server 2008. We have 5 databases. One database is corrupt. When loading SQL Server Management Studio, we cannot expand to see all the databases. SSMS hangs. Any attempt to detach the corrupt database brings a database in use error. The server is a workstation. We changed the name of the computer a few weeks ago. I'm not sure if that has any affect. The database became corrupt while trying to replace the mdf file from a backup mdf. Any ideas?

Dolph · 24 Jul 2015 at 19:59

Do you have a good backup you could try restoring again from? Backups are always the best recovery solution.

Beyond that, you can repair the database by setting it into emergency mode (which will allow you to access it again) and running through some repair steps, although this method will result in potential data loss.

http://www.snapdba.com/2011/11/how-to-repair-a-sql-server-20052008-database-in-suspect-mode/

Please note that this is last resort stuff, and should not be attempted if you aren't either (a) confident you know what you are doing and have exhausted other options or (b) dealing with something that isn't that important.

This type of recovery is likely to see some data loss, so if successful, a full integrity check is going to be needed as well.

Gzero · 24 Jul 2015 at 21:36

Employ a proper DBA.

Use SQLCMD to backup the other databases pronto, and don't let the guy who tried copying mdf files around near it?

And who's great idea was it to rename a machine with SQL on it?

Nanoman · 25 Jul 2015 at 22:33

Try running a dbcc checkdb query. There are a few repair options for corruption if you google it.

ultimoalbino · 27 Jul 2015 at 22:58

More information you can dig out from resources directly connected with SQL Server databases and database corruption in MS SQL Server any version...
http://www.sqlservercentral.com/Forums/Topic1602448-266-1.aspx
http://www.filerepairforum.com/foru.../sql-server/1413-sql-database-in-suspect-mode
https://www.repairtoolbox.com/sqlserverrepair.html SQL Server Repair Toolbox

One of the commands and types of restoring must assist you, good luck...

Restore full backup WITH RECOVERY

Note: As mentioned above this option is the default, but you can specify as follows.

Command:
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH RECOVERY
GO

Recover a database that is in the "restoring" state

Note: The following command will take a database that is in the "restoring" state and make it available for end users.

Command:
RESTORE DATABASE AdventureWorks WITH RECOVERY
GO

Restore multiple backups using WITH RECOVERY for last backup

Note: The first restore uses the NORECOVERY option so additional restores can be done. The second command restores the transaction log and then brings the database online for end user use.

Command:
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
WITH RECOVERY
GO

oneilldo · 28 Jul 2015 at 13:50

what is a backup mdf? A SQL Server DB will consist of a minimum of a .mdf and a .ldf file. If you are restoring from backup you would restore a DB backup (which would replace the .mdf and .lrn file). You can detach a DB and then attach a database using .mdf and .lrn files but it isn't a restore.
You can use SP_WHO or SP_WHO2 to see active connections to a database as well which should allow you to see what connections are active (if it is indeed in use).

As said above backup all existing databases that you can and it might be worth stopping SQL server (if you have restarted the workstation since this problem has happened) and making a copy of the problem mdf and ldf file whilst it is offline before doing anything else that may be destructive.