<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Schedule MS SQL Backups via GUI With SQL Express

Published on
11,053 Points
5,053 Views
Last Modified:
Approved
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does have a few limitations.

Most small companies would tend to use SQL Express, however setting up backups using the express manager can be a bit of a pain, as it doesn't allow you to schedule them.  Well luckily the express manager still lets you script actions to an SQL script using the GUI - so for example you can setup a once off backup operation, then script that operation to an SQL script file.

Using the following method, you can schedule the backup of a live SQL database.  Please note that these steps assume you are familiar with the SQL Express GUI and also familiar with creating a scheduled task within windows.
 
The following steps must be taken:

 1. First, you must have a database on a MS SQL Server.

 2. Open SQL Express manager and right click the database that requires backing up.  Go to all tasks, backup.

 3. This will open a backup window, where you can perform one time backups with the GUI, this GUI also has an option to ‘script’ the action that you just setup.  So basically, you setup the backup location etc, then script that to a file that can be called in the batch file.

 4. Select script action and let the script generate.  Save this script to your SQL server Eg: C:\scripts\SQL\

 5. Make a new Text file, rename to ‘sqlbackup.bat’ and add the following line:
 sqlcmd -U user -P Pass -i PathToSQLScript\script.sql

 6. Save it to the same spot as your .sql script
 
That gives you the 2 files that you need.  Then all that is left to do is open up windows scheduler on the SQL server, setup a new task that calls the batch file and set it to run as often as you need (daily, weekly etc).
 
This is a great way to do backups for small companies that dont want to buy the full version of MS SQL Server, but still want the reassurance of scheduled backups.
 
Glen
0
Author:gwickert
0 Comments

Featured Post

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month