variables in stored procedure 1

[richey1]

Hi I have the following stored procedure which runs overnight.
runs ok................but for both lines that hardcodes a filename I'd like to be able to use a variable for the bak name - the backup runs every morning so this morning's was 200512190200, tomorrows will be 200512200200 etc etc

how can i use a variable of sorts instead of hardcoding the filename in ?

thanks very much
tony


this line for example I'd like to use a variable (todays date) instead of 20051219 (the 0200 part should always stay the same)

restore filelistonly
        from disk='\\liveservername\E$\crm_backups\crmlive\crmlive_db_200512190200.BAK'

/*

	SQL		RestoreCRM_Live_to_Train.sql
	Author		*
	Date Written 	19 December 2005
	Amendments	
	
*/

use master
go

restore filelistonly
        from disk='\\liveservername\E$\crm_backups\crmlive\crmlive_db_200512190200.BAK'

restore database crmtrain
        from disk='\\liveservername\E$\crm_backups\crmlive\crmlive_db_200512190200.BAK'
        with move 'crmlive_Data'  to 'E:\crm_dbs\crmtrain_Data.mdf',
             move 'crmlive_Index' to 'E:\crm_dbs\crmtrain_Index.ndf',
             move 'crmlive_User'  to 'E:\crm_dbs\crmtrain_User.ndf',
             move 'crmlive_Log'   to 'E:\crm_dbs\crmtrain_Log.ldf',
        replace

alter database crmtrain modify file (name=crmlive_Data,newname=crmtrain_Data)
go
alter database crmtrain modify file (name=crmlive_Index,newname=crmtrain_Index)
go
alter database crmtrain modify file (name=crmlive_User,newname=crmtrain_User)
go
alter database crmtrain modify file (name=crmlive_Log,newname=crmtrain_Log)
go

use crmtrain
go


/*
this code fixes the orphaned user problem
*/

EXEC sp_change_users_login 'Auto_Fix', 'loginname', NULL, 'live'
go

/*
this code clears down all train data and resets the sequences
*/

Delete from CTS_AUDIT_LOG
Delete from CTS_CALLBACKS
Delete from CTS_CUST_ADDR
	Where CUSTOMER <> 0
Delete from CTS_CUSTOMER_LINKS
Delete from CTS_EMAIL_HISTORY
Delete from CTS_ENQUIRIES2
Delete from CTS_ENQUIRY_ADDR
Delete from CTS_ENQUIRY_SUMMARY
Delete from CTS_ENQUIRY_SUSPEND
Delete from CTS_FORM_LOG
Delete from CTS_PAYMENTS
Delete from CTS_SESSIONS
Delete from CTS_SIMPLE_SERVICE_LOG
Delete from CTS_VISITS
Delete from ENQUIRY_LINKS
Delete from CTS_CUSTOMER_NOTES
Delete from CTS_CUSTOMERS
	Where CUSTOMERID <> 0
Delete from CTS_SIMPLE_SERVICES
	Where VOID = 'Y'
	and SERVICEID not in 
		(Select SERVICE from CTS_SIMPLE_SERVICE_LOCATIONS)
Go

Update CTS_SEQUENCES
	Set CALLBACK_ID_SEQ = 0,
	    ENQUIRY_ID_SEQ = 0,
	    ENQUIRY_SUSPEND_ID_SEQ = 0,
	    PAYMENT_ID_SEQ = 0,
	    VISIT_ID_SEQ = 0,
	    CUSTOMER_ID_SEQ = 20000000,
	    UPRN_ID_SEQ = 0
Go

 

[Jamfool]

something like:

declare @filename nvarchar(4000)
select @filename = '\\liveservername\E$\crm_backups\crmlive\crmlive_db_' + CONVERT(varchar(30),getdate(),112) + '200' + '.BAK'

select @filename

--restore filelistonly
--        from disk = @filename

 

[richey1]

brilliant !
thanks