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 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)
• 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 [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.

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 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:

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.

Step 1. In Centralized Backupper Database, navigate to Tasks > New Task > 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.

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

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.

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

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