Automate Backup Database on SQL Server

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.

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 

  

PART 1: 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()

3. 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.
4. 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.
5. 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.
     
6. 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.
     

Part 2 : Create Scheduled Task

1. Open Scheduled Task. Start -> Programs -> Accessories -> System Tools -> Scheduled Task.
2. Double-click on Add Scheduled Task to create a new task schedule.
   
3. On Scheduled Task Wizard, click Next.
   
4. On Select Program to Run, click Browse and select the VB script file.
   
5. Define name of the task and select when to perform the task. In this example, I want to backup daily. Click Next.
   
6. Select time to run this task. I set to 1:00 AM every day. Click Next.
   
7. Enter the username and password. The task will be run by this user account. If you create a VB Script that using Windows Authentication mode, you have to enter the account that has backup privilege on the SQL Server.
   
8. Click Finish to complete creating a task schedule.
   
9. The task schedule has been created. Now when the time is 1:00 AM, the task will be run as the user account that you’ve spcified in the task schedule.
   
   

 Happy learning........