I am using SQL Server Express edition, so there is no features for backup plan, I want to take databases backup using bat script, and create a scheduler to be run everyday. I have created .bat file but need to place a time stamp at the end of backup file like test_13_April_2017.

cd "C:\Program Files\Microsoft SQL Server\110\Tools\Binn"
SqlCmd -S SERVER02 -Q "Backup Database testdb To Disk='C:\backup\testdb.bak'"

Is there is any way to give time stamp at the end of every DB backup.


I'd recommend you look at some of the freely available backup code. Here are two possible options I would recommend, as both work on SQL Server Express Edition.

1) Ola Hallengren's backup code.

You can install Ola's code in a database on your server (ex, in a "DBA" utility database), and then backing up the databases will be done by executing a stored procedure. The stored procedure takes care of putting date/time stamps in the file name, and can also take care of cleaning up old backups (and more).

Then, you can use a PowerShell (or cmd file) to call Invoke-SqlCmd (or sqlcmd) to simply call the procedure with the proper parameters.


Invoke-SqlCmd -ServerInstance SERVER02 -Database DBA -Query "EXEC dbo.BackupDatabase @Databases='testdb'"


SqlCmd -S SERVER02 -Q "EXEC DBA.dbo.BackupDatabase @Databases='testdb'"

2) The Dbatools PowerShell Module

This module includes a simple Backup-DbaDatabase command that will perform the backup and include the date/time stamp in the file name. This has fewer bells & whistles than Ola's procedure, but if you don't need the bells & whistles, this simple solution might be just what you need.

Backup-DbaDatabase -SqlInstance SERVER02 -Databases testdb -Type Full -BackupDirectory \server2\backups

You just need to make the backup location a variable and load that up with the current datetime.

Sqlcmd -S SERVER02 -Q "DECLARE @BackupLocation NVARCHAR(2000) = 'C:\Backup\testdb ' + REPLACE(CONVERT(VARCHAR(20), GETDATE(), 120) + '.bak', ':', ''); BACKUP DATABASE testdb TO DISK = @BackupLocation;"

Something like this will work:

FOR /F "TOKENS=1,2 eol=/ DELIMS=/ " %%A IN ('DATE/T') DO SET mm=%%B
FOR /F "TOKENS=1,2 DELIMS=/ eol=/" %%A IN ('echo %CDATE%') DO SET dd=%%B
FOR /F "TOKENS=2,3 DELIMS=/ " %%A IN ('echo %CDATE%') DO SET yyyy=%%B
SET date=%mm%%dd%%yyyy%
cd "C:\Program Files\Microsoft SQL Server\110\Tools\Binn"
SqlCmd -S SERVER02 -Q "Backup Database testdb To Disk='C:\backup\testdb"%date%".bak'"

Variables in CMD

DateTime in CMD

Side note, you should check out Ola or Minion to see if they can make your backup needs more simple.

You can run jobs and things on Express, you don't get the GUI to do it though. Also, maybe there's a circumstance where you can use SQL 2016 Dev instead as that's similar to enterprise but free for development instances only?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.