Introduction

Last year, I wrote an article about backup and restore database on SQL Server with Microsoft SQL Server Management Studio. That article (How to backup and restore database on Microsoft SQL Server 2005) was simple and plain. But it will be inconvenience if you have to backup databases frequently. So I decide to write another article. This article, I show you how to backup databases automatically on scheduled time. Therefore, you don’t have to waste time to manually backup databases on SQL Server anymore.

Let’s me explain what I’m going to do to automate the task that I’ve mentioned above. First, I’ll create a VB Script file that perform backup database on SQL Server. Then, I create a Scheduled Task to execute the script daily. That’s it, the script will be executed according to the scheduled time without any user interaction.

The article is divided into 2 parts:

  1. Part I: Create VB Script
    This is the part which you’re reading show how to create a VB Script for backup database.
  2. Part II: Create Scheduled Task
    In this part, I’ll create a task schedule to execute the VB Script on scheduled time.

Create a VB Script

  1. In the example below, I’m going to create a VB Script that backup a database Northwind on SQL Server 2005 (INSTANCE01). Then, I’ll create a Scheduled Task to execute the script at 1:00 AM daily. Sounds easy, isn’t it? Let’s see it in action.
  2. On SQL Server 2005 server, open Notepad and type the following code: 
    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

    On Error Resume Next 
 
strComputer = "."
 
'Set objWMIService = GetObject("winmgmts:" _
'    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
 
Dim sDBUser
Dim sDBPwd
Dim sDBServer
Dim sDBName
 
sDBUser = "sa"
sDBPwd = "password"
sDBServer = ".\INSTANCE01"
sDBName = "Northwind"
backupPath = "C:\Test\"
 
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
Set oBackup = CreateObject("SQLDMO.Backup")
 
oSQLServer.LoginTimeout = 30
oSQLServer.LoginSecure = True
'oSQLServer.Connect sDBServer
oSQLServer.Connect sDBServer, sDBUser, sDBPwd
 
oBackUp.Initialize = "TRUE" ' Means overwrite existing .bak file. 
oBackup.Database = sDBName
oBackup.Action = SQLDMOBackup_Database
oBackup.Files = backupPath & sDBName & ".bak"
oBackup.SQLBackup oSQLServer
 
oSQLServer.Close()

    Code Explanation:

    • Line 3: Specify server name. “.” means the local computer.
    • Line 5-6 and 24: Connect to SQL Server with Windows Authentication mode (Using current user credential). If you don’t want to specify username and password in the script, uncomment these line and comment the line 25 instead.
    • Line 8-11: Variables Declaration
    • Line 13-17: Assign values to variables.
      • Line 13: Username for connect to SQL Server
      • Line 14: Password of the username
      • Line 15: The SQL Server. For SQL Server Express Edition, the value should be “.\SQLEXPRESS”.
      • Line 16: The Database name. In this example, it is Northwind.
      • Line 17: Define location where you want to keep the backup file.
    • Line 19-20: Create Objects to perform backup.
    • Line 22-23: SQL Connection attributes.
    • Line 24: Connect to SQL Server with Windows Authentication Mode (Doesn’t need username and password). See Line 5-6 and 24 for more detail.
    • Line 25: Connect to SQL Server with SQL Authentication Mode (Specify username and password). The code above is set to connect by this method.
    • Line 27: Set to True to overwrite the existing backup file.
    • Line 28-29: Backup attributes,
    • Line 30: Set location of the backup file.
    • Line 31: Perform backup operation.
    • Line 33: Close the connection to SQL Server.
  3. Customize the code as you desired. You should change the configurations on line 13-17 to match your environment. Then, save the file to .vbs format. In this example, I save to Northwind.vbs.
  4. Next, test the script by double-click the script to execute it. You should see the Northwind.bak file in the location where you have specified in the script.
    Backup Northwind
  5. If you didn’t see the Northwind.bak, check the Application event log to see if there is any error. The figure below is the success backup message.
    Backup Message Log
  6. For create a schedule task, I’ll write it soon.

Back to top

Share and Enjoy:
  • Digg
  • del.icio.us
  • Reddit
  • Slashdot
  • Technorati
  • Google Bookmarks
  • Live
  • MSN Reporter
  • RSS
  • Twitter
  • email
  • Facebook
  • Netvibes
  • PDF
  • Yahoo! Buzz

Related post