help assigning current date to a variable

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

SQL Server 2005 Forums

Transact-SQL (2005)

help assigning current date to a variable

Author

Topic

infinitiguy
Starting Member

3 Posts

Posted - 2009-09-14 : 16:47:55

I am trying to automate a restore process we have, and I want to grab the current date - 1 day and assign it to a variable to use in a backup name.

For example, I know the below prints me what I'm looking for..

print replace(str(DatePart(yyyy, GetDate()), 4), ' ', '0') +
replace(str(DatePart(mm, GetDate()), 2), ' ', '0') +
replace(str(DatePart(dd, GetDate()), 2), ' ', '0') - 1

but what I need to do is get that output and assign it to a variable called backupdate and then incorporate that into the below statement

restore database stuff from disk = '\\backupserver\d$\sqlbackup\stuff<backupdate>12345.bak' with recovery,
move 'stuff' to 'C:\delete\dbtest\newdbname.mdf', move 'stuff_Log' to 'C:\delete\dbtest\newdbname.ldf'

So I need the <backupdate> in the backup name to be able to be whatever the yyyymmdd for yesterday is.

Is this something easy to do? I can't really find something similar in the forums

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-09-14 : 17:00:40

SELECT convert(char(8), getdate()-1, 112)

infinitiguy
Starting Member

3 Posts

Posted - 2009-09-15 : 13:14:12

So how does that work within my restore statement? What is the proper syntax to use that sql statement in the middle of my disk identification? I'm assuming I can't just stuff it in the middle of the filename?

restore database stuff from disk = '\\backupserver\d$\sqlbackup\stuff<backupdate>12345.bak' with recovery,
move 'stuff' to 'C:\delete\dbtest\newdbname.mdf', move 'stuff_Log' to 'C:\delete\dbtest\newdbname.ldf'

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-09-15 : 13:23:17

Sure you can:

declare @filename varchar(128)
set @filename='\\backupserver\d$\sqlbackup\stuff' + convert(char(8), getdate()-1, 112) + '12345.bak'
restore database stuff from disk = @filename with recovery,
move 'stuff' to 'C:\delete\dbtest\newdbname.mdf', 
move 'stuff_Log' to 'C:\delete\dbtest\newdbname.ldf'

infinitiguy
Starting Member

3 Posts

Posted - 2009-09-15 : 13:31:34

ah. I was looking for the ' '. I didn't know if nesting them would work. Thank you VERY much!

VicM
Starting Member

2 Posts

Posted - 2009-09-15 : 13:31:42

The '112' parameter in the CONVERT function displays the date in the yyyymmdd format, 8 characters.

I think you'd do something like this:

Declare @BUString AS nvarchar(100)

SET @BUString = '\\backupserver\d$\sqlbackup\stuff' + convert(char(8), getdate()-1, 112) + '12345.bak'

restore database stuff from disk = @BUString with .........

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources