Restoring .mdf files from D:|sqldata to database | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Restoring .mdf files from D:|sqldata to database

Hi!<br />I faced a big trouble in restoring.<br />I have a backup of abc.mdf and abc_log.ldf files in d:sqldata folder.<br />Due to some reasons the original database is dropped and then we recreated the database abc using the create database wizard in EM.I alloted the necerrary permissions.I checked the sysdatabases table to check weather this new database is having record in master or not.It was there.Then I tried to attach the abc.mdf, abc_log.ldf to this new database using the sp_attach_db stored procedure but of no use. many times i tried to detach and attach but invain<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />During this process permissions and other system database tables get automatically disappread.<br />How to restore this datafiles.??<br />all suggestions are welcome…<br />Sandy
Hi Sandy, Do you have backups for these databases? If you have, then restore the databases and it will be perfectly alright. Thanks,
Manoj.
What was the error while attaching the database using SP_ATTACH_DB statement? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Regarding these ‘backups’ of abc.mdf and abc_log.ldf :
Were these files correctly detached to begin with ?
You will probably not be able to re-attach these files to the new database if they were not correctly detached before using the sp_detach_db statement. When attaching database files, it is not really necessary to create the database first in Enterprise Manager. The database will be created automaticaly when you run the attach statement. Similarly, the database is automaticaly dropped when you run the sp_detach_db statement and you would need to drop or detach the current database anyway in order to replace those files with your ‘backup’ files.
So, what you could try now is to DROP the current database and then run the attach statement: EXEC sp_attach_db @dbname = N’abc’,
@filename1 = N’D:SQLDataabc.mdf’,
@filename2 = N’D:SQLDataabc_log.ldf’ If you decide to run the DROP statement, be sure that these proper ‘backup’ files are not already attached as that would delete these files.
Thanx for your suggesions.The problem is now resolved.
]]>