Does anyone know if there is a way to point the Blackbaud Console backup function to use a backup device in SQL server? Currently, we running SQL 2000 enterprise edition. As far as I can tell the Blackbaud console schedule a backup by creating a database maintenance plan job being run by the SQL agent but it create a filename with date and time as part of the backup file name. We backup the backup file from disk to tape using IBM Tivoli (TSM) but there is an issue with expiring files because everday their names are different. We are considering renaming the file to the same name everyday after the backup is complete. Is there any issue with the BMC if I try to do a restore using the BMC? Since I presume the BMC track the history of the backup by the backup file name. I know a few folks out there are backing up the database bypassing the BMC. Is there any issue with Blackbaud Support when an issue arise with the application? Also, is there anyone out there is willing to share their SQL script for backing up the RE7 database to a backup device? We are running it in simple recovery mode.

We only use the Blackbaud Console to do the initial connecting of the database to SQL (so the installs will work correctly). All of our backups and maintenance jobs are scheduled through SQL so we can use the advanced functions (such as backup devices). Dan Kimble Database Technician Home School Legal Defense Association

Below is a link to some sql server best practices. http://vyaskn.tripod.com/sql_server_administration_best_practices.htm  The following script will backup all non system databases to a file of the same name. You will need to update the script by entering the directory where backups are to be stored currently the script uses the root directory of the c:\ drive. Robert Seese Network Administrator Long Trail School declare @dbname as varchar(40) declare @msgdb as varchar(40) declare @dbbkpname as varchar(40) declare @dbbkpath as varchar(40) --define cursor to hold non system database names declare rs_cursor CURSOR for select name from master.dbo.sysdatabases where name not in('master','model','pubs','tempdb') --set the location for your backup files on line below select @dbbkpath = 'c:\' open rs_cursor Fetch next from rs_cursor into @dbname IF @@FETCH_STATUS <> 0 PRINT " <<db>>" WHILE @@FETCH_STATUS = 0 BEGIN select @msgdb= "database backup in progress on: " + @dbname PRINT @msgdb select @dbbkpname= @dbbkpath + @dbname + '.dat' --backup database with init option overwrites previous database backup backup database @dbname to disk=@dbbkpname with init FETCH NEXT FROM rs_cursor INTO @dbname END CLOSE rs_cursor deallocate rs_cursor For reference below is the syntax for the transact sql backup command. BACKUP DATABASE { database_name | @database_name_var } TO < backup_device > [ ,...n ] [ WITH [ BLOCKSIZE = { blocksize | @blocksize_variable } ] [ [ , ] DESCRIPTION = { 'text' | @text_variable } ] [ [ , ] DIFFERENTIAL ] [ [ , ] EXPIREDATE = { date | @date_var } | RETAINDAYS = { days | @days_var } ] [ [ , ] PASSWORD = { password | @password_variable } ] [ [ , ] FORMAT | NOFORMAT ] [ [ , ] { INIT | NOINIT } ] [ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ] [ [ , ] MEDIANAME = { media_name | @media_name_variable } ] [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ] [ [ , ] NAME = { backup_set_name | @backup_set_name_var } ] [ [ , ] { NOSKIP | SKIP } ] [ [ , ] { NOREWIND | REWIND } ] [ [ , ] { NOUNLOAD | UNLOAD } ] [ [ , ] RESTART ] [ [ , ] STATS [ = percentage ] ] ]

Anthony, I'd stick to SQL Server best practices and not do any renaming. With TSM, you can exclude the directory containing the backup files generated by SQL Server. Consider adding a script to make a copy of the appropriate SQL Server backup file, renaming the copy and putting it into your own custom backup directory tree that is included in the TSM backup (using the dsm.opt parms or Edit>Preferences>Include/Exclude in the TSM client). This way, you can save nightly, weekly, monthly backups in a structured directory tree and avoid needlessly piling up a bunch of files in TSM. BTW, your database data files should be excluded from TSM because they are large and they change every day. And, unless you shut down the database before TSM backs them up, the TSM images of the database files are useless for recovery anyway. Tim Levatich

Thanks, for all the reply. I did some testing using the backup device in SQL server with the sample database. This is what I ended up doing: I created a backup device to backup the RE7_SAMPLE database and I do not expire the backup set but overwrite it everytime. Instead of setting the expiration date on the backup device, I use the TSM to keep 15 days of backups in TSM. The backup device essentially stores the backup set in a file and the name does not change which helps me to get around the issue with TSM expiration policy. I tried to restore the database backup set from the SQL backup device using the BMC and it seemed to work. Also, I tried to use a backup device containing two backup sets and restore the database using the BMC and the BMC use the first backup set in the device and restored the database. It did not give any error or option to pick a different backup set in the backup device. Now, we have to decide whether we want to use the SQL backup and forget about the backup from the BMC.

Speaking of best practices, is anyone out there using a SQL Server backup device to back up directly to tape? Currently we back up to files and then back up those files on a tape, but our accounting manager has gotten it into her head that making a file copy first is inferior to backing up directly to tape. I guess her theory is that the more times information is copied, the more opportunities there are for data corruption. I'm not an IT professional, but I did work for many years as an application and database programmer, and this idea seems a little wacky to me. It seems to me, if your operating system can't even copy a file without screwing it up, you've got BIG problems. And if the device can't output your data correctly to a file, what's to say it will do any better on a tape? The best practices documents referred to earlier in this thread says that that disk file backups "are the most common and easiest medium for storing backups" in conjunction with a subsequent tape backup. My accounting person nonetheless thinks that this approach is not only inferior, it is just wrong. Opinions? Mark DeWitt Database Administrator East Bay Zoological Society [Email Removed]

I think the current backup industry best-practice for backups is D2D2T. Or Disk to Disk to Tape. I would say that backup directly to tape is not only inferior to disk first, I agree with your assessment that it is bad. We actually, back up from SQL server to disk. Then have our backup software backup that file to another disk, then it backs up that disk to tape.

We use BMC to backup to disk, then use a batch file to automatically move it to the backup server. Then we use backup software to backup to tape. Dan Barber Technology Administrator Campus Outreach Ministries

Mark, Sounds like your accounting manager is the type of person that rock climbs with only one rope, secured to the rock face by only one piece of hardware. It's lighter and more efficient, right? Less chance that you'll drop something while selecting the correct piece of equipment for the next pitch. I wouldn't climb with her and neither should you. Having multiple copies of backups is done to avoid human error and device failure (most commonly media failure of disk or tape, or tape drive device failure). The OS folks solved the problem of data copy corruption a long time ago. The weakest links in the chain are tape drives and tape cartridges. Don't make tape your first destination. Speed is an issue too. Backups are fast to disk and slow to tape. You want your database to be impeded to the least extent possible. This is another reason that D2D2T is the MINIMUM you should run for your production system. Tape should be stored offsite. If you back up to disk then to tape and these copies are kept in the same building, what is the first thing you should do when the building burns or the sprinkler system goes off by mistake? Update your resume. After all, it was YOUR responsibility to make sure the data was backed up - which REALLY means it was your job to make sure that the data was RESTORABLE. (have you actually performed a complete restore to prove that your procedures work?) Tell your accounting manager to stick to counting beans. Tim Levatich Cornell Lab of Ornithology [Email Removed]

Very well put! Good to have a chuckle first thing this morning. Bernie Maguire Oxfam Australia