The database that I'm working for has been suddenly corrupted.
Fortunately, I performed a backup on it and save to a backup tape. Therefore, I reinstalled SQL Server and I tried to restore those backup. However, it failed and I suspect that maybe the backup is corrupted too as well. Is there any possibility on it?
Ok this won't help with this problem but you should have an automatic backup job which backs up every day - it can be set to delete them when they are older then a set time - eg 4 weeks.
Its a 5 minute job to set this up, unfortunaley people only learn the hard way!
**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
More information you can dig out from resources directly connected with SQL Server databases and database corruption in MS SQL Server any version...
http://itknowledgeexchange.techtarget.com/itanswers/how-to-restore-mdf-file-as-bakcup-databe/
http://www.filerepairforum.com/forum/microsoft/microsoft-aa/sql-server/1413-sql-database-in-suspect-mode
http://www.sqlserver.repairtoolbox.com/ SQL Server Repair Toolbox
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.