I'm using the below to backup a db from a SQL job. Can someone tell me how to add the current date to the output filename? Preferably in YYYYMMDD format.
BACKUP DATABASE [myDB] TO DISK = N'\\myPath\myDB.bak' WITH NOFORMAT, INIT, NAME = N'myDB', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
Thanks!
It includes the original file name, plus the date of the backup. This can be useful when trying to locate a suitable backup file to restore the database.
DECLARE @MyFileName varchar(1000)
SELECT @MyFileName = (SELECT '\\ServerToSave\Path\MyDB_' + convert(varchar(500),GetDate(),112) + '.bak')
BACKUP DATABASE [myDB] TO DISK=@MyFileName ...
If you want to include the date and time, so you can use:
DECLARE @MyFileName varchar(200)
SELECT @MyFileName='\\ServerToSave\Path\MyDB_' + REPLACE(convert(nvarchar(20),GetDate(),120),':','-') + '.bak'
BACKUP DATABASE [myDB] TO DISK=@MyFileName ...
The 120 in the Convert
gives you the yyyy-mm-dd hh:mi:ss(24h)
The REPLACE
function is necessary because the filename can not have the :
character.
Use the following
DECLARE @BackupFileName varchar(20)
SELECT @BackupFileName = '\\ServerName\SharedFolder\DatabaseName_' + CONVERT (VarChar, GetDate(), 112) + '.bak'
BACKUP DATABASE [myDB] TO DISK = @BackupFileName WITH NOFORMAT, INIT, NAME = N'myDB', SKIP, REWIND, NOUNLOAD, STATS = 10
Read up on Cast and Convert here http://msdn.microsoft.com/en-us/library/ms187928.aspx
Try this.
DECLARE @MyFileName varchar(50)
SELECT '\\ServerToSave\Path\MyDB_' + convert(nvarchar(20),GetDate(),112) + '.bak'
BACKUP DATABASE [myDB] TO DISK=@MyFileName ...
The 112 in the Convert gives you the YYYYMMDD format
Maybe you want to use windows task, just put this code in a .BAT file and add to your Windows tasks:
for /f "tokens=2 delims==" %%a in ('wmic OS Get localdatetime /value') do set "dt=%%a"
set "YY=%dt:~2,2%" & set "YYYY=%dt:~0,4%" & set "MM=%dt:~4,2%" & set "DD=%dt:~6,2%"
set "HH=%dt:~8,2%" & set "Min=%dt:~10,2%" & set "Sec=%dt:~12,2%"
set "fullstamp=%YYYY%%MM%%DD%-%HH%%Min%%Sec%"
set bkfile=D:\bk-sqlserver\dbname%fullstamp%.bak
set path_sqlcmd="C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.exe"
%path_sqlcmd% -S .\SQLEXPRESS -E -Q "BACKUP DATABASE dbname TO DISK='%bkfile%' WITH FORMAT"
It's a bit long, but i think it's a practical solution.
Use sqlcmd for SQL Server 2005 or later
Use osql for SQL Server 2000 or oldies
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With