can this be done - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

can this be done

Author

Topic

PatMc
Starting Member

16 Posts

Posted - 2009-08-21 : 14:38:45

hi all,

I use a simple script to backup my Tlogs to a device. see script below. so you see where the name is NuNames_tlog20090821

I need to go into the script and update the date everytime I run this script. so is there a way i can have the sysdate put into the script automatically so i can run it as a automatic task??

thank you

--If device is missing in sysdvices create a new backup device in the db Backup tool
USE NuNames

GO

EXEC sp_addumpdevice 'disk', 'NuNames_tlog20090821', 'D:\Mssql7\Backup\NuNames_tlog20090821'

GO

BACKUP LOG NuNames to NuNames_tlog

go

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-21 : 14:49:53

Have you tried this?


declare @logicalName sysname
       ,@phisicalName nvarchar(200)
select @logicalName = 'NuNames_tlog' + replace(convert(varchar(10), getdate(), 121),'-','')
       ,@phisicalName N'D:\Mssql7\Backup\NuNames_tlog' + replace(convert(varchar(10), getdate(), 121),'-','')

EXEC sp_addumpdevice 'disk', @logicalName, @phisicalName

Be One with the Optimizer
TG

PatMc
Starting Member

16 Posts

Posted - 2009-08-21 : 15:35:55

Thank you TG

But I an getting an incorrect syntax error near '+' in this line and I have tried to fix it but can't seem to get it.

,@phisicalName N'D:\Mssql7\Backup\NuNames_tlog' + replace(convert(varchar(10), getdate(), 121),'-','')

thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-21 : 15:41:14

woops - I missed the "=" sign. Should be:

,@phisicalName = N'D:\Mssql7\Backup\NuNames_tlog' + replace(convert(varchar(10), getdate(), 121),'-','')

EDIT:


declare @logicalName sysname
       ,@phisicalName nvarchar(200)
select @logicalName = 'NuNames_tlog' + replace(convert(varchar(10), getdate(), 121),'-','')
       ,@phisicalName = N'D:\Mssql7\Backup\NuNames_tlog' + replace(convert(varchar(10), getdate(), 121),'-','')

select @logicalName
       ,@phisicalName

--EXEC sp_addumpdevice 'disk', @logicalName, @phisicalName

OUTPUT:

--------------------------------------------------------------------
NuNames_tlog20090821        D:\Mssql7\Backup\NuNames_tlog20090821

Be One with the Optimizer
TG

PatMc
Starting Member

16 Posts

Posted - 2009-08-21 : 15:43:57

thank you TG

worked liked a charm

Pat

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources