DB

T-SQLでSQL Serverの特定テーブルのバックアップ取得／リストアを行うプログラムを作成してみた

 

T-SQL(Transact-SQL)はSQLの拡張機能として開発されたもので、SQLに、条件分岐や繰り返しなどを含む制御構造を加えることができる。

今回は、T-SQLを用いて、特定テーブルのバックアップ取得／リストアを行う処理を実装してみたので、そのサンプルプログラムを共有する。

なお、バックアップテーブル名には、バックアップ取得を行った日付を含むようにすると共に、INFORMATION_SCHEMA.TABLESビューを用いて、データベース内に存在するテーブル名を取得している。

INFORMATION_SCHEMA.TABLESビューについては、例えば、以下のサイトを参照のこと。
https://learn.microsoft.com/ja-jp/sql/relational-databases/system-information-schema-views/tables-transact-sql?view=sql-server-ver17

前提条件

下記記事の「前提条件」の内容が完了していること。

Spring BootでSQL Serverに接続しMyBatisを利用してみた今回は、Spring Bootアプリケーションで接続するデータベースをSQL Serverに変更してみたので、そのサンプルプログラムを共...
 エンジニアファーストバナー

サンプルプログラムの作成

特定テーブル(USER_DATA)のバックアップ取得用プログラム(bkup_user_data.sql)の内容は、以下の通り。

BEGIN TRANSACTION;

-- 定数宣言(実行日付)
DECLARE @nowDate VARCHAR(8) = FORMAT(GETDATE(),'yyyyMMdd');
-- 変数宣言
DECLARE @sql VARCHAR(1000);

-----------------------------------------------------
-- バックアップ前データの確認
-----------------------------------------------------
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'USER_DATA%';

-----------------------------------------------------
-- バックアップの取得
-----------------------------------------------------
SET @sql = 'SELECT * INTO dbo.USER_DATA_' + @nowDate + ' FROM dbo.USER_DATA';
EXEC(@sql);

-----------------------------------------------------
-- バックアップ後データの確認
-----------------------------------------------------
-- COMMIT TRANSACTION;

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'USER_DATA%';

SET @sql = 'SELECT * FROM dbo.USER_DATA_' + @nowDate + ' ORDER BY id ASC';
EXEC(@sql);

ROLLBACK TRANSACTION;

また、特定テーブル(USER_DATA)のリストア用プログラム(restore_user_data.sql)の内容は、以下の通り。

BEGIN TRANSACTION;

-- 変数宣言
DECLARE @bkTblName VARCHAR(30);
DECLARE @sql VARCHAR(1000);

-----------------------------------------------------
-- バックアップテーブルのテーブル名を取得
-----------------------------------------------------
SELECT @bkTblName = TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'USER_DATA%' AND TABLE_NAME <> 'USER_DATA';

-----------------------------------------------------
-- バックアップ前データの確認
-----------------------------------------------------
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'USER_DATA%';

SET @sql = 'SELECT * FROM dbo.' + @bkTblName + ' ORDER BY id ASC';
EXEC(@sql);

SELECT * FROM dbo.USER_DATA ORDER BY id ASC;

-----------------------------------------------------
-- バックアップを元に戻す
-----------------------------------------------------
TRUNCATE TABLE dbo.USER_DATA;

SET @sql = 'INSERT INTO dbo.USER_DATA SELECT * FROM dbo.' + @bkTblName;
EXEC(@sql);

-----------------------------------------------------
-- バックアップテーブルを削除する
-----------------------------------------------------
SET @sql = 'DROP TABLE dbo.' + @bkTblName;
EXEC(@sql);

-----------------------------------------------------
-- バックアップ戻し後データの確認
-----------------------------------------------------
-- COMMIT TRANSACTION;

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'USER_DATA%';

SELECT * FROM dbo.USER_DATA ORDER BY id ASC;

ROLLBACK TRANSACTION;



サンプルプログラムの実行結果

SSMS(SQL Server Management Studio)を利用して、サンプルプログラムの実行結果を確認する。その手順は、以下の通り。なお、バックアップは2025/9/18に取得しているため、バックアップテーブルは「USER_DATA_20250918」というテーブル名になっている。

1) 実行前のデータの状態は、以下の通り。

SELECT * FROM dbo.USER_DATA ORDER BY id ASC
サンプルプログラムの実行結果_1_1
SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE 'USER_DATA%'
サンプルプログラムの実行結果_1_2

2) bkup_user_data.sqlを、赤枠のロールバックを有効にし、コミットをコメントアウトして実行した結果は以下の通りで、バックアップテーブル(USER_DATA_20250918)がUSER_DATAテーブルと同じ内容で作成されていることが確認できる。
サンプルプログラムの実行結果_2

3) ロールバックで実行した場合、バックアップテーブル(USER_DATA_20250918)は、実際には作成されていないことが確認できる。

SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE 'USER_DATA%'
サンプルプログラムの実行結果_3

4) bkup_user_data.sqlを、赤枠のコミットを有効にし、ロールバックをコメントアウトして実行した結果は以下の通りで、2)ロールバックで実行した時と同じ結果が出力されることが確認できる。
サンプルプログラムの実行結果_4

5) コミットで実行した場合、以下のように、バックアップテーブル(USER_DATA_20250918)が、実際に作成されていることが確認できる。

SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE 'USER_DATA%'
サンプルプログラムの実行結果_5_1
SELECT * FROM dbo.USER_DATA_20250918 ORDER BY id ASC
サンプルプログラムの実行結果_5_2

なお、SSMSで上記SQLを実行し「USER_DATA_20250918」テーブルが存在しない旨のエラーが出た場合、「Edit」メニューの「IntelliSense」、「Refresh Local Cache」を選択すればよい。
サンプルプログラムの実行結果_5_3

エンジニアファーストバナー

6) 次にリストア用プログラムの動作を確認するため、USER_DATAテーブルのデータを更新する。

UPDATE dbo.USER_DATA SET name = name + N'更新後'
サンプルプログラムの実行結果_6_1
SELECT * FROM dbo.USER_DATA ORDER BY id ASC
サンプルプログラムの実行結果_6_2

7) restore_user_data.sqlを、赤枠のロールバックを有効にし、コミットをコメントアウトして実行した結果は以下の通りで、バックアップテーブル(USER_DATA_20250918)が削除され、USER_DATAテーブルが更新前状態に戻っていることが確認できる。
サンプルプログラムの実行結果_7

8) ロールバックで実行した場合、バックアップテーブル(USER_DATA_20250918)は削除されず、USER_DATAテーブルが更新後状態のままであることが確認できる。

SELECT * FROM dbo.USER_DATA ORDER BY id ASC
サンプルプログラムの実行結果_8_1
SELECT * FROM dbo.USER_DATA_20250918 ORDER BY id ASC
サンプルプログラムの実行結果_8_2

9) restore_user_data.sqlを、赤枠のコミットを有効にし、ロールバックをコメントアウトして実行した結果は以下の通りで、7)ロールバックで実行した時と同じ結果が出力されることが確認できる。
サンプルプログラムの実行結果_9

10) コミットで実行した場合、バックアップテーブル(USER_DATA_20250918)は削除され、USER_DATAテーブルが更新前状態に戻っていることが確認できる。

SELECT * FROM dbo.USER_DATA ORDER BY id ASC
サンプルプログラムの実行結果_10_1
SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE 'USER_DATA%'
サンプルプログラムの実行結果_10_2

要点まとめ

  • T-SQL(Transact-SQL)はSQLの拡張機能として開発されたもので、SQLに、条件分岐や繰り返しなどを含む制御構造を加えることができる。
  • INFORMATION_SCHEMA.TABLESビューを用いると、データベース内に存在するテーブル名を取得できる。
RELATED POST