MSDE FunClub 現在までのアクセスカウント数 最終更新日 : 2000/07/29
Microsoft Data Engine FunClub
Since 2000.07.29
検証用スクリプト−２番 
/*
**  [2.SQL]
**
**   このプログラムは、
**           1)トランザクションログのバックアップを実施する
**           2)データベースを削除する
**           3)完全バックアップの復元
**           4)そしてログの適用
**   を実行するものです。
**
**   プログラムが複雑なのは、データベースの名前などが、皆様の環境に合わせて書き換えが
**   できるように、パラメータで定義しました。
**   パラメータは、1.SQL と同じものを設定してください
**
**        (C)（株）日本技術ソフト開発　堀川　明  2000/07/29
**
*/



/*
**  ( 件処理されました)のメッセージの抑止
*/
    SET NOCOUNT ON
GO


/*
** バッチ間で、データの受け渡しを行なうための、ローカル一時テーブル
** が存在していますか？  存在していれば、それを削除します
*/
    IF EXISTS ( SELECT NAME FROM TEMPDB.DBO.SYSOBJECTS
                  WHERE( ID = OBJECT_ID('TEMPDB.DBO.#MyTmpTbl') AND TYPE = 'U ') )
       BEGIN
              --PRINT 'ローカル一時テーブルを削除しました'
              DROP TABLE #MyTmpTbl
       END
GO


/*
**  バッチ間で、データの受け渡しを行なうためのローカル一時テーブルを作成します
*/
    CREATE TABLE #MyTmpTbl (
                   Param_Name VARCHAR(20) ,   --データを検索するときの名前
                   Param_Data VARCHAR(80)     --その値（パラメータ）
    )
GO


/*
**  バッチ間で受け渡すデータのセット
**  みなさんの環境に合わせて、適当に書き換えてください
*/
    --新しく作成するデータベースの名前（MySampleTestDB_20000729）
    --もしその名前のデータベースが存在したら、それを削除します!!
    --絶対に存在する名前は、避けてください
    INSERT INTO #MyTmpTbl VALUES( 'DB_NAME'  , 'MySampleTestDB_20000729' )

    --MDFファイルの物理的なパス名の定義（データファイル名）
    INSERT INTO #MyTmpTbl VALUES( 'MDF_FILE' , 'D:\DATA\MySampleTestDB.mdf' )

    --LDFファイルの物理的なパス名の定義（トランザクションログファイル名）
    INSERT INTO #MyTmpTbl VALUES( 'LDF_FILE' , 'D:\DATA\MySampleTestDB.ldf' )

    --データベースのバックアップファイル名
    INSERT INTO #MyTmpTbl VALUES( 'BAK_FILE' , 'D:\DATA\MySampleTestDB.bak' )

    --レコードの確認
    --SELECT * FROM #MyTmpTbl
GO


/*
** 現在実行しているSQLServerのバージョン番号（７または８）を判断する
*/
   IF EXISTS ( SELECT NAME FROM TEMPDB.DBO.SYSOBJECTS
                  WHERE( ID = OBJECT_ID('TEMPDB.DBO.#XP_MSVER') AND TYPE = 'U ') )
       BEGIN
              --PRINT 'ローカル一時テーブルを削除しました'
              DROP TABLE #XP_MSVER
       END

    CREATE TABLE #XP_MSVER (
                   Idx             SMALLINT    ,
                   Nm              VARCHAR(80) ,
                   Internal_Value  VARCHAR(80) , 
                   Character_Value VARCHAR(80)
    )

    --バージョン番号の取得
    --  Character_Valueの左側文字が、７または８(SQLServer2000)を判断する
    INSERT INTO #XP_MSVER EXEC master.dbo.xp_msver 'ProductVersion'

    
    DECLARE @VER CHAR(1)
    SET @VER = LEFT((SELECT Character_Value FROM #XP_MSVER WHERE( Idx = 2 ) ),1)

    --バージョン番号を登録する
    INSERT INTO #MyTmpTbl VALUES( 'SQL_VER' , @VER )
GO



/*
**  データベースを誰か使用しているかどうか判断するストアドプロシージャの登録
*/
    USE TEMPDB
    IF ( (OBJECT_ID('#InUse') IS NOT NULL ) AND
         OBJECTPROPERTY(object_id('#InUse'),'IsProcedure') = 1 )
      BEGIN
              --PRINT '#InUseストアドプロシージャを削除します'
              DROP PROCEDURE #InUse    --削除
      END
GO
    CREATE PROCEDURE #InUse
             @dbname  sysname          --調査を行なうデータベース    
    AS
        --sp_whoの結果セットを受け取ります
        --SQLServer2000では、ecid列が追加されている
        DECLARE @VER CHAR(1)
        SET @VER = (SELECT Param_Data FROM #MyTmpTbl WHERE( Param_Name = 'SQL_VER' ) )
        --PRINT 'VER=' + @VER

        IF( @VER = '7' )
           BEGIN
                PRINT 'Now SQLServer 7.x  ...'
                CREATE TABLE #SP_WHO_7(
                        spid  smallint   ,  --システム プロセス ID
                      status  nchar(30)  ,  --プロセスの状態
                    loginame  nchar(128) ,  --ログイン名
                    hostname  nchar(128) ,  --コンピュータ名 
                      blk     char(5)   ,   --ブロック中のプロセス 
                     dbname   nchar(128) ,  --プロセスで使用されているデータベース
                       cmd    nchar(16)     --SQL Server コマンド 
                 )

                 --sp_who を実行します
                 INSERT INTO #SP_WHO_7 EXEC SP_WHO

                 --データベースの接続を調べる
                 IF EXISTS ( SELECT dbname FROM #SP_WHO_7 WHERE( dbname = @dbname ) )
                 BEGIN
                    RETURN 1    --データベースを使用しているユーザがいます
                 END
           END
       ELSE
           BEGIN
               PRINT 'Now SQLServer 2000  ...'
               CREATE TABLE #SP_WHO_8(
                        spid  smallint   ,  --システム プロセス ID
                       ecid   smallint   ,  --SQLServer2000で新規に追加されました
                      status  nchar(30)  ,  --プロセスの状態
                    loginame  nchar(128) ,  --ログイン名
                    hostname  nchar(128) ,  --コンピュータ名 
                      blk     char(5)   ,   --ブロック中のプロセス 
                     dbname   nchar(128) ,  --プロセスで使用されているデータベース
                       cmd    nchar(16)     --SQL Server コマンド 
                 )

                 --sp_who を実行します
                INSERT INTO #SP_WHO_8 EXEC SP_WHO

                --データベースの接続を調べる
                IF EXISTS ( SELECT dbname FROM #SP_WHO_8 WHERE( dbname = @dbname ) )
                BEGIN
                   RETURN 1    --データベースを使用しているユーザがいます
                END
        END
        RETURN 0        --誰も使用していません
GO



/*
**  誰かデータベースを使用していたら、このプログラムの実行は止めます
*/
    --バッチ間の受け渡しパラメータの取得
    DECLARE @DB_NAME VARCHAR(80)
    SET @DB_NAME = (SELECT Param_Data FROM #MyTmpTbl WHERE( Param_Name = 'DB_NAME' ) )

    --データベースを誰か使用していますか？
    DECLARE @RET INT
    EXEC @RET = #InUse @DB_NAME
    IF( @RET = 1 )
      BEGIN
           RAISERROR('データベース %s は使用中。中止します',0,127,@DB_NAME )
           INSERT INTO #MyTmpTbl VALUES( 'STOP' , 'STOP' )
           RETURN
      END
GO



/*
**  トランザクションログファイルのバックアップを実行します
*/
   -- バッチの継続実行ができますか？
    IF EXISTS ( SELECT Param_Name FROM #MyTmpTbl WHERE( Param_Name = 'STOP' ) )
       BEGIN
              PRINT 'バッチの継続実行はできません'
              RETURN
       END

    --バッチ間の受け渡しパラメータの取得
    DECLARE @DB_NAME VARCHAR(80),@BK_FILE VARCHAR(80)
    SET @DB_NAME = (SELECT Param_Data FROM #MyTmpTbl WHERE( Param_Name = 'DB_NAME' ) )
    SET @BK_FILE = (SELECT Param_Data FROM #MyTmpTbl WHERE( Param_Name = 'BAK_FILE' ) )


    --トランザクションログファイルのバックアップを実施する
    --完全バックアップの後に追加します
    --NO_TRUNCATEオプションを忘れずに
    BACKUP LOG @DB_NAME TO DISK = @BK_FILE
     WITH
           NO_TRUNCATE , 
           NOINIT
           

    --正常に終了しましたか？
    IF( @@ERROR <> 0 )
     BEGIN
           PRINT ''
           RAISERROR('******************************************',0,1)
           RAISERROR('ログのバックアップ処理が、異常終了しました',0,1)
           RAISERROR('******************************************',0,127)
           INSERT INTO #MyTmpTbl VALUES( 'STOP' , 'STOP' )
           RETURN
     END
GO


/*
** データベースを削除します
*/
    -- バッチの継続実行ができますか？
    IF EXISTS ( SELECT Param_Name FROM #MyTmpTbl WHERE( Param_Name = 'STOP' ) )
       BEGIN
              PRINT 'バッチの継続実行はできません'
              RETURN
       END

    --バッチ間の受け渡しパラメータの取得
    DECLARE @DB_NAME VARCHAR(80)
    SET @DB_NAME = (SELECT Param_Data FROM #MyTmpTbl WHERE( Param_Name = 'DB_NAME' ) )

    --データベースが存在したら、それを削除する
    IF EXISTS ( SELECT NAME FROM MASTER.DBO.SYSDATABASES WHERE( NAME = @DB_NAME ) )
       BEGIN
              PRINT 'データベースが存在しますので、それを削除します'
              EXEC( 'DROP DATABASE ' + @DB_NAME ) 
       END
GO


/*
**  データベースを復元します
*/
    -- バッチの継続実行ができますか？
    IF EXISTS ( SELECT Param_Name FROM #MyTmpTbl WHERE( Param_Name = 'STOP' ) )
       BEGIN
              PRINT 'バッチの継続実行はできません'
              RETURN
       END

    --バッチ間の受け渡しパラメータの取得
    DECLARE @DB_NAME VARCHAR(80),@BK_FILE VARCHAR(80)
    SET @DB_NAME = (SELECT Param_Data FROM #MyTmpTbl WHERE( Param_Name = 'DB_NAME'  ) )
    SET @BK_FILE = (SELECT Param_Data FROM #MyTmpTbl WHERE( Param_Name = 'BAK_FILE' ) )

    --完全バックアップファイルを復元する
    RESTORE DATABASE @DB_NAME
       FROM DISK = @BK_FILE
       WITH FILE = 1 ,  REPLACE , NORECOVERY

    --トランザクションログを復元する
    RESTORE LOG @DB_NAME
       FROM DISK = @BK_FILE
       WITH FILE = 2 ,  RECOVERY
GO


/*
** 復元されたデータベースの確認
*/
    -- バッチの継続実行ができますか？
    IF EXISTS ( SELECT Param_Name FROM #MyTmpTbl WHERE( Param_Name = 'STOP' ) )
       BEGIN
              PRINT 'バッチの継続実行はできません'
              RETURN
       END

    --バッチ間の受け渡しパラメータの取得
    DECLARE @DB_NAME VARCHAR(80)
    SET @DB_NAME = (SELECT Param_Data FROM #MyTmpTbl WHERE( Param_Name = 'DB_NAME'  ) )

    --登録したレコードの表示
    DECLARE @SQL_CMD VARCHAR(128)
    SET @SQL_CMD = 'SELECT * FROM ' +  @DB_NAME + '.DBO.Test1 '
    EXEC( @SQL_CMD )

    PRINT ''
    SET @SQL_CMD = 'SELECT * FROM ' +  @DB_NAME + '.DBO.Test2 '
    EXEC( @SQL_CMD )
GO

    PRINT ''
    PRINT 'プログラムの終了です'
GO
ウィンドウを閉じる

（株）日本技術ソフト開発　責任編集：堀川　明
 MSDE FunClubに関するご意見・ご要望等ございましたら、 msdefun@horikawa.ne.jp　までご連絡下さい。
HOME: http://www.horikawa.ne.jp/msde/

MSDE FunClubの運営は、マイクロソフト社とは一切の関係はありません