Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Add timestamp to sql backup file 1

Status
Not open for further replies.

johnc83

Technical User
Jan 29, 2008
154
GB
Hi all.

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

Code:
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
 
Code:
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
 
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top