SQL Server 를 운영하다, ldf가 손상되거나 분실 됐을 경우에 다음과 같이 DB를 살리면 됩니다.
EXEC sp_attach_single_file_db @dbname = 'AdventureWorks',
@physname = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf';
만약 그래도 않살아 난다면 아래의 방법을 사용하시기 바랍니다.
if this doesnt work then try the following:
(Ive used this script myself and found it a life saver, I believe it may have come from Jasper Smith, although I cant find an original ref)
1) Make sure you have a copy of your.MDF
2) Create a new database called fake (default file locations)
3) Stop SQL Service
4) Delete the fake_Data.MDF and copy your.MDF to where fake_Data.MDF used to be and rename the file to fake_Data.MDF
5) Start SQL Service
6) Database fake will appear as suspect in EM
7) Open Query Analyser and in master database run the following :
CODE
sp_configure 'allow updates',1
go
reconfigure with override
go
update sysdatabases set
status=-32768 where dbid=DB_ID('fake')
go
sp_configure 'allow updates',0
go
reconfigure with override
go
This will put the database in emergency recovery mode
8) Stop SQL Service
9) Delete the fake_Log.LDF file
10) Restart SQL Service
11) In QA run the following (with correct path for log)
CODE
dbcc rebuild_log('fake','h:\fake_log.ldf')
go
dbcc checkdb('fake') -- to check for errors
go
12) Now we need to rename the files, run the following (make sure there are no connections to it) in Query Analyzer (At this stage you can actually access the database so you could use DTS or bcp to move the data to another database).
CODE
use master
go
sp_helpdb 'fake'
go
/* Make a note of the names of the files , you will need them
출처 : http://www.tek-tips.com/faqs.cfm?fid=6260
in the next bit of the script to replace datafilename and
logfilename - it might be that they have the right names */
sp_renamedb 'fake','your'
go
alter database your
MODIFY FILE(NAME='datafilename', NEWNAME = 'your_data')
go
alter database PowerDVD301
MODIFY FILE(NAME='logfilename', NEWNAME = 'your_Log')
go
dbcc checkdb('your')
go
sp_dboption 'your','dbo use only','false'
go
use your
go
sp_updatestats
go
13) You should now have a working database. However the log file will be
small so it will be worth increasing its size. Unfortunately your files will
be called fake_Data.MDF and fake_Log.LDF but you can get round this by
detaching thedatabase properly and then renaming the files and reattaching it.
14) Run the following in QA
CODE
sp_detach_db your
--now rename the files then reattach
sp_attach_db 'your','your.mdf','your.ldf'