SE-Pro.com: Перевод статьи http://www.sql.ru/faq/faq_topic.aspx?fid=123

 

SQL Faq 1:

Problem: The disk with transaction log crashed. Database have status suspect. Detach was succeed, but attach_single_file_db is not. i.e. attempt for attaching Database (without log) return this error:



Server: Msg 945, Level 14, State 2, Line 1

Database 'test1' cannot be opened because some of the files could not be activated.

Server: Msg 1813, Level 16, State 2, Line 1

Could not open new database 'test1'. CREATE DATABASE is aborted.



Creating new database and substitution of garbled file is not useful.





A.

1. Create new database with same name and same by name and same placement of .mdf and .ldf files

2. Stop server, replace .mdf file

3. Start server, but do not pay attention for database status.

4. From Query Analyzer (QA) run script

Use master

go

sp_configure 'allow updates', 1

reconfigure with override

go



4a. In the same place run

select status from sysdatabases where name = '<db_name>'

and save value for accident failure



5. In the same place run

update sysdatabases set status= 32768 where name = '<db_name>'



6. Restart SQL Server



7. Database should be seen (in emergency mode).



8. From QA run:

DBCC REBUILD_LOG('<db_name>', '<name of new log file with full path>')



SQL Server will say - Warning: The log for database '<db_name>' has been rebuilt.



9. If all ok, in the same place run

Use master

go

sp_dboption '<db_name>', 'single_user', 'true'

go

USE <db_name>

GO

DBCC CHECKDB('<db_name>', REPAIR_ALLOW_DATA_LOSS)

go



9a.

If you attempt for moving database in single user mode is not successful, then for cheking data you could try

dbo only mode

sp_dboption '<db_name>', 'dbo use only', 'false'



10. If all ok:

sp_dboption '<db_name>', 'single_user', 'false'

go

Use master

go

sp_configure 'allow updates', 0

go





Translated by SE-Pro.com from:

http://www.sql.ru/faq/faq_topic.aspx?fid=123