August 21, 2014 at 3:05 pm
I feel like this is a dumb question, but here we go.
I have a SQL database on Server1 called DB1. I need that entire database copied over to a different server (call it Server2) each night. I'd like to schedule a SQL Agent job to do this.
I'd prefer not to setup a linked server on Server2 so that's why I'm asking this question.
What is the best practices method of accomplishing this? Is the best route to schedule a native backup of DB1 on Server1 and then schedule a restore of that .bak onto Server2? I worry about the job failing if people are connected to DB1 on Server2 at the time of the scheduled restore.
Thank you.
Joel
August 22, 2014 at 5:38 am
hughesj23 (8/21/2014)
I feel like this is a dumb question, but here we go.I have a SQL database on Server1 called DB1. I need that entire database copied over to a different server (call it Server2) each night. I'd like to schedule a SQL Agent job to do this.
I'd prefer not to setup a linked server on Server2 so that's why I'm asking this question.
What is the best practices method of accomplishing this? Is the best route to schedule a native backup of DB1 on Server1 and then schedule a restore of that .bak onto Server2? I worry about the job failing if people are connected to DB1 on Server2 at the time of the scheduled restore.
Thank you.
Joel
Whenever I copy a database between servers, that's exactly how I do it. I create a backup of the database on Server1, copy the backup file to the file system on Server2 and restore the database to Server2. To automate it, you will have to deal with people using the database on Server2 and give them the boot before dropping the database so you can restore it. I know there's the WITH REPLACE option in the RESTORE DATABASE command. In theory, it should work, but I've never tried automating it before.
The one "gotcha" you're going to have to deal with is the SID mismatch problem. If the servers are on the same domain, the database users tied to Windows accounts will be fine. The SQL users will not be fine. The SIDs of the database users won't match the SIDs of the logins on Server2. I've dealt with it by dropping and recreating the database users on Server2. I've read that there's a way to fix the SID mismatch by altering the users instead of recreating them, but I've never done it.
August 22, 2014 at 10:36 am
Thank you sir. I am going to try and automate that same process. And thank you for the tip on the SID mismatch. Luckily, all of the users associated with this particular db are windows authentication.
August 22, 2014 at 10:57 am
No problem. Glad I could help.
August 22, 2014 at 10:57 am
Hi,
1.Create a job take a backup of the database on Server A
2.Create a job on Server A to Copy the backup to share in the B.
3.Create a job to restore the database from the share.
4.Make sure that one account is able to login to the database
5.Send a mail to users that database restore is complete and available for use.
In case users/Login’s are different then you need to script the users in server B before the restore the database. Once database is restored then drop the login’s in server B that came as part of the restore.
Then stored the login’s that we have scripted earlier.
There are lot of scripts available on the net on how to extract the users.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply