Hello, Welcome to my blog. If you like feel free to refer others

Friday, 7 October 2011

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:
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

  1. 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.
  2. 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.
  3. 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  
  4. 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  

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.
    Add Scheduled Task
  3. On Scheduled Task Wizard, click Next.
    Scheduled Task Wizard
  4. On Select Program to Run, click Browse and select the VB script file.
    Browse to VB Script
  5. Define name of the task and select when to perform the task. In this example, I want to backup daily. Click Next.
    Select Daily
  6. Select time to run this task. I set to 1:00 AM every day. Click Next.
    Set Time
  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.
    User Account
  8. Click Finish to complete creating a task schedule.
    Finish Create 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.

    Task Schedule
 Happy learning........

    No comments:

    Post a Comment