How to take SQL Server databases backup using .bat script file with time stamp

Question

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.

asked 2017-04-17 by Kishor Kumar

---

Answer

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.

PowerShell:

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

cmd:

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

answered 2017-04-17 by Andy Mallon