SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Differential backups
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pdset
Constraint Violating Yak Guru

300 Posts

Posted - 06/16/2010 :  23:55:47  Show Profile  Reply with Quote
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

United Kingdom
22403 Posts

Posted - 06/17/2010 :  02:36:46  Show Profile  Reply with Quote
"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

Edited by - Kristen on 06/17/2010 04:24:46
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 06/17/2010 :  03:47:52  Show Profile  Reply with Quote
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.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/17/2010 :  04:23:39  Show Profile  Reply with Quote
Good point, well made!
Go to Top of Page

pdset
Constraint Violating Yak Guru

300 Posts

Posted - 06/17/2010 :  22:17:31  Show Profile  Reply with Quote
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.

Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 06/18/2010 :  02:35:29  Show Profile  Reply with Quote
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 differential
backup database mydatabase to disk = 'c:\backup_diff2.bak' with differential
backup database mydatabase to disk = 'c:\backup_diff3.bak' with differential

restore database mydatabase from disk = 'c:\backup_full.bak' with norecovery
restore database mydatabase from disk = 'c:\backup_diff3.bak' with recovery




- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com
Go to Top of Page

Yeoh Ray Mond
Starting Member

Malaysia
49 Posts

Posted - 06/20/2010 :  04:37:11  Show Profile  Reply with Quote
>> 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 Mond
SQLBackupRestore.com - your quick guide to SQL Server backup and recovery issues
SQL 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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000