検証作業等でbat処理でバックアップ取得やリストア作業を便利にする。
・バックアップの取得:
下記ファイルを用意する
backup.sql
BACKUP DATABASE [Table1] TO DISK='C:\BackupTest\Table1.bak' WITH INIT
BACKUP DATABASE [Table2] TO DISK='C:\BackupTest\Table2.bak' WITH INIT
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
SQLCMD -S YourPC\SQLEXPRESS -U sa -P sa -i backup.sql > Backup.LOG
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
RESTORE DATABASE [Table1] FROM DISK = N'C:\BackupTest\Table1.bak' WITH FILE = 1, NOUNLOAD, STATS = 5
RESTORE DATABASE [Table2] FROM DISK = N'C:\BackupTest\Table2.bak' WITH FILE = 1, NOUNLOAD, STATS = 5
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
SQLCMD -S YourPC\SQLEXPRESS -U sa -P sa -i restore.sql > Restore.LOG
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
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
Execute ('Kill ' + @spid)
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname) and spid > @spid
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
Execute ('Kill ' + @spid)
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname) and spid > @spid
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
SQLCMD -S YourPC\SQLEXPRESS -U sa -P sa -i ForceKill.sql > ForceKill.LOG
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を実行する。