How to Schedule SQL Server Database Backups.

Never Lose Data Again: The Definitive Guide to Scheduling Automatic SQL Server Backups and Cleanup

Welcome to Darren’s Tech Tutorials! The Importance of Automated Backups

Hey tech fans! Welcome back to the channel. If you run databases—especially mission-critical ones—you already know that data loss is the single biggest threat to your sanity and your business. Relying on manual backups is a recipe for disaster. What happens if you forget one day? What if your server crashes at 3 AM?

That’s why today, we’re diving deep into SQL Server Management Studio (we’re using 2014, but these principles apply widely) to set up two crucial maintenance plans. By the end of this tutorial, your database backups will be completely automatic—and we’ll also set up a safety net to clean up those old, unnecessary files, saving you valuable disk space. Let’s get started!

Prerequisite: Ensuring the SQL Server Agent is Running

The SQL Server Agent is the unsung hero of scheduling. It’s a Windows service that executes the scheduled jobs we create. If it’s not running, your backups won’t happen! We need to make sure it’s running and set to start automatically.

Here’s how to check and configure the Agent:

1. Open SQL Server Configuration Manager (this is separate from Management Studio).
2. In the left pane, select SQL Server Services.
3. Locate SQL Server Agent ($InstanceName).
4. If the service is stopped, right-click and select Start.
5. Crucially, double-click the Agent service, go to the Service tab, and set the Start Mode to Automatic. This ensures the Agent starts every time your computer or server boots up, guaranteeing your schedules remain active.

Once the Agent is running, we can move on to creating the backup routine itself.

Step 1: Creating the Database Backup Maintenance Plan

We will use the Maintenance Plan Wizard within SQL Server Management Studio (SSMS) to define the backup task and its schedule.

1. Launch the Maintenance Plan Wizard

1. Open SSMS and connect to your database instance.
2. In the Object Explorer, expand Management.
3. Right-click on Maintenance Plans and select Maintenance Plan Wizard.
4. Click Next on the welcome screen.
5. Define the Properties: Give your plan a descriptive name (e.g., Daily_Full_Database_Backup).
6. Select Separate schedule for each task. (This gives us maximum control.)

2. Define the Backup Task

1. On the Select Maintenance Tasks screen, check the box next for Back Up Database (Full). Click Next.
2. Define the Backup Parameters:
   • Database(s): Select the databases you wish to back up (e.g., specific databases, or the catch-all “All user databases”).
   • Backup component: Ensure Database is selected.
   • Backup set will expire: You can set an expiration date if required, but for scheduled jobs, this is often left blank.
   • Backup to: Select Disk.
   • Create a subdirectory for each database: It is highly recommended to check this box. This keeps your backup folder organized and prevents files from overwriting each other if you back up multiple databases.
   • Folder: Specify the path where you want the backup files (.bak) to be saved (e.g., C:\SQLBackups\).

3. Scheduling the Backup

After defining the task parameters, the wizard prompts you to define the schedule.

1. Click the Change button next to the schedule description.
2. Set the Schedule:
   • Schedule Type: Recurring.
   • Frequency: Select Daily.
   • Occurs once at: Set a time when the server usage is lowest (e.g., 2:00 AM).
3. Click OK and then Finish the wizard.

Congratulations! Your SQL Server now has a recurring, automatic backup schedule set up. You can view the scheduled job under SQL Server Agent > Jobs.

Step 2: Creating the Old Backup Cleanup Plan

A successful backup strategy involves two parts: creating the backup and cleaning up the mess afterward. Without cleanup, your hard drive will eventually fill up, causing new backups to fail.

We need a second, separate maintenance plan dedicated solely to deleting old files.

1. Launch the Maintenance Plan Wizard (Again)

1. Right-click on Maintenance Plans and select Maintenance Plan Wizard.
2. Define the Properties: Name this plan clearly, perhaps Weekly_Backup_Cleanup.

2. Define the Cleanup Task

1. On the Select Maintenance Tasks screen, check the box for Maintenance Cleanup Task. Click Next.
2. Define Cleanup Parameters:
   • Delete files: Select Backup files.
   • File extension: Type in .bak.
   • Search folder: Enter the exact same folder path you used in the backup plan (e.g., C:\SQLBackups\).
   • Include subfolders: Crucial! Check this box since our backup plan created subdirectories for each database.
   • Delete files based on the age of the file at the time the task runs: Check this box.
   • Delete files older than: Specify your retention policy. A common setting is 1 Week or 7 Days.

3. Scheduling the Cleanup

The cleanup job should run after a backup has successfully completed, but you usually don’t need it to run as frequently as the backup itself. A weekly cleanup is often sufficient.

1. Click the Change button next to the schedule description.
2. Set the Schedule:
   • Frequency: Select Weekly.
   • Occurs once at: Set this time slightly later than your backup, perhaps 3:00 AM on a Sunday morning.
3. Click OK and then Finish the wizard.

Final Thoughts and Testing

You have now implemented a robust, hands-off database protection strategy. You have a plan creating the safety copies, and a second plan ensuring your server doesn’t run out of space.

Crucial Step: Test It!

Wait for the scheduled time, or right-click the jobs under the SQL Server Agent and select Start Job at Step… to run them manually. Confirm that the backup files are created and that the cleanup job successfully removes the files that meet your age criteria.

If you found this guide helpful and are ready to tackle more advanced SQL Server management, make sure you hit that Like button and Subscribe to Darren’s Tech Tutorials. Your data is safe now—go forth and build amazing things!