I've been using the copy database wizard to create a development copy of my database.

Unfortunately, the copy database wizard has repeatedly failed. I suspect the problem is that the sql server agent service account does not have the necessary permissions. I can't verify this because the log file isn't actually being saved. I've tried having it save to the default directory where I know the account running the copy database operations must have permissions and to another location on the server.

Any thoughts about how I can resolve this problem? The new database is showing up but without any tables...

up vote 5 down vote accepted

Please use backup / restore. The copy database wizard is a disappointing sack of, well, let's leave it at that.

  • 1
    Would it be wise to point out that the OP may want to create a COPY_ONLY backup so as not to disrupt the backup sequence? It sounds like it's going to be a backup from production. +1, I agree with your answer by the way. – Thomas Stringer Jun 18 '12 at 18:39
  • @Shark yes, I wasn't getting into the details, just suggesting a better overall approach. A recent enough full backup may (and probably should) already exist, so it may not be necessary to take a new, COPY_ONLY backup. Scope creep. – Aaron Bertrand Jun 18 '12 at 18:48
  • Good point!! Well said. – Thomas Stringer Jun 18 '12 at 18:50
  • @AaronBertrand Can someone please give some good reasons not to use the Copy Wizard? Everywhere I look I see anecdotal advice to avoid it but never details as to why! If I'm moving medium sized non critical databases outside of normal hours, whats the problem? – userSteve Apr 4 '17 at 12:34

As I encountered this problem I suspect it is likely due to an incorrect user id and/or password in the credentials.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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