Backing Up SQL Iris

We use NT Backup (comes with Windows) to back up all our IRIS data, etc (and everything else) onto Tape as an automated process every night.

I also use NTBackup at home to back up my personal stuff (photos, etc) to an external disk drive. This also can run automatically & you select what to back up, where to & how often. It nags you if a backup fails for some reason, too.

To find NTBackup, on XP or a Server, just type ntbackup into Start--Run & it will appear. I think it's available via Control Panel on Vista.

If you're using Symantec/Veritas, say, you may also need a Backup Agent for SQL Server supplied with those products.

I noticed a piece of software on the IRIS website on Friday.  It was call SQL Automatic Backup Creation Tool
(For Windows XP/Vista machines).  It's a utility that allows you to create automatic scheduled back ups and/or change the scheduled backups installed at start up.  Although I have not used it yet, I wondered if anybody else had.

Within the SQL Server Manager, it is possible to create automated scheduled tasks.

We have a scheduled task within SQL server which backs up the iris database to a file on the server every night at 10.30pm.

Then at 11pm our normal server backup runs, which backs up all files, including the SQL backup created.

For some reason the iris utility won't backup our database, so the iris support staff kindly set up the SQL server automated backup for me via remote log-in.   I understand however that their official policy is to not touch your SQL server, but was very grateful for the member of staff to put this to one side and help us out. 

Just reading the original post again, yes the automated backup which I refer to in my post immediately above is indeed called a "maintenance plan" within SQL Server manager.

We too came up against the "you need your IT people to do this" response, but as all our IT is managed in-house we did eventually manage to get them to help us out.

Having been forced down the SQL server route, I do find it frustrating that they seem unwilling to assist with anything SQL related, and seem to assume that everyone has access to (and is willing to pay for) specialist SQL server IT support.

Hi, Mike, I already checked this point some time ago with our IT Support (which I wouldn't be without, by the way...see below). Their response was that the process uses volume shadow copy to allow snapshots of the open database files. Our backup logs show that the selected files are backed up without errors. I just checked this on a production tape & the SQL MDF & LDF files (the important ones) are there.

However#1, more to the point, I have not tested restoring the IRIS Database from this source. Obviously you would only do this if you had a serious issue with IRIS, and you wouldn't lightly test this on a production server, except maybe over a weekend.

However#2, I HAVE tested this using Vertias Backup Exec (which is now owned by Symantec). In this case you need the product that includes the SQL Backup Agent....and I know this works because we had a serious IRIS problem a couple of years ago which meant we had to restore all of IRIS from Tape. Not pretty, but it works.

Personally, I'm a bit of a Veritas fan...although we are not using it at the moment, it is easy to set up backup & restore jobs...particularly to restore "that file that someone sent us last year that got deleted"...depending on your tape retention policy, of course...

Software, tapes (or whatever), etc, cost money, but I think it is easy to undervalue the backup process. Worst case: fire or flood, & last night's tape might be all that's left of your business...not much, but better than nothing at all...

On IT Support: I think IRIS are in a tough position here. They cannot be expected support every possible combination of system, from Standalone PC to multi-server environment, at any level of detail except in so far as it directly applies to the delivery of their product. Equally, they can't expect "Accountants" necessarily to be IT experts. My personal view on this is that, given the importance of IT to the Profession (try managing without...), some level of dedicated IT support is essential. In our practice, I provide first-level support (including installing IRIS releases, etc) plus we have a monthly support contract with a local IT Company who provide Technical (Windows, etc) HelpDesk, support to major hardware/software upgrades, etc.

A common misconception of users is that existing backup facilities work fine for SQL Server applications and in the main this is a false assumption. Due to the nature of SQL Server it data files remain open as long as databases are online and available for applications to access. Most existing backup solutions unless specifically configured for SQL Server will be unable to backup SQL Server data as the files appear to be open and in-use. There are a variety of solutions including, creating timed tasks on the server which stop SQL Services enabling non-SQL supported backup solutions to backup the MDF and LDF files and then re-starting the SQL Services.  Also using timed scripts in SQL Server to create nightly SQL Server Backups (.BAK files) in a predefined location again which can be backed up by non-SQL supported backup solutions. There are also SQL Agents for backup solutions such as Veritas and ARC Server which allow direct backing up of SQL Server data and Log files.

Data can be lost from a failure to deal properly with SQL backups.It is understandable that IRIS and other suppliers are reluctant to take this responsibility but good documentation and help to IT suppliers must be provided.It is vital that computer suppliers get involved. We have however seen plenty of suppliers who have low levels of knowledge to deal with SQL themselves.Another major issue is Server and location moves. These need to be planned and dealt with in advance, if they are not thought out, you can end up with a mess on your hands. Users tend to think they can just pick the files up and move them around!!The upside of using SQL is the stability and robustness of the database, hopefully you will never need to go to a backup.

Yes I agree it is an absolute pain, I use IRIS back up within the program for the SQL database and a separate back up software for the IRIS data directory. Although it is expensive I am currently looking at the 'online' version

Having responded to the original post on this, I'm becoming curious about how complicated this seems to be getting.

I'm assuming

a) that everyone wants to back up all their data (and possibly some programs as well). The alternative is too horrible to contemplete. Obviously there are different media types (tape, DVD-RW, removable disk, memory stick, etc), but that's another discussion.

b) the same process, and hence tool, should be used for everything.

As I've said before, we use NT Backup. The comes for free with Windows XP and Vista (if you're running standalone) and with Windows Servers. It's a bit elusive to find...under Start - Programs - Accessories - System Tools on XP, for example (or just go to "Run" & type NTBACKUP into the panel), but quite easy to set up.

The advice I have, and the evidence of my tape catalogs tells me that this works for SQL. The only aspect I have not tested is a full SQL MDF/LDF file restore, but I am assured this works. It certainly works to restore other files (eg Excel XLS files) as I have tested that.

Has anyone out there used NTBackup for SQL & experienced any problems with it....? Specifically, has anyone got a catalogued tape (etc) with the SQL MDF & LDF shown, where they were UNABLE to restore those files?

Or, is NT Backup not delivered on Servers that ONLY run MSSQL?

If not, why not use it...it's easy, free & it seems to work...???

If you don't want to use NT Backup, Veritas/Symantec is excellent & definitely works, so long as yout instal & set up the SQL Backup Agent. I have tested this to Backup & Restore MS SQL successfully.

Or am I missing something?

Hi Ian

Is your Iris making backup copies of the SQL database every night to the SQLBAK folder? (IRIS:\Data\SQLBAK)

If it is, then your NT Backup will backup these files and they would sufficient for a SQL restore. (Or buy a copy of Backup exec with the SQL Agent).

If it's not then I would speak to Iris and they can set this up for you.

I would suggest running up an old machine with Windows server and trying a restore. Just have the latest Iris CD ready and your copy of SQL.

The Iris menu backup doesn't work for us but the SQLBAK files do. You do need to run a small utility which Iris will pass onto you after the restore to relink the database to the Iris program.

It's only a simply command line command and will take a few seconds to run.

Once you have done this it will give you a lot more confidence that the backups you have been taking will actually work should they be called upon.  

NTBackup is only for older operating systems which is perhaps why it isn't mentioned much these days, up to Win XP or Server 2003.  http://en.wikipedia.org/wiki/NTBackup

Server 2008 & Small Business Server 2008 has its own newer built in backup which we use, but we need the SQL Maintenance Plan to backup SQL database into a .bak file first, so that the backup file is then backed up with the main backup.

Whether NTBackup is reliable to create a backup of the SQL database which can be guaranteed to restore ok, without doing the actual database backup into a file first, I wouldn't know the answer to that.

Having a reliable backup of the database is so critical that I personally would only use the database backup method suggested by iris, or if using another method such as NTbackup then somehow test it out to be absolutely certain if there is any doubt!

Firstly thanks to all on this, it's been helpful following comments and has encouraged me to sort this out once & for all.  I run iris on a MS SBS 2003 server.

Every evening, for months, I've been running the Iris manual backup of SQL in time for the tape backup (ironically Veritas) later @ night.  They sent me scripts to schedule bak files to do the same automatically but (despite many emails, now going unanswered) they never worked besides which it hacks me off having to use techniques that I unlearned when DOS went.

A major problem I hit was our IT guys telling me that the server was rapidly running out of space and it transpired that the huge backups just build up in the SQL BAK folder, meaning that every couple of weeks I have to delete a load.

When doing this a couple of weeks ago I spotted 2 files, one called iris.bak & another iris_old.bak both timed last midnight.  I've watched the folder and sure enough they appear every night, again, no answer from Iris, or the IT support company.

Having followed the above thread I went on search and sure enough found the SQL Server Management Studio with a list of logs corresponding to these backups.  I haven't got any idea who set these up but at least I can drop the manual backups & deletes.

The final unknown however is that the "Maintenance Plans" window is blank and I can't see how or what to do in it, ie where is the bleedin routine, script, schedule that runs this every night?  Anyone know?

An interesting thread....we're on SBS 2003...when we last replaced our servers I wasn't brave enough to go for SBS 2008 as it had only just been announced....

A couple of comments to add:

1. If you're using Veritas AND the SQL Agent is available, then this backs up all the SQL Databases and you can ignore anything to do with IRIS specifically. The SQL Agent, as far as I recall, only comes with the "premium" or equivalent version, and is not necessarily configured or installed by default. But I know it works: I have had to use it for real, so "trust me on this!!!"

2. Ref earlier comments, I have now tested NT Backup to back up & restore the SQL LDF & MDF files...it works fine but for the restored files to be activated you need either a Server restart (might only require a restart of MSSQL) or you need to detach & reattach the relevant databases.

3. IRIS is by no means the only SQL database you're likely to encounter so if you have other SQL Databases you need a solution for them as well.

4. I've been reminded that NT Backup isn't used on Server 2008, but, as above, I have no live experience of this.

So for my money Veritas with the SQL Agent OR NT Backup (up to Server 2003/SQL 2005) are the simplest solution and they "just work". No need for scripts, copying files, etc.

Paul

If you have a .bak file being created at midnight, then it means the iris automatic task for backup would appear to be working.

As for where to find this - have a look under scheduled tasks as I think you may find it there.  I'm not sure where on SBS 2003 you find this.  What you are looking for is the equivalent of "Scheduled Tasks" under Control Panel in XP. 

This is what iris first tried to make work for us, but for some reason it doesn't work (maybe because we're on SBS 2008).  So this is when instead they kindly setup the task in maintenance plan in SQL for us to perform the same function.

As long as your .bak file is new every night, it sounds like your task is setup and creating the backup file for you.

It is interesting to hear that some of your emails to iris support go unanswered.  I was wondering if it was just me.  (and this isn't just on technical things like SQL).   Even an email to support asking for the contact details of the manager in charge of support for one of the modules was not answered.   The reason for the request was that I wanted to make a complaint about the standard of support in relation to an unsolved issue!

Many thanks for that, you are absolutely right, there are two schaduled tasks, one to rename the previous and another to create the new one, so what I need to do now is to try & change the timing as the tape backup happens before these run.

So a relief and a good reason to stay on SBS 2003 and leave well alone for as long as possible.  I will however keep watching the screen to make sure it continues to run.

On the question of failure of iris support to respond to emails, thinking about it we will tend to call &/or access the KB if anything needs immediate attention whereas anything less urgent I leave to an email and so perhaps that's the reason it takes them time to get back to us, ie they are deemed to be less priority?  There have been a couple of Co Sec issues like this but that was a complete re-write recently and so I'd expect things to slow up.   Generally though if you kick up a bit of a fuss someone will pay attention.

I agree, the first instance for something urgent is normally by phone.

But sometimes the problem can't be solved there and then, and so tends to "transfer" to email communication.

Or sometimes it is easier to attach a screen shot, and so the initial problem raised by email.

The thing that I find frustrating is that upon sending an email, a reply is received which states that "We aim to respond to all emails as quickly as possible and within 8 business hours.".   That would be great if it was true.

If there was a reply within 8 business hours  (even if to say that "we're looking into it and will reply within 3 days") then I would be happy, assuming that there was then a subsequent followup within the stated time.

But too often there is no reply, and a week later I'm sending another message asking for the courtesy of a response, or telephoning instead!

Hi, my name is Kip Wright, I manage the customer support team at IRIS and have been reading these posts with interest. I am always keen to hear of any ways in which we can improve our service, especially around email handling which is a channel of choice for many of our customers. We currently dedicate staff to the handling of customer emails and what I do need to do is find out specific incidents of these going astray to track them down in our system. Please supply me with contact details so I can get in touch or call me direct on 01753 212437. Thank you. Kip.

On the assumption that we are talking about SQL server standard edition upwards.  You can get reliable backups of your SQL data without complexity and cost.

The SQL Management console allows you set up a maintainence plan that will not only backup your data nightly; but also to take incremental backups every hour or so we have customers who want not only a daily backup, but they want to go back to the last few hours too.

There is only one drawback with creating a daily full back up and hourly incremental ones is that you need lots of disk space and some one to delete the backups once a week. With this proviso it is easy.

And of course you can back up the daily file to tape etc.

This is a service we can set up remotely contact me jason@healingbreeze.net

We also offer online backup solutions at good rates.

-- Jason Richards