Create Daily SQL Database Backups with Unique Names [Guide]

Can I Create Daily Database Backups with Unique Names in SQL Server?

Over time, the backups of SQL Server databases can accumulate and take up more and more disk space, making them very difficult to distinguish when restoring. Once when I was in this situation, I wondered can I and how do I create a daily database backup in SQL Server with a unique name, making it easier to restore? And I found that there are many ways to do this.

Here I summarized 4 common ways and their specific steps in this article. Hope it could help you.

4 Ways for SQL Server Daily Backup with Unique Name

Scheduling daily backups and adding specific dates in the file names, is probably the most common type of ways to create daily database backups with unique name in SQL Server. The following ways are all based on this principle. Here I made a table showing how they meet the basic needs. You can click the directory index on the left to jump to the specific steps.

Backup Ways	Backup Multiple Database Once	Need Scripts	Support SQL Server Versions
Maintenance Plan	√	×	Non-Express Version
SQL Server Agent	√	√	Non-Express Version
Task Scheduler	√	√	All Versions
Backup Software	√	×	All Versions

1. Create Maintenance Plans to Schedule Daily Database Backups

1. Launch SQL Server Management Studio (SSMS) and connect to your instance. Right-click SQL Server Agent and select Start to enable this feature.

2.Open up Management menu, right-click Maintenance Plans and select New Maintenance Plan…

3. Click Toolbox next to Object Explorer or on the upper bar, select Back Up Database Task and drag it into the right blank to create a backup task. Then double-click this task.

4. In the pop-up settings window, select Backup type (Full/Differential/Transaction Log) as need.

Select one or more databases you want to back up in Database(s).

In Back up to section, select Create a backup file for every database, and specify a destination location in Folder.

Then you can click OK to finish the setup.

5. Click Subplan Schedule (calendar icon), arrange the backup Frequency and Duration in the pop-up window. Click OK to finish the setup.

6. Save this task and then find it in SQL Server Agent > Jobs, right-click this task and select Start Job at Step…

7. When it’s accomplished, come to the destination folder and you can see the backup is created with a unique filename containing the date.

Note: If you think operations above are complicated, you can also use Maintenance Plan Wizard to create daily database backups with unique names in SQL Server. The steps are basically the same.

2. Use SQL Server Agent to Create Daily Backup with Unique Names

1. Launch SSMS and connect to your instance. Right-click SQL Server Agent and select Start to enable this feature.

2. Open up SQL Server Agent menu, right-click Jobs and select New Job…

3. In the pop-up window, select General page in the left tab, fill in the Name and Description.

4. Turn to Steps page in the left tab, click New… to open New Job Step window.

Name this step, and select Transact-SQL script (T-SQL) in Type.

Input the backup script in Command. Here I provide you 2 scripts for backing up single database, and multiple databases respectively.

Script to Backup Single Database:

-- 1. Variable declaration
DECLARE @path VARCHAR(500)
DECLARE @name VARCHAR(500)
DECLARE @pathwithname VARCHAR(500)
DECLARE @time DATETIME
DECLARE @year VARCHAR(4)
DECLARE @month VARCHAR(2)
DECLARE @day VARCHAR(2)
DECLARE @hour VARCHAR(2)
DECLARE @minute VARCHAR(2)
DECLARE @second VARCHAR(2)

-- 2. Setting the backup path
>SET @path = 'filepath'

-- 3. Getting the time values
SELECT @time = GETDATE()
SELECT @year = (SELECT CONVERT(VARCHAR(4), DATEPART(yy, @time)))
SELECT @month = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mm,@time),'00')))
SELECT @day = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(dd,@time),'00')))
SELECT @hour = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(hh,@time),'00')))
SELECT @minute = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mi,@time),'00')))
SELECT @second = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(ss,@time),'00')))

-- 4. Defining the filename format
SELECT @name ='databasename' + '_' + @year + @month + @day + @hour + @minute + @second
SET @pathwithname = @path + @name + '.bak'

--5. Executing the backup command
BACKUP DATABASE [databasename]
TO DISK = @pathwithname WITH NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10

Note:
1. In the first part Variable declaration, all necessary variables are listed, you can delete them according to what you want to include in the filename.
2. In the second part Setting the backup path, you need to specify a destination location. For example: D:\Backup\
3. In the third part Getting the time values, the GETDATE, CONVERT, FORMAT and DATEPART functions are used to assign specific values to each variable.
GETDATE: get the current date and time, and assign them to the @time variable
CONVERT: extract the specific value (year, month, day, hour, minute, second) for each variable.
FORMAT: format the values from the DATEPART function to two-digit strings. Then the single-digit months, dates, hours, minutes and seconds will get an additional 0 in front of them.
DATEPART: convert all results VARCHAR strings.
4. In the fourth part Defining the filename format, the output format of filename will be defined. You can custom the variables and their order as demand.
5. In the fifth part Executing the backup command,
NOFORMAT: Specifies that the backup operation will not overwrite existing media header and backup sets on the media volumes. This is the default behavior.
NOINIT: Indicates that the backup set is appended to the specified media set, preserving existing backup sets. This is the default behavior.
SKIP: Disables backup set expiration and name checking performed by the BACKUP statement, to prevent overwriting backup sets.
REWIND: Specifies that SQL Server releases and rewinds the tape. This is the default behavior.
NOUNLOAD: Specifies that after the backup operation the tape remains loaded on the tape drive.
STATS: Displays a backup execution progress message each time another percentage completes.

Script to Backup All Non-System Databases

--1. Variable declaration
DECLARE @path VARCHAR(500)
DECLARE @name VARCHAR(500)
DECLARE @filename VARCHAR(256)
DECLARE @time DATETIME
DECLARE @year VARCHAR(4)
DECLARE @month VARCHAR(2)
DECLARE @day VARCHAR(2)
DECLARE @hour VARCHAR(2)
DECLARE @minute VARCHAR(2)
DECLARE @second VARCHAR(2)

-- 2. Setting the backup path
SET @path = 'filepath'

-- 3. Getting the time values
SELECT @time = GETDATE()
SELECT @year = (SELECT CONVERT(VARCHAR(4), DATEPART(yy, @time)))
SELECT @month = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mm,@time),'00')))
SELECT @day = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(dd,@time),'00')))
SELECT @hour = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(hh,@time),'00')))
SELECT @minute = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mi,@time),'00')))
SELECT @second = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(ss,@time),'00')))

-- 4. Defining cursor operations
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') -- system databases are excluded

--5. Initializing cursor operations
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN

-- 6. Defining the filename format
SET @fileName = @path + @name + '_' + @year + @month + @day + @hour + @minute + @second + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor

Note:
1. The first three parts are basically the same as backing up single database.
2. In the fourth part Defining cursor operations and the fifth part Initializing cursor operations, the script will use cursor operations to automatically get the name of each database and backs them up one by one.
3. In the sixth part Defining the filename format, the output format of the backup filename will be defined.

5.Turn to Schedules page, click New… to arrange the Frequency and Duration in the pop-up window. After setup, click OK to save.

6.Right-click the newly created job in Jobs, select Start Job at Step…When it’s accomplished, come to the destination location and you can see the backups with unique filenames.

3. Schedule Daily Backup Batch File via Task Scheduler

1. Input following Script in Notepad or other text editors. Save the file and change the suffix .txt into .bat.

DECLARE @strPath NVARCHAR(200)
set @strPath = convert(NVARCHAR(19),getdate(),120)
set @strPath = REPLACE(@strPath, ':' , '.')
set @strPath = 'filepath' + @strPath + '.bak'
BACKUP DATABASE [databasename] TO DISK = @strPath WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

2. Open the Task Scheduler (you can directly search it in search box), and click Create Basic Task in the right directory.

3. Name this Task, and then click Next to turn to Trigger tab.

4. Select the frequency you want to back up the database, then click Next.

5. Arrange the specific time to start this task. Then click Next.

6. Select Start a Program in Action tab, then click Next.

7. Click Browse… to select the newly created batch file. Then click Next.

8. Then you will see the execution of this task. When it’s done, you can click Finish to accomplish this task. Come to the destination location and you will see the backups with unique name.

4. Use Backup Software to Create Daily Backups with Unique Names

As you can see, scripts can be complex and error-prone, and Maintenance Plan is not available on Express version. So, if you are not familiar with them, or you are running SQL Server Express, I suggest you use professional backup software to create daily backups with unique names. AOMEI Cyber Backup can be a very good alternative.

AOMEI Cyber Backup provides an easy and error-free way to schedule SQL Server backup on basis of daily, weekly or monthly. It allows you to select multiple databases or even instances at once, so you can simply set up a backup task with customizable name, let it to run on its own.

Also, the created tasks can be managed and restored centrally. It enables you to select all or specific databases in the task to restore, and select which full / incremental / differential backup at which time points to restore from. This definitely frees you from the hassle of managing and finding tons of .bak files with complex names. You can even restore SQL backups to new database on another server. In a word, it can enhance efficiency and reduce cost greatly. In addition, it also allows one specialist to centrally manage and operate backups of VMware and Hyper-V virtual machines within LAN.

AOMEI Cyber Backup supports Microsoft SQL 2005-2022, including SQL Express. Next, I will demonstrate how to create a daily database backup for free. You can click the following button to download the freeware.

How to Schedule Daily Backups of SQL Server database with Unique Names

✍ Please note these prerequisites:
▪ Computer with both AOMEI Cyber Backup Agent and Microsoft SQL Server installed
▪ Local disk or network share to store backup files

1. Access to Source Device > Add Microsoft SQL. If the database exists and the version is supported, it will appear automatically. Otherwise, you can click Add Microsoft SQL > Download proxy program, and install the program on the device with SQL Server installed. Then click "Already installed proxy" and select the proxies you want to add.

2. Click "..."-->"Authentication" to validate the database instance. You can choose "Windows Authentication" or "SQL Authentication". Enter the credentials and click "Verify".

3. Go to create a daily SQL server database backup as following:

▪ Click "Backup Task" > "Create New Task" to backup your SQL databases.

▪ Choose backup type as "Microsoft SQL Backup", and you could also try virtual machine Backup.

▪ Enter a special name for the backup task, then go to select the databases you want to backup and specify a location as a storage end.

▪ Select backup methods as full/incremental/differential and specify the backup time as daily/weekly/monthly.

✍ For example, select a backup method as incremental backup and then go to set up start/end time for this backup task.

4. Click Start backup.

Summary

In this article, I provide 4 ways to create daily database backups with unique names in SQL Server SQL. If you think these steps are too tedious and you are not familiar with T-SQL scripts, or if you are running SQL Server Express, I suggest you try using AOMEI Cyber Backup as an alternative to create daily backup with unique name.

It is also a centralized data protecting solution. In addition to SQL Server backup and restore, it also offers features of virtual machine backups. You can quickly accomplish these tasks with simple clicks.