April 13, 2017 at 2:43 am
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
April 13, 2017 at 3:07 am
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
April 13, 2017 at 3:27 am
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
April 13, 2017 at 3:44 am
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
April 13, 2017 at 5:51 am
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
April 13, 2017 at 5:58 am
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