mssql database backup and restore script

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		고지현
-- Create date: 2010-11-02
-- Description: 해당 DATABASE 를 백업(현재 폴더)하고 백업한 파일을 다시 복구 한다.
/*
	UP_DATABASE_BACKUPnRESTORE 'DataBaseName', 'DataBaseName_101102'
*/
-- =============================================
CREATE PROCEDURE UP_DATABASE_BACKUPnRESTORE
	-- Add the parameters for the stored procedure here
	@DATABASE		VARCHAR(100)
	,@NEW_DATEBASE	VARCHAR(100)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE	@QUERY	VARCHAR(4000)

	SET	@QUERY = '
		CREATE TABLE #DBFILE_INFO(
			name		VARCHAR(20)
			,fileid		INT
			,[filename]	NVARCHAR(500)
			,filegroup	VARCHAR(30)
			,[size]		VARCHAR(30)
			,maxsize	VARCHAR(30)
			,growh		VARCHAR(30)
			,usage		VARCHAR(30)
		)

		INSERT INTO #DBFILE_INFO
		EXEC ' + @DATABASE + '.dbo.SP_HELPFILE

		DECLARE @DATA			VARCHAR(20)
				,@DATA_LOG		VARCHAR(20)
				,@DATA_PATH		VARCHAR(20)
				,@DATA_LOG_PATH	VARCHAR(20)

		SELECT	@DATA = name
				,@DATA_PATH = filename
		  FROM	#DBFILE_INFO
		 WHERE	CHARINDEX(''data'', usage) > 0

		SELECT	@DATA_LOG = name
				,@DATA_LOG_PATH = filename
		  FROM	#DBFILE_INFO
		 WHERE	CHARINDEX(''log'', usage) > 0

		DECLARE	@NEW_PATH		VARCHAR(500)
				,@BAK_PATH		VARCHAR(500)
				,@NEW_DATA_PATH	VARCHAR(500)
				,@NEW_LOG_PATH	VARCHAR(500)
				
		SET	@NEW_PATH = SUBSTRING(@DATA_PATH, 1, LEN(@DATA_PATH) - CHARINDEX(''\'', REVERSE(@DATA_PATH)) + 1)
		SET	@BAK_PATH = @NEW_PATH + ''' + @NEW_DATEBASE + '.bak''
		SET	@NEW_DATA_PATH = @NEW_PATH + ''' + @NEW_DATEBASE + '_Data.mdf''
		SET	@NEW_LOG_PATH = @NEW_PATH + ''' + @NEW_DATEBASE + '_Log.ldf''


		BACKUP DATABASE [' + @DATABASE + '] TO
		DISK = @BAK_PATH
		WITH NOFORMAT, NOINIT, NAME = N''' + @NEW_DATEBASE + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10

		RESTORE DATABASE [' + @NEW_DATEBASE + '] 
			FROM  DISK = @BAK_PATH
			WITH
				FILE = 1
				,MOVE @DATA TO @NEW_DATA_PATH
				,MOVE @DATA_LOG TO @NEW_LOG_PATH
				,NOUNLOAD
				,STATS = 10

		DROP TABLE #DBFILE_INFO'
		
	--PRINT(@QUERY)
	EXEC @QUERY
END
GO

현재 디비명, 신규 디비명을 입력하면 현재 디비명을 가지고 백업하고 다시 신규 디비명으로 복구 하는 스크립트.

여기에 살을 붙이자면, 백업용, 복구용, 디렉토리 설정 등을 할 수 있겠으나, 귀찮아서 패스~


내가 하는게 항상 그런 듯 ㅋ
아~ 이 놈의 디비의 세계를 어떻게 파해치나 *_*;;;;


갈길이 멀구나 ㅋ