question

baldwin avatar image
baldwin asked

How to restore database MSSQL 2005 SP4?

How to restore base after RAID crash? I hoped to restore using Backup, but very late noticed that the Backup was not done. Now how do I restore the database on MSSQL 2005 SP4, which is marked with Suspend?
sql-server-2008databasetroubleshooting
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
If you can't get good files from the RAID array and you have no backups then you cannot restore
1 Like 1 ·
KennethHancock avatar image
KennethHancock answered
Use master go sp_configure 'allow updates', 1 go ---Execute--- reconfigure with override ---Execute--- select status from sysdatabases where name = 'DataBaseName' ---Execute--- EXEC sp_resetstatus 'DataBaseName'; ALTER DATABASE DataBaseName SET EMERGENCY ---Execute--- alter database DataBaseName set EMERGENCY, SINGLE_USER ---Execute--- dbcc checkdb ('DataBaseName', REPAIR_ALLOW_DATA_LOSS) ---Execute--- alter database DataBaseName set ONLINE, MULTI_USER ---Execute--- Use master go sp_configure 'allow updates', 0 go ---Execute--- reconfigure with override P. S. If in the end "dbcc checkdb" to swear on something that can't create log file then create the folder that it asks for and repeat the operation with this item again (if the base stood in other place earlier). In that case, if this way cannot restore a database, then use the Recovery Toolbox for SQL Server. It often helped out me. https://sql.recoverytoolbox.com/ [1]: + https://sql.recoverytoolbox.com/
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Just so you know, REPAIR_ALLOW_DATA_LOSS could lead to losing some or all of the data in a database. There is no limit on what the system can do to eliminate the corruption once that gets passed.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
You can't restore from that situation. If you mean that your server is back online and the database is marked as Suspect (not Suspend), then you can do two things. First, set the database into EMERGENCY mode: ALTER DATABASE yourDBname SET EMERGENCY; Then, run your database consistency check to see what's wrong with the database. It could be a corrupted log which you can fix. It could be a nonclustered index that you can drop and recreate. If it's anything else, you may be in serious trouble.For more information on this, see [Paul Randal][1]. If nothing above works out, again, see [Paul Randal][2]. Just a reminder, you can have an updated resume or a good tested backup. Pick one. [1]: http://www.sqlskills.com/blogs/paul/search-engine-qa-4-using-emergency-mode-to-access-a-recovery-pending-or-suspect-database/ [2]: http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.