SQL Server Performance Forum – Threads Archive

restore database with MDF file only

I need to restore a SQL Server database with data file only. I try to run the following command:
exec sp_attache_single_file_db @dbname = ‘TEST’
@physname="D:ackupcustomer filesMICRO Metadata_Data.MDF’
However, I get the error message as:
"Could not open new database ‘TEST’. Create Database is aborted.
Device activation error. The physical file name ‘d:mssqldataMICRO Metadata_Data.MDF’ maybe incorrect." Thanks for your help. – Kathy

Was Test originally detached from the source server?

And where is the file? in d:ackup…. or in d:mssql…..? Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

It is in D:ackupcustomer filesMICRO Metadata_Data.MDF. I have to copy this file to d:mssqldata… to keep it same as physname path. Is that correct?
BTW, does this database have to be detached from the source server? Thanks,
– Kathy

Yes, if you want to use attach you have to dettach previusly.
If there other database running with the same test?.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Check the user account has required privileges on the specified path. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I use the following way to do it:
1. I create DB named TEST
2. I copy the MDF to the location d:dataTEST1_DATA.MDF
3. exec sp_detach_db @dbname=’TEST’
exec sp_attach_single_file_db @dbname=’TEST’,
@physname=’d:dataTEST1_DATA.MDF’ then I got error message: "Create Database aborted. Device aviation error. ‘d:mssqldataTEST1_LOG.LDF’ maybe incorrect. Thanks,
– Kathy

Ensure you’ve deleted the previous .LDF file from the specified path, as this SP_ATTACH_SINGLE_FILE_DB will recreate Log file afresh and also check the specified path exists. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>