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 Server110円\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.
4 Answers 4
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
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* DELIMS= " %%A IN ('DATE/T') DO SET CDATE=%%B
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 Server110円\Tools\Binn"
SqlCmd -S SERVER02 -Q "Backup Database testdb To Disk='C:\backup\testdb"%date%".bak'"
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?
The PowerShell solution looks more compact and clearer than the batch file. Here is a simple command that, can be executed via the PowerShell console, will perform a backup to a file with a current timestamp.
SqlCmd -Q "BACKUP DATABASE [MyDB] TO DISK='C:\temp\$(Get-Date -Format "yyyy-MM-dd_HH-mm-ss").bak'"
However, PowerShell has a module for working with SQL Server which can be installed via the following command:
Install-Module -Name SqlServer
Then to perform a backup the following command can be used:
Backup-SqlDatabase -ServerInstance "." -Database "MyDB" -BackupFile c:\temp\$(Get-Date -Format "yyyy-MM-dd_HH-mm-ss").bak
Such a command is more descriptive, and does not contain nested quotes :-)
This script can be run from a regular Windows Command Line or a batch script, for this use powershell -Command :
powershell -Command Backup-SqlDatabase -ServerInstance "." -Database "MyDB" -BackupFile c:\temp\$(Get-Date -Format "yyyy-MM-dd_HH-mm-ss").bak
For automation, it's better (and easier) to use a PowerShell script instead of batch.
More examples on PowerShell and batch.
Learn more about the backup-database and restore-database command.