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

Solution 1

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

Solution 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

Comments

[Ben Tam]

Should I create a text file with the following statements:

[Ben Tam]

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]

Then put the file under Windows Scheduler to make it run periodically.

[Ben Tam]

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]

I created the following (I'll post it as a new question)