Using SQL 2008 DB copy function

  • Hey,

    I'm trying to copy a database from SQL Server A to SQL Server B.
    I wanted to use the builtin copy mechanism/feature because that way I can schedule the task.

    The creation of the copy jobs works fine and they do work great for simple databases.
    But for our big ERP database which has some custom users/roles I'm getting errors which I can't resolve.

    The error messages always have something to do with user or role creation. Which is really strange, because in the copy task definition I always disable that the task copys Logins.

    Error1

    Message: ERROR : errorCode=-1073548784 description=Executing the query "CREATE USER [MsAccessReader] FOR LOGIN [MsAccessRe..." failed with the following error: "'MsAccessReader' is not a valid login or you do not have permission.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    After that I created this user by hand, that seemed to resolve this error, but then I got a new one.

    Error2

    Message: ERROR : errorCode=-1073548784 description=Executing the query "sys.sp_addrolemember @rolename = N'MsAccessReaderR..." failed with the following error: "The role 'MsAccessReaderRole' does not exist in the current database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    Creating this role by hand didn't resolve the error.

    So now I'm curious why the task is trying to copy users/roles.

    thanks!
    Dominik

  • Dominik

    My advice is to use backup and restore.  It'll be a lot quicker for a big database and it's not susceptible to the issues you're seeing here.  Don't forget to reassociate users based on SQL logins as a final step.  Yes, it involves manual scripting, but you only need to do that once, and you end up with a process that is quick and resilient.

    John

  • Hi John,

    that was my first idea, but I preferred the onboard method because I thought it would be quicker.

    Backup/Restore requires multiple steps.
    1. Backup on the Source Server to a local drive (because thats the SQL requirement).
    2. Copy to the Destination Server
    3. Restore on the Destination Server
    Every step is depended on each other, so you have to implement high pauses between the jobs.
    Because you can't start the copy job before the backup finishes. We are talking about a 800gb database which takes roughly 35 minutes to back up.
    Also the database grows by time, so in order for it to be resilent I have to factor in that the filesystem is probably slower on some days and the growth.
    And im writing my data 2 times, one time the backup, 2nd time the copy job.
    What makes you think that backup/restore can be faster? Am I thinking about this the wrong way? Is there a possibility to copy over network with an SQL Agent task (rather than a scheduled copy.bat)?

    thanks,
    Dominik

  • Dominik

    You don't have to back up to a local drive - you can back up to a network share, thus rendering the copy step unnecessary.  Even if you can't do that, backing up with compression and restoring from the compressed backup (ou are using compression, right?) has got to be quicker than copying 800GB of data row by row, which is (I believe) what the copy job does.  Don't have multiple jobs - use a single job with several steps, and then you don't need to leave gaps.  Yes, you can use a CmdExec job step to copy across the network.  You can write a PowerShell script to do the copy, but the easiest way to invoke it would still be from a CmdExec job step.

    Please don't take my word for any of the above - do it your way, do it my way, then compare.

    John

  • John Mitchell-245523 - Thursday, April 13, 2017 3:44 AM

    ...You can write a PowerShell script...

    This.
    And do it using the backup & restore mechanisms outlined. That's what they're there for. The copy database thing is... really poor. You could do a better job on your own, again with PowerShell.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I didnt know that it was possible to backup to a network drive.
    Now I found the corresponding technet article on how to do it and it works right away.
    Thanks for pointing me to this.

    Dominik

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply