In the previous section, we introduced the automatic database backup and upload operations, and then completed the automatic remote database function.
First, close all user connections. The Code is as follows;
Create proc killDb
@ Dbname varchar (1000)
As
Begin
Declare @ s varchar (40)
Declare tb cursor
Select N 'Kill '+ cast (spid as varchar) from master... sysprocesses
Where dbid = db_id (@ dbname)
Open tb
Fetch next from tb into @ s
While @ fetch_status = 0
Exec (@ s)
Fetch next from tb into @ s
Close tb
Deallocate tb
End
Next, execute Restoration:
Exec killDb 'northwind '-- close all user connections
Exec xp_cmdshell '"C: \ Program Files \ WinRAR \ UNRAR.exe" e-df-O + D :\~ Temp \ Northwind.rar D :\~ Temp \'
If day (getdate () = 8
Begin
Restore database [Northwind] from disk = 'd :\~ Temp \ Northwind. bak 'with recovery, replace, move n 'mxcement _ data' TO n' e: \ Northwind. mdf ',
Move n 'mxcement _ log' TO n' e: \ Northwind. ldf' -- recovery cannot restore other Log files
End
Else
Begin
Restore database [Northwind] from disk = 'd :\~ Temp \ Northwind. bak 'with norecovery, replace move n 'mxcement _ data' TO n' e: \ Northwind. mdf ',
Move n 'mxcement _ log' TO n' e: \ Northwind. ldf' -- norecovery can restore other Log files
Restore database [Northwind] from disk = 'd :\~ Temp \ Northwind. cha 'with recovery
End
