* Microsoft SQL Server 2000
* Microsoft SQL Server 2008 R2
Want to :
* Create Schedulling Automatic Restore of Database
* Use an Task Scheduler
* Using VBScript
* Using osql command
* Create an output file if already done restore processs
* To know the last progress date of restore
Step :
1. Example, Database "Sharing_File"
2. Example, Source file that want to Restore "sharing_file.dat"
3.
Create a new file with name
A. "AutoRestoreDB_SharingFile.vbs"
B. "AutoRestoreDB_SharingFile_LOG.txt"
and fill the below script ( in file AutoRestoreDB_SharingFile.vbs ),
-----CODE START-----
OPTION EXPLICIT
dim TimeStampInfo
dim objFSO, objoutputFile, objFile
Const ForAppending = 8
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objoutputFile = objFSO.CreateTextFile("AutoRestoreDB_SharingFile_bat.bat")
Set objFile = objFSO.OpenTextFile("D:\AutoRestoreDB\AutoRestoreDB_SharingFile_LOG.txt", ForAppending)
TimeStampInfo = cstr(year(now)) & _
right(cstr(100+month(now)),2) & _
right(cstr(100+day(now)),2)
objoutputFile.writeline "osql -S (local) -U sa -P sasasa -d master -Q ""ALTER DATABASE [sharing_file] SET SINGLE_USER WITH ROLLBACK IMMEDIATE"""
objoutputFile.writeline "osql -S (local) -U sa -P sasasa -d master -Q ""EXEC sp_detach_db 'sharing_file'"""
objoutputFile.writeline "osql -S (local) -U sa -P sasasa -d master -Q ""EXEC sp_attach_db 'sharing_file', 'D:\Database\sharing_file_Data.MDF', 'D:\Database\sharing_file_Log.LDF'"""
objoutputFile.writeline "osql -S (local) -U sa -P sasasa -d master -Q ""RESTORE DATABASE sharing_file FROM DISK ='D:\SourceRestore\sharing_file.dat' WITH FILE = 1, MOVE 'sharing_file_Data' TO 'D:\Database\sharing_file_Data.MDF', MOVE 'sharing_file_Log' TO 'D:\Database\sharing_file_Log.LDF' , NOUNLOAD, REPLACE, STATS = 10 "" -o D:\AutoRestoreDB\AutoRestoreDB_SharingFile_Output.txt "
objFile.WriteBlankLines(0)
objFile.WriteLine Now
objoutputFile.close
objFile.Close
-----CODE END-----
4.
Try to run,
first, double-click of file "AutoRestoreDB_SharingFile.vbs"
then, the file "AutoRestoreDB_SharingFile_bat.bat" will be created automatically.
5.
Next, double-click the file "AutoRestoreDB_SharingFile_bat.bat"
and will create automatically file results name "AutoRestoreDB_SharingFile_Output.txt"
6.
Here, will create in "Control Panel > Scheduled Task", for
A. "AutoRestoreDB_SharingFile.vbs"
B. "AutoRestoreDB_SharingFile_bat.bat"
6.A.
Schedulling for "AutoRestoreDB_SharingFile.vbs"
6.B.
Schedulling for "AutoRestoreDB_SharingFile_bat.bat"
And there is 2 Scheduled Task,
7.
Testing to run the schedule task,
First run the VBScript
Then run then Batch File
8.
Check the results, to make sure the Database is already restore successfully.
