By Alison / Last Updated June 20, 2022

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

 

I want to make full database backup of my server's all databases with unique name daily. For that I have an idea to keep timestamp which will make database copy separate. How can I do this. I don't know much about these types of SQL Backup JOBS.

- stackoverflow.com

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.

Start SQL Server Agent

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

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.

Backup Database 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).

Select Database

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.

Create a backup file for every database

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

Subplan schedule

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

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…

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.

700

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.

Frequency and duration

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.

Start job at  step

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.

Create basic task

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

Name this task

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

Select frequency

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

Start time

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

Start a program

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

Browse

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 Centralized Backupper Database can be a very good alternative.

AOMEI Centralized Backupper Database 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 or 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.

In addition, specially designed for enterprises, it allows one specialist to centrally manage and operate backups of all laptops, workstations, and servers within LAN from one single computer. You can even restore SQL backups to new database on another server. In a word, it can enhance efficiency and reduce cost greatly.

Here’s a 30-day free trial for you:

Download Free TrialWin PCs & Servers
Secure Download

How to Backup and Restore Daily Backups with Unique Names

To properly use Centralized Backupper Database, especially if you want to manage multiple computers within LAN, you need to go to the Computers page first. Click Install client program on the upper bar to download Agent on client computers manually or remotely.

Install Client Program

For those computers which download client program manually, you need to request control over them for further operations.

Click Computers > Uncontrolled Computers, select the IPs of single or multiple client computers and click Request Control on the upper bar. A pop-up window will appear on their computers, once all permissions are received you can manage them whenever you like.

Request Control

How to perform SQL Server Auto Backup Every Day:

1. Click Tasks > New Task and select SQL Server Backup to create a new backup task. Moving your cursor to the task name in pop-up window, and you can change it as you like. Then follow the 3 steps beneath the task name.

SQL Server Backup

2. Step 1, click Add Computers to add the controlled client computer you want to back up with.

Add Computers

3. Step 2, click Add, you can select single or multiple SQL instances and databases you want to back up. Click OK to the next step.

Select Databases

4. Step 3, select a location as the destination path. You can click Add Storage to add a share or NAS path as a storage end.

Select Path

Besides, click Settings you can enable encryption, email notification, and compression for backups, there are 2 compression levels you can choose. The higher the level is, the smaller the files are. It can save you more disk space, and time to copy or move them.

Compression

5. Click Schedule next to Settings, you can select to run this task Once, Daily, Weekly, or Monthly. Each option you can select specific days and start time. You can also change it after.

Schedule Daily Backup

Click Advanced you can choose to perform Full or Differential Backup.

Advanced Settings

6. After all these settings, you can click Start Backup to select Only create the task, or Create and execute the task. Once created, the tasks will be listed in Tasks separately for further edit or restore.

Start Backup

How to Restore Specific Backups to Original or New Location:

1. Turn to Tasks page, click Advanced on the upper right of the backup task. Select Restore.

Restore Database

2. Select the client computer to be restored and click Next. In the next tab, you can select the specific databases and backups (full/differential) with unique dates to be restored. Then click Next.

Select Backups

3. Choose to Restore to original location, or Restore to a new location. If you choose the later, you can restore the backup to another instance on controlled computers within LAN.

Besides, you can also choose to Overwrite the existing databases or not.

Restore to a new location

4. After the setup, you can click Start Restore.

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 Centralized Backupper Database Edition 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 File Backup, System Backup, Disk Backup, Partition Backup, Basic Sync, Real-Time Sync, and Mirror Sync. You can quickly accomplish these tasks with simple clicks.