恢復資料庫 (程式自動化)

恢復測試之前，最好先移除已存在的資料庫

Drop_DataBase_All.bat
--------------------------------------------
echo "DROP DATABASE IEDB01"
osql -S. -Usa -P1234 -Q"DROP DATABASE IEDB01"

echo "DROP DATABASE IEDB03"
osql -S. -Usa -P1234 -Q"DROP DATABASE IEDB03"

接下來的工作，就交給電腦慢慢做吧 !!
(用個 Excel 檔來設定環境，自動產生相關的批次檔案)

解壓批次檔_ALL-20110131.bat
--------------------------------------------
Title 解壓批次檔_ALL-20110131.bat

CALL 解壓批次檔_IEDB01.bat
CALL 解壓批次檔_IEDB03.bat

osql -S. -Usa -P1234 -iLocalDB_iemis_delete.sql
osql -S. -Usa -P1234 -iLocalDB_iemis_owner.sql
osql -S. -Usa -P1234 -iLocal_tempdb_iemis_owner.sql
' (這是修正資料庫擁有者權限的必要指令)

解壓批次檔_IEDB01.bat
--------------------------------------------
echo "==== DB恢復及刪除解壓檔案 ===="
osql -S. -Usa -P1234 -iRestore_IEDB01.sql

Restore_IEDB01.sql
--------------------------------------------
RESTORE DATABASE IEDB01 FROM DISK = 'E:\A-Zip\IEDB01_20110131\IEDB01-2011-01-31.bak'
Go

LocalDB_iemis_delete.sql
--------------------------------------------
delete IEDB01..sysusers
where name='iemis'
Go

.
.
.

LocalDB_iemis_owner.sql
--------------------------------------------
print 'IEDB01'
use IEDB01
Go
sp_adduser   @loginame ='iemis' , @name_in_db = 'iemis'
Go
sp_addrolemember @rolename = 'db_owner' , @membername = 'iemis'
Go

.
.
.

Local_tempdb_iemis_owner.sql
--------------------------------------------
use tempdb
GO
sp_adduser   @loginame ='iemis' , @name_in_db = 'iemis'
GO
sp_addrolemember @rolename = 'db_owner' , @membername = 'iemis'
GO