Creating the Database Backup Script

1. Once the tool is installed, launch Microsoft SQL Server Management Studio Express and connect to your database instance.
   
2. Expand Databases and you will see list of databases on the server/instance
3. Right click the database you want to create the script for and select “Tasks | Backup”.  A dialog box appears allowing you to define different options such as what type of backup (full or differential) you want to do, backup destination etc. Configure the available options as desired then click the “Options” page on the left-hand column. Continue configuring options accordingly. For example, you may want to select “overwrite all existing backup sets.”
4. Once all desired options are set, select “Script | Actions to File” and enter a desired file name, for example, “DatastoreBackup,” and specify the location where to save the file.This creates a .SQL file which scripts the options you defined in the prior step. The contents of your .SQL file may look like this: 

Automating the Database Backup

You can automate the backup process by creating two Scheduled Tasks.

SQLCMD Scheduled Task

1. First, create a Scheduled Task to automate the .SQL script created above. Use the Scheduled Task Wizard and when asked to select a program browse to use browse to C:\Program Files\Microsoft SQL Server\90\Tools\binn\ SQLCMD.exe. Define the Schedule Task parameters accordingly and click “Finish”.
2. Go the properties of the newly created Scheduled Task and edit the Run command as such.
   

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S .\SERVER_NAME\SQLEXPRESS -i "C:\Program Files\Microsoft SQL Server\DatastoreBackup.sql"

Note: This script/setup may not work under all cases and we hold no responsibility for information provided in this artcile. All information is provided on a "Do at your own risk" basis and may require some customisation for your setup/configuration.

Add to Favourites
Print this Article