برگرداندن اطلاعات Suspect شده [بایگانی]

42li42li

شنبه 08 تیر 1387, 16:53 عصر

در 2005 دیتابیس را دوباره attache میکنیم البته با رعایت این نکته که از لیست logfile رو حذف می کنیم
اما در 2000 به روش زیر عمل میشود

Would you like to...
Print this page
Email this page
Post a comment
Subscribe me
Add to favorites
User Opinions
No users have voted.
--------------------------------------------------------------------------------
How would you rate this answer?
Helpful
Not helpful
Help us improve. Why was this unhelpful?

Thank you for rating this answer.
If you have an mdf file that was not properly detached from SQL Server (possibly due to a hard drive crash), then you may need to repair the mdf before you are able to attach the database. The following are instructions on how to repair the mdf file. Replace the filenames with your filename !!!
Make sure you have a copy of eshadata.MDF (or gendata.mdf)
Create a new database called fake (default file locations)
Stop SQL Service
Delete the fake_Data.MDF and copy eshadata.MDF (or gendata.mdf) to where fake_Data.MDF used to be and rename the file to fake_Data.MDF
Start SQL Service
Database fake will appear as suspect in EM
Open Query Analyser and in master database run the following :
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
Stop SQL Service
Delete the fake_Log.LDF file
Restart SQL Service
In QA run the following (with correct path for log)
dbcc rebuild_log(´fake´,´h:\fake_log.ldf´)
go
dbcc checkdb(´fake´) -- to check for errors
go
Now we need to rename the files, run the following (make sure there are no connections to it) in Query Analyser (At this stage you can actually access the database so you could use DTS or bcp to move the data to another database .)
use master
go
sp_helpdb ´fake´
go
/* Make a note of the names of the files , you will need them in the next bit of the script to replace datafilename and logfilename - it might be that they have the right names */
sp_renamedb ´fake´,´eshadata´
go
alter database eshadata
MODIFY FILE(NAME=´datafilename´, NEWNAME = ´eshadata´)
go
alter database eshadata
MODIFY FILE(NAME=´logfilename´, NEWNAME = ´eshadata_Log´)
go
dbcc checkdb(´eshadata´)
go
sp_dboption ´eshadata´,´dbo use only´,´false´
go
use eshadata
go
sp_updatestats
go
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 the database properly and then renaming the files and reattaching it.
Run the following in QA
sp_detach_db eshadata
--now rename the files then reattach
sp_attach_db ´eshadata´,´h:\dvd.mdf´,´h:\DVD.ldf´