如何修复数据库？？？？？

显示全部楼层 · 2005-10-30 17:05:33

前一阵系统崩溃，导致日志文件丢失，在SQLServer Eneterprise Manager中还可以看到数据库的名字，但不能对其操作。
请问如何恢复数据库？？？？？
sp_attach_db
sp_detach_db
sp_attach_single_file_db
都试过，不好使。
我的环境是：NT4.0SQLServer7.0
十万火急，谢谢！！！！！！！！！！！

千问 · 2005-10-30 17:05:33

1. First, Shutdown the SQL service and backup all existing db files in case you have to rollback to call MS Support.
2. Are your db mark "Suspect" status ?
3.sp_attach_single_file_db will only work if you have cleanly or explicitly detached DB, may not work sometimes.
3. there is an undocumented cmd DBCC rebuild_log to rebuild
tran log. You should be able to get more than 99% of your data if not all of them.
4. Revisit your Error Log again....and seek technet KB prior Hero's Action.
Warning :
DBAs should consider all the alternatives before contemplating DBCC REBUILD_LOG. Running DBCC REBUILD_LOG can cause irretrievable data loss. As Chang notes, this method of shrinking log files is undocumented and unsupported; the consequences of using it explain why. If you do decide to run DBCC REBUILD_LOG, back up the database or copy all data files (the database's and master's) first. These backups and file copies will be your only fallback if you discover DBCC REBUILD_LOG's undocumented, disastrous consequences.

SQL Server Books Online (BOL) clearly documents that you must run sp_detach_db on a database to let the database reattach with sp_attach_db or sp_attach_single_file_db. Using sp_detach_db ensures transactional consistency within the database and ensures data integrity.
However, if complete data integrity isn't important or you know that no data has changed recently, you might be able to use the undocumented Database Consistency Checker (DBCC) REBUILD_LOG command that Listing 3 shows to attach the database. REBUILD_LOG will recreate a new log file and let you reattach a database even if a good log file doesn't exist. However, the data might not be transactionally consistent because you might have thrown away active and uncommitted transactions. Use this command only for emergency recovery when you move data to a new database.

千问 · 2005-10-30 17:05:33

各位大侠，请帮我具体分析一下
当我用sp_attach_single_file_db 时
提示:
Server: Msg 945, Level 14, State 2, Line 1
Database 'dbname' 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 'dbname'. CREATE DATABASE is aborted
我又建一个同名数据库，然后把原数据文件覆盖上去，再一次sp_attach_single_file_db 时
提示：
server:msg 5173,level 16,state 1,line 1
cannot associate files with different databases
server:msg 5105,level 16,state 1,line 1
device activation error,the physical file name 'd:\mssql7\data\dbname' may be incorrect
Server: Msg 945, Level 14, State 2, Line 1
Database 'dbname' 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 'dbname'. CREATE DATABASE is aborted
为什么？
难道我的数据库坏了吗？

千问 · 2005-10-30 17:05:33

try:
http://www.itpub.net/showthread. ... %D5%D6%BE%CE%C4%BC%
FE

千问 · 2005-10-30 17:05:33

最初由 luv2bc2ca 发布
[B]1. First, Shutdown the SQL service and backup all existing db files in case you have to rollback to call MS Support.
2. Are your db mark "Suspect" status ?
3.sp_attach_single_file_db will only work if you have cleanly or explicitly detached DB, may not work sometimes.
3. there is an undocumented cmd DBCC rebuild_log to rebuild
tran log. You should be able to get more than 99% of your data if not all of them.
4. Revisit your Error Log again....and seek technet KB prior Hero's Action.
Warning :
DBAs should consider all the alternatives before contemplating DBCC REBUILD_LOG. Running DBCC REBUILD_LOG can cause irretrievable data loss. As Chang notes, this method of shrinking log files is undocumented and unsupported; the consequences of using it explain why. If you do decide to run DBCC REBUILD_LOG, back up the database or copy all data files (the database's and master's) first. These backups and file copies will be your only fallback if you discover DBCC REBUILD_LOG's undocumented, disastrous consequences.

SQL Server Books Online (BOL) clearly documents that you must run sp_detach_db on a database to let the database reattach with sp_attach_db or sp_attach_single_file_db. Using sp_detach_db ensures transactional consistency within the database and ensures data integrity.
However, if complete data integrity isn't important or you know that no data has changed recently, you might be able to use the undocumented Database Consistency Checker (DBCC) REBUILD_LOG command that Listing 3 shows to attach the database. REBUILD_LOG will recreate a new log file and let you reattach a database even if a good log file doesn't exist. However, the data might not be transactionally consistent because you might have thrown away active and uncommitted transactions. Use this command only for emergency recovery when you move data to a new database.

[/B]
问一下，在sql 2000里是否还存在dbcc rebuild_log的用法，如果没有，变成了什么

千问 · 2005-10-30 17:05:33

有没有备份

千问 · 2005-10-30 17:05:33

真不错,没有log文件时仍然可以恢复
我关闭数据库,将一个文件全部删掉,然后按下文说的方法,成功恢复
参考

http://www.itpub.net/showthread.php...D6%BE%CE%C4%BC%
FE

千问 · 2005-10-30 17:05:33

1.将数据库的状态更改为紧急状态
2. 使用DTS可以数据从该数据库中导出,同时利用导出功能生成另一数据库