How to schedule a SQL Server 2016 database backup

Question

How to schedule a SQL Server 2016 database backup? The backup will be run every week.

TIA

Take a look at [this](http://stackoverflow.com/questions/14616764/sql-database-automatic-back-up) — Gurwinder Singh, Dec 30 '16 at 03:26
To run the backups according to your schedule you can simply use this solution https://sqlbak.com — Alexandr Omelchenko, Jan 20 '17 at 09:53

score 3 · Accepted Answer · edited Aug 08 '18 at 13:34

3

If you are using SQL Server Express, you can't use SQL Agent, so you must do it using a script (.bat or another) and schedule it with Windows Schedule task (or another program).

If you have another version, you can create a Maintenance plan for a full backup and then with SQL Agent create a Job to run it.

See this answer for more details

edited Aug 08 '18 at 13:34

Andrew Myers

2,754
5
32
40

answered Dec 30 '16 at 15:38

M84

727
6
14

I created the following (I'll post it as a new question) – Ben Tam Jan 03 '17 at 07:05

score 2 · Answer 2 · edited Aug 08 '18 at 13:31

2

You can create the backup script like below for you database backup:

BACKUP DATABASE your_database TO DISK = 'full.bak'
BACKUP DATABASE your_database TO DISK = 'diff.bak' WITH DIFFERENTIAL 
BACKUP LOG your_database TO DISK = 'log.bak'

Schedule it as per your requirement.

You can also use the Maintenance Plan Wizard; please refer to this link for reference:

https://msdn.microsoft.com/en-IN/library/ms191002.aspx?f=255&MSPPError=-2147217396

edited Aug 08 '18 at 13:31

Andrew Myers

2,754
5
32
40

answered Dec 30 '16 at 03:35

Shankar Walvekar

21
2

Should I create a text file with the following statements: – Ben Tam Dec 30 '16 at 06:28
BACKUP DATABASE your_database TO DISK = 'full.bak' BACKUP DATABASE your_database TO DISK = 'diff.bak' WITH DIFFERENTIAL BACKUP LOG your_database TO DISK = 'log.bak' – Ben Tam Dec 30 '16 at 06:30
Then put the file under Windows Scheduler to make it run periodically. – Ben Tam Dec 30 '16 at 06:31
About the Maintenance Plan Wizard, when I create a new maintenance plan, it ask me to enable the 'Agent XPs' component using the sp_configure procedure. Could you show me the syntax? – Ben Tam Dec 30 '16 at 06:36

score 0 · Answer 3 · answered May 11 '18 at 13:12

0

Assuming it's not Express, you create a backup job, which you can do in SQL Server management Studio, and then schedule that backup job: https://learn.microsoft.com/en-us/sql/ssms/agent/schedule-a-job?view=sql-server-2017#SSMS

answered May 11 '18 at 13:12

Beck

1

score 0 · Answer 4 · answered May 14 '19 at 12:54

0

Check this opensource backup windows service MFSQLBackupService, it did a great job for me. It performs daily backup, but it is too easy to patch the weekly job.

answered May 14 '19 at 12:54

peter bence

782
3
14
34

How to schedule a SQL Server 2016 database backup

4 Answers4