question

mssql.sree avatar image
mssql.sree asked

Database backups for prod to dr without creating maintenance jobs in production

I had two servers(SQL 2005) A and B. B is Production server and A is DR. I want to take backups for B server databases from A server. We can not create any maintenance plans or jobs in B production server. We can create any jobs in DR server to take backups for Prod server. Is it possible? both are in different domain. If the servers are in same domain is it possible? and please send the procedure. Please help me asap. Thanks, sree
backupsql 2005
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This web site runs based on your votes. Please indicate all the helpful answers below by clicking on the thumbs up next to them. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
raadee avatar image
raadee answered
To solve your problem you can use Maintenance Plans on server A. If you create a empty maintenance plan (right click - new maintenance plan) you see the manage connection option. In mange connection you can specify what server you want to backup and you can use SQL authentication. So basically: - Create a SQL Authentication account that has the right to take backups on server B - Create a Maintenance PLan on server A, add a connection that points to Server B and add the created user/password for the account that you created on server B. - Add a Backup database task, choose the connection that you created that points to server B and complete all the steps. And I would like to recommend not to use Maintenance Plans, it works but I would rather use [Ola Hallengrens maintenance solution][1] since it is way more flexible. [1]: http://ola.hallengren.com
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Raadee.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
To take a backup simply requires running the [BACKUP DATABASE command][1]. Make sure you use the COPY_ONLY part of the command (documented in the link) to ensure you don't mess with any existing differential backups. That's the easy part. Done. Now, you have to have cross-domain access from the account that is going to call that command. You'll have to set that up. There's not a script I can provide to do it for you. Also, backing up across a domain like this is likely to take a long time. There's a lot of latency involved in hopping across domains. A better approach might be to have the backup process be called and run locally on the prod server and then have the resulting backup file copied over in a separate step. You can schedule this using the Windows Scheduler. But that's the account you'll have to use for your security. It'd be much easier to just create an Agent Job. I'm unclear as to why that's not possible. [1]: https://msdn.microsoft.com/en-us/library/ms186865.aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Grant
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.