Cari Blog Ini

05 Oktober 2011

Script for Automatic Generate and Restore Database

Cara Kerja:
1. Detach Database
2. Attach Database
3. Delete file backup yang sudah tidak diperlukan
4. Copy file database yang akan di-restore
5. Extract file database yang sudah di-copy
6. Restore Database



Save code dibawah ini dengan nama file "GenerateRestoreDatabase.vbs"

===================================================================

OPTION EXPLICIT
dim MyTimeStamp
dim objFSO, objoutputFile

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objoutputFile = objFSO.CreateTextFile("restore.bat")


MyTimeStamp = cstr(year(now)) & _
    right(cstr(100+month(now)),2) & _
    right(cstr(100+day(now)),2)


objoutputFile.writeline "osql -S (local) -U sa -P lg -d master -Q ""EXEC sp_detach_db 'MyDatabase_1'"""
objoutputFile.writeline "osql -S (local) -U sa -P lg -d master -Q ""EXEC sp_detach_db 'MyDatabase_2'"""

objoutputFile.writeline "osql -S (local) -U sa -P lg -d master -Q ""EXEC sp_attach_db 'MyDatabase_1', 'D:\Database\MyDatabase_1.mdf', 'D:\Database\MyDatabase_1_Log.ldf'"""
objoutputFile.writeline "osql -S (local) -U sa -P lg -d master -Q ""EXEC sp_attach_db 'MyDatabase_2', 'D:\Database\MyDatabase_2.mdf', 'D:\Database\MyDatabase_2_Log.ldf'"""

objoutputFile.writeline "del ""D:\Dari DBBackup\*.bak"""
objoutputFile.writeline "del ""D:\Dari DBBackup\*.rar"""

objoutputFile.writeline "xcopy /y ""\\200.0.0.0\backup\MyDatabase_1\MyDatabase_1_" & MyTimeStamp & "_00*.rar"" ""D:\Dari DBBackup\*.*"""
objoutputFile.writeline "xcopy /y ""\\200.0.0.0\backup\MyDatabase_2\MyDatabase_2_" & MyTimeStamp & "_00*.rar"" ""D:\Dari DBBackup\*.*"""

objoutputFile.writeline """d:\rar.exe"" x ""D:\Dari DBBackup\MyDatabase_1_" & MyTimeStamp & "_00*.rar"" ""D:\Dari DBBackup\"""
objoutputFile.writeline """d:\rar.exe"" x ""D:\Dari DBBackup\MyDatabase_2_" & MyTimeStamp & "_00*.rar"" ""D:\Dari DBBackup\"""

objoutputFile.writeline "osql -S (local) -U sa -P lg -d master -Q ""RESTORE DATABASE MyDatabase_1 FROM DISK ='D:\Dari DBBackup\MyDatabase_1_db_" & MyTimeStamp & "0000.BAK' WITH MOVE 'MyDatabase_1_Data' TO 'D:\Database\MyDatabase_1.mdf',MOVE 'LGInsureNet_Log' TO 'D:\Database\MyDatabase_1_Log.ldf'"""
objoutputFile.writeline "osql -S (local) -U sa -P lg -d master -Q ""RESTORE DATABASE MyDatabase_2 FROM DISK ='D:\Dari DBBackup\MyDatabase_2_db_" & MyTimeStamp & "0000.BAK' WITH MOVE 'MyDatabase_2' TO 'D:\Database\MyDatabase_2.mdf',MOVE 'InsAccounts_Log' TO 'D:\Database\MyDatabase_2_Log.ldf'"""


objoutputFile.close

===================================================================


Cara menjalankan:
* Double klik file "GenerateRestoreDatabase.vbs"
* Kemudian akan menghasilkan file "restore.bat"