---
title: "Automated Daily SQL Server Backup with Date-Wise Folders (2 AM)"  
description: "Backing up your database daily is not optional—it’s essential. In this guide, you’ll learn how to automate SQL Server backups, store them in date-wise"  
author: "Anubhav Sharma"  
published: 2026-04-28  
updated: 2026-04-29  
canonical: https://www.mindstick.com/articles/342094/automated-daily-sql-server-backup-with-date-wise-folders-2-am  
category: "database"  
tags: ["database", "sql server"]  
reading_time: 3 minutes  

---

# Automated Daily SQL Server Backup with Date-Wise Folders (2 AM)

[Backing up](https://www.mindstick.com/forum/158359/what-are-some-common-techniques-for-backing-up-and-restoring-sql-server-databases) your database daily is not optional—it’s essential. In this guide, you’ll learn how to **automate SQL Server backups**, store them in **date-wise folders**, and schedule everything to run **every day at 2 AM**.

## Why Date-Wise Backup Folders?

Instead of dumping all `.bak` files in one location, organizing backups like this:

```plaintext
C:\SQLBackup\
   ├── 2026-04-28\
   ├── 2026-04-29\
   ├── 2026-04-30\
```

gives you:

- Clean structure
- Easy restore by date
- Better maintenance & cleanup

## Step 1: Backup Script with Date Folder

Here’s a [dynamic SQL](https://www.mindstick.com/interview/623/what-is-dynamic-sql) script that:

- Creates a folder based on today’s date
- Saves the backup inside it

```plaintext
DECLARE @BackupPath NVARCHAR(500)
DECLARE @Date NVARCHAR(20)
DECLARE @Cmd NVARCHAR(1000)

-- Format date (yyyy-mm-dd)
SET @Date = CONVERT(VARCHAR(10), GETDATE(), 23)

-- Folder path
SET @BackupPath = 'C:\SQLBackup\' + @Date + '\'

-- Create folder
SET @Cmd = 'mkdir "' + @BackupPath + '"'
EXEC xp_cmdshell @Cmd

-- Backup database
BACKUP DATABASE YourDatabaseName
TO DISK = @BackupPath + 'YourDatabaseName.bak'
WITH FORMAT, INIT, NAME = 'Daily Full Backup', COMPRESSION;
```

## Step 2: Enable xp_cmdshell (if not enabled)

> Required to create folders from SQL (use carefully in production)

```plaintext
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
```

## Step 3: Create SQL Server Agent Job

- Open **SQL Server [Management Studio](https://answers.mindstick.com/qa/93946/how-to-rename-and-drop-a-database-form-sql-server-using-command-and-management-studio) (SSMS)**
- Go to **[SQL Server Agent](https://answers.mindstick.com/qa/92510/what-is-the-sql-server-agent) → Jobs → New Job**
- Name: `Daily Database Backup`

## Step 4: Schedule at 2 AM

- Go to **Schedules → New**
- Frequency: **Daily**
- Time: **02:00:00 AM**
- Now your backup runs automatically every night.

## Step 5: Auto Delete Old Backups (Optional)

To avoid [disk space](https://answers.mindstick.com/qa/109058/why-does-my-computer-keep-showing-low-disk-space) issues, delete backups older than 7 days:

```plaintext
EXEC xp_cmdshell 'forfiles /p "C:\SQLBackup\" /s /d -7 /c "cmd /c rd /s /q @path"'
```

## Step 6: Restore Example (From Date Folder)

```plaintext
RESTORE DATABASE YourDatabaseName
FROM DISK = 'C:\SQLBackup\2026-04-29\YourDatabaseName.bak'
WITH REPLACE;
```

## Best Practices

- Use **COMPRESSION** to reduce file size
- Store backups on **separate drive/server**
- Monitor job status in SQL Server Agent
- Avoid using `xp_cmdshell` in high-security environments
- Consider **PowerShell or maintenance plans** for enterprise setups

## Common Mistakes

- Not testing restore process
- Keeping all backups in [one folder](https://www.mindstick.com/forum/159900/how-can-i-copy-one-file-from-one-folder-to-another-within-an-azure-fileshare-using-c-sharp)
- No cleanup → disk full
- SQL Server Agent not running

## Final Thoughts

Automating backups with **date-wise folders + scheduled jobs** gives you a **clean, reliable, and production-ready [backup system](https://answers.mindstick.com/qa/50038/i-don-t-have-a-backup-system-what-do-you-recommend)**. Running it at **2 AM** ensures minimal load and zero manual effort.

---

Original Source: https://www.mindstick.com/articles/342094/automated-daily-sql-server-backup-with-date-wise-folders-2-am

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.