How get back up of database and store it in another server automatically

Question

Hi,

I have two servers, I want to schedule to take the backup of the database and store it in another server automatically.

SQL version:

Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
Sep 24 2019 13:48:23
Copyright (C) 2019 Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: )

Answer 1

Hi @nawid ahmad noori ,

In order to allow SQL to backup directly to a network share, we have to run the SQL Server service as a local account which does have access to network resources. So you can create a same account in Server1 and Server2, and then you can continue to create maintenance plan.

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

First of all, you should apply the most recent cumulative update, which is CU9. This has nothing to do with your question, but I always point this out as a matter of principle. Beware that if your databases have scalar user-defined functions, you should run sp_sqlrefreshmodule on them after the installation of CU9. This is because of some bug fixes related to scalar-function inlining, an improvement in SQL 2019.

Now, over to your question. You can schedule a backup job which runs

BACKUP DATABASE db TO DISK = '\\Otherserver\share\backups\db.bak WITH COMPRESSION, INIT

This requires that the service account for SQL Server has write permission to the share. In many cases, the service account is a local machine account, so it cannot have permissions on other machines. An alternative is to give permission to the machine account DOMAIN\Machine$, but it's not really best practice.

A better alternative is to set up a proxy and run the job as a CmdExec job that run SQLCMD.

As for the backup command, the above will always write to the same file name which is rarely what you want. Typically, you want a new file every time, as you may need to restore an older backup. But if you do this, you also need something to delete very old backups.

While you can roll your own, most people use existing maintenance solutions. There are two main choices:

1. The maintenance plans that comes with SSMS.
2. Ola Hallengren's maintenance solution, http://ola.hallengren.com
   Ola's solution is definitely the one that most people recommend.

If you have further questions, it helps to know if you have a domain or only a workgroup.

Answer 3

Note: I am using SQL server 18.5.1

No, you are not, because there is no such version. 18.5.1 may be the version of SQL Server Management Studio, but SSMS is not SQL Server.

Please post the output of "SELECT @@version" so that we know which version and edition of SQL Server that you have.

Answer 4

Hi @nawid ahmad noori ,

I assume that the ServerA (product environment) and ServerB (store the backups). I recommend that you can follow below steps to create a shared folder and Maintenance Plan to back up database.

1. Create a share folder in ServerB as following steps:

1. Right-click on the folder you want to share. 2)Select Give Access to > Specific people.

3)From there, you can choose specific users and their permission level (whether they can read-only or read/write). After making your selection, click Share.

4)If a user doesn’t appear on the list, type their name into the taskbar and hit Add. You can also share with Everyone.

5)Click Share.

2.Create a Maintenance Plan in ServerA as following steps: 1)Right-click the Maintenance Plans folder and select Maintenance Plan Wizard.

2)Modify the Name and schedule the backup time

3)Select Maintenance Tasks which you need.

4)select databases which you need to back up.

5)According to your situation, change to Shared Folder.

6)Generated the Maintenance Plan.

Best regards, Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it. Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.