<

Schedule MS SQL Backups via GUI With SQL Express

Published on
10,978 Points
4,978 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
Comment
Author:gwickert
0 Comments

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Join & Write a Comment

Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month