Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Add date to SQL database backup filename

Tags:

sql

sql-server

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!

like image 376
Cdl56 Avatar asked Mar 09 '10 16:03

Cdl56


People also ask

When backing up a database what is added to the file name?

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.


5 Answers

DECLARE @MyFileName varchar(1000)

SELECT @MyFileName = (SELECT '\\ServerToSave\Path\MyDB_' + convert(varchar(500),GetDate(),112) + '.bak') 

BACKUP DATABASE [myDB] TO DISK=@MyFileName ...
like image 76
JonH Avatar answered Oct 04 '22 22:10

JonH


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.

like image 41
Juan Carlos Velez Avatar answered Oct 04 '22 21:10

Juan Carlos Velez


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

like image 41
Raj More Avatar answered Oct 04 '22 22:10

Raj More


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

like image 34
CResults Avatar answered Oct 04 '22 20:10

CResults


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

like image 37
JorgeM Avatar answered Oct 04 '22 22:10

JorgeM