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