February 21, 2008 at 9:53 pm
Hello,
I'm a SQL newbie.
We have a MS SQL Server.
a) It has it's own native SQL backup that runs every night (6 p.m.) and it does a full backup every night.
b) Backup Exec backups the DB on this SQL server as well, Differential on the weekdays (7 p.m.) and Full on Friday (7 p.m.).
I'm not sure why it's setup that way but those are the backup jobs that run.
Doesn't the native SQL backup that's done as full backup every night reset the archive bits and thus the b)'s differential backups are just backing up only the changes since the a)'s very last full backup?
If so, if the DB needs to be restored to say last Tuesday, we cannot restore successfully using only the b) backups since b)'s differential backup that was run on last Tuesdy only has the changes since 6 p.m. on that day since a)'s full backup has reset the archive bits.
Is this how it works?
Thank you!
February 26, 2008 at 1:42 pm
I am behind you.
February 26, 2008 at 5:26 pm
Yes, you are correct - full backups reset the flags unless you use the COPY_ONLY option.
February 27, 2008 at 11:43 am
I hope you are confusing yourself with SQL and windows backups.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 28, 2008 at 10:13 am
Most people advise you keep products such as Bacukp Exec well away from SQL Server. They may produce a backup directly from SQL Server, but you may not get a workable recovery...
The best way to use Backup Exec is to back up your SQL Server backups. This works fine, and exploits the strengths of both products.
When you do a SQL Server backup, normally it will write to a new file. If you configure SQL Server backup to re-use an existing file, it will reset the Archive bit on the file. If Backup Exec uses the Archive bit to determine what should be in its differential backup, then it will include the SQL backup file in each differential, which should be what you want to happen.
If you do a SQL Server differential backup, the SQL differential will only back up those pages marked dirty. When you do a full SQL Server backup, within SQL Server it will reset the 'updated' flag for every page it backs up.
If you have databases in 'Full' or 'Bulk Logged' recovery mode, you must also do transaction log backups.
Books Online (BOL) has a lot of useful information on backups and recovery. It is also worth Googling for articles about SQL Backup and Recovery. This can give you a broader understanding of backup and recovery issues than a few sentences in a newsgroup.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 3, 2008 at 9:04 pm
Thanks guys.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply