Backup script

Hi,

I'm currently working on a backup script for my MSSQL databases. I have managed to make a working backup command but I have one problem.
I need every new backup file to have a uniqe filename, for example backup_UNIQE.bak. How can I make a variable that can do this?

Alexander

Here's the basis of one that can be used which uses the date as a unique identifier:

Code Snippet

declare @.filename nvarchar(2000)

set @.filename = 'c:\yourbackupdirectory\dbname_bk_' + convert(varchar,getdate(),112) + '.bak'

backup database northwind
to disk = @.filename

HTH!

|||

Hi,

Can you stick a date on the end of it? See below and then pass the filename into you script somehow.

declare @.datet varchar(10), @.filename varchar(20)

set @.datet = convert(varchar(10),getdate(),112)

set @.filename = 'backup'+@.datet+'.bak'

print @.filename

will give you

backup20070824.bak

You could always add the time to it :

set @.datet = replace(convert(varchar(8),getdate(),114),':', '')

Any help to you?

Cheers

Matt

|||Thank you both! This worked like a charm!

Matt Tolhurst:

Can you please explain this one: set @.datet = replace(convert(varchar(8),getdate(),114),':', '') ?

I'm not TSQL coder yet, and I would appricitate it very much!

- Alexander
|||

Alexander Davidsen wrote:

Can you please explain this one: set @.datet = replace(convert(varchar(8),getdate(),114),':', '') ?

Hi,

That bit is simply taking the time part of the getdate function and removing the colons, as you don't really want to put colons in the file name.

declare @.datet2 datetime, @.datet varchar(10), @.filename varchar(20)

set @.datet2 = getdate()

print @.datet2 --

Aug 24 2007 11:07AM

set @.datet = convert(varchar(10),@.datet2,112)

print @.datet

20070824

here is the time part:

set @.datet = convert(varchar(8),@.datet2,114)

print @.datet

11:07:44

set @.datet = replace(convert(varchar(8),@.datet2,114),':', '')

print @.datet

110744

Any clearer?

Matt

|||Thanks Matt

This made it alot clearer!

Have a nice weekend!

- Alexander