Hi all, I've been running FE and RE on MSSQL2000 for a while now, years actually. I've always used the BMC to schedule backups. Those backup bumps just get picked up by our normal backup process, everything seems to work, I've use the backup mnany times for DR testing and such. I'd like to be able to check the success/failure of the backup, or maintenance task without loading BMC, I want this to be automatically monitored by another system. What do other people do regarding backups ? Are there any logs I can monitor ? I've heard some don't use BMC and just use MSSQL to manage these tasks. I'd like to start backing up several times a day, is there a best way to do this ? Thanks [i]--- Edited at 6/7/2006 10:54:41 PM by Matthew Joyce[/i]

We MSSQL's management tools to perform backups. For me, it simply gives the options that I want. I think even if you use BMC, it creates a job on the MSSQL server which can be montiored via the Enterprise Manager. I'm not sure what your other system is, but you can use various tools (some from Microsoft, some not) to monitor it. One thing I have done is set up a notification in MSSQL to send me an email when the job completes. If I come in to work, and don't have an email saying it was successful, then I know it failed.

You can also set it up to notify you when it fails. I find that this option works better for me. I get so many emails, I can easily miss the fact that I didn't get an email about a successful backup. If I'm sending out emails only on failure, it's much harder to miss and I can also use my email filtering tools to bump up any message from the SQL Server to highest priority. Drew J. Drew Allen Children's Hospital of Philadelphia [Email Removed]

I use the BMC to backup prior to by normal daily backup using Veritas. This was the "recommended Blackbaud method". I think you could schedule multiple backups throughout the day using the BMC scheduler. Nora Nora Isaac Manager, Information Technology The ALS Association, Greater Phila. Chapter [Email Removed]

Nora, The BMC only support 1 backup schedule. To configure multiple backup schedules you would need a full version of SQL Server 2000 or 2005. And if you're setting up multiple backups on a full version of SQL you need to configure the recovery mode to FULL, the default is BASIC. Jeff Schroeder Database Manager Francis Parker School San Diego, CA [Email Removed] "Work Smarter, Not Harder!"

Thanks, Jeff. I wasn't aware of that. We currently backup once at night, but I'm looking to backup at least once more during the day. Nora Nora Isaac Manager, Information Technology The ALS Association, Greater Phila. Chapter [Email Removed]

We switched from BASIC recovery mode to FULL a while ago so we could do periodic Transaction Log backups during the day. When we saw that there was almost no impact on server performance, we increased the frequency to every 15 minutes. It's come in very handy. With the nightly full backups and then transaction log backups on the quarter hour, we can restore the database to within 15 minutes of a problem happening. We've used it several times. Considering that the only cost was not using BMC for backups - it was a no brainer. Mitch Gibbs Director, IS Advocate Charitable Foundation

Mitch, that is a very good point. I didn't go into much detail on that when I mentioned the recovery mode. The transaction logs are sequential and if a failure were to happen and you had to recover from the last full backup and the transaction logs. You would have to first restore the database and then every transaction log in the proper order until you get to the last one. But there is a catch, you have to know what you are doing. There is an option to set when you restore so that you can apply the transaction logs, Restore with No Recovery. And on the last one you change the setting so you can activate the database. I would highly recommend that anyone who is going to be doing this take a classes on SQL Administration and Programming. There is a lot to understand and know about the system. Jeff Schroeder Database Manager Francis Parker School San Diego, CA [Email Removed] "Work Smarter, Not Harder!"

Hi Mitchell! I realize this is an old post, but I wanted to inquire as to how your Full Recovery model affects your RE log (.LDF file). We have been trying to run RE7 in Full Recovery mode with transaction log backups every 15 minutes, however the .LDF grows out of control at certain points in the day (jumping by 10-15 GB). The transaction log backup is not truncating the log, and a DBCC shrinkfile command does not shrink the log either. The only solution to shrink the log after this rapid growth is to change the recovery model to simple, and then run DBCC shrinkfile. Obviously this is not practical. Have you encountered anything similar? Thanks, Kate

Associate Database Administrator Chesapeake Bay Foundation6 Herndon AvenueAnnapolis, MD 21403Ph. 443.482.2016www.cbf.org

 

You need to check the file size of data files and log files through database properties of SQL- Sever. If initial size of your data files is around 600 MB for example, try setting the initial size of your log files to 60 MB, with auto growth increment of 50%. Then you can schedule maintenance plans for data back ups (to be executed may be once a day), and log back ups (to be executed multiple times a day depending on your organization or department data recovery rules).

 

In addition you can run (schedule) the following command through the maintenance plan to shrink data and log files:

DBCC SHRINKDATABASE

 

which will shrink your database when it grows certain level (you would need to specify that level). This is a little tricky. You need to gather the following information:

• how many reports are run every day,
• how many batches are created every day
• how many static queries are created every day and
• any other users’ or systemss activities that might increase the size of the log files.

 

This would help you decide on the initial size of the log files and specify how much free space you would want to retain after executing DBCC SHRINKDATABASE.

 

I hope this helps,

Mark.