Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pdset
Constraint Violating Yak Guru
310 Posts |
 Posted - 2010-06-16 : 23:55:47
|
| Patrons,I want to establish the differential backups in addition to the existing full backups.Through the Maintenance plans I haven't got through not knowing what could be the reason obviously it says with the user it connected unable to proceed.If not with maintenance plans (which is easy to do) is it required to do with the scripting for Differential backups?If so what kind of script like a stored procedure?Is it necessary to have previous full backup on the disk to proceed with Differential backup?Thanks to all who participate. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-17 : 02:36:46
|
| "Is it necessary to have previous full backup on the disk to proceed with Differential backup?"I can answer that part: Yes, you do have to have made a previous FULL backup (and you will need that Full backup if you want to restore from the Differential)Edit: clarified |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-06-17 : 03:47:52
|
| It's not necessary to have the full backup on disk *at the time you run* the differential backup but a differential backup is not possible if a full backup has never been made. If you need to do a restore you need to first restore the full backup with norecovery, then apply the differential backup with recovery.- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-17 : 04:23:39
|
| Good point, well made! |
|
|
|
pdset
Constraint Violating Yak Guru
310 Posts |
Posted - 2010-06-17 : 22:17:31
|
| Proved right here Lum bago.I could able to generate without Full Backup, but what would be the scenario for second time getting Differential backup - is it based on the last differential or do I need to get once more full backup to get the second differential. As How I need to script as this into one single execution. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-06-18 : 02:35:29
|
| The difference between a differential backup and a translogbackup is that only the last diff-backup has to be restored in addition to the full backup while all the translog backups will have to be restored following the full backup. If you make one full backup and three diff-backups the first two diff-backups will be obsolete since the last diff backup will contain all of the changes since the last full backup. Pseudo code:backup database mydatabase to disk = 'c:\backup_full.bak'backup database mydatabase to disk = 'c:\backup_diff1.bak' with differentialbackup database mydatabase to disk = 'c:\backup_diff2.bak' with differentialbackup database mydatabase to disk = 'c:\backup_diff3.bak' with differentialrestore database mydatabase from disk = 'c:\backup_full.bak' with norecoveryrestore database mydatabase from disk = 'c:\backup_diff3.bak' with recovery- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
Yeoh Ray Mond
Starting Member
49 Posts |
Posted - 2010-06-20 : 04:37:11
|
| >> is it based on the last differential or do I need to get once more full backup to get the second differential.A differential backup is based off the last full database backup. There is no limit as to the number of differential backups you can make.Ray MondSQLBackupRestore.com - your quick guide to SQL Server backup and recovery issuesSQL Image Viewer - retrieve, view, convert and export images directly from SQL Server, SQLite and Firebird databases.SQL Data Sets - share and distribute SQL Server and Oracle data sets securely and easily |
|
|
|
|
|
|
Differential backups