SQL Backup 1

[TinRam]

I have a client with a Backup Exec 8.5 install. The issue is with backuping up a SQL database (with the SQL agent).

He has been told that you can' t backup database structure while the SQL service is running, that you can only backup data in the structure while it is running. This means that if they want to do a full backup, they will have to stop the SQL server services and be down during the backup.

Does anybody know if this is correct?

 

[Zelandakh]

You can back up SQL server using BE with the agent, but you run into big problems if data changes part way through the back up.

To be certain that things get done correctly, use the SQL server manager and create a database dump file using the wizard (SQL 7) or manually doing a database dump to disk. Either way you end up with a static data file for each database which can be backed up by BE without the SQL agent.

It basically ensures that you get no problems when you restore.

 

[Scanner]

You can also configure a "Maintenance Plan" from within the SQL Server Manager to automate the process.

I'm not sure that an MP is quite as thorough as a dump, but it has worked for us pretty well.

I have our DB Servers configured to create a complete backup, including the Tranlog, and optimizing space every four to six hours.

The frequency of your scheduling will depend on:

1 - How much data or activity you will need to recover (longer time between backups means more info you could possibly lose in case of a crash)

2 - The size of your database (can eat HUGE amounts of space if not watched)

3 - The amound of disc space available on your system (you're almost creating a copy of your database every time the MP runs)

4 - How diligent you wish to be in maintaining the right balance between points 1, 2 and 3.

I hope this helps...

 

[Zelandakh]

A maintenance plan in SQL 7 is actually an wizard based data dump and also a load of error checking and other bits.

My main databases only take up a couple of GB but the dumps take up that EVERY day and I set them to get removed every 3 days as I would run out of disk space much after that.