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 MattThis made it alot clearer!
Have a nice weekend!
- Alexander
