Backup to Network Drive: differential

Showing posts with label differential. Show all posts

Thursday, March 8, 2012

backupset

Here is my situation:
I have a stored procedure that runs full, differential and
log backups for a particular database.
Each backup has unique name based on the type, day of the
week and the time (e.g. Full_Backup_Monday_12-30).
Thus, backups are kept for one week.
When I review the backup set table, I see the name of my
backup, but in many cases I see an old date for the backup
and start and completion dates. However, when I review
the server, the newer backups are present.
My questions are:
Why isn't the backupset table being updated with the most
recent information?
When is the backupset table updated?
Thanks,
MichaelHi Michael,
Could you create a database backup as following steps?
1. Expand a server group, and then expand a server.
2. Expand databases, right-click the database, point to all tasks, and then
click backup database
3. Type the backup set name in the Name box and Select Database -complete
4. Under Destination, click Tape or Disk, and then specify a backup
destination.
If no backup destinations appear, click Add to add an existing backup
device or to create a new one.
5. Click Ok to create (Don't select the Schedule check box)
6. Check to see the backupset table again.
Is the backupset table updated immediately?
According to my test, the backupset table is updated on my side. When a
real backup finishes, a new record will be inserted in the table. Does it
work on your side? Generally, we do not recommended to directly query
system table. Could you tell me your detailed scenario?
This posting is provided "AS IS" with no warranties, and confers no rights.
Sincerely,
Michael Shao
Microsoft Support Engineer
| Content-Class: urn:content-classes:message
| From: "Michael" <michael_schall@.unionsanitary.com>
| Sender: "Michael" <michael_schall@.unionsanitary.com>
| Subject: backupset
| Date: Mon, 7 Jul 2003 16:51:46 -0700
| Lines: 22
| Message-ID: <06c801c344e2$b9f90bf0$a301280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcNE4rn5YC8MduACSZi2OM0YFuqZFQ==| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa09.phx.gbl
| Xref: cpmsftngxa09.phx.gbl microsoft.public.sqlserver.server:23185
| NNTP-Posting-Host: TK2MSFTNGXA11 10.40.1.163
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Here is my situation:
|
| I have a stored procedure that runs full, differential and
| log backups for a particular database.
|
| Each backup has unique name based on the type, day of the
| week and the time (e.g. Full_Backup_Monday_12-30).
| Thus, backups are kept for one week.
|
| When I review the backup set table, I see the name of my
| backup, but in many cases I see an old date for the backup
| and start and completion dates. However, when I review
| the server, the newer backups are present.
|
| My questions are:
| Why isn't the backupset table being updated with the most
| recent information?
|
| When is the backupset table updated?
|
| Thanks,
| Michael
|

Backups taking progressively longer

My Backups are taking progressively longer to complete. I run Full once a
week, Differential once a night and TLogs every hour. The TLogs are what are
really bothersome. They used to take less than a minute but now are going on
40 minutes. The databases are not that large. One is 108MB and the other is
80. I am lookng at defragmenting and Re-Indexing options but not aware of any
other place to look. Any Ideas? Thanks.Run DBCC OPENTRAN against the DB. It should point you to the SPID of an
open transaction. You may have to kill the SPID.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
My Backups are taking progressively longer to complete. I run Full once a
week, Differential once a night and TLogs every hour. The TLogs are what are
really bothersome. They used to take less than a minute but now are going on
40 minutes. The databases are not that large. One is 108MB and the other is
80. I am lookng at defragmenting and Re-Indexing options but not aware of
any
other place to look. Any Ideas? Thanks.|||Do you have another server you can copy the databases and test the backups
there. Judging by the size, you can even test it in a laptop.
Are you backing up locally or to a network share?
"AkAlan" wrote:
> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what are
> really bothersome. They used to take less than a minute but now are going on
> 40 minutes. The databases are not that large. One is 108MB and the other is
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
> other place to look. Any Ideas? Thanks.|||Hi,
Just take a look into the old transaction log backup files and new
ones..Probaly you will be having huge Transaction log backup files.
As well as monitor the server processes using SP_WHO and see if there is any
blocks. Also ensure that 2 backups are not running in parallel.
Thanks
Hari
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what
> are
> really bothersome. They used to take less than a minute but now are going
> on
> 40 minutes. The databases are not that large. One is 108MB and the other
> is
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of
> any
> other place to look. Any Ideas? Thanks.|||In addition tot he other posts, please show us your BACKUP LOG command. (I just want to verify that
don't use NO_TRUNCATE or COPY_ONLY option for these.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> My Backups are taking progressively longer to complete. I run Full once a
> week, Differential once a night and TLogs every hour. The TLogs are what are
> really bothersome. They used to take less than a minute but now are going on
> 40 minutes. The databases are not that large. One is 108MB and the other is
> 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
> other place to look. Any Ideas? Thanks.|||Thanks for everyones help. I do have a development copy on another server and
will give that a try today. There are no Running Transactions when I run the
DBCC OPENTRAN . There are occasions of parallel backups running, I start one
database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
Here is the command I use to run the backup on one database.
BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
I'm going to research defragmentation today and see what is going on with
that. I haven't run any maintenance on the indexes since converting from an
mdb 10 months ago. I'm wearing both an administrator and developer hat and
have been slacking in the admin department. Thanks again for all the help.
"Tibor Karaszi" wrote:
> In addition tot he other posts, please show us your BACKUP LOG command. (I just want to verify that
> don't use NO_TRUNCATE or COPY_ONLY option for these.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> > My Backups are taking progressively longer to complete. I run Full once a
> > week, Differential once a night and TLogs every hour. The TLogs are what are
> > really bothersome. They used to take less than a minute but now are going on
> > 40 minutes. The databases are not that large. One is 108MB and the other is
> > 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
> > other place to look. Any Ideas? Thanks.
>
>|||In 2000 and earlier, a database backup will block a transaction log backup (and vice versa).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
> Thanks for everyones help. I do have a development copy on another server and
> will give that a try today. There are no Running Transactions when I run the
> DBCC OPENTRAN . There are occasions of parallel backups running, I start one
> database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
> at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
> Here is the command I use to run the backup on one database.
> BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME => N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
>
> I'm going to research defragmentation today and see what is going on with
> that. I haven't run any maintenance on the indexes since converting from an
> mdb 10 months ago. I'm wearing both an administrator and developer hat and
> have been slacking in the admin department. Thanks again for all the help.
>
> "Tibor Karaszi" wrote:
>> In addition tot he other posts, please show us your BACKUP LOG command. (I just want to verify
>> that
>> don't use NO_TRUNCATE or COPY_ONLY option for these.)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
>> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
>> > My Backups are taking progressively longer to complete. I run Full once a
>> > week, Differential once a night and TLogs every hour. The TLogs are what are
>> > really bothersome. They used to take less than a minute but now are going on
>> > 40 minutes. The databases are not that large. One is 108MB and the other is
>> > 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
>> > other place to look. Any Ideas? Thanks.
>>|||Thanks Tibor, The two backups running are from different databases, is that
an issue?
"Tibor Karaszi" wrote:
> In 2000 and earlier, a database backup will block a transaction log backup (and vice versa).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
> > Thanks for everyones help. I do have a development copy on another server and
> > will give that a try today. There are no Running Transactions when I run the
> > DBCC OPENTRAN . There are occasions of parallel backups running, I start one
> > database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
> > at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
> > Here is the command I use to run the backup on one database.
> > BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
> > Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME => > N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
> >
> >
> > I'm going to research defragmentation today and see what is going on with
> > that. I haven't run any maintenance on the indexes since converting from an
> > mdb 10 months ago. I'm wearing both an administrator and developer hat and
> > have been slacking in the admin department. Thanks again for all the help.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> In addition tot he other posts, please show us your BACKUP LOG command. (I just want to verify
> >> that
> >> don't use NO_TRUNCATE or COPY_ONLY option for these.)
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> >> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> >> > My Backups are taking progressively longer to complete. I run Full once a
> >> > week, Differential once a night and TLogs every hour. The TLogs are what are
> >> > really bothersome. They used to take less than a minute but now are going on
> >> > 40 minutes. The databases are not that large. One is 108MB and the other is
> >> > 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
> >> > other place to look. Any Ideas? Thanks.
> >>
> >>
> >>
>|||That should not be an issue, blocking only occurs on the same database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:39CED623-D1D8-495B-A79A-34EF26510754@.microsoft.com...
> Thanks Tibor, The two backups running are from different databases, is that
> an issue?
> "Tibor Karaszi" wrote:
>> In 2000 and earlier, a database backup will block a transaction log backup (and vice versa).
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
>> news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
>> > Thanks for everyones help. I do have a development copy on another server and
>> > will give that a try today. There are no Running Transactions when I run the
>> > DBCC OPENTRAN . There are occasions of parallel backups running, I start one
>> > database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
>> > at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
>> > Here is the command I use to run the backup on one database.
>> > BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
>> > Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =>> > N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
>> >
>> >
>> > I'm going to research defragmentation today and see what is going on with
>> > that. I haven't run any maintenance on the indexes since converting from an
>> > mdb 10 months ago. I'm wearing both an administrator and developer hat and
>> > have been slacking in the admin department. Thanks again for all the help.
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> In addition tot he other posts, please show us your BACKUP LOG command. (I just want to verify
>> >> that
>> >> don't use NO_TRUNCATE or COPY_ONLY option for these.)
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
>> >> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
>> >> > My Backups are taking progressively longer to complete. I run Full once a
>> >> > week, Differential once a night and TLogs every hour. The TLogs are what are
>> >> > really bothersome. They used to take less than a minute but now are going on
>> >> > 40 minutes. The databases are not that large. One is 108MB and the other is
>> >> > 80. I am lookng at defragmenting and Re-Indexing options but not aware of any
>> >> > other place to look. Any Ideas? Thanks.
>> >>
>> >>
>> >>
>>|||Why are you using NO_TRUNCATE? This will cause your log to grow.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
Thanks for everyones help. I do have a development copy on another server
and
will give that a try today. There are no Running Transactions when I run the
DBCC OPENTRAN . There are occasions of parallel backups running, I start one
database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
Here is the command I use to run the backup on one database.
BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME =N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
I'm going to research defragmentation today and see what is going on with
that. I haven't run any maintenance on the indexes since converting from an
mdb 10 months ago. I'm wearing both an administrator and developer hat and
have been slacking in the admin department. Thanks again for all the help.
"Tibor Karaszi" wrote:
> In addition tot he other posts, please show us your BACKUP LOG command. (I
> just want to verify that
> don't use NO_TRUNCATE or COPY_ONLY option for these.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
> > My Backups are taking progressively longer to complete. I run Full once
> > a
> > week, Differential once a night and TLogs every hour. The TLogs are what
> > are
> > really bothersome. They used to take less than a minute but now are
> > going on
> > 40 minutes. The databases are not that large. One is 108MB and the other
> > is
> > 80. I am lookng at defragmenting and Re-Indexing options but not aware
> > of any
> > other place to look. Any Ideas? Thanks.
>
>|||Darn... The reason I asked for the BACKUP command in the first place was to spot if there is a
NO_TRUNCATE or COPY_ONLY option in there. So the command was posted but I didn't see the option when
reading the command. Time for a visit with the eye doctor methinks.
NO_TRUNCATE is the reason the log backup takes progressively longer.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23VI1JrdCHHA.4832@.TK2MSFTNGP06.phx.gbl...
> Why are you using NO_TRUNCATE? This will cause your log to grow.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> .
> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
> news:15185BD8-8010-4EC8-B103-C28E487D137D@.microsoft.com...
> Thanks for everyones help. I do have a development copy on another server
> and
> will give that a try today. There are no Running Transactions when I run the
> DBCC OPENTRAN . There are occasions of parallel backups running, I start one
> database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedule
> at 7:00 AM. The first one that ran at 6:00 this morning still took 20 min.
> Here is the command I use to run the backup on one database.
> BACKUP LOG [Operations] TO DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\Operations backup' WITH NOINIT , NOUNLOAD , NAME => N'Operations backup', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
>
> I'm going to research defragmentation today and see what is going on with
> that. I haven't run any maintenance on the indexes since converting from an
> mdb 10 months ago. I'm wearing both an administrator and developer hat and
> have been slacking in the admin department. Thanks again for all the help.
>
> "Tibor Karaszi" wrote:
>> In addition tot he other posts, please show us your BACKUP LOG command. (I
>> just want to verify that
>> don't use NO_TRUNCATE or COPY_ONLY option for these.)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
>> news:B4F0AD9F-4F0B-4CFE-8F96-781369BC470F@.microsoft.com...
>> > My Backups are taking progressively longer to complete. I run Full once
>> > a
>> > week, Differential once a night and TLogs every hour. The TLogs are what
>> > are
>> > really bothersome. They used to take less than a minute but now are
>> > going on
>> > 40 minutes. The databases are not that large. One is 108MB and the other
>> > is
>> > 80. I am lookng at defragmenting and Re-Indexing options but not aware
>> > of any
>> > other place to look. Any Ideas? Thanks.
>>
>

backups SQL

I want to have a full backup once per month and the a twice daily differential back up.So that I can go back to any half day point in the last 4 weeks

But do I get my full monthly backup to overwrite the existing full one in order to start again. and then does this mean that if i want to restore I can't go back 1 week because I have just done a full back up. (hope that makes sense)

e.g. I do a full monthly on the 30th which overwrites the existing media (incl all the diffs) then decide I want to go back to how the database was on the 22nd of the month...I can't because I've only got a full back up from the 30th)

Cheers

ICW

Why are you only doing a full backup once a month?

My standard practice is to do a full backup once a day, and backup the transaction log once an hour. (I've also got alerts set up to trigger a transaction log backup if the log exceeds 50% of its capacity.) I back up to disk, which creates files with the date and time of the backup, and then back those files to tape each night. I keep the disk backups on disk for 3 days, which allows me quick access to the backup in case of a problem. Beyond the 3 days it's relatively easy to recover the disk files from the appropriate tape, which is kept for 90 days. With this design I can recover any database to any time within the last 3 months.

Saturday, February 25, 2012

backups - complete, differential and transaction log

Hi,
I am new to database administration and I was looking at the backups setup
on a database-
Full backup - every week, overwrite existing media
Differential backup - every night, append to media
Transaction log backup - every hour, append to media
The size of the differential backup file has grown to 40 GB while the actual
database full backup is just 1 GB. This led me to wonder why do we need to
use the setting "Append to media" for the Differential Backup. It should be
"Overwrite existing media" since I am using a disk to write the backups?
Am I correct? What's the correct setting for transaction log backups? Thanks.For DIFFERENTIAL backups: When you have to restore from backups, you will
need to restore the last FULL backup, and then restore the last DIFFERENTIAL
backup made since the last FULL backup (OR) ALL TRANSACTION LOG backups
since the last FULL backup. (And then, of course, the remaining TLOG backups
made since the last DIFFERENTIAL.)
Having DIFFERENTIAL backups mean we have to 'handle' fewer TLOG backups
during the stressed restore
It is ok to have only the last DIFFERENTIAL.
TLOG backups should be 'Append to Media' DO NOT overwrite. When there is a
FULL backup, you may not 'need' to keep any DIFFERENTIAL or TLOG backups.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:39DA30A9-9846-4802-A326-288F1C909F64@.microsoft.com...
> Hi,
> I am new to database administration and I was looking at the backups setup
> on a database-
> Full backup - every week, overwrite existing media
> Differential backup - every night, append to media
> Transaction log backup - every hour, append to media
> The size of the differential backup file has grown to 40 GB while the
> actual
> database full backup is just 1 GB. This led me to wonder why do we need to
> use the setting "Append to media" for the Differential Backup. It should
> be
> "Overwrite existing media" since I am using a disk to write the backups?
> Am I correct? What's the correct setting for transaction log backups?
> Thanks.|||Thanks, Arnie for the quick reply. When you say that "TLOG backups should be
'Append to Media' DO NOT overwrite", we just need to keep the TLOG backups
since the last DIFFERENTIAL BACKUP, we don't need any TLOG backup before that.
Am I correct?
"Arnie Rowland" wrote:
> For DIFFERENTIAL backups: When you have to restore from backups, you will
> need to restore the last FULL backup, and then restore the last DIFFERENTIAL
> backup made since the last FULL backup (OR) ALL TRANSACTION LOG backups
> since the last FULL backup. (And then, of course, the remaining TLOG backups
> made since the last DIFFERENTIAL.)
> Having DIFFERENTIAL backups mean we have to 'handle' fewer TLOG backups
> during the stressed restore
> It is ok to have only the last DIFFERENTIAL.
> TLOG backups should be 'Append to Media' DO NOT overwrite. When there is a
> FULL backup, you may not 'need' to keep any DIFFERENTIAL or TLOG backups.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:39DA30A9-9846-4802-A326-288F1C909F64@.microsoft.com...
> > Hi,
> >
> > I am new to database administration and I was looking at the backups setup
> > on a database-
> > Full backup - every week, overwrite existing media
> > Differential backup - every night, append to media
> > Transaction log backup - every hour, append to media
> >
> > The size of the differential backup file has grown to 40 GB while the
> > actual
> > database full backup is just 1 GB. This led me to wonder why do we need to
> > use the setting "Append to media" for the Differential Backup. It should
> > be
> > "Overwrite existing media" since I am using a disk to write the backups?
> >
> > Am I correct? What's the correct setting for transaction log backups?
> > Thanks.
>
>|||What you need to keep only you can decide. When you do a restore, you use:
Most recent db backup
Most recent diff backup
All subsequent log backups after the diff backup.
But:
Say you had a database corruption introduced between the last db backup and the most recent diff
backup. And say that the corruption is not still present if you restore according to above scheme.
You would not want to restore the most recent db backup, and all subsequent log backup (so the
corruption is not likely to be re-introduced).
Or say that you need a state of the database as it were at tuesday 14:34. If you did db backup sun,
diff mon, diff, tue, diff wed, diff thu, you would now want to restore db, diff tue night, and log
backups until that point in time.
So you do need to carefully consider your possible restore scenarios, before you can say what you
need.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:3E7C4E57-2AAB-47C1-A365-99AA537DC2AD@.microsoft.com...
> Thanks, Arnie for the quick reply. When you say that "TLOG backups should be
> 'Append to Media' DO NOT overwrite", we just need to keep the TLOG backups
> since the last DIFFERENTIAL BACKUP, we don't need any TLOG backup before that.
> Am I correct?
> "Arnie Rowland" wrote:
>> For DIFFERENTIAL backups: When you have to restore from backups, you will
>> need to restore the last FULL backup, and then restore the last DIFFERENTIAL
>> backup made since the last FULL backup (OR) ALL TRANSACTION LOG backups
>> since the last FULL backup. (And then, of course, the remaining TLOG backups
>> made since the last DIFFERENTIAL.)
>> Having DIFFERENTIAL backups mean we have to 'handle' fewer TLOG backups
>> during the stressed restore
>> It is ok to have only the last DIFFERENTIAL.
>> TLOG backups should be 'Append to Media' DO NOT overwrite. When there is a
>> FULL backup, you may not 'need' to keep any DIFFERENTIAL or TLOG backups.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>> You can't help someone get up a hill without getting a little closer to the
>> top yourself.
>> - H. Norman Schwarzkopf
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:39DA30A9-9846-4802-A326-288F1C909F64@.microsoft.com...
>> > Hi,
>> >
>> > I am new to database administration and I was looking at the backups setup
>> > on a database-
>> > Full backup - every week, overwrite existing media
>> > Differential backup - every night, append to media
>> > Transaction log backup - every hour, append to media
>> >
>> > The size of the differential backup file has grown to 40 GB while the
>> > actual
>> > database full backup is just 1 GB. This led me to wonder why do we need to
>> > use the setting "Append to media" for the Differential Backup. It should
>> > be
>> > "Overwrite existing media" since I am using a disk to write the backups?
>> >
>> > Am I correct? What's the correct setting for transaction log backups?
>> > Thanks.
>>

Backups

I am using the maintenance plan wizard for Full backups and tlog backups. How do I include differential backups? Apparently I can't as part of the wizard, can I combine the current plan (full backups + tlog backups) thru the wizard and add a diff backup
seperately?
How to schedule them so that recovery would make sense?
Thanks
Yes you would simply create another job that does the diff backup... ( You
can acutally do this with the backup job Dialog box in SQL Enterprise
manager, then choose schedule.)
If you backup the whole db daily you might diff every 4 hours.. but most
folks will use diffs when they back the database up weekly... They'll do a
diff at the end of the business day, and perhaps another after nightly
batches...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:EAF59FCE-E8ED-4C23-ACC8-4A089CD1815E@.microsoft.com...
> I am using the maintenance plan wizard for Full backups and tlog backups.
How do I include differential backups? Apparently I can't as part of the
wizard, can I combine the current plan (full backups + tlog backups) thru
the wizard and add a diff backup seperately?
> How to schedule them so that recovery would make sense?
> Thanks
>

backups

database backups and differential backups

i took database back up on 01-jan-2005 and from 2nd jan onwards im taking differential backups. if i want to restore the database to a new system, is it sufficient that i restore the back up which i took on 1st jan , and the most recent differential backup ? will the entire data till the last back up date be restored?From BOL:
The sequence for restoring differential database backups is:

1.Restore the most recent database backup.

2.Restore the last differential database backup.

3.Apply all transaction log backups created after the last differential database backup was created if you use Full or Bulk-Logged Recovery.

yes if u restore to the last differential database backup the database will be recoverd to that point of time.|||I'm 99% sure that you'll need to restore the last full backup, plus each differential backup in sequence in order to get back to the state of the last differential dump you've got. In other words, if you are missing the backup from day 13 out of 30, you can only get to the point of the backup from day 12.

You only explicitly restore the last file, but all of the files need to be present in order for the restore to be successful.

-PatP|||that means if i have 10 differential back ups named 1,2,..10

i'll have to restore the last full back up (on jan 1st) and then restore each of 1,2,3...10 in order. am i correct?

one more question.

BOL (under Differential Database Backups ) says that "A differential database backup records only the data that has changed since the last database backup".

so is it necessary that i restore full backup & differential backups from 1 to 10

i think restoring full back up + differential backup no 10 will give me all the data .. i donno whether im correct..

pl discuss|||You have to restore only the last diff. backup made, not the whole sequence.
See 'Tracking Modified Extents' topic in Books Online for details. mojza

Backups

I am using the maintenance plan wizard for Full backups and tlog backups. H
ow do I include differential backups? Apparently I can't as part of the wiz
ard, can I combine the current plan (full backups + tlog backups) thru the w
izard and add a diff backup
seperately?
How to schedule them so that recovery would make sense?
ThanksYes you would simply create another job that does the diff backup... ( You
can acutally do this with the backup job Dialog box in SQL Enterprise
manager, then choose schedule.)
If you backup the whole db daily you might diff every 4 hours.. but most
folks will use diffs when they back the database up weekly... They'll do a
diff at the end of the business day, and perhaps another after nightly
batches...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:EAF59FCE-E8ED-4C23-ACC8-4A089CD1815E@.microsoft.com...
> I am using the maintenance plan wizard for Full backups and tlog backups.
How do I include differential backups? Apparently I can't as part of the
wizard, can I combine the current plan (full backups + tlog backups) thru
the wizard and add a diff backup seperately?
> How to schedule them so that recovery would make sense?
> Thanks
>

Monday, February 13, 2012

backup types

hi
Can any one recomend what is difference between
differential and incremental backup in SQL Server 2000.
Thanks
KalyanThere is no difference. There is no such thing in SQL Server as an
Incremental backup command just Differential. People use the terms as one
all the time though.
--
Andrew J. Kelly SQL MVP
"Kalyan" <Kalyan@.discussions.microsoft.com> wrote in message
news:7ACE845D-46D1-4374-B273-C2B30589BC31@.microsoft.com...
> hi
> Can any one recomend what is difference between
> differential and incremental backup in SQL Server 2000.
> Thanks
> Kalyan|||There isn't an "incremental" backup in SQL. However, if you are familiar
with NT backup. The term differential backup in SQL is equal to incremental
backup in NT. It backs up the changes since the last full backup.
"Kalyan" wrote:
> hi
> Can any one recomend what is difference between
> differential and incremental backup in SQL Server 2000.
> Thanks
> Kalyan

Thursday, March 8, 2012

backupset

Backups taking progressively longer

Backups taking progressively longer

Backups taking progressively longer

backups SQL

Saturday, February 25, 2012

backups - complete, differential and transaction log

backups - complete, differential and transaction log

backups - complete, differential and transaction log

Backups

backups

Backups

Monday, February 13, 2012

backup types

Backup to Network Drive

Blog Archive

About Me