---

MSDE FunClub		最終更新日 : 2000/07/29
Microsoft Data Engine FunClub	Since 2000.07.29
検証用スクリプト−１番

---

/*
**  [1.SQL]
**
**   このプログラムは、
**           1)新しいデータベースを作成する
**           2)テーブル１番を作成し、レコードの登録
**           3)データベースの完全バックアップの実施
**           4)テーブル２番を作成し、レコードの登録
**   を実行するものです。
**
**   プログラムが複雑なのは、データベースの名前などが、皆様の環境に合わせて書き換えが
**   できるように、パラメータで定義しました。
**
**   このプログラムは、(3)の完全バックアップデータファイルの中に、(4)の２番目テーブルの
**   情報が含まれていないことを、意識することです。
**   しかし幸いにも、(4)の情報は、トランザクションログの方に記録されています。
**
**        (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 @RET INT

    --バッチ間の受け渡しパラメータの取得
    DECLARE @DB_NAME VARCHAR(80) , @MDF_FILE VARCHAR(80) , @LDF_FILE VARCHAR(80)
    SET @DB_NAME = (SELECT Param_Data FROM #MyTmpTbl WHERE( Param_Name = 'DB_NAME' ) )
    IF( @DB_NAME IS NULL ) 
      BEGIN
            -- OSQLコマンドで実行しているときは、RAISERROR(127番)で終了させることができる
            RAISERROR('パラメータの検索 DB_NAME に失敗しました',0,127 )

            -- このプログラムは、クエリアナライザで実行していますので、STOPレコードを登録
            INSERT INTO #MyTmpTbl VALUES( 'STOP' , 'STOP' )

            -- バッチの終了
            RETURN
      END
    ELSE
      PRINT '作成するデータベースの名前：' + @DB_NAME


    SET @MDF_FILE = (SELECT Param_Data FROM #MyTmpTbl WHERE( Param_Name = 'MDF_FILE' ) )
    PRINT 'MDFファイル名：' + @MDF_FILE

    SET @LDF_FILE = (SELECT Param_Data FROM #MyTmpTbl WHERE( Param_Name = 'LDF_FILE' ) )
    PRINT 'LDFファイル名：' + @LDF_FILE


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

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


    --データベースを新しく作成します
    DECLARE @CRLF CHAR(2) , @SQL_CMD VARCHAR(512)
    SET @CRLF = CHAR(13) + CHAR(10)
    SET @SQL_CMD = 'CREATE DATABASE ' + @DB_NAME             + @CRLF +
                   'ON PRIMARY '                             + @CRLF +
                   '  ( '                                    + @CRLF +
                   '     NAME = '+@DB_NAME+'_DAT' + ','      + @CRLF +
                   '     FILENAME = ''' + @MDF_FILE + ''''   + @CRLF +
                   '  ) '                                    + @CRLF +
                   'LOG ON '                                 + @CRLF +
                   '  ( '                                    + @CRLF +
                   '     NAME = '+@DB_NAME+'_LOG' + ','      + @CRLF +
                   '     FILENAME = ''' + @LDF_FILE + ''''   + @CRLF +
                   '  ) '                                    + @CRLF
    --PRINT @SQL_CMD
    EXEC( @SQL_CMD )  -- SQL文を実行する
GO


/*
** トランザクションログの機能を有効にします
** MSDEの場合、初期状態では、ログの機能が停止となっています
*/
   -- バッチの継続実行ができますか？
    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' ) )


     -- SP_DBOPTIONを受け取るローカル一時テーブルの作成
     IF EXISTS ( SELECT NAME FROM TEMPDB.DBO.SYSOBJECTS
                  WHERE( ID = OBJECT_ID('TEMPDB.DBO.#GetDBOption') AND TYPE = 'U ') )
       BEGIN
              DROP TABLE #GetDBOption
       END

     CREATE TABLE #GetDBOption (
              OptionName      VARCHAR(35)  , 
              CurrentSetting  varchar(10)
     )

             
    -- データベースオプション select into/bulkcopy を調べる
    DECLARE @VALUE VARCHAR(35)
    INSERT INTO  #GetDBOption EXEC SP_DBOPTION @DB_NAME , 'select into/bulkcopy'
    SET @VALUE = (SELECT CurrentSetting FROM #GetDBOption WHERE( OptionName = 'select into/bulkcopy' ) )
    --PRINT 'select into/bulkcopy の値は [' + @VALUE + ']'
    --ONのときは、OFFに設定する
    IF( @VALUE = 'ON' )
      BEGIN
            PRINT ''
            PRINT 'select into/bulkcopy オプションが設定されていました'
            PRINT 'このままでは、ログに記録されない操作が許されます'
            PRINT 'データベースの運用上良くありませんので、禁止にします'
            EXEC SP_DBOPTION @DB_NAME , 'select into/bulkcopy' , 'FALSE'
      END


    -- データベースオプション trunc. log on chkpt. を調べる
    INSERT INTO  #GetDBOption EXEC SP_DBOPTION @DB_NAME , 'trunc. log on chkpt.'
    SET @VALUE = (SELECT CurrentSetting FROM #GetDBOption WHERE( OptionName = 'trunc. log on chkpt.' ) )
    --PRINT 'trunc. log on chkpt. の値は [' + @VALUE + ']'
    --ONのときは、OFFに設定する
    IF( @VALUE = 'ON' )
      BEGIN
            PRINT ''
            PRINT 'trunc. log on chkpt. オプションが設定されていました'
            PRINT 'このままでは、チェックポイント時にログが切り捨てられます'
            PRINT 'データベースの運用上良くありませんので、禁止にします'
            EXEC SP_DBOPTION @DB_NAME , 'trunc. log on chkpt.' , 'FALSE'
      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' ) )

    -- テーブル作成SQL文の作成
    DECLARE @SQL_CMD VARCHAR(512),@CRLF CHAR(2)
    SET @CRLF = CHAR(13) + CHAR(10)
    SET @SQL_CMD = 'USE '  + @DB_NAME                        + @CRLF +
                   '   CREATE TABLE Test1 ( '                + @CRLF +
                   '          ID  CHAR(4)  PRIMARY KEY , '   + @CRLF +
                   '          DT  CHAR(10) '                 + @CRLF +
                   '   )'
    --PRINT @SQL_CMD
    EXEC( @SQL_CMD )

    --レコードの登録
    SET @SQL_CMD = 'INSERT INTO ' +  @DB_NAME + '.DBO.Test1 VALUES( ''0001'' , ''AAAAA'' ) '
    EXEC( @SQL_CMD )

    SET @SQL_CMD = 'INSERT INTO ' +  @DB_NAME + '.DBO.Test1 VALUES( ''0002'' , ''BBBBB'' ) '
    EXEC( @SQL_CMD )

    SET @SQL_CMD = 'INSERT INTO ' +  @DB_NAME + '.DBO.Test1 VALUES( ''0003'' , ''CCCCC'' ) '
    EXEC( @SQL_CMD )

    SET @SQL_CMD = 'INSERT INTO ' +  @DB_NAME + '.DBO.Test1 VALUES( ''0004'' , ''DDDDD'' ) '
    EXEC( @SQL_CMD )

    --登録したレコードの表示
    --SET @SQL_CMD = 'SELECT * FROM ' +  @DB_NAME + '.DBO.Test1 '
    --EXEC( @SQL_CMD )
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' ) )

    --完全バックアップの実施
    PRINT ''
    BACKUP DATABASE @DB_NAME TO DISK = @BK_FILE WITH INIT
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'  ) )

    -- テーブル作成SQL文の作成
    DECLARE @SQL_CMD VARCHAR(512),@CRLF CHAR(2)
    SET @CRLF = CHAR(13) + CHAR(10)
    SET @SQL_CMD = 'USE '  + @DB_NAME                        + @CRLF +
                   '   CREATE TABLE Test2 ( '                + @CRLF +
                   '          ID2 CHAR(4)  PRIMARY KEY , '   + @CRLF +
                   '          DT2 CHAR(10) '                 + @CRLF +
                   '   )'
    --PRINT @SQL_CMD
    EXEC( @SQL_CMD )

    --レコードの登録
    SET @SQL_CMD = 'INSERT INTO ' +  @DB_NAME + '.DBO.Test2 VALUES( ''AAAA'' , ''11111'' ) '
    EXEC( @SQL_CMD )

    SET @SQL_CMD = 'INSERT INTO ' +  @DB_NAME + '.DBO.Test2 VALUES( ''BBBB'' , ''22222'' ) '
    EXEC( @SQL_CMD )

    SET @SQL_CMD = 'INSERT INTO ' +  @DB_NAME + '.DBO.Test2 VALUES( ''CCCC'' , ''33333'' ) '
    EXEC( @SQL_CMD )

    SET @SQL_CMD = 'INSERT INTO ' +  @DB_NAME + '.DBO.Test2 VALUES( ''DDDD'' , ''44444'' ) '
    EXEC( @SQL_CMD )

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


    PRINT ''
    PRINT 'プログラムの終了です'
GO