• システム開発に関わる内容をざっくりと書いていく

batでSQLServerのバックアップとリストアを行う

検証作業等でbat処理でバックアップ取得やリストア作業を便利にする。

・バックアップの取得:

下記ファイルを用意する

backup.sql

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
BACKUP DATABASE [Table1] TO DISK='C:\BackupTest\Table1.bak' WITH INIT
GO
BACKUP DATABASE [Table2] TO DISK='C:\BackupTest\Table2.bak' WITH INIT
GO
BACKUP DATABASE [Table1] TO DISK='C:\BackupTest\Table1.bak' WITH INIT GO BACKUP DATABASE [Table2] TO DISK='C:\BackupTest\Table2.bak' WITH INIT GO
BACKUP DATABASE [Table1] TO DISK='C:\BackupTest\Table1.bak' WITH INIT 
GO
BACKUP DATABASE [Table2] TO DISK='C:\BackupTest\Table2.bak' WITH INIT 
GO

backup.bat

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SQLCMD -S YourPC\SQLEXPRESS -U sa -P sa -i backup.sql > Backup.LOG
pause
SQLCMD -S YourPC\SQLEXPRESS -U sa -P sa -i backup.sql > Backup.LOG pause
SQLCMD -S YourPC\SQLEXPRESS -U sa -P sa -i backup.sql > Backup.LOG
pause

同階層に上記ファイルを配置して、backup.batを実行する。

・リストアの実行:(上記で作成されたバックアップをリストアする)

下記ファイルを用意する

restore.sql

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
RESTORE DATABASE [Table1] FROM DISK = N'C:\BackupTest\Table1.bak' WITH FILE = 1, NOUNLOAD, STATS = 5
GO
RESTORE DATABASE [Table2] FROM DISK = N'C:\BackupTest\Table2.bak' WITH FILE = 1, NOUNLOAD, STATS = 5
GO
RESTORE DATABASE [Table1] FROM DISK = N'C:\BackupTest\Table1.bak' WITH FILE = 1, NOUNLOAD, STATS = 5 GO RESTORE DATABASE [Table2] FROM DISK = N'C:\BackupTest\Table2.bak' WITH FILE = 1, NOUNLOAD, STATS = 5 GO
RESTORE DATABASE [Table1] FROM  DISK = N'C:\BackupTest\Table1.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5
GO
RESTORE DATABASE [Table2] FROM  DISK = N'C:\BackupTest\Table2.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5
GO

restore.bat

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SQLCMD -S YourPC\SQLEXPRESS -U sa -P sa -i restore.sql > Restore.LOG
pause
SQLCMD -S YourPC\SQLEXPRESS -U sa -P sa -i restore.sql > Restore.LOG pause
SQLCMD -S YourPC\SQLEXPRESS -U sa -P sa -i restore.sql > Restore.LOG
pause

同階層に上記ファイルを配置して、restore.batを実行する。

・エラー発生時の強制解放

下記ファイルを用意する

forceKill.sql

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
Use DbName
Go
Declare @dbname sysname
Set @dbname = '[Table1]'
Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
Execute ('Kill ' + @spid)
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname) and spid > @spid
End
Set @dbname = '[Table2]'
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
Execute ('Kill ' + @spid)
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname) and spid > @spid
End
Use DbName Go Declare @dbname sysname Set @dbname = '[Table1]' Declare @spid int Select @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) While @spid Is Not Null Begin Execute ('Kill ' + @spid) Select @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) and spid > @spid End Set @dbname = '[Table2]' Select @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) While @spid Is Not Null Begin Execute ('Kill ' + @spid) Select @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) and spid > @spid End
Use DbName
Go
 
Declare @dbname sysname
Set @dbname = '[Table1]'
 
Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
 
While @spid Is Not Null
 Begin
 Execute ('Kill ' + @spid)
 Select @spid = min(spid) from master.dbo.sysprocesses
 where dbid = db_id(@dbname) and spid > @spid
 End

Set @dbname = '[Table2]'
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
 
While @spid Is Not Null
 Begin
 Execute ('Kill ' + @spid)
 Select @spid = min(spid) from master.dbo.sysprocesses
 where dbid = db_id(@dbname) and spid > @spid
 End

forceKill.bat

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SQLCMD -S YourPC\SQLEXPRESS -U sa -P sa -i ForceKill.sql > ForceKill.LOG
pause
SQLCMD -S YourPC\SQLEXPRESS -U sa -P sa -i ForceKill.sql > ForceKill.LOG pause
SQLCMD -S YourPC\SQLEXPRESS -U sa -P sa -i ForceKill.sql > ForceKill.LOG
pause

同階層に上記ファイルを配置して、forceKill.batを実行する。