Add timestamp to sql backup file 1

[johnc83]

Hi all.

I have a .sql file which backs up my sql express 2005 database and the code is

BACKUP DATABASE [DatabaseName] TO  DISK = N'D:\SharedFiles\My System\Database\Backup\MyDatabase.bak' WITH NOFORMAT, INIT,  NAME = N'MyDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

This works great but I would like to know how to include a date stamp in the filename so when it runs instead of the file being created as 'MyDatabase.bak' it would be 'MyDatabase_Tue05Feb_2200.bak' (Obviously if it was ran on 5th Feb at 22:00pm)

Can anyone advise pls.

Thanks

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[bborissov]

DECLARE @BackUpName NVARCHAR(4000)
SET @BackUpName = 'D:\SharedFiles\My System\Database\Backup\MyDatabase'+REPLACE(REPLACE(CONVERT(varchar(100), GETDATE(), 100),':','_'),' ','_')+'.BAK'
BACKUP DATABASE [DatabaseName] TO  DISK = @BackUpName WITH NOFORMAT, INIT,  NAME = N'MyDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

not tested

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[djj55]

For sorting purposes you might consider using numeric month. Also if the year is important the something like 20080205 would help in a file listing sort.
Just an idea,
djj

 

[johnc83]

Hi Borislav, thanks for the reply.

I tried something similar which gives the same error but I don't think it is with the date format entry. The error is

Incorrect syntax near the keyword 'with'.

It seems it cannot accept something between the file name and the .bak part for example..

'D:\SharedFiles\My System\Database\Backup\MyDatabase'+'HI'+'.BAK'

Produces the same error.

Is it that the + is not being accepted by SQL or something?

Thanks again

John

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express

 

[johnc83]

Forget that, I only took the format for the date from your code but now I have tried your full code it has worked great.

Thanks a lot

John

p.s thanks djj for your input, i'll keep that in mind

.NET 2.0, Visual Studio 2005, SQL Server 2005 Express