SQL Server question database solution

Method 1:

Symptom: the database log is too large, so the shrink cannot be dropped. So I want to unload the database file, delete the log, and attach it. Attaching failed.

Error message:

Server: Message 1813, level 16, status 2, Row 1

The new database 'metadb' cannot be opened '. Create database will be terminated.

Device activation error. The physical file name 'd: \ metadb. ldf' may be incorrect.

Environment: MSSQL Server 2000 Enterprise Edition

　Solution Process:

1. Create a new database newdb

2. Stop the database. Delete the log file of the new database. metadb. MDF overwrites newdb. MDF.

3. Start the database server. The status of the database newdb is "questionable ".

4. allow direct modification to the system directory

Use master Go Sp_configure 'Allow updates', 1 Go Reconfigure with override Go Update sysdatabases set status =-32768 where dbid = db_id ('newdb ')

5. Rebuild log

DBCC rebuild_log ('newdb', 'c: \ Program Files \ Microsoft SQL Server \ MSSQL \ data \ newdb_log.ldf ')

6. DBCC check

DBCC checkdb ('newdb ')

7. Set the database to normal

Sp_dboption 'newdb', 'dbo use only', 'false'

8 direct modification to the system directory is not allowed

Sp_configure 'Allow updates', 0 Go Reconfigure with override Go

　　Method 2:

Solution:

This is the easiest way to back up the database and restore it.

Steps:

1. Delete the original database:

Use master Go Drop database db_suepect

 

2. Create a database with the same name:

Use master Go Create Database db_suspect On (Name = dbname_dat, Filename = 'C :', Size = 10, Filegrowth = 5) Log On (Name = 'dbname _ log ', Filename = 'G :', Size = 5 MB, Filegrowth = 5 MB) Go

3. Restore the database:

Restore database db_suspect From dbname_backup.dat

4. database integrity check:

DBCC checkdb ('db _ suspect ')

5. Restart the MSSQLServer service.

If there is no full backup, you need to use some special methods:

1. Set the database to emergency mode

Use master Go Sp_configure 'Allow updates', 1 Reconfigure with override Go Update sysdatabases set status = 32768 where name = 'db _ suspect' Go

2. Stop the SQL Server service:

Net stop MSSQLServer

 

3. Remove the data files dbname_dat.mdf and dbname_log.ldf from the original database:

4. Start the SQL Server service:

Net start MSSQLServer

5. Create a database named db_suspect again;

Use master Go Create Database db_suspect On (Name = dbname_dat, Filename = 'C :', Size = 10, Filegrowth = 5) Log On (Name = 'dbname _ log ', Filename = 'G :', Size = 5 MB, Filegrowth = 5 MB) Go

6. Set the mode in which the database runs in a single user:

Use master Go Alter database db_suspect set single_user Go

7. Stop the SQL Service:

Net stop MSSQLServer

8. overwrite the original data file back:

9. Start the SQL Server service:
 

Net start MSSQLServer

10. Reset the SQL Server Status:

Use master Go Exec sp_resetstatus "db_suspect"

11. Database Integrity Detection:

DBCC checkdb ('db _ suspect ')

12. Restore the database to the multi-user mode:

Use master Go Alter database db_suspect set multi_user Go

13. restore the original sqlserver Configuration:

Use mater Go Update sysdatabases set status = 4194320 where name = 'db _ suspect' Go

14. configuring sqlserver to update system tables is not allowed:

Use master Go Sp_configure 'Allow updates', 0 Reconfigure with override Go

15. Restart the MSSQLServer service:

It is best to restart the Operating System

16. Back up the database:

You can back up Master and db_suspect through sqlserver Enterprise Manager or T-SQL.

Source: http://hi.baidu.com/genersoft/blog/item/d5fd818b1b25367b9f2fb490.html