Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Incremental backup
- Thread starter Zoom1234
- Start date
When you say "incremental backup" are you refering perhaps to a differential backup? Are you talking SQL Server 2000 or SQL Server 2005 (though I don't know of an incremental backup in 2k5 either)?
If SQL 2000, that's fairly easy. Look up "BACKUP, differential database backup" in the Books Online Index for a fairly comprehensive description of the process.
Differentials only backup the stuff in your DB that has changed since the last full backup. It is NOT a replacement for a complete backup because you still need the complete backup to restore your DB if it crashes. Also, you can't create a differential backup if you haven't first created a complete backup file.
Usually, you need the most recent complete backup, the last (most recent) differential backup after that date and then all the transaction log backup files since the most recent differential backup to get the most comprehensive restore possible after a disaster. Ideally, if you use differentials, you should do a complete backup at least once a week, perhaps twice a week. Otherwise, you're just asking for trouble.
All the above information is SQL 2000 related backup information. Backups for SQL 2005 have changed enormously as they allow for such additional things as database snapshots for data only restoration, online file restoration, and online page restoration. Differential backups still exist in 2k5, but I'm still playing around with the interface and haven't used that option yet. However, there is a new feature called "partial backups" which is mainly designed for SIMPLE recovery DBs but can be used with FULL & BULK-LOGGED. It backs up only the data that is on Read/Write files (does not backup Read Only files), including the primary file.
To get full details on the new SQL 2005 backup processes, you'll want to go to Microsoft's website and get the SQL Server 2005 Books Online unless you already have a copy available to you.
Hope this helps you out.
Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.
If SQL 2000, that's fairly easy. Look up "BACKUP, differential database backup" in the Books Online Index for a fairly comprehensive description of the process.
Differentials only backup the stuff in your DB that has changed since the last full backup. It is NOT a replacement for a complete backup because you still need the complete backup to restore your DB if it crashes. Also, you can't create a differential backup if you haven't first created a complete backup file.
Usually, you need the most recent complete backup, the last (most recent) differential backup after that date and then all the transaction log backup files since the most recent differential backup to get the most comprehensive restore possible after a disaster. Ideally, if you use differentials, you should do a complete backup at least once a week, perhaps twice a week. Otherwise, you're just asking for trouble.
All the above information is SQL 2000 related backup information. Backups for SQL 2005 have changed enormously as they allow for such additional things as database snapshots for data only restoration, online file restoration, and online page restoration. Differential backups still exist in 2k5, but I'm still playing around with the interface and haven't used that option yet. However, there is a new feature called "partial backups" which is mainly designed for SIMPLE recovery DBs but can be used with FULL & BULK-LOGGED. It backs up only the data that is on Read/Write files (does not backup Read Only files), including the primary file.
To get full details on the new SQL 2005 backup processes, you'll want to go to Microsoft's website and get the SQL Server 2005 Books Online unless you already have a copy available to you.
Hope this helps you out.
Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.
- Thread starter
- #3
Catadmin,
Thanks very much for the reply and it certainly was helpful.
Now frankly writing, i am not sure what back up i need, wheather its a differntial or incremantal backup.
What i want is to deploy changes in development server to production server (that may be sql server data as well as .net scripts) at the same time ensuring the production server data is sucure and not getting overwritten.
I am clueless how to go about it.
Any inputs are welcome.
TIA
Thanks very much for the reply and it certainly was helpful.
Now frankly writing, i am not sure what back up i need, wheather its a differntial or incremantal backup.
What i want is to deploy changes in development server to production server (that may be sql server data as well as .net scripts) at the same time ensuring the production server data is sucure and not getting overwritten.
I am clueless how to go about it.
Any inputs are welcome.
TIA
Your first task is to do some research. Honestly, no one here on this group can tell you what backup solutions to use because none of us work with your network & SQL setup. Because each environment is different, each environment requires a different solution.
Do some research in the Books Online on backups, then do some googling on SQL Server 2000 (assuming that's your version) Backups. Microsoft has some good articles, so check there too.
is one of the papers they have on the issue.
Once you've done your research, check with the hardware /network guys to see what kind of backup solutions they implement, because you'll want to integrate their backups with yours. Backing up your databases won't do you a lot of good if you just keep the files on the machine. You need to back up the backup files to removable media which is taken off-site as part of good disaster recovery practices.
Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.
Do some research in the Books Online on backups, then do some googling on SQL Server 2000 (assuming that's your version) Backups. Microsoft has some good articles, so check there too.
is one of the papers they have on the issue.
Once you've done your research, check with the hardware /network guys to see what kind of backup solutions they implement, because you'll want to integrate their backups with yours. Backing up your databases won't do you a lot of good if you just keep the files on the machine. You need to back up the backup files to removable media which is taken off-site as part of good disaster recovery practices.
Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question