I am from Microsoft SQL Developer Support team , we support many data access technologies . Today I am blogging on how VBScript also can help to take backup and restore database .
I had a Developer, who was facing problem to write VBScript to backup and restore the database , which he was later adding to the Windows Setup file . Here the VBScript code I wrote to help him to resolve the issue.
VBScript to Back Up the database
Option Explicit
Dim adoCommand
Dim strConnectionstring
Dim strBackupFile
Dim strServreName
Dim strDatabaseName
Dim strfileName
' Specify backup path to save the .bak file.
strBackupFile = "D:\BackUpPath\"
'Specify the name of server and instance if present to which backup file need to be created .
strServreName ="ServerName "
'Specify the database name to which backup file need to be generated.
strDatabaseName ="DataBaseName"
' Specify connection string to Master Database on SQL server as best practise.
strConnectionstring= "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MASTER;Data Source=" & strServreName
' Backup the database .
Set adoCommand = CreateObject("ADODB.Command")
On Error Resume Next
adoCommand.ActiveConnection = strConnectionstring
If (Err.Number <> 0) Then
Wscript.Echo "Unable to connect to SQL database."
Wscript.Echo Err.Description
Wscript.Quit
End If
On Error GoTo 0
strfileName = strBackupFile & replace(replace(now,"/","_"),":","_") & "_" & strDatabaseName & ".bak"
adoCommand.CommandText = " Create database " & strDatabaseName
adoCommand.CommandText = "BackUp DATABASE "& strDatabaseName &" to disk='" & strfileName & "'"
adoCommand.Execute
MsgBox "BackUp Completed Successfully"
Set adoCommand = Nothing
VBScript to Restore the database
Option Explicit
Dim adoCommand
Dim strConnectionstring
Dim strBackupFile
Dim strServreName
Dim strDatabaseName
' Specify backup path to be restored to the database.
strBackupFile = "D:\BackUpPath\BackUpFile.bak"
'Specify the name of server and instance if present to which backup file need to be restored .
strServreName ="ServerName"
'Specify the database name to restore the file.
strDatabaseName ="DataBaseName"
' Specify connection string to Master Database on SQL server, else u may see errors like
' Error: RESTORE cannot process database 'DataBaseName' because it is in use by this session. It is recommended that the master database be used when performing this operation.
' If using the default instance, use SERVER=MyServer.
strConnectionstring= "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MASTER;Data Source=" & strServreName
' Restore database from backup.
Set adoCommand = CreateObject("ADODB.Command")
On Error Resume Next
adoCommand.ActiveConnection = strConnectionstring
If (Err.Number <> 0) Then
Wscript.Echo "Unable to connect to SQL database."
Wscript.Echo Err.Description
Wscript.Quit
End If
On Error GoTo 0
adoCommand.CommandText = " Create database " & strDatabaseName
adoCommand.CommandText = "RESTORE DATABASE "& strDatabaseName &" FROM DISK='" & strBackupFile & "' with Replace"
adoCommand.Execute
MsgBox "Restore Completed Successfully "
Set adoCommand = Nothing
Note : Please change the Path, Server name and Database name to successfully execute the code.
Happy Coding !!!
