backup question

[gatoradeguy]

Hey all,
I'm just starting to learn SQL server administration. I'm having trouble understanding what backing up a file group actually is? How does it affect the restore versus backing up a full database? I've not had any problems with backing up the full database.

 

[DrSql]

The best reccomened method is Complete DB backup.
Backing up file groups will backup the data files and transactions logs. Then you need to attach that in case of a restore.
Common practiced backup process is,
Daily nightly full bacup, and trasnaction log backup 2 to 4 hours depenidng the transaction.
This link will give you a good start.

http://vyaskn.tripod.com/sql_server_administration_best_practices.htm

Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[mrdenny]

Backing up a filegroup can be very dangerous. If you database has 3 file groups. Primary (the default one), Active (tables with current records) and Archive (tables with old records).

If you do a full backup all 3 file groups are backed up. If you only backup the Active filegroup none of the tables in the Primary and Archive file groups are backed up.

This can then cause data integrity problems.

Now however if you have static tables which change say once per quarter then you could put those tables into a seperate file group and back that file group up after the data is changed. Then you backup the other file groups and the logs to reduce backup time.

While on paper this all looks great, I've never had to setup a system to do this. Odds are you will want to stick to the full backups, with the T-Log backups.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)