Script Backup Sql Server Express

Chopin breaks that paradigm by presenting the suicide of Edna Pontellier as heroic. The art of dying book 1450.

If your current implementation of SQL Server Express meets your needs with the exception of this dilemma with obtaining a backup, you will be enthused to discover that there is a way to accomplish this task using the Express Edition (without pausing the SQL Server service.

Related Articles

• 1 Auto-Update AVG Free
• 2 Run an Excel Spreadsheet As a Service
• 3 Set Avast to Auto-scan
• 4 Auto Schedule With Tweetdeck

You use the 'sqlcmd' command in the Windows command line to set up a task to back up a SQL Server Express computer. SQL Server Express is provided by Microsoft for free, so you can create a database and store values in small businesses or for personal use. Zindagi gulzar hai episode 1. Scheduled backups copy your database tables, users and stored procedures to a separate file in case the database crashes.

Click the Windows 'Start' button and type 'cmd' in the search text box. Press 'Enter' to open the Windows command line.

Type 'sqlcmd' and press 'Enter.' This command starts the SQL Server Express service on your server.

Create variables for the schedule. The following code creates a location for the backup, the frequency when the backup occurs and the database name:

set @db = 'customers' set @frequency = 'weekly' set @backup_location = 'c:backups'

Call the stored procedure to execute the scheduled task. The following code schedules the task:

exec expressmaint @db, @frequency, @backup_location

About the Author

Jim Campbell has been a computer engineer for over five years. He excels in hardware repair, computer programming and troubleshooting, and software design. He is currently attending Florida Atlantic University, pursuing a master's degree in computer and electrical engineering and fine-tuning his technical writing abilities.

Choose Citation Style

By: Greg Robidoux Updated: 2007-02-07 Comments (38) Related: More >Express Edition

Problem

One problem with SQL Server Express is that it does not offer a way to schedule jobs. In a previous tip, Free Job Scheduling Tool for SQL Server Express and MSDE, we looked at a free tool that allows you to create scheduled jobs for SQL Server. The one issue people often face though is what to install and what not to install on their production servers and therefore these items go without resolution. One very important part of managing SQL Server is to ensure you run backups on a set schedule. I often hear about corrupt databases and no backups, so let's take a look at another approach of scheduling backups using the included tools in both the operating system and SQL Server.

Solution

There are two components to this; the first is the backup command and the second is the scheduling needed to run the backups.

SQL Server Backup Commands

There are a few things that we need to setup. The first is to create a stored procedure that allows us to dynamically generate the backup file name as well as what type of backup to run Full, Differential or Transaction Log backup. The default for this stored procedure is to create the backups in the 'C:Backup' folder. This can be changed to any folder you like.

The following stored procedure should be created in the master database. This is just one way of handling this. There are several other options and enhancements that can be made.

The second part of this is to create a SQLCMD file to run the backup commands. Here is a simple SQLCMD file that backups databases master, model and msdb.

This file gets saved as backup.sql and for our purposes this is created in the 'C:Backup' folder, but again this could be put anywhere.

Scheduling SQL Server Backups

Included with the Windows operating system is a the ability to setup and run scheduled tasks. This is generally not used for SQL Server environments, because SQL Server Agent is so robust and gives you a lot more control and options for setting up re-occurring jobs. With SQL Server Express the only choice is to set a scheduled task at the operating system level or look for some third party tool.

To setup a scheduled task you need to open the folder where you can create a new scheduled task. This can be found under Accessories -> System Tools -> Scheduled Tasks or under Control Panel.

The first thing to do is to click on 'Add Scheduled Task' and the following wizard will run.

Select the application that you want to run. For our purposes we will be using SQLCMD.EXE. In order to find SQLCMD.EXE you will need to click on the Browse.. button.

You should be able to find this in the following directory 'C:Program FilesMicrosoft SQL Server90ToolsBinn'.

Give the scheduled task a name and specify when to perform the task.

Specify the time that this should be run.

Provide the credentials for the account that will run this task.

Finish and save the task. One thing you want to do is click on the 'Open advanced properties' so you can edit the command.

Below is the advanced properties screen. You will need to change the 'Run' command to the following:

This is broken down as follows:

• sqlcmd
• -S (this specifies the serverinstance name for SQL Server)
• serverName (this is the serverinstance name for SQL Server)
• -E (this allows you to make a trusted connection)
• -i (this specifies the input command file)
• C:BackupBackup.sql (this is the file that we created above with the command steps)

That should do it. The scheduled task should now be setup.

If you want to run the command now to make sure it works go back to the Scheduled Tasks view and right click on the task and select 'Run'.

Next Steps

• Although this is a pretty simple example this should allow you to backup your SQL Server databases pretty easily
• Modify the process to handle errors and also to take other parameters
• Also take a look at this tip, Free Job Scheduling Tool for SQL Server Express and MSDE, to see if this tool makes more sense for your environment

About the author