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.
Asked2017-04-17byKishor 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
Answered2017-04-17byAndy Mallon