append variable to name.

[jstephens]

I am trying to create a backup of my transactional logs. what I want is to create a variable with the date and time. Then use that variable as part of the backup name.

Here is what I got.

declare @bkupdate datetime
set @bkupdate = getdate()

BACKUP LOG CA001 TO DISK = 'S:\AZTARGP\CA001\' + @bkupdate + '_TRNLOG.trn' WITH RETAINDAYS = 1


but it does not work. It gives me a syntax error.

[exterminator]

Try with a simpler name without concatenation.. that will help you know if its the concatenation thats creating the problem or if the backup command is faulty.

[jstephens]

I think I see what you are saying and I did. When I do it it just uses the name @bkupdate for the file name.

so the file name becomes @bckup_TRNLOG.trn.

If I do it with out the variable It works just fine. IE


BACKUP LOG CA001 TO DISK = 'S:\aztargp\CA001\CA001_TRNLOG.trn' with retaindays = 1


The filename shows up as CA001_TRNLOG.trn. So I am pretty sure the syntax is correct. I just don't know how to input the value of the variable into the logfile name. That way each logfile will have a date and time to the log file name.

[Shuja Ali]

You could try casting your date to varchar.
declare @bkupdate datetime
set @bkupdate = getdate()

BACKUP LOG CA001 TO DISK = 'S:\AZTARGP\CA001\' + Cast(@bkupdate As varchar)+ '_TRNLOG.trn' WITH RETAINDAYS = 1

And can you run Select GetDate() and show us the result.

[cjard]

the other option you have is to output to a fixed filename and write a very small, simple .net app that uses a filesystemwatcher to look for the newly created file and rename it when it is finished.

[jstephens]

Thanks guys I figured it out. this is what I got.


declare @bkdbName varchar(50)
declare @bkName varchar(100)

set @bkdbName = 'CA001db' + Convert(varchar(50),getdate(),110)
set @bkName = 'S:\aztargp\CA001\' + @bkdbName + '.trn'

BACKUP LOG CA001 TO DISK = @bkName with INIT