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)
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)
Code:
restore filelistonly
from disk='\\liveservername\E$\crm_backups\crmlive\crmlive_db_200512190200.BAK'
Code:
/*
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