question

nawidahmadnoori-8726 avatar image
0 Votes"
nawidahmadnoori-8726 asked ·

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

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: )

sql-server-general
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered ·

Hi @nawidahmadnoori-8726,

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.

76644-1.png
76626-3.png
76625-2.png
76645-4.png
76627-5.png
76651-6.png


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.



1.png (40.2 KiB)
3.png (38.8 KiB)
2.png (43.6 KiB)
4.png (38.3 KiB)
5.png (38.7 KiB)
6.png (43.5 KiB)
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

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.


· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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: )

0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered ·

Hi @nawidahmadnoori-8726,

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.
73640-1.png

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.

72951-3.png

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.

72898-2.png

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.

72860-4.png

2)Modify the Name and schedule the backup time

72923-5.png

3)Select Maintenance Tasks which you need.

72961-6.png

4)select databases which you need to back up.

72943-7.png

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

74106-s.png

6)Generated the Maintenance Plan.

72971-9.png

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.



3.png (16.5 KiB)
2.png (15.1 KiB)
4.png (12.8 KiB)
5.png (24.0 KiB)
6.png (25.2 KiB)
7.png (39.9 KiB)
9.png (27.5 KiB)
1.png (18.3 KiB)
s.png (36.0 KiB)
· 11 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi CarrinWu-msft,

thanks for your response,


I followed the steps, I just have a problem in step 5, what should I enter in the destination folder.

my shared folder name is WeeklyBackup in the storage server, and I can access it through 172.16.12.78\\WeeklyBackup




0 Votes 0 ·
CarrinWu-MSFT avatar image CarrinWu-MSFT nawidahmadnoori-8726 ·

Hi @nawidahmadnoori-8726, I updated the STEP 5. The destination folder is your shared folder (e.g. \\MININT-8QOBNQC\ShareFolder)

0 Votes 0 ·

But the slashes should be tilted the other way, for instance \\172.16.12.78\WeeklyBackup.

0 Votes 0 ·
Show more comments

Hi CarrinWu-msft,

thanks for your response,

i get the below error in step 5
75127-capture2.jpg


0 Votes 0 ·
capture2.jpg (89.6 KiB)
Show more comments