MSP_Restore

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MSP_RESTORE_TEST1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MSP_RESTORE_TEST1]
GO

CREATE PROCEDURE MSP_RESTORE_TEST1

-- Simple Procedure to rapidly rebuild a copy of Development TEST1 DB
-- Lovely and fast when the Tard Developers have highlevels of access <grrr> and you just 
-- need them out to restore...KILL KILL KILL !!!
 
-- THIS SCRIPT IS TO BE USED AT YOUR OWN RISK
-- Target Server -XXXX
-- CR DATE: 11-26-2003
-- Jbabington@hotmail.com


-- Verify Location of Vision_db Backup File
-- REMOVED: DTS Run Statement for DTS Package for adding / Resolving users and permissions
-- REMOVED: DTS package Name = 'ProdFix-UserRolePerm-Pkg'
-- LOCATION: \\XXXXX G:\SQL_Backup\Production\Tests1\ Directory
-- Example Usage: Exec MSP_RESTORE_TEST1 'TEST1_db_200311260200.bak'


@Db_ReStoreFileName Varchar(100)

AS
SET NOCOUNT ON
DECLARE @PathVar Varchar(256)

-- Kill UserConnections and Set DB OffLine

EXEC MASTER..MSP_KILLUSERCONN 'Test1'
-- Thanks to B.Knights Proc <USP_KILLUSERS> in his Book "Admin 911 Sql Server 2000"
-- A must have DBA resource for MS SQL 2000 DBA's <gotta give a plug on this book !>
-- Url: http://www.sqlservercentral.com/scripts/contributions/30.asp
-- yea yea could be more dynamic...

EXEC MASTER..SP_DBOPTION 'TEST1', 'OFFLINE', 'TRUE'

SET @PathVar = 'G:\SQL_Backup\Production\TEST\' + @Db_ReStoreFileName

RESTORE DATABASE Test1
FROM DISK = @PathVar WITH REPLACE,  
MOVE 'Test1_Data' TO 'E:\DATA\Test1_Data.mdf',
MOVE 'Test1_Log'  TO 'F:\LOG\Test1_log.ldf'


PRINT ' *********************************************************************'
PRINT ' Now Run DTS Package 'ProdFix-UserRolePerm-Pkg'
PRINT ' *********************************************************************'


-- DTS RUN ?
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO