Backup Guidance

  • I want to make sure I have go database maintenance adequately set up.

    Eventually this application will be supported by SQl Server experts in our offshore support centre but in the meantime I am working with technical advice from local colleagues who are all experienced with Oracle but have no SQL Server experience.

    I have a dedicated IBM X Series 255 with 3 SCSI drives - 2 RAID and 1 used for hot swop to completely backup.

    I have set up separate database maintenance plans for system and user databases.

    A full backup is taken every night

    Optimisation and integretity checking once per week.

    The transaction log for user databases is backed up once per hour.

    All these backups are to the local hard driver. The plan is to user Tivoli Storage Manager in a similar way to that used by our Oracle backups and add a step in the jobs using command DSMC i File Name so that any backups are sent to the network and then secured for as long as required.

    We have not yet worked out how to monitor SQL Server possible using a Netview SNMP trap or maybe SMTP (our mainframe email system does not directly support SMTP)

    Questions:

    Does above sound a reasonable plan?

    As there is no facility to backup Master transaction logs, should the full Master backup be more frequent so we do not lose to many login, user, password changes. Or should I instead use incremental backups for Master?

    Any advice on the Optimisation settings? What settings could I start off with.

    Is weekly for optimisation and integrity checking sufficient?

  • Your plan sounds very reasonable. You could have SQL Server backup directly to the Network share to avoid the Tivoli piece of copying files but if the Tivoli solution is something you are comfortable with then stick with it.

    As for backing up the master database, it all depends on the amount of changes you are making and whether or not you can recover from a failure with scripts, etc. I would like to think that there are not too many changes taking place in your master database within a day (or a week for that matter). Since this database stays pretty small I would just do full backups.

    Optimizations are dependant on your utilization as well. I start with a weekly schedule and see how things go from there. You can run DBCC SHOWCONTIG to see how the indexes are fairing through the week.

    I would stick with the weekly integrity checks unless you see a need to do them more frequently.

    Hope this helps.

    David

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • Thanks for the advice, just a couple of supplementaries:

    I could not get SQL Server to accept a network drive as the backup destination so I assumed it could only backup to local hard drive or tape. I get an error 'cannot open backup device \\ihqs65s1\data$\testdirectory. Device error or device off line. See the Sql server error log for more details.

    Regarding optimisation. Should change free space percentage? Should I shrink database and how much free space should I leave?

  • To back up to network file share, you need the SQL Server service and SQL server agent accounts to be a domain accounts with access to the file share. Also I do not think you can browse for the file share, you will need to type it in.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply