Microsoft SQL Server has several powerful Management Tools, not least of which are the free to download Microsoft SQL Server 2014 Express. See below for download links.
MrBackup is designed for the Microsoft Windows environment, and the discussion which follows is based on this OS.
SQL
Like all SQL databases, the actual on-disk data can not simply be copy & pasted. Attempting to copy & paste a SQL data file while it is in use invariably invites disaster. In the case MS SQL Server the on-disk *.mdf, *.ldf and *.nfd files should not be copied except in disaster recovery situations. Make a proper backup instead.
MS SQL Server Studio Express
From the Microsoft Download Center:
http://www.microsoft.com/en-za/download/details.aspx?id=42299
- Microsoft SQL Server 2014 Express is a powerful and reliable free data management system that delivers a rich and reliable data store for lightweight Web Sites and desktop applications.
A Simple Solution
A simple solution is to navigate the Studio Express Management console, select the relevant database, right click Tasks and select Backup. If your options are configured, all that remains is to click the OK button, and your backup is done!
With small business, the case is often that the business lacks high quality IT skills. After all, the purpose of the business is to pursue its primary business objective and managing IT becomes just one of many, and not the only concern. In this environment easy, safe backup solutions become critical.
This type of backup requires the confidence and the skill to use the Management Tool and backup the correct database to the preferred backup media. This backup media is typically a local hard disk drive — the ‘C:\’ drive on the Windows ‘This PC’.
Disadvantages
The disadvantages of making a backup like this include being unfamiliar with the Management Studio — after all, the user is used to dealing with software, not the underlying database. Also, making a backup like this to the local, internal media, is relatively fast and simple, but the data does not leave the system, or worse, does not leave the premises. No external backup exist: the missing step is to backup the data to an off site location. Various media options exist, FTP solutions, External Hard Disk Drives and CDROMS.
Command Line Automated Backup
A suggested solution pertaining to the making of actual SQL backup, is to include this in a Windows Batch File which can be run on demand. The advantage is that the making of the backup becomes a one step, one touch backup solution.
The language used to communicate with the MS SQL Server is called Transact-SQL, or t-sql for short. A suggested simple t-sql backup script looks like this:
-- t-sql simple backup script
USE dbname;
GO
BACKUP DATABASE dbname
TO DISK = 'c:\temp\data\ondiskfilename.bak'
WITH compression, noFORMAT;
GO
With MS SQL Server, the following command line tool options are available:
- invoke-sqlcmd: the PowerShell commandlet which allows .sql scripts to be run through PowerShell to communicate on the command line with SQL Server
- sqlcmd: current, feature packed command line interface to SQL Server.
- isql : older, DB-Library way of command line communication with SQL Server. The native SQL Server protocol.
- osql : older, ODBC-based way of command line communication with SQL Server.
Take care when selecting a command line tool. PowerShell should probably be selected as the tool of choice, as it includes a power house of tools in addition to the sql commands to be invoked. In a Server environment, PowerShell should be the primary option. PowerShell is however not included by default in older Windows distributions and it may need to be installed as an additional component. Not the best choice if one is looking for a quick fix backup implementation.
The good news is that sqlcmd, osql and osql are largely compatible and that a script that works well with one of these tools should work equally well with a script originally written for another. Take care though and test the output. There are good reasons for the introduction of new, and better tools. Quirks or personal preference included in a script designed to work with one tool, may cause chaos when migrating to a new tool. And specifically, new options introduced should not be expected to work with an older tool.
The MrBackup solution!
MrBackup will select the tool to use based on various criteria, not least of which are software already installed, processing power of the host computer, process speed and compatibility with other options.
MrBackup is designed as a managed backup solution provider for small business. Are you using software in your business which runs on MS SQL Server? Then consider MrBackup as your solution of choice!
The true difference lies in the fact that MrBackup can be configured to make a one-touch backup, or run an automated, unsupervised backup process. The database will be backed up using the command line tool of choice, and this on disk file will then be further processed, with optional user encryption, before being sent offsite to a our secure data server.
This means that you do not need to know which option to select, nor understand the complex command line syntax, MrBackup will do it all for you!
Unlimited snap-shot backups of the MS SQL Server data stored on a remote secure server at any time!
Is there any better solution available?
DOWNLOAD
Microsoft SQL Server 2014 Express
http://www.microsoft.com/en-za/download/details.aspx?id=42299
Microsoft SQL Server Management Studio Express (SSMSE)
http://www.microsoft.com/en-za/download/details.aspx?id=8961