By Delia / Last update October 18, 2022

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 centralized management 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 [email protected]

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

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 computer, but also supports all the Windows PCs and servers within LAN. Download the 30-day free trial to have a try:

Download Free TrialWin PCs & Servers
Secure Download

After installing the software properly, you can then install Agent package on all the client computers you want to manage. Request Control over them before creating a centralized backup task.

request control

Step 1. In Centralized Backupper Database, navigate to Tasks > New TaskSQL Server Backup.

sql server backup

Step 2. Click + Add Computers to detect the computers with SQL Server database, and select a computer from the list to backup.

add computer

Step 3. Click + Add to detect the instance on selected computer. You can choose any database you want, or even all databases at once.

select database

Step 4. click Add Storage to specify a destination path. The added network share or NAS will be saved for your direct use in the future. Then click Start Backup to execute the task.

select storage

◉ You can also set up a Schedule to automate the task, and choose full backup or differential backup in Advanced tab. The created backup files will be saved in a folder with corresponding date.

schedule settings

◉ 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 sql backup

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

select date and database to restore

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