restoring sql with VBscrip Costas Markou 4/17/2007 3:28:03 PM I am trying to find a way of restoring an SQL database with a VBScript. I found everything for backup and no topics, articles or examples for the restore process. If some one could help??! Thanx!

Re: restoring sql with VBscrip "Richard Mueller [MVP]" <rlmueller-nospam[ at ]ameritech.nospam.net> 4/17/2007 4:52:06 PM Costas Markou wrote: [Quoted Text] >I am trying to find a way of restoring an SQL database with a VBScript. > I found everything for backup and no topics, articles or examples for the > restore process. > If some one could help??! Thanx! I have used VBScript program similar to below to restore. This only restores a full backup. It does not handle Transaction Log backups. See SQL books online documentation for RESTORE DATABASE. ============== ' VBScript Program to restore the MyDatabase database from a specified ' backup. Modify the backup file name and connection string (server ' name) for your situation. The database cannot be in use during the ' restore. Note that the connection string references the "Master" ' database rather than "MyDatabase". Nothing, not even this script, ' can be accessing the MyDatabase database during the restore. That is ' why the script connects to the Master database. Option Explicit Dim adoCommand Dim strConnect Dim strBackupFile ' Specify backup to be restored. strBackupFile = "c:\scripts\Backup\12172006_MyDatabase.bak" ' Specify connection string to Master Database on SQL server. ' If using the default instance, use SERVER=MyServer. strConnect = "DRIVER=SQL Server;Trusted_Connection=Yes;" _ & "DATABASE=Master;SERVER=MyServer\MyInstance" ' Restore database from backup. Set adoCommand = CreateObject("ADODB.Command") On Error Resume Next adoCommand.ActiveConnection = strConnect If (Err.Number <> 0) Then Wscript.Echo "Unable to connect to Master database." Wscript.Echo Err.Description Wscript.Quit End If On Error GoTo 0 adoCommand.CommandText = "RESTORE DATABASE MyDatabase FROM DISK='" _ & strBackupFile & "'" adoCommand.Execute Set adoCommand = Nothing -- Richard Mueller Microsoft MVP Scripting and ADSI Hilltop Lab - http://www.rlmueller.net --

Re: restoring sql with VBscrip Costas Markou 4/18/2007 9:14:04 AM Thanks for your help. I have a problem with the connection string "as i suppose" because i get an error message "[Microsoft][ODBC SQL Server Driver]Timeout expired" in line 35 "adoCommand.Execute". Should i make a system DSN connection in the ODBC administrator "Richard Mueller [MVP]" wrote: [Quoted Text] > Costas Markou wrote: > > >I am trying to find a way of restoring an SQL database with a VBScript. > > I found everything for backup and no topics, articles or examples for the > > restore process. > > If some one could help??! Thanx! > > I have used VBScript program similar to below to restore. This only restores > a full backup. It does not handle Transaction Log backups. See SQL books > online documentation for RESTORE DATABASE. > ============== > ' VBScript Program to restore the MyDatabase database from a specified > ' backup. Modify the backup file name and connection string (server > ' name) for your situation. The database cannot be in use during the > ' restore. Note that the connection string references the "Master" > ' database rather than "MyDatabase". Nothing, not even this script, > ' can be accessing the MyDatabase database during the restore. That is > ' why the script connects to the Master database. > > Option Explicit > > Dim adoCommand > Dim strConnect > Dim strBackupFile > > ' Specify backup to be restored. > strBackupFile = "c:\scripts\Backup\12172006_MyDatabase.bak" > > ' Specify connection string to Master Database on SQL server. > ' If using the default instance, use SERVER=MyServer. > strConnect = "DRIVER=SQL Server;Trusted_Connection=Yes;" _ > & "DATABASE=Master;SERVER=MyServer\MyInstance" > > ' Restore database from backup. > Set adoCommand = CreateObject("ADODB.Command") > On Error Resume Next > adoCommand.ActiveConnection = strConnect > If (Err.Number <> 0) Then > Wscript.Echo "Unable to connect to Master database." > Wscript.Echo Err.Description > Wscript.Quit > End If > On Error GoTo 0 > adoCommand.CommandText = "RESTORE DATABASE MyDatabase FROM DISK='" _ > & strBackupFile & "'" > adoCommand.Execute > > Set adoCommand = Nothing > > -- > Richard Mueller > Microsoft MVP Scripting and ADSI > Hilltop Lab - http://www.rlmueller.net > -- > > >

Re: restoring sql with VBscrip Costas Markou 4/18/2007 9:20:02 AM ....And i get a message about the database i am restoring "Database 'MyDatabase' cannot be opend. It is in the middle of a restore" "Richard Mueller [MVP]" wrote: [Quoted Text] > Costas Markou wrote: > > >I am trying to find a way of restoring an SQL database with a VBScript. > > I found everything for backup and no topics, articles or examples for the > > restore process. > > If some one could help??! Thanx! > > I have used VBScript program similar to below to restore. This only restores > a full backup. It does not handle Transaction Log backups. See SQL books > online documentation for RESTORE DATABASE. > ============== > ' VBScript Program to restore the MyDatabase database from a specified > ' backup. Modify the backup file name and connection string (server > ' name) for your situation. The database cannot be in use during the > ' restore. Note that the connection string references the "Master" > ' database rather than "MyDatabase". Nothing, not even this script, > ' can be accessing the MyDatabase database during the restore. That is > ' why the script connects to the Master database. > > Option Explicit > > Dim adoCommand > Dim strConnect > Dim strBackupFile > > ' Specify backup to be restored. > strBackupFile = "c:\scripts\Backup\12172006_MyDatabase.bak" > > ' Specify connection string to Master Database on SQL server. > ' If using the default instance, use SERVER=MyServer. > strConnect = "DRIVER=SQL Server;Trusted_Connection=Yes;" _ > & "DATABASE=Master;SERVER=MyServer\MyInstance" > > ' Restore database from backup. > Set adoCommand = CreateObject("ADODB.Command") > On Error Resume Next > adoCommand.ActiveConnection = strConnect > If (Err.Number <> 0) Then > Wscript.Echo "Unable to connect to Master database." > Wscript.Echo Err.Description > Wscript.Quit > End If > On Error GoTo 0 > adoCommand.CommandText = "RESTORE DATABASE MyDatabase FROM DISK='" _ > & strBackupFile & "'" > adoCommand.Execute > > Set adoCommand = Nothing > > -- > Richard Mueller > Microsoft MVP Scripting and ADSI > Hilltop Lab - http://www.rlmueller.net > -- > > >

Re: restoring sql with VBscrip "Richard Mueller [MVP]" <rlmueller-nospam[ at ]ameritech.nospam.net> 4/18/2007 7:03:42 PM Sorry. You must replace "MyDatabase" throughout with the actual name of your database. Also, replace "MyServer" with the actual name of your SQL Server. If you use a named instance, replace "MyInstance" with the name of your instance. Otherwise, if you use the default instance, remove "MyInstance" and the leading slash. The connection string I used assumes Windows authenticated logons, so no username and password are required. More on connection strings at this site: http://www.connectionstrings.com/ -- Richard Mueller Microsoft MVP Scripting and ADSI Hilltop Lab - http://www.rlmueller.net -- "Costas Markou" <CostasMarkou[ at ]discussions.microsoft.com> wrote in message news:A3594B4D-96BD-473E-A568-74CB07E0F2B6[ at ]microsoft.com... [Quoted Text] > ...And i get a message about the database i am restoring "Database > 'MyDatabase' cannot be opend. It is in the middle of a restore" > > "Richard Mueller [MVP]" wrote: > >> Costas Markou wrote: >> >> >I am trying to find a way of restoring an SQL database with a VBScript. >> > I found everything for backup and no topics, articles or examples for >> > the >> > restore process. >> > If some one could help??! Thanx! >> >> I have used VBScript program similar to below to restore. This only >> restores >> a full backup. It does not handle Transaction Log backups. See SQL books >> online documentation for RESTORE DATABASE. >> ============== >> ' VBScript Program to restore the MyDatabase database from a specified >> ' backup. Modify the backup file name and connection string (server >> ' name) for your situation. The database cannot be in use during the >> ' restore. Note that the connection string references the "Master" >> ' database rather than "MyDatabase". Nothing, not even this script, >> ' can be accessing the MyDatabase database during the restore. That is >> ' why the script connects to the Master database. >> >> Option Explicit >> >> Dim adoCommand >> Dim strConnect >> Dim strBackupFile >> >> ' Specify backup to be restored. >> strBackupFile = "c:\scripts\Backup\12172006_MyDatabase.bak" >> >> ' Specify connection string to Master Database on SQL server. >> ' If using the default instance, use SERVER=MyServer. >> strConnect = "DRIVER=SQL Server;Trusted_Connection=Yes;" _ >> & "DATABASE=Master;SERVER=MyServer\MyInstance" >> >> ' Restore database from backup. >> Set adoCommand = CreateObject("ADODB.Command") >> On Error Resume Next >> adoCommand.ActiveConnection = strConnect >> If (Err.Number <> 0) Then >> Wscript.Echo "Unable to connect to Master database." >> Wscript.Echo Err.Description >> Wscript.Quit >> End If >> On Error GoTo 0 >> adoCommand.CommandText = "RESTORE DATABASE MyDatabase FROM DISK='" _ >> & strBackupFile & "'" >> adoCommand.Execute >> >> Set adoCommand = Nothing >> >> -- >> Richard Mueller >> Microsoft MVP Scripting and ADSI >> Hilltop Lab - http://www.rlmueller.net >> -- >> >> >>