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.
|||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...
>
>
|||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...
>
|||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...
>
>
Showing posts with label complete. Show all posts
Showing posts with label complete. Show all posts
Thursday, March 8, 2012
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 an
y
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 a
re
> 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 i
s
> 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 j
ust 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 a
re
> 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 i
s
> 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 an
d
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...
>
>|||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...[vbcol=seagreen]
> 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 t
he
> DBCC OPENTRAN . There are occasions of parallel backups running, I start o
ne
> database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedu
le
> 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 a
n
> 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:
>|||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...
>|||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...[vbcol=seagreen]
> Thanks Tibor, The two backups running are from different databases, is tha
t
> an issue?
> "Tibor Karaszi" wrote:
>|||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...
>
>
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 an
y
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 a
re
> 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 i
s
> 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 j
ust 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 a
re
> 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 i
s
> 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 an
d
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...
>
>|||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...[vbcol=seagreen]
> 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 t
he
> DBCC OPENTRAN . There are occasions of parallel backups running, I start o
ne
> database on a 30 minute schedule at 6:00 AM and another on a 1 hour schedu
le
> 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 a
n
> 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:
>|||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...
>|||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...[vbcol=seagreen]
> Thanks Tibor, The two backups running are from different databases, is tha
t
> an issue?
> "Tibor Karaszi" wrote:
>|||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...
>
>
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.
>>
>
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.
>>
>
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 tha
t.
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 DIFFERENTI
AL
> backup made since the last FULL backup (OR) ALL TRANSACTION LOG backups
> since the last FULL backup. (And then, of course, the remaining TLOG backu
ps
> 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 th
e
> top yourself.
> - H. Norman Schwarzkopf
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:39DA30A9-9846-4802-A326-288F1C909F64@.microsoft.com...
>
>|||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 acco
rding 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, d
iff 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...[vbcol=seagreen]
> 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 t
hat.
> Am I correct?
> "Arnie Rowland" wrote:
>
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 tha
t.
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 DIFFERENTI
AL
> backup made since the last FULL backup (OR) ALL TRANSACTION LOG backups
> since the last FULL backup. (And then, of course, the remaining TLOG backu
ps
> 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 th
e
> top yourself.
> - H. Norman Schwarzkopf
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:39DA30A9-9846-4802-A326-288F1C909F64@.microsoft.com...
>
>|||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 acco
rding 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, d
iff 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...[vbcol=seagreen]
> 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 t
hat.
> Am I correct?
> "Arnie Rowland" wrote:
>
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...
>
>
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...
>
>
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.
>>
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
Is there a max size of database till which complete SQL
backups can be taken
Is it that for db's > 60GB only filegroup backups can be
taken
SanjayWe have a database thats over 175gig although its split
into two datafiles, the largest datafile is 114gig. I'm
not sure if there is a max size to backup.
>--Original Message--
>Is there a max size of database till which complete SQL
>backups can be taken
>Is it that for db's > 60GB only filegroup backups can be
>taken
>Sanjay
>.
>|||There is no size limit that I know of for sql server. It's usually limited
by how fast the hardware can handle it. 60GB is not a large db to back up
at all.
--
Andrew J. Kelly
SQL Server MVP
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:067901c356ee$4fdaa440$a301280a@.phx.gbl...
> Is there a max size of database till which complete SQL
> backups can be taken
> Is it that for db's > 60GB only filegroup backups can be
> taken
> Sanjay
backups can be taken
Is it that for db's > 60GB only filegroup backups can be
taken
SanjayWe have a database thats over 175gig although its split
into two datafiles, the largest datafile is 114gig. I'm
not sure if there is a max size to backup.
>--Original Message--
>Is there a max size of database till which complete SQL
>backups can be taken
>Is it that for db's > 60GB only filegroup backups can be
>taken
>Sanjay
>.
>|||There is no size limit that I know of for sql server. It's usually limited
by how fast the hardware can handle it. 60GB is not a large db to back up
at all.
--
Andrew J. Kelly
SQL Server MVP
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:067901c356ee$4fdaa440$a301280a@.phx.gbl...
> Is there a max size of database till which complete SQL
> backups can be taken
> Is it that for db's > 60GB only filegroup backups can be
> taken
> Sanjay
Sunday, February 19, 2012
Backup/Restore Problem
I have a SQL 2000 Server that I want to create a test version of a database
on another SQL 2000 server. After creating a backup (database – complete) of
the desired database and attempting a restore on a dev server (creating a new
db name on the dev server), it fails with a consistency error. The log
contains the following message: 3266 :The backup data in 'D:\PPTest' is
incorrectly formatted. Backups cannot be appended, but existing backup sets
may still be usable. I have run dbcc checkdb checks on the source db and
they seem ok. Any ideas why I am getting this error on the restore?
Hi
Did you back this up to a tape or to disc?
Does this help?
http://msdn2.microsoft.com/en-us/library/aa238258(SQL.80).aspx
John
"TC" wrote:
> I have a SQL 2000 Server that I want to create a test version of a database
> on another SQL 2000 server. After creating a backup (database – complete) of
> the desired database and attempting a restore on a dev server (creating a new
> db name on the dev server), it fails with a consistency error. The log
> contains the following message: 3266 :The backup data in 'D:\PPTest' is
> incorrectly formatted. Backups cannot be appended, but existing backup sets
> may still be usable. I have run dbcc checkdb checks on the source db and
> they seem ok. Any ideas why I am getting this error on the restore?
|||I am having the same problem. I am backing it up to disk on another server.
Thanks
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Did you back this up to a tape or to disc?
> Does this help?
> http://msdn2.microsoft.com/en-us/library/aa238258(SQL.80).aspx
> John
> "TC" wrote:
|||Hi
If you back up locally you can run RESTORE VERIFYONLY to check it is ok
before moving it.
John
"Agho" wrote:
[vbcol=seagreen]
> I am having the same problem. I am backing it up to disk on another server.
> Thanks
>
> "John Bell" wrote:
on another SQL 2000 server. After creating a backup (database – complete) of
the desired database and attempting a restore on a dev server (creating a new
db name on the dev server), it fails with a consistency error. The log
contains the following message: 3266 :The backup data in 'D:\PPTest' is
incorrectly formatted. Backups cannot be appended, but existing backup sets
may still be usable. I have run dbcc checkdb checks on the source db and
they seem ok. Any ideas why I am getting this error on the restore?
Hi
Did you back this up to a tape or to disc?
Does this help?
http://msdn2.microsoft.com/en-us/library/aa238258(SQL.80).aspx
John
"TC" wrote:
> I have a SQL 2000 Server that I want to create a test version of a database
> on another SQL 2000 server. After creating a backup (database – complete) of
> the desired database and attempting a restore on a dev server (creating a new
> db name on the dev server), it fails with a consistency error. The log
> contains the following message: 3266 :The backup data in 'D:\PPTest' is
> incorrectly formatted. Backups cannot be appended, but existing backup sets
> may still be usable. I have run dbcc checkdb checks on the source db and
> they seem ok. Any ideas why I am getting this error on the restore?
|||I am having the same problem. I am backing it up to disk on another server.
Thanks
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Did you back this up to a tape or to disc?
> Does this help?
> http://msdn2.microsoft.com/en-us/library/aa238258(SQL.80).aspx
> John
> "TC" wrote:
|||Hi
If you back up locally you can run RESTORE VERIFYONLY to check it is ok
before moving it.
John
"Agho" wrote:
[vbcol=seagreen]
> I am having the same problem. I am backing it up to disk on another server.
> Thanks
>
> "John Bell" wrote:
Sunday, February 12, 2012
backup to two different locations
Is it possible to backup a database to two seperate
locations? Not filegroups or striping, but have two
complete copies of the backup in two seperate locations
using a single "BACKUP DATABASE" command? ThanksI do this with an alert that copies the first backup to a secondary
location. To do this create an alert that is triggered when a backup is
taken. The alert the fires off a job the copies the database backup to
another location.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:2319f01c45e05$e79f22f0$a501280a@.phx.gbl...
> Is it possible to backup a database to two seperate
> locations? Not filegroups or striping, but have two
> complete copies of the backup in two seperate locations
> using a single "BACKUP DATABASE" command? Thanks|||Not that I am aware of. You could add a job step that copies the file to
the alternate location after the backup step finishes.
--
Keith
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:2319f01c45e05$e79f22f0$a501280a@.phx.gbl...
> Is it possible to backup a database to two seperate
> locations? Not filegroups or striping, but have two
> complete copies of the backup in two seperate locations
> using a single "BACKUP DATABASE" command? Thanks|||Not in SQL Server 2000, but most likely available in SQL Server 2005
(http://www.microsoft.com/sql/yukon/productinfo/top30features.asp).
<plug> MiniSQLBackup has this feature now, allowing you to make 2 or more
copies of the same backup file using a single BACKUP DATABASE command. All
backup files are created concurrently, not copied off the first backup file.
Download the trial and give it a spin.</plug>
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:2319f01c45e05$e79f22f0$a501280a@.phx.gbl...
> Is it possible to backup a database to two seperate
> locations? Not filegroups or striping, but have two
> complete copies of the backup in two seperate locations
> using a single "BACKUP DATABASE" command? Thanks
locations? Not filegroups or striping, but have two
complete copies of the backup in two seperate locations
using a single "BACKUP DATABASE" command? ThanksI do this with an alert that copies the first backup to a secondary
location. To do this create an alert that is triggered when a backup is
taken. The alert the fires off a job the copies the database backup to
another location.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:2319f01c45e05$e79f22f0$a501280a@.phx.gbl...
> Is it possible to backup a database to two seperate
> locations? Not filegroups or striping, but have two
> complete copies of the backup in two seperate locations
> using a single "BACKUP DATABASE" command? Thanks|||Not that I am aware of. You could add a job step that copies the file to
the alternate location after the backup step finishes.
--
Keith
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:2319f01c45e05$e79f22f0$a501280a@.phx.gbl...
> Is it possible to backup a database to two seperate
> locations? Not filegroups or striping, but have two
> complete copies of the backup in two seperate locations
> using a single "BACKUP DATABASE" command? Thanks|||Not in SQL Server 2000, but most likely available in SQL Server 2005
(http://www.microsoft.com/sql/yukon/productinfo/top30features.asp).
<plug> MiniSQLBackup has this feature now, allowing you to make 2 or more
copies of the same backup file using a single BACKUP DATABASE command. All
backup files are created concurrently, not copied off the first backup file.
Download the trial and give it a spin.</plug>
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:2319f01c45e05$e79f22f0$a501280a@.phx.gbl...
> Is it possible to backup a database to two seperate
> locations? Not filegroups or striping, but have two
> complete copies of the backup in two seperate locations
> using a single "BACKUP DATABASE" command? Thanks
Subscribe to:
Posts (Atom)