CODEWITHSUNDEEP
sql-serverbackup
Ben Tam
Ben Tam

Reputation: 677

How to schedule a SQL Server 2016 database backup

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

TIA

Upvotes: 4

Views: 12314

Answers (4)

peter bence
peter bence

Reputation: 822

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.

Upvotes: 0

M84
M84

Reputation: 745

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

Upvotes: 3

Shankar Walvekar
Shankar Walvekar

Reputation: 21

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

Upvotes: 2

Beck
Beck

Reputation: 1

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

Upvotes: 0

Related Questions