Automate Backup Database on SQL Server, Part 1: Create VB Script

This entry is part 1 of 2 in the series Automate Backup Database on SQL Server


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.

Create 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

Series NavigationAutomate Backup Database on SQL Server, Part 2: Create Scheduled Task >>

47 Comments

  1. Marco February 10, 2009
  2. linglom February 10, 2009
  3. april February 18, 2009
  4. linglom February 19, 2009
  5. juvy cagape March 8, 2009
  6. V May 28, 2009
  7. linglom May 31, 2009
  8. Joe Hogan June 9, 2009
  9. oliver June 18, 2009
  10. linglom June 18, 2009
  11. Learning Sql June 19, 2009
  12. linglom June 26, 2009
  13. loupi July 22, 2009
  14. linglom July 23, 2009
  15. loupi July 24, 2009
  16. Nithn August 8, 2009
  17. linglom August 9, 2009
  18. Nithn August 11, 2009
  19. hcs October 21, 2009
  20. linglom October 26, 2009
  21. Marcus November 6, 2009
  22. linglom November 9, 2009
  23. Marcus November 9, 2009
  24. linglom November 17, 2009
  25. Abhishek November 22, 2009
  26. linglom November 25, 2009
  27. Ya S December 14, 2009
  28. Ya S December 14, 2009
  29. JK December 16, 2009
  30. linglom January 6, 2010
  31. SU January 7, 2010
  32. SU January 7, 2010
  33. Ya S January 8, 2010
  34. SU January 8, 2010
  35. SU January 11, 2010
  36. Ya S January 11, 2010
  37. linglom January 12, 2010
  38. SU January 12, 2010
  39. linglom January 13, 2010
  40. SU January 13, 2010
  41. Jamil Shah February 12, 2010
  42. Peter February 20, 2010
  43. linglom February 23, 2010
  44. Alext82 July 11, 2010
  45. sql_neophyte September 13, 2010
  46. Carlos October 17, 2010
  47. MarkV June 11, 2012

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.