1

I attempted to copy a database, using Microsoft SQL Server Management Studio (SSMS), but right-clicking and following the wizard. I wanted to target server to be the same as the source, so I added "_new" to the database name.

The process failed (unfortunately I missed the error message) and now the source database is missing as well. I found another post on the same topic, which suggests locating and atttaching the .mdf file.

However, when I try to attach the mdf file, I get the following error meesage:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

Error when I try to attach the mdf file.

Luckily this was just a test database, so I didn't lose any data, that couldn't be easily reproduced. But is there a way to recover from this? If it ever happened on a production database, I would like to be prepared.

  • Does the file actually exist? Can the SQL Server account access it? – mustaccio Jan 16 at 13:49
  • @mustaccio as far as I can tell the file exists. Windows shows it and its around 260 MB. I cannot open it in any other way either, though. I am unsure of how to tell, if SQL server can see it...? – Noceo Jan 16 at 13:56
  • When you say "source database is missing as well" you mean it was somehow detached from the source, but not deleted? because if you have a .mdf to attach now, I assume the original source wasn't lost. if that's the case, check Detaching a database also alters file permissions and File permissions change when you “Detach” section of this one. – Ronaldo Jan 16 at 14:46
  • @Ronaldo yes, the file still exists. So your link might be helpful, I will try it when I get to work tomorrow. Thanks! – Noceo Jan 16 at 16:35
  • @Ronaldo that first link solved my issue. If you post it as an answer, I can accept it, to help other people in the future :-). – Noceo Jan 17 at 8:50
1

The process failed (unfortunately I missed the error message) and now the source database is missing as well. I found another post on the same topic, which suggests locating and atttaching the .mdf file.

If the database is no longer listed on the instance, but you can still see the .mdf file, it somehow got detached during this first unsuccessful attempt. The screenshot error says:

CREATE FILE encountered operating system error 5(Access denied.)

The error is related to security and Microsoft states something about security changes during the process of detaching or attaching a database:

File access permissions are set during a number of database operations, including detaching or attaching a database.

In order to fix it, check the permissions of the .mdf file and if you confirm it has really been changed, alter them to allow you to execute the attach procedure or run the attach procedure with the same login that is now the owner of the .mdf file.


DANIEL HUTMACHER described this behaviour very well in his article Detaching a database also alters file permissions and his conclusions are:

  • Detaching a database when logged on with a Windows account will change the file permissions, restricting SQL Server’s service account and only allowing your user to access the files.
  • Detaching a database as “sa” or a SQL Server account will leave the files’ permission sets unchanged.
  • If you’re connected as “sa” or another SQL Server account, SQL Server’s service account needs permissions on any database files you
    want to attach.
  • If you’re connected as a Windows login, you need sufficient permissions on the files to attach them.
2

I have never tried to use SSMS to copy a live database as you have done. But I have copied many, many databases without an issue.

Use the following approach in the future and you will not have the same issue again.

Take a full backup (use COPY_ONLY)

Then restore the backup with a new name.

Your live database will never be detached, so you won't have to try and figure out how reattach it

  • Seems interesting. My goal, in this specific case, wasn't to backup, but to create a copy for some testing purpose. Would this be viable for that as well? – Noceo Jan 16 at 13:58
  • 4
    Yes. Backup/Restore is the right way to copy a production databases. Don't detach your production databases unless you have no other choice. Once you do they are just some files on the disk, and all sorts of bad things can happen. – David Browne - Microsoft Jan 16 at 14:05
  • Thanks. That will be really helpful advise for the future. It doesn’t really solve my issue, so I can’t mark it as the answer, but I still very much appreciate it 🙂 – Noceo Jan 16 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.