Suspect database status is often the case in SQL Server 2005/2000, this is because a lot of things, one of them because of power outages. There are several ways to restore the database with the status of suspect, one of them as below:
- In Enterprise Manager SQL Server 2000 / 2xxx, create a database with the same name as the database is corrupted
- Point the database file and log the new database according to the location of the damaged database file.
- Stop the SQL Server service.
- Replace or copy the database file (. mdf and. ldf) which supect to the directory or folder newly created database file.
- Run the following scripts in Query Analyzer :
sp_configure ‘allow updates’, 1
select status from sysdatabases where name = ‘nama database’
update sysdatabases set status= 32768 where name = ‘nama database’
- Create a bin database, for example: db_generate (you can make it with whatever name you want)
- Right click on the first database, then select All Task, and then select Export. Then navigate the destination dabatase to the bin database created earlier.
- Click on the process to completion. Congratulations, you got back data from damaged database in the bin database that you created.
- Run the script below in Query Analyzer :
sp_configure ‘allow updates’,
SQLServerRecoveryToolboxSetup : This is software for recovery sql server, I've never tried. but if you need it please click to download.
Tips: Backup first your database files that will be used in repair