How to Backup SQL Database with Date in File Name

How can you save SQL backup with date in filename? Here are some scripts proven to work, and the steps to automate them. Or you can try an easier alternative.

Delia

By Delia / Updated on June 13, 2023

Share this: instagram reddit

How can I save SQL backup with date in filename?

 

I am creating a SQL query script to backup a database, which I have done fine. But now I have been asked to put a date and time stamp at the end of the .bak file. Any ideas on how the can be done?

Normally you can specify the filenames yourself when using SSMS GUI or T-SQL to backup database. But if there are a large number of databases, or you want to perform backup tasks regularly and keep different versions of bak files (create daily database backups with unique names in SQL Server, for example), you may need a more automated and intelligent way to add date to filename of SQL backup.

To do this, the most common method is using effective T-SQL scripts. Or you can turn to a specialized SQL backup tool to schedule SQL database backup and revert to a previous date intuitively.

Keep reading and pick the way you prefer:

SQL backup command with date in filename (one database)

To back up one specific SQL database with date, you can use SQL backup command with date in filename, such as:

DECLARE @FileName varchar(1000)
SELECT @FileName = (SELECT 'filepath\databasename' + convert(varchar(500), GetDate(),112) + '.bak')
BACKUP DATABASE databasename TO DISK=@FileName

Fill in your own backup path and database name, and copy the script to a new query window. After executing it, you will get a bak file with a name consisting of database plus backup date.

backup database with date

Script to backup all SQL databases and add dates to filenames

In many cases you may want to back up not just one or two databases, but all the databases of an instance, then the following script can save you a lot of time:

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify database backup directory e.g. 'D:\backup\'
SET @path = 'backuppath'

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Execute the command, and it will back up all your non-system databases with dates and even specific time points in filenames. You can also exclude other databases you don’t want to back up.

backup all databases with date

How to schedule SQL backup with date in filename?

Now you've known how to perform SQL backup and add date to filename. Here's another possible scenario: you want to automate the operation, like creating daily database backups with unique names in SQL Server, but it is difficult to achieve this with only scripts. Therefore, you can use SQL Server Agent.

Schedule backup with SQL Server Agent

With this tool you can add a schedule to certain T-SQL commands, but please note, it’s only available in non-Express editions of SQL Server.

1. Make sure SQL Server Agent is enabled, otherwise, you need to right-click it and Start it. Then choose New > Job… to get started.

sql server agent

2. When the New Job window pop up, give it a name and description to the job first

3. In Steps tab, click New… and enter the Step name. Then you can copy the script you want to automate to the Command section, and click OK to confirm it.

enter commands

4. Now move to Schedules tab, click New… to set up a new schedule for the backup script you just entered.

add schedule

5. Click OK to create the job. Then you can find the new job under SQL Server Agent > Jobs, and choose Start Job at Step… to test it immediately.

What if you are running SQL Server Express editions?

As earlier mentioned, SQL Server Agent is not applied to Express editions. If that’s your case, maybe you can try the combination of batch file and Windows Task Scheduler.

Open your text editor, copy the backup commands into it. Note the batch script to backup SQL database here is different from the T-SQL script used in SSMS, you can try this one:

@ECHO OFF
SETLOCAL
REM Get date in format YYYY-MM-DD
FOR /F "tokens=1,2,3,4 delims=/ " %%A IN ('Date /T') DO SET NowDate=%%D-%%B-%%C

REM Build a list of databases to backup
SET DBList=D:\DBList.txt
SqlCmd -E -S DESKTOP-AC51C0M\MSSQLSERVER_01 -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('master','model','msdb','tempdb')" > "%DBList%"

REM Backup each database, prepending the date to the filename
FOR /F "tokens=*" %%I IN (%DBList%) DO (
ECHO Backing up database: %%I
SqlCmd -E -S DESKTOP-AC51C0M\MSSQLSERVER_01 -Q "BACKUP DATABASE [%%I] TO Disk='D:\Backup\%%I-%NowDate%.bak'"
ECHO.)

REM Clean up the temp file
IF EXIST "%DBList%" DEL /F /Q "%DBList%"

ENDLOCAL

Save the text as a batch file (with .bat extension). Thus every time you double-click it, the script will backup all your SQL databases with dates in filenames.

And to automate the task, you need to access Task Scheduler via Windows search bar, or by this path: Control Pane (Small icons/Large icons) > Administrator Tools > Task Scheduler.

By using this tool, you can create a basic task, set up a trigger (schedule), and set the action as starting the program (i.e. the batch file you created). Afterwards, the task will be auto executed as scheduled.

task scheduler

Easier way to schedule SQL backup and restore to any precise date

The main benefit of including date in file name is to retain and identify different versions of backups so that the SQL database can be restored to an exact date when needed. For this purpose, you can also use a simpler SQL backup management tool like AOMEI Cyber Backup.

It allows you to auto backup SQL Server on a schedule, and accurately restore database to a previous version of any date (full or differential). With it, you don’t need to use complex T-SQL commands or bother to distinguish backup files. All the operations can be done with simple clicks.

As a centralized backup tool, it not only works for your device, but also supports all the SQL Servers within LAN. Download the 30-day free trial to have a try:

Download Free TrialMicrosoft SQL Server 2005-2022
Centralized and secure SQL backup

Before you create backup tasks with AOMEI Cyber Backup, you should:

  • Run the downloaded .exe file to install AOMEI Cyber Backup, and launch its main interface. Click Source Device -> Microsoft SQL -> Add Microsoft SQL.

Microsoft SQL

  • Choose Download proxy program or Copy link to download the Agent program and install it on the device with SQL Server installed. Then, click Already installed proxy to select the device.
  • Next, click icon -> Authentication to validate the database instance. You can choose Windows Authentication or SQL Authentication.

Add Device

That's it, now you can perform SQL Server auto backup with the following steps:

1. Click Backup Task -> Create New Task to create a Microsoft SQL Backup task.

Backup Type

2. Click on Device Name to specify the instance and databases you want to back up. You can select one or multiple databases as you need.

Select Database

3. Select a Target storage to save your SQL backups, it could be a local path or network path.

Choose Target

4. Set up a backup Schedule to run the SQL database backup daily/weekly/monthly, and choose the backup method as Full/Incremental/Differential.

Schedule Backup

5. Click Start Backup to execute the task. Once it's created, you will find it in the Backup Task tab.

✍More useful features:

  • Backup Cleanup helps you to delete older backup version automatically and therefore save storage space. 
  • Email Notification enables you to receive email notifications when the task is abnormal or successful.

â—‰ When you want to restore from a backup, just find the task in Tasks tab, and choose to Restore it from the upper-right menu.

Restore

â—‰ You can choose which date of the backup to restore, or specifically which databases in the backup to restore.

Select Content

Conclusion

To facilitate the management and accurate restoration, you may want to save SQL backup with date in filename, and this can usually be done with T-SQL commands.

But if you don’t feel like to use scripts, or find it complicated to automate the tasks, AOMEI Cyber Backup is an alternative worth trying. It makes it easier to backup multiple databases, schedule backups, restore databases to a certain date, and even allows you to restore SQL database to another server directly.

Delia
Delia · Editor
Delia owns extensive experience in writing technology-related blog posts, and has been a part of AOMEI since 2020 to provide expertise in data security and disaster recovery. She works with Windows operating systems, SQL databases, and virtualization platforms such as VMware and Hyper-V, specializing in troubleshooting and advising on data protection and migration.