How to Backup SQL Database with Date in File Name

How can I save SQL backup with date in filename?

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)
• Script to backup all SQL databases and add dates to filenames
• How to schedule SQL backup with date in filename?
• Easier way to schedule SQL backup and restore to any precise date

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.

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.

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.

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.

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

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.

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:

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.

• 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  -> Authentication to validate the database instance. You can choose Windows Authentication or SQL Authentication.

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.

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.

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

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

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.
• Archive: It can archive backup versions to an Amazon S3 cloud storage.

◉ 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.

◉ You can choose which date of the backup to restore, or specifically which databases in the backup 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 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.