restore LIVE TO DEV automatically

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 2000 Forums

SQL Server Administration (2000)

restore LIVE TO DEV automatically

Author

Topic

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-02-07 : 09:30:53

Hi
I run the following code to restore my LIVE DATA to DEV overnight.
The problem is the .BAK filename changes every night.
Any ideas on how to programatically get the filename into this code?


USE Master
GO

SET NOCOUNT ON

-- 1 - Variable Declaration
DECLARE @DBID int
DECLARE @CMD1 varchar(8000)
DECLARE @spidNumber int
DECLARE @SpidListLoop int
DECLARE @SpidListTable table
(UIDSpidList int IDENTITY (1,1),
SpidNumber int)

-- 2 - Populate @SpidListTable with the spid information
INSERT INTO @SpidListTable (SpidNumber)
SELECT spid
FROM Master.dbo.sysprocesses
--WHERE DBID NOT IN (1,2,3,4) -- Master, Tempdb, Model, MSDB
WHERE DBID IN (10) -- CCAPP_DEV
AND spid > 50
AND spid <> @@spid
ORDER BY spid DESC

-- 3b - Determine the highest UIDSpidList to loop through the records
SELECT @SpidListLoop = MAX(UIDSpidList) FROM @SpidListTable

-- 3c - While condition for looping through the spid records
WHILE @SpidListLoop > 0
BEGIN

-- 3d - Capture spids location
SELECT @spidNumber = spidnumber
FROM @spidListTable
WHERE UIDspidList = @SpidListLoop

-- 3e - String together the KILL statement
SELECT @CMD1 = 'KILL ' + CAST(@spidNumber AS varchar(5))

-- 3f - Execute the final string to KILL the spids
-- SELECT @CMD1
EXEC (@CMD1)

-- 3g - Descend through the spid list
SELECT @SpidListLoop = @SpidListLoop - 1
END

SET NOCOUNT OFF
GO

ALTER DATABASE CCAPP_DEV SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

RESTORE DATABASE CCApp_DEV
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Backup\ccapp_db_200802070200.BAK'
WITH MOVE 'CCApp_Data' TO 'C:\MIS\SQLData\CCApp_DEV.mdf',
MOVE 'CCApp_Log' TO 'C:\MIS\SQLData\CCApp_DEV_log.ldf' 
GO

ALTER DATABASE CCAPP_DEV SET NULTI_USER 
GO

Thanks

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2008-02-07 : 12:14:25

ok

1. you don't have to kill the spids, the ALTER DATABASE SET SINGLE USER ROLLBACK IMMEDIATE will do that for you

2. You could interrogate the prod systems msdb..backupmediafamily table for the filename and put the backup file name in a variable to use in your code.

-ec

qwertyjjj
Posting Yak Master

131 Posts

Posted - 2008-02-07 : 12:28:08

That's what I thought but I tried it yesterday and an open connection prevented me from entering single user mode!

I managed to get some SQL going but not ideal:


DECLARE @filename varchar(100)
SET @filename = 
'C:\Program Files\Microsoft SQL Server\MSSQL\Backup\ccapp_db_' + 
CAST(Year(getDate()) AS varchar(4))+
RIGHT('0' + CAST(Month(getDate()) AS varchar(3)), 2)+
RIGHT('0' + CAST(Day(getDate()) AS varchar(3)), 2)
+
'0200.BAK'
--select @filename

RESTORE DATABASE CCApp_DEV
FROM DISK = @filename--'C:\Program Files\Microsoft SQL Server\MSSQL\Backup\ccapp_db_200802070200.BAK'
WITH MOVE 'CCApp_Data' TO 'C:\MIS\SQLData\CCApp_DEV.mdf',
MOVE 'CCApp_Log' TO 'C:\MIS\SQLData\CCApp_DEV_log.ldf' 
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources