[轉貼][MSSQL]Attach DB without LDF

有關資料庫的討論, 都可以在這發表哦~~
回覆文章
頭像
tim
文章: 1288
註冊時間: 2008年 11月 26日, 00:49

[轉貼][MSSQL]Attach DB without LDF

文章 tim » 2008年 11月 28日, 19:24

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=41253

==========
1. Back up the .mdf/.ndf files at first!!!

2. Change the database context to Master and allow updates to system tables:

Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go

3. Set the database in Emergency (bypass recovery) mode:

select * from sysdatabases where name = '<db_name>'
-- note the value of the status column for later use in # 6
begin tran
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran

4. Stop and restart SQL server.

5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
supsected db.
The syntax for DBCC REBUILD_LOG is as follows:

DBCC REBUILD_LOG('<db_name>','<log_filename>')

where <db_name> is the name of the database and <log_filename> is
the physical path to the new log file, not a logical file name. If you
do not
specify the full path, the new log is created in the Windows NT system
root
directory (by default, this is the WinntSystem32 directory).

6. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:

sp_dboption '<db_name>', 'single user', 'true'
DBCC CHECKDB('<db_name>')
Go
begin tran
update sysdatabases set status = <prior value> where name = '<db_name>'
-- verify one row is updated before committing
commit tran
Go

7. Turn off the updates to system tables by using:

sp_configure 'allow updates', 0
reconfigure with override
Go
============

3. The final option is to get the data using bcp utility:
Though some file may be lost, However, much of the data in your database is
often still available, albeit transactionally (and physically)
inconsistent. We
can access this data by setting the database status to bypass, or emergency
mode. This is done by setting sysdatabases.status to -32768 for the
database,
after turning "allow updates" on. For example, use the following command:
UPDATE SYSDATABASES SET STATUS=32768 WHERE NAME='DBNAME'
After doing this, we can enter the database and SELECT the data or use BCP
to
get it out. We may encounter errors while doing this, but in most cases
much of
the data can be retrieved.

Resolution
=============
Please follow the steps below to get the data out.
0) Backup all the databases first!!!!

1) Execute the scripts below in Query analyzer
use master
go
sp_configure 'allow update',1
go
RECONFIGURE with override
go
UPDATE SYSDATABASES SET STATUS=32768 WHERE NAME='your_database_name'
go
sp_configure 'allow update',0
go
RECONFIGURE with override
go
2) Using select, BCP or other tools to retrieve the data

Hope it help!
-kevin

This posting is provided ¡°AS IS¡± with no warranties, and confers no
rights.
多多留言, 整理文章, 把經驗累積下來.....

頭像
tim
文章: 1288
註冊時間: 2008年 11月 26日, 00:49

Re: [轉貼][MSSQL]Attach DB without LDF

文章 tim » 2010年 4月 5日, 09:18

這裡也有一篇圖文並茂的說明.

MSSQL 2000 附加資料庫時, 只有MDF檔 或 出現LSN錯誤
http://polinwei.blogspot.com/2009/04/mssql-2000-mdf-lsn.html
多多留言, 整理文章, 把經驗累積下來.....

頭像
tim
文章: 1288
註冊時間: 2008年 11月 26日, 00:49

Re: [轉貼][MSSQL]Attach DB without LDF

文章 tim » 2010年 4月 5日, 09:23

這篇是直接利用指令來操作的方式: http://bbs.diary.tw/viewtopic.php?f=13&t=352
多多留言, 整理文章, 把經驗累積下來.....

回覆文章