Long times for backup

Hi

I am trying to backup a sql server 2005 database of 12GB....It is residing on a windows 2003 server with pretty good configuration. It is taking extremely long time to do the backup...More than 10 mins elapsed but no backups...

Can some one suggest what the problem might be?

Regards

Imtiaz

What is your disk configuration?

Are you backing up to different disks from you the disks your data and log files reside?

I assume you are using a RAID configuration. How many disks are in your RAID arrays?

|||

We have 2 physical disks + 1 SAN

C drive -- Program files -- 12GB

D Drive -- not being used (empty) -- 55 GB

H Drive - (SAN- ATA) - 200 GB

Database files reside on H drive. Instance related infor/ master/model/tempdb databases resides on C: drive.

I am just backing upto H drive. Wehn I tried backing upto H: and D: the backup time was cut to 8 minutes totally...

This is really good but can I have further reduction in backup times.

Also, from this backup I am doing a restore of a different database on a different instance. How can I optimise my restore times also...The restore time is pretty much around 10 mins....

Thanks anyway for the reply.

|||

Without knowing much more about your hardware. To get better performance you need to have more disks in your disk groups/reduce contention in your SAN, increase CPU on your server, increase memory on your server. AS I don't know which one is the bottleneck I can say which one you need to address

You can look at thrid party backup solutions like Redgate SQL Backup and Litespeed from Quest

long running RESTORE

Hi all !
I have 5 GB backup file of DB running in SIMPLE recovery mode on SQL
2000(SP3). It takes a few minutes to restore it on another box which has
enough resurses, but when I tried to do the same on a box that is a bit old
and not so powerfull I just couldn't wait until it's done, - i canceled
restore operation. As a workaround I detached DB file and then attached them
on that box. But I'm still wondering is there another way to restore DB, may
be group by group or ...?
Thanks a lot in advance,
Alex
Backup and restore
and
sp_detach_db and sp_attach_db
are the best options
You could also try using DTS, but that would probably involve much more
effort on your part.
Keith
"Alex" <amakarov_removethis_@.healthmetrx.com> wrote in message
news:10fo5ivqif63e54@.corp.supernews.com...
> Hi all !
> I have 5 GB backup file of DB running in SIMPLE recovery mode on SQL
> 2000(SP3). It takes a few minutes to restore it on another box which has
> enough resurses, but when I tried to do the same on a box that is a bit
old
> and not so powerfull I just couldn't wait until it's done, - i canceled
> restore operation. As a workaround I detached DB file and then attached
them
> on that box. But I'm still wondering is there another way to restore DB,
may
> be group by group or ...?
> --
> Thanks a lot in advance,
> Alex
>
|||Did you restore the database on the 'old box' when the database was not yet
created? If this was the first time, the 5 Gig file needs to be created
first before the restore can start, and depending on the speed of your disk
controllers, this might take some time. When you subsequently restore the
database again, don't delete the old database first.
Since you already have the database on the old box, try restoring using the
normal way, and see if it still takes as long. And by any chance, are you
using compressed folders?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Alex" <amakarov_removethis_@.healthmetrx.com> wrote in message
news:10fo5ivqif63e54@.corp.supernews.com...
> Hi all !
> I have 5 GB backup file of DB running in SIMPLE recovery mode on SQL
> 2000(SP3). It takes a few minutes to restore it on another box which has
> enough resurses, but when I tried to do the same on a box that is a bit
old
> and not so powerfull I just couldn't wait until it's done, - i canceled
> restore operation. As a workaround I detached DB file and then attached
them
> on that box. But I'm still wondering is there another way to restore DB,
may
> be group by group or ...?
> --
> Thanks a lot in advance,
> Alex
>
|||Yes, I created DB before restoring with two file groups 2 G each and log
file. And I wasn't using compressed folder. My guess is maybe I wasn't
patient enough although I waited for about half an hour to let it finish.
But that 'old box' is really slow and that could be the case. I'll give it
another try and be more patien this time.
Thank you,
Alex
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:u2AMSofbEHA.1732@.TK2MSFTNGP09.phx.gbl...
> Did you restore the database on the 'old box' when the database was not
yet
> created? If this was the first time, the 5 Gig file needs to be created
> first before the restore can start, and depending on the speed of your
disk
> controllers, this might take some time. When you subsequently restore the
> database again, don't delete the old database first.
> Since you already have the database on the old box, try restoring using
the[vbcol=seagreen]
> normal way, and see if it still takes as long. And by any chance, are you
> using compressed folders?
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backups? Try MiniSQLBackup
>
> "Alex" <amakarov_removethis_@.healthmetrx.com> wrote in message
> news:10fo5ivqif63e54@.corp.supernews.com...
has
> old
> them
> may
>

long running RESTORE

Hi all !
I have 5 GB backup file of DB running in SIMPLE recovery mode on SQL
2000(SP3). It takes a few minutes to restore it on another box which has
enough resurses, but when I tried to do the same on a box that is a bit old
and not so powerfull I just couldn't wait until it's done, - i canceled
restore operation. As a workaround I detached DB file and then attached them
on that box. But I'm still wondering is there another way to restore DB, may
be group by group or ...?
--
Thanks a lot in advance,
AlexBackup and restore
and
sp_detach_db and sp_attach_db
are the best options
You could also try using DTS, but that would probably involve much more
effort on your part.
Keith
"Alex" <amakarov_removethis_@.healthmetrx.com> wrote in message
news:10fo5ivqif63e54@.corp.supernews.com...
> Hi all !
> I have 5 GB backup file of DB running in SIMPLE recovery mode on SQL
> 2000(SP3). It takes a few minutes to restore it on another box which has
> enough resurses, but when I tried to do the same on a box that is a bit
old
> and not so powerfull I just couldn't wait until it's done, - i canceled
> restore operation. As a workaround I detached DB file and then attached
them
> on that box. But I'm still wondering is there another way to restore DB,
may
> be group by group or ...?
> --
> Thanks a lot in advance,
> Alex
>|||Did you restore the database on the 'old box' when the database was not yet
created? If this was the first time, the 5 Gig file needs to be created
first before the restore can start, and depending on the speed of your disk
controllers, this might take some time. When you subsequently restore the
database again, don't delete the old database first.
Since you already have the database on the old box, try restoring using the
normal way, and see if it still takes as long. And by any chance, are you
using compressed folders?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Alex" <amakarov_removethis_@.healthmetrx.com> wrote in message
news:10fo5ivqif63e54@.corp.supernews.com...
> Hi all !
> I have 5 GB backup file of DB running in SIMPLE recovery mode on SQL
> 2000(SP3). It takes a few minutes to restore it on another box which has
> enough resurses, but when I tried to do the same on a box that is a bit
old
> and not so powerfull I just couldn't wait until it's done, - i canceled
> restore operation. As a workaround I detached DB file and then attached
them
> on that box. But I'm still wondering is there another way to restore DB,
may
> be group by group or ...?
> --
> Thanks a lot in advance,
> Alex
>|||Yes, I created DB before restoring with two file groups 2 G each and log
file. And I wasn't using compressed folder. My guess is maybe I wasn't
patient enough although I waited for about half an hour to let it finish.
But that 'old box' is really slow and that could be the case. I'll give it
another try and be more patien this time.
Thank you,
Alex
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:u2AMSofbEHA.1732@.TK2MSFTNGP09.phx.gbl...
> Did you restore the database on the 'old box' when the database was not
yet
> created? If this was the first time, the 5 Gig file needs to be created
> first before the restore can start, and depending on the speed of your
disk
> controllers, this might take some time. When you subsequently restore the
> database again, don't delete the old database first.
> Since you already have the database on the old box, try restoring using
the
> normal way, and see if it still takes as long. And by any chance, are you
> using compressed folders?
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backups? Try MiniSQLBackup
>
> "Alex" <amakarov_removethis_@.healthmetrx.com> wrote in message
> news:10fo5ivqif63e54@.corp.supernews.com...
has[vbcol=seagreen]
> old
> them
> may
>

long running RESTORE

Hi all !
I have 5 GB backup file of DB running in SIMPLE recovery mode on SQL
2000(SP3). It takes a few minutes to restore it on another box which has
enough resurses, but when I tried to do the same on a box that is a bit old
and not so powerfull I just couldn't wait until it's done, - i canceled
restore operation. As a workaround I detached DB file and then attached them
on that box. But I'm still wondering is there another way to restore DB, may
be group by group or ...?
--
Thanks a lot in advance,
AlexBackup and restore
and
sp_detach_db and sp_attach_db
are the best options
You could also try using DTS, but that would probably involve much more
effort on your part.
--
Keith
"Alex" <amakarov_removethis_@.healthmetrx.com> wrote in message
news:10fo5ivqif63e54@.corp.supernews.com...
> Hi all !
> I have 5 GB backup file of DB running in SIMPLE recovery mode on SQL
> 2000(SP3). It takes a few minutes to restore it on another box which has
> enough resurses, but when I tried to do the same on a box that is a bit
old
> and not so powerfull I just couldn't wait until it's done, - i canceled
> restore operation. As a workaround I detached DB file and then attached
them
> on that box. But I'm still wondering is there another way to restore DB,
may
> be group by group or ...?
> --
> Thanks a lot in advance,
> Alex
>|||Did you restore the database on the 'old box' when the database was not yet
created? If this was the first time, the 5 Gig file needs to be created
first before the restore can start, and depending on the speed of your disk
controllers, this might take some time. When you subsequently restore the
database again, don't delete the old database first.
Since you already have the database on the old box, try restoring using the
normal way, and see if it still takes as long. And by any chance, are you
using compressed folders?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Alex" <amakarov_removethis_@.healthmetrx.com> wrote in message
news:10fo5ivqif63e54@.corp.supernews.com...
> Hi all !
> I have 5 GB backup file of DB running in SIMPLE recovery mode on SQL
> 2000(SP3). It takes a few minutes to restore it on another box which has
> enough resurses, but when I tried to do the same on a box that is a bit
old
> and not so powerfull I just couldn't wait until it's done, - i canceled
> restore operation. As a workaround I detached DB file and then attached
them
> on that box. But I'm still wondering is there another way to restore DB,
may
> be group by group or ...?
> --
> Thanks a lot in advance,
> Alex
>|||Yes, I created DB before restoring with two file groups 2 G each and log
file. And I wasn't using compressed folder. My guess is maybe I wasn't
patient enough although I waited for about half an hour to let it finish.
But that 'old box' is really slow and that could be the case. I'll give it
another try and be more patien this time.
--
Thank you,
Alex
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:u2AMSofbEHA.1732@.TK2MSFTNGP09.phx.gbl...
> Did you restore the database on the 'old box' when the database was not
yet
> created? If this was the first time, the 5 Gig file needs to be created
> first before the restore can start, and depending on the speed of your
disk
> controllers, this might take some time. When you subsequently restore the
> database again, don't delete the old database first.
> Since you already have the database on the old box, try restoring using
the
> normal way, and see if it still takes as long. And by any chance, are you
> using compressed folders?
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backups? Try MiniSQLBackup
>
> "Alex" <amakarov_removethis_@.healthmetrx.com> wrote in message
> news:10fo5ivqif63e54@.corp.supernews.com...
> > Hi all !
> >
> > I have 5 GB backup file of DB running in SIMPLE recovery mode on SQL
> > 2000(SP3). It takes a few minutes to restore it on another box which
has
> > enough resurses, but when I tried to do the same on a box that is a bit
> old
> > and not so powerfull I just couldn't wait until it's done, - i canceled
> > restore operation. As a workaround I detached DB file and then attached
> them
> > on that box. But I'm still wondering is there another way to restore DB,
> may
> > be group by group or ...?
> > --
> >
> > Thanks a lot in advance,
> >
> > Alex
> >
> >
>

Long Backups with SQL Agent

We use Omniback for a backup solution. However most of the time, we could
not restore a database from the backups. We changed our strategy last week
to have the SQL Agent to backup all of the SQL Severs to a central location
and have Omniback backup the files.
Omniback took about an hour to backup a 1gig+ database. Here is the log file.
Database backed up: Database: pdi_warehouse_001_001, creation date(time):
2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, last
LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1,
TYPE=VIRTUAL_DEVICE: {'Data
Protector_(DEFAULT)_pdi_warehouse_001_001_17_36_36 '}).
Now, the same database took approximately 10 hours to back up last night.
Here is the log file.
Database backed up: Database: pdi_warehouse_001_001, creation date(time):
2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, last
LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'\\co-ap-08\SqlBkups\FOCALPOINT_pdi_warehouse_001_001_20070 4111705.BAK'}).
Why so log when using the SQL Agent? There is a 1 gig connection from the
sql server to the backup media. Any suggestion will be appreciated.
brymer28303 wrote:
> We use Omniback for a backup solution. However most of the time, we could
> not restore a database from the backups. We changed our strategy last week
> to have the SQL Agent to backup all of the SQL Severs to a central location
> and have Omniback backup the files.
> Omniback took about an hour to backup a 1gig+ database. Here is the log file.
> Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> 2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, last
> LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1,
> TYPE=VIRTUAL_DEVICE: {'Data
> Protector_(DEFAULT)_pdi_warehouse_001_001_17_36_36 '}).
> Now, the same database took approximately 10 hours to back up last night.
> Here is the log file.
> Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> 2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, last
> LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1,
> TYPE=DISK:
> {'\\co-ap-08\SqlBkups\FOCALPOINT_pdi_warehouse_001_001_20070 4111705.BAK'}).
> Why so log when using the SQL Agent? There is a 1 gig connection from the
> sql server to the backup media. Any suggestion will be appreciated.
>
Hi,
Have you tried running the backup to a local disk in order to rule out
network issues? 10 hours for doing a 1 GB database backup is definitely
far too long, so something must have gone wrong. The backup destination
for the 2 backup scenarios seems to be different, so I think you should
start looking here.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
|||Thanks Steen. Since this post, we have started received network error 64.
This gives us a place to look.
""Steen Schlüter Persson (DK)"" wrote:

> brymer28303 wrote:
> Hi,
> Have you tried running the backup to a local disk in order to rule out
> network issues? 10 hours for doing a 1 GB database backup is definitely
> far too long, so something must have gone wrong. The backup destination
> for the 2 backup scenarios seems to be different, so I think you should
> start looking here.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>

Long Backups with SQL Agent

We use Omniback for a backup solution. However most of the time, we could
not restore a database from the backups. We changed our strategy last week
to have the SQL Agent to backup all of the SQL Severs to a central location
and have Omniback backup the files.
Omniback took about an hour to backup a 1gig+ database. Here is the log file.
Database backed up: Database: pdi_warehouse_001_001, creation date(time):
2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, last
LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1,
TYPE=VIRTUAL_DEVICE: {'Data
Protector_(DEFAULT)_pdi_warehouse_001_001_17_36_36'}).
Now, the same database took approximately 10 hours to back up last night.
Here is the log file.
Database backed up: Database: pdi_warehouse_001_001, creation date(time):
2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, last
LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'\\co-ap-08\SqlBkups\FOCALPOINT_pdi_warehouse_001_001_200704111705.BAK'}).
Why so log when using the SQL Agent? There is a 1 gig connection from the
sql server to the backup media. Any suggestion will be appreciated.brymer28303 wrote:
> We use Omniback for a backup solution. However most of the time, we could
> not restore a database from the backups. We changed our strategy last week
> to have the SQL Agent to backup all of the SQL Severs to a central location
> and have Omniback backup the files.
> Omniback took about an hour to backup a 1gig+ database. Here is the log file.
> Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> 2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, last
> LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1,
> TYPE=VIRTUAL_DEVICE: {'Data
> Protector_(DEFAULT)_pdi_warehouse_001_001_17_36_36'}).
> Now, the same database took approximately 10 hours to back up last night.
> Here is the log file.
> Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> 2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, last
> LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1,
> TYPE=DISK:
> {'\\co-ap-08\SqlBkups\FOCALPOINT_pdi_warehouse_001_001_200704111705.BAK'}).
> Why so log when using the SQL Agent? There is a 1 gig connection from the
> sql server to the backup media. Any suggestion will be appreciated.
>
Hi,
Have you tried running the backup to a local disk in order to rule out
network issues? 10 hours for doing a 1 GB database backup is definitely
far too long, so something must have gone wrong. The backup destination
for the 2 backup scenarios seems to be different, so I think you should
start looking here.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Thanks Steen. Since this post, we have started received network error 64.
This gives us a place to look.
""Steen Schlüter Persson (DK)"" wrote:
> brymer28303 wrote:
> > We use Omniback for a backup solution. However most of the time, we could
> > not restore a database from the backups. We changed our strategy last week
> > to have the SQL Agent to backup all of the SQL Severs to a central location
> > and have Omniback backup the files.
> >
> > Omniback took about an hour to backup a 1gig+ database. Here is the log file.
> >
> > Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> > 2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, last
> > LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1,
> > TYPE=VIRTUAL_DEVICE: {'Data
> > Protector_(DEFAULT)_pdi_warehouse_001_001_17_36_36'}).
> >
> > Now, the same database took approximately 10 hours to back up last night.
> > Here is the log file.
> >
> > Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> > 2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, last
> > LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1,
> > TYPE=DISK:
> > {'\\co-ap-08\SqlBkups\FOCALPOINT_pdi_warehouse_001_001_200704111705.BAK'}).
> >
> > Why so log when using the SQL Agent? There is a 1 gig connection from the
> > sql server to the backup media. Any suggestion will be appreciated.
> >
> Hi,
> Have you tried running the backup to a local disk in order to rule out
> network issues? 10 hours for doing a 1 GB database backup is definitely
> far too long, so something must have gone wrong. The backup destination
> for the 2 backup scenarios seems to be different, so I think you should
> start looking here.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>

Long Backups with SQL Agent

We use Omniback for a backup solution. However most of the time, we could
not restore a database from the backups. We changed our strategy last week
to have the SQL Agent to backup all of the SQL Severs to a central location
and have Omniback backup the files.
Omniback took about an hour to backup a 1gig+ database. Here is the log fil
e.
Database backed up: Database: pdi_warehouse_001_001, creation date(time):
2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, last
LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1,
TYPE=VIRTUAL_DEVICE: {'Data
Protector_(DEFAULT)_pdi_warehouse_001_00
1_17_36_36'}).
Now, the same database took approximately 10 hours to back up last night.
Here is the log file.
Database backed up: Database: pdi_warehouse_001_001, creation date(time):
2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, last
LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1,
TYPE=DISK:
{'\\co-ap- 08\SqlBkups\FOCALPOINT_pdi_warehouse_001
_001_200704111705.BAK
'}).
Why so log when using the SQL Agent? There is a 1 gig connection from the
sql server to the backup media. Any suggestion will be appreciated.brymer28303 wrote:
> We use Omniback for a backup solution. However most of the time, we could
> not restore a database from the backups. We changed our strategy last wee
k
> to have the SQL Agent to backup all of the SQL Severs to a central locatio
n
> and have Omniback backup the files.
> Omniback took about an hour to backup a 1gig+ database. Here is the log f
ile.
> Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> 2004/05/17(18:29:34), pages dumped: 18105899, first LSN: 5111210:44:1, las
t
> LSN: 5111210:327:1, number of dump devices: 1, device information: (FILE=1
,
> TYPE=VIRTUAL_DEVICE: {'Data
> Protector_(DEFAULT)_pdi_warehouse_001_00
1_17_36_36'}).
> Now, the same database took approximately 10 hours to back up last night.
> Here is the log file.
> Database backed up: Database: pdi_warehouse_001_001, creation date(time):
> 2004/05/17(18:29:34), pages dumped: 18546755, first LSN: 5143133:52:1, las
t
> LSN: 5148376:689:1, number of dump devices: 1, device information: (FILE=1
,
> TYPE=DISK:
> {'\\co-ap- 08\SqlBkups\FOCALPOINT_pdi_warehouse_001
_001_200704111705.B
AK'}).
> Why so log when using the SQL Agent? There is a 1 gig connection from the
> sql server to the backup media. Any suggestion will be appreciated.
>
Hi,
Have you tried running the backup to a local disk in order to rule out
network issues? 10 hours for doing a 1 GB database backup is definitely
far too long, so something must have gone wrong. The backup destination
for the 2 backup scenarios seems to be different, so I think you should
start looking here.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Thanks Steen. Since this post, we have started received network error 64.
This gives us a place to look.
""Steen Schlüter Persson (DK)"" wrote:

> brymer28303 wrote:
> Hi,
> Have you tried running the backup to a local disk in order to rule out
> network issues? 10 hours for doing a 1 GB database backup is definitely
> far too long, so something must have gone wrong. The backup destination
> for the 2 backup scenarios seems to be different, so I think you should
> start looking here.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>sql

long backup duration

I recently moved my backups from the wizard generated maintenance plans to a
simple backup script. My 180GB database is now taking 7 hours to backup with
instead of the normal 4 hours. The step details show 7.359 MB/sec when the
backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
hours.
Below is my script. This is actually step 2. Step 1 deletes the previous
backup.
BACKUP DATABASE MyDB
TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
WITH INIT
During the backup, pinging the NAS device from the SQL Server takes 0ms.
I have used Perfmon and the counters seem usual from what I have researched
(e.g., http://www.sql-server-performance.co...ore_tuning.asp).
Average Device Throughput bytes/sec: 5570346
Average % Disk Time: 3.854
Average Disk Queue Length: .0193
Average IO Write Bytes/sec: 5897638
Average Split IO/sec: 0
As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
Alert Engine.
No compression is taking place on either machine.
Does anyone have any suggestions as what is causing the backup time to
almost double?
Thanks,
ray
SS2K
Hi,
Was your Wizard generated backup maintenance plan overwriting the same
backup file each time or writing to a new file each time? The only
thing I can think of is that if the backup file already exists and you
are re-initializing it takes less time. From an OS/Network standpoint
that should not hinder performance. Perhaps you can look at the wizard
generated backup plan to see if there are some option enabled by SQL
Server which may increase performance.
Shahryar
raybouk wrote:

>I recently moved my backups from the wizard generated maintenance plans to a
>simple backup script. My 180GB database is now taking 7 hours to backup with
>instead of the normal 4 hours. The step details show 7.359 MB/sec when the
>backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
>hours.
>Below is my script. This is actually step 2. Step 1 deletes the previous
>backup.
>BACKUP DATABASE MyDB
>TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
>WITH INIT
>During the backup, pinging the NAS device from the SQL Server takes 0ms.
>I have used Perfmon and the counters seem usual from what I have researched
>(e.g., http://www.sql-server-performance.co...ore_tuning.asp).
>Average Device Throughput bytes/sec: 5570346
>Average % Disk Time: 3.854
>Average Disk Queue Length: .0193
>Average IO Write Bytes/sec: 5897638
>Average Split IO/sec: 0
>As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
>Alert Engine.
>No compression is taking place on either machine.
>Does anyone have any suggestions as what is causing the backup time to
>almost double?
>Thanks,
>ray
>SS2K
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohi
bited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.
|||Delete the old backup file prior to performing the backup. Dont init the new
backup device. I found this to work the best.
On a sidenote, ping rates do not necessarily reflect thoughput performance.
"raybouk" wrote:

> I recently moved my backups from the wizard generated maintenance plans to a
> simple backup script. My 180GB database is now taking 7 hours to backup with
> instead of the normal 4 hours. The step details show 7.359 MB/sec when the
> backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
> hours.
> Below is my script. This is actually step 2. Step 1 deletes the previous
> backup.
> BACKUP DATABASE MyDB
> TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
> WITH INIT
> During the backup, pinging the NAS device from the SQL Server takes 0ms.
> I have used Perfmon and the counters seem usual from what I have researched
> (e.g., http://www.sql-server-performance.co...ore_tuning.asp).
> Average Device Throughput bytes/sec: 5570346
> Average % Disk Time: 3.854
> Average Disk Queue Length: .0193
> Average IO Write Bytes/sec: 5897638
> Average Split IO/sec: 0
> As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
> Alert Engine.
> No compression is taking place on either machine.
> Does anyone have any suggestions as what is causing the backup time to
> almost double?
> Thanks,
> ray
> SS2K
|||What was the destination of the Wizard generated backups?
There is going to be a world of difference between writing to a local disk
vs going to a NAS device.
You should be able to go into the Management Studio and look at the script
that it generates.
See what the differences are in the backup statement.
"Raoul Laoyan" <RaoulLaoyan@.discussions.microsoft.com> wrote in message
news:0C4A91B3-D06B-40DA-87EE-1D94A94A707F@.microsoft.com...[vbcol=seagreen]
> Delete the old backup file prior to performing the backup. Dont init the
> new
> backup device. I found this to work the best.
> On a sidenote, ping rates do not necessarily reflect thoughput
> performance.
> "raybouk" wrote:

long backup duration

I recently moved my backups from the wizard generated maintenance plans to a
simple backup script. My 180GB database is now taking 7 hours to backup with
instead of the normal 4 hours. The step details show 7.359 MB/sec when the
backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
hours.
Below is my script. This is actually step 2. Step 1 deletes the previous
backup.
BACKUP DATABASE MyDB
TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
WITH INIT
During the backup, pinging the NAS device from the SQL Server takes 0ms.
I have used Perfmon and the counters seem usual from what I have researched
(e.g., http://www.sql-server-performance.c...tore_tuning.asp).
Average Device Throughput bytes/sec: 5570346
Average % Disk Time: 3.854
Average Disk Queue Length: .0193
Average IO Write Bytes/sec: 5897638
Average Split IO/sec: 0
As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
Alert Engine.
No compression is taking place on either machine.
Does anyone have any suggestions as what is causing the backup time to
almost double?
Thanks,
ray
SS2KHi,
Was your Wizard generated backup maintenance plan overwriting the same
backup file each time or writing to a new file each time? The only
thing I can think of is that if the backup file already exists and you
are re-initializing it takes less time. From an OS/Network standpoint
that should not hinder performance. Perhaps you can look at the wizard
generated backup plan to see if there are some option enabled by SQL
Server which may increase performance.
Shahryar
raybouk wrote:

>I recently moved my backups from the wizard generated maintenance plans to
a
>simple backup script. My 180GB database is now taking 7 hours to backup wit
h
>instead of the normal 4 hours. The step details show 7.359 MB/sec when the
>backup completes in 7 hours and 12.453 MB/sec when the backup completes in
4
>hours.
>Below is my script. This is actually step 2. Step 1 deletes the previous
>backup.
>BACKUP DATABASE MyDB
>TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
>WITH INIT
>During the backup, pinging the NAS device from the SQL Server takes 0ms.
>I have used Perfmon and the counters seem usual from what I have researched
>(e.g., http://www.sql-server-performance.c...tore_tuning.asp).
>Average Device Throughput bytes/sec: 5570346
>Average % Disk Time: 3.854
>Average Disk Queue Length: .0193
>Average IO Write Bytes/sec: 5897638
>Average Split IO/sec: 0
>As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent
-
>Alert Engine.
>No compression is taking place on either machine.
>Does anyone have any suggestions as what is causing the backup time to
>almost double?
>Thanks,
>ray
>SS2K
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is
legally privileged. The information is solely for the use of the intended
recipient(s); any disclosure, copying, distribution, or other use of this in
formation is strictly prohi
bited. If you have received this e-mail in error, please notify the sender
by return e-mail and delete this message. Thank you.|||Delete the old backup file prior to performing the backup. Dont init the ne
w
backup device. I found this to work the best.
On a sidenote, ping rates do not necessarily reflect thoughput performance.
"raybouk" wrote:

> I recently moved my backups from the wizard generated maintenance plans to
a
> simple backup script. My 180GB database is now taking 7 hours to backup wi
th
> instead of the normal 4 hours. The step details show 7.359 MB/sec when the
> backup completes in 7 hours and 12.453 MB/sec when the backup completes in
4
> hours.
> Below is my script. This is actually step 2. Step 1 deletes the previous
> backup.
> BACKUP DATABASE MyDB
> TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
> WITH INIT
> During the backup, pinging the NAS device from the SQL Server takes 0ms.
> I have used Perfmon and the counters seem usual from what I have researche
d
> (e.g., http://www.sql-server-performance.c...tore_tuning.asp).
> Average Device Throughput bytes/sec: 5570346
> Average % Disk Time: 3.854
> Average Disk Queue Length: .0193
> Average IO Write Bytes/sec: 5897638
> Average Split IO/sec: 0
> As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent
-
> Alert Engine.
> No compression is taking place on either machine.
> Does anyone have any suggestions as what is causing the backup time to
> almost double?
> Thanks,
> ray
> SS2K|||What was the destination of the Wizard generated backups?
There is going to be a world of difference between writing to a local disk
vs going to a NAS device.
You should be able to go into the Management Studio and look at the script
that it generates.
See what the differences are in the backup statement.
"Raoul Laoyan" <RaoulLaoyan@.discussions.microsoft.com> wrote in message
news:0C4A91B3-D06B-40DA-87EE-1D94A94A707F@.microsoft.com...[vbcol=seagreen]
> Delete the old backup file prior to performing the backup. Dont init the
> new
> backup device. I found this to work the best.
> On a sidenote, ping rates do not necessarily reflect thoughput
> performance.
> "raybouk" wrote:
>

long backup duration

I recently moved my backups from the wizard generated maintenance plans to a
simple backup script. My 180GB database is now taking 7 hours to backup with
instead of the normal 4 hours. The step details show 7.359 MB/sec when the
backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
hours.
Below is my script. This is actually step 2. Step 1 deletes the previous
backup.
BACKUP DATABASE MyDB
TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
WITH INIT
During the backup, pinging the NAS device from the SQL Server takes 0ms.
I have used Perfmon and the counters seem usual from what I have researched
(e.g., http://www.sql-server-performance.com/backup_restore_tuning.asp).
Average Device Throughput bytes/sec: 5570346
Average % Disk Time: 3.854
Average Disk Queue Length: .0193
Average IO Write Bytes/sec: 5897638
Average Split IO/sec: 0
As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
Alert Engine.
No compression is taking place on either machine.
Does anyone have any suggestions as what is causing the backup time to
almost double?
Thanks,
ray
SS2KHi,
Was your Wizard generated backup maintenance plan overwriting the same
backup file each time or writing to a new file each time? The only
thing I can think of is that if the backup file already exists and you
are re-initializing it takes less time. From an OS/Network standpoint
that should not hinder performance. Perhaps you can look at the wizard
generated backup plan to see if there are some option enabled by SQL
Server which may increase performance.
Shahryar
raybouk wrote:
>I recently moved my backups from the wizard generated maintenance plans to a
>simple backup script. My 180GB database is now taking 7 hours to backup with
>instead of the normal 4 hours. The step details show 7.359 MB/sec when the
>backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
>hours.
>Below is my script. This is actually step 2. Step 1 deletes the previous
>backup.
>BACKUP DATABASE MyDB
>TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
>WITH INIT
>During the backup, pinging the NAS device from the SQL Server takes 0ms.
>I have used Perfmon and the counters seem usual from what I have researched
>(e.g., http://www.sql-server-performance.com/backup_restore_tuning.asp).
>Average Device Throughput bytes/sec: 5570346
>Average % Disk Time: 3.854
>Average Disk Queue Length: .0193
>Average IO Write Bytes/sec: 5897638
>Average Split IO/sec: 0
>As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
>Alert Engine.
>No compression is taking place on either machine.
>Does anyone have any suggestions as what is causing the backup time to
>almost double?
>Thanks,
>ray
>SS2K
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||Delete the old backup file prior to performing the backup. Dont init the new
backup device. I found this to work the best.
On a sidenote, ping rates do not necessarily reflect thoughput performance.
"raybouk" wrote:
> I recently moved my backups from the wizard generated maintenance plans to a
> simple backup script. My 180GB database is now taking 7 hours to backup with
> instead of the normal 4 hours. The step details show 7.359 MB/sec when the
> backup completes in 7 hours and 12.453 MB/sec when the backup completes in 4
> hours.
> Below is my script. This is actually step 2. Step 1 deletes the previous
> backup.
> BACKUP DATABASE MyDB
> TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
> WITH INIT
> During the backup, pinging the NAS device from the SQL Server takes 0ms.
> I have used Perfmon and the counters seem usual from what I have researched
> (e.g., http://www.sql-server-performance.com/backup_restore_tuning.asp).
> Average Device Throughput bytes/sec: 5570346
> Average % Disk Time: 3.854
> Average Disk Queue Length: .0193
> Average IO Write Bytes/sec: 5897638
> Average Split IO/sec: 0
> As for sp_who2, the only thing peculiar is a DISKIO of 875659 for SQLAgent -
> Alert Engine.
> No compression is taking place on either machine.
> Does anyone have any suggestions as what is causing the backup time to
> almost double?
> Thanks,
> ray
> SS2K|||What was the destination of the Wizard generated backups?
There is going to be a world of difference between writing to a local disk
vs going to a NAS device.
You should be able to go into the Management Studio and look at the script
that it generates.
See what the differences are in the backup statement.
"Raoul Laoyan" <RaoulLaoyan@.discussions.microsoft.com> wrote in message
news:0C4A91B3-D06B-40DA-87EE-1D94A94A707F@.microsoft.com...
> Delete the old backup file prior to performing the backup. Dont init the
> new
> backup device. I found this to work the best.
> On a sidenote, ping rates do not necessarily reflect thoughput
> performance.
> "raybouk" wrote:
>> I recently moved my backups from the wizard generated maintenance plans
>> to a
>> simple backup script. My 180GB database is now taking 7 hours to backup
>> with
>> instead of the normal 4 hours. The step details show 7.359 MB/sec when
>> the
>> backup completes in 7 hours and 12.453 MB/sec when the backup completes
>> in 4
>> hours.
>> Below is my script. This is actually step 2. Step 1 deletes the previous
>> backup.
>> BACKUP DATABASE MyDB
>> TO DISK = '\\MyNAS\sql\server\MyDB\MyDB.bak'
>> WITH INIT
>> During the backup, pinging the NAS device from the SQL Server takes 0ms.
>> I have used Perfmon and the counters seem usual from what I have
>> researched
>> (e.g., http://www.sql-server-performance.com/backup_restore_tuning.asp).
>> Average Device Throughput bytes/sec: 5570346
>> Average % Disk Time: 3.854
>> Average Disk Queue Length: .0193
>> Average IO Write Bytes/sec: 5897638
>> Average Split IO/sec: 0
>> As for sp_who2, the only thing peculiar is a DISKIO of 875659 for
>> SQLAgent -
>> Alert Engine.
>> No compression is taking place on either machine.
>> Does anyone have any suggestions as what is causing the backup time to
>> almost double?
>> Thanks,
>> ray
>> SS2K

logtransaction backup

Hi all,
Sql server 7.
I have been given task to schedule transaction log backup for every six hours. Pls give me the steps as how to schedule a transaction log backup.
TIA
Waiting for replyUhhh with job scheduler?

That's the easy way...

with code you'll need to use system stored procedures...look them up in BOL...

sp_Add_Job, ect|||sorry if i am not clear, i want to know how to take transaction log backup.|||Do you have client tools installed?

Do you have Books Online?

Look up BACKUP LOG...

If you want to do it with T-SQL

You could use the DB Maint Wizard in Enterprise Manager as well...

Logshipping problem with naming transaction log backups

Hey, my logshipping is working just fine but the name of the backup file is of a concern to me. It's using the name in the format of DbName_20061011200002.trn. I know 20061011 is the date. That's fine. 2000 is supposed to be the time but it's giving the wrong time. It's supposed to be 1500 because the time right now is 3pm. And I dont know why the last 2 digits before the .trn are for. Is this something new in SQL 2005 logshipping? SQL 2000 didn't have it. Thank you.

Log shipping uses UTC time when naming the files. This ensures that global deployments of log shipping work consistently.

Regards,

Matt Hollingsworth

Sr. Program Manager

Microsoft SQL Server

|||Is there any possibility for me to change the naming convention. I wanted to go back to SQL 2000 naming convention. Please let me know. Thank you for your help.|||

You can use VBScript to rename your transaction log files to your requirements. Here's a sample script I use:

'============================
'Script to change a filename using timestamps
strMonth = DatePart("m", Now())
strDay = DatePart("d",Now())

if Len(strMonth)=1 then
strMonth = "0" & strMonth
else
strMonth = strMonth
end if

if Len(strDay)=1 then
strDay = "0" & strDay
else
strDay = strDay
end if

strFileName = "LOG_" & DatePart("yyyy",Now()) & strMonth & strDay & FormatDateTime(Now(), vbShortTime) & ".TRN"
strFileName = Replace(strFileName,":","")

Set objFSO = CreateObject("Scripting.FileSystemObject")
'===============================================
'Change the drive letter, folder and filename if necessary
objFSO.MoveFile "D:\SQL_Backups\LOG.TRN" , "D:\SQL_Backups\" & strFileName

You can include this in your Log Shipping Jobs on both the source and the destination servers. One thing to note is that the names should be the same as what you created when the transaction logs are restored on the destination. Your Database maintenance plan won't work on this. Might as well use a customized log shipping plan.

Logshipping out of sync

Every once in a while, my logshipping gets out of sync, the backup server
cannot restore. I found out that each time this happens, it is because on
the primary server about 17 tlog files get deleted, rather than the usual 1
file. The maintenance plan is set to keep files for 4 hours. IN the logs, I
see that most of the time, there is a tlog backup, followed by 1 file deleted
- every once in a while, it shows 17 or 18 files deleted, and then the
restore job fails, apparently because the remaining tlogs are out of sync.
I am new to logshipping and really don't know what is causing this - any
help would be greatly appreciated!
Better delete logs on daily basis rather than hourly one. It will save your
life a lot.
In our design we keep last 8 days logs with us. After that only we delete
the logs. We do logshipping manually not using maintenance plan. Our DB size
is 60 GB, whats the size of your DB?
Thanks
Sree
"megabyte" wrote:

> Every once in a while, my logshipping gets out of sync, the backup server
> cannot restore. I found out that each time this happens, it is because on
> the primary server about 17 tlog files get deleted, rather than the usual 1
> file. The maintenance plan is set to keep files for 4 hours. IN the logs, I
> see that most of the time, there is a tlog backup, followed by 1 file deleted
> - every once in a while, it shows 17 or 18 files deleted, and then the
> restore job fails, apparently because the remaining tlogs are out of sync.
> I am new to logshipping and really don't know what is causing this - any
> help would be greatly appreciated!
|||Well, this is a tiny database so far, but it is mission critical. I actually
found the problem: I had a full backup of the DB going to tape overnight -
when that backup worked, there was no problem with the logshipping, but when
the backup got stuck for some reason, it must have kept the DB locked or
something, because that's when everything went out of whack.
Thanks for the reply.
"Sreejith G" wrote:
[vbcol=seagreen]
> Better delete logs on daily basis rather than hourly one. It will save your
> life a lot.
> In our design we keep last 8 days logs with us. After that only we delete
> the logs. We do logshipping manually not using maintenance plan. Our DB size
> is 60 GB, whats the size of your DB?
> Thanks
> Sree
>
> "megabyte" wrote:
|||Yes thats a reason, If backup procedure and logshipping collide it can mess
up things.
Thanks,
Sree
"megabyte" wrote:
[vbcol=seagreen]
> Well, this is a tiny database so far, but it is mission critical. I actually
> found the problem: I had a full backup of the DB going to tape overnight -
> when that backup worked, there was no problem with the logshipping, but when
> the backup got stuck for some reason, it must have kept the DB locked or
> something, because that's when everything went out of whack.
> Thanks for the reply.
> "Sreejith G" wrote:

Logshipping out of sync

Every once in a while, my logshipping gets out of sync, the backup server
cannot restore. I found out that each time this happens, it is because on
the primary server about 17 tlog files get deleted, rather than the usual 1
file. The maintenance plan is set to keep files for 4 hours. IN the logs,
I
see that most of the time, there is a tlog backup, followed by 1 file delete
d
- every once in a while, it shows 17 or 18 files deleted, and then the
restore job fails, apparently because the remaining tlogs are out of sync.
I am new to logshipping and really don't know what is causing this - any
help would be greatly appreciated!Better delete logs on daily basis rather than hourly one. It will save your
life a lot.
In our design we keep last 8 days logs with us. After that only we delete
the logs. We do logshipping manually not using maintenance plan. Our DB size
is 60 GB, whats the size of your DB?
Thanks
Sree
"megabyte" wrote:

> Every once in a while, my logshipping gets out of sync, the backup server
> cannot restore. I found out that each time this happens, it is because on
> the primary server about 17 tlog files get deleted, rather than the usual
1
> file. The maintenance plan is set to keep files for 4 hours. IN the logs
, I
> see that most of the time, there is a tlog backup, followed by 1 file dele
ted
> - every once in a while, it shows 17 or 18 files deleted, and then the
> restore job fails, apparently because the remaining tlogs are out of sync.
> I am new to logshipping and really don't know what is causing this - any
> help would be greatly appreciated!|||Well, this is a tiny database so far, but it is mission critical. I actuall
y
found the problem: I had a full backup of the DB going to tape overnight -
when that backup worked, there was no problem with the logshipping, but when
the backup got stuck for some reason, it must have kept the DB locked or
something, because that's when everything went out of whack.
Thanks for the reply.
"Sreejith G" wrote:
[vbcol=seagreen]
> Better delete logs on daily basis rather than hourly one. It will save you
r
> life a lot.
> In our design we keep last 8 days logs with us. After that only we delete
> the logs. We do logshipping manually not using maintenance plan. Our DB si
ze
> is 60 GB, whats the size of your DB?
> Thanks
> Sree
>
> "megabyte" wrote:
>|||Yes thats a reason, If backup procedure and logshipping collide it can mess
up things.
Thanks,
Sree
"megabyte" wrote:
[vbcol=seagreen]
> Well, this is a tiny database so far, but it is mission critical. I actua
lly
> found the problem: I had a full backup of the DB going to tape overnight -
> when that backup worked, there was no problem with the logshipping, but wh
en
> the backup got stuck for some reason, it must have kept the DB locked or
> something, because that's when everything went out of whack.
> Thanks for the reply.
> "Sreejith G" wrote:
>sql

Logshipping out of sync

Every once in a while, my logshipping gets out of sync, the backup server
cannot restore. I found out that each time this happens, it is because on
the primary server about 17 tlog files get deleted, rather than the usual 1
file. The maintenance plan is set to keep files for 4 hours. IN the logs, I
see that most of the time, there is a tlog backup, followed by 1 file deleted
- every once in a while, it shows 17 or 18 files deleted, and then the
restore job fails, apparently because the remaining tlogs are out of sync.
I am new to logshipping and really don't know what is causing this - any
help would be greatly appreciated!Better delete logs on daily basis rather than hourly one. It will save your
life a lot.
In our design we keep last 8 days logs with us. After that only we delete
the logs. We do logshipping manually not using maintenance plan. Our DB size
is 60 GB, whats the size of your DB?
Thanks
Sree
"megabyte" wrote:
> Every once in a while, my logshipping gets out of sync, the backup server
> cannot restore. I found out that each time this happens, it is because on
> the primary server about 17 tlog files get deleted, rather than the usual 1
> file. The maintenance plan is set to keep files for 4 hours. IN the logs, I
> see that most of the time, there is a tlog backup, followed by 1 file deleted
> - every once in a while, it shows 17 or 18 files deleted, and then the
> restore job fails, apparently because the remaining tlogs are out of sync.
> I am new to logshipping and really don't know what is causing this - any
> help would be greatly appreciated!|||Well, this is a tiny database so far, but it is mission critical. I actually
found the problem: I had a full backup of the DB going to tape overnight -
when that backup worked, there was no problem with the logshipping, but when
the backup got stuck for some reason, it must have kept the DB locked or
something, because that's when everything went out of whack.
Thanks for the reply.
"Sreejith G" wrote:
> Better delete logs on daily basis rather than hourly one. It will save your
> life a lot.
> In our design we keep last 8 days logs with us. After that only we delete
> the logs. We do logshipping manually not using maintenance plan. Our DB size
> is 60 GB, whats the size of your DB?
> Thanks
> Sree
>
> "megabyte" wrote:
> > Every once in a while, my logshipping gets out of sync, the backup server
> > cannot restore. I found out that each time this happens, it is because on
> > the primary server about 17 tlog files get deleted, rather than the usual 1
> > file. The maintenance plan is set to keep files for 4 hours. IN the logs, I
> > see that most of the time, there is a tlog backup, followed by 1 file deleted
> > - every once in a while, it shows 17 or 18 files deleted, and then the
> > restore job fails, apparently because the remaining tlogs are out of sync.
> >
> > I am new to logshipping and really don't know what is causing this - any
> > help would be greatly appreciated!|||Yes thats a reason, If backup procedure and logshipping collide it can mess
up things.
Thanks,
Sree
"megabyte" wrote:
> Well, this is a tiny database so far, but it is mission critical. I actually
> found the problem: I had a full backup of the DB going to tape overnight -
> when that backup worked, there was no problem with the logshipping, but when
> the backup got stuck for some reason, it must have kept the DB locked or
> something, because that's when everything went out of whack.
> Thanks for the reply.
> "Sreejith G" wrote:
> > Better delete logs on daily basis rather than hourly one. It will save your
> > life a lot.
> > In our design we keep last 8 days logs with us. After that only we delete
> > the logs. We do logshipping manually not using maintenance plan. Our DB size
> > is 60 GB, whats the size of your DB?
> >
> > Thanks
> > Sree
> >
> >
> >
> > "megabyte" wrote:
> >
> > > Every once in a while, my logshipping gets out of sync, the backup server
> > > cannot restore. I found out that each time this happens, it is because on
> > > the primary server about 17 tlog files get deleted, rather than the usual 1
> > > file. The maintenance plan is set to keep files for 4 hours. IN the logs, I
> > > see that most of the time, there is a tlog backup, followed by 1 file deleted
> > > - every once in a while, it shows 17 or 18 files deleted, and then the
> > > restore job fails, apparently because the remaining tlogs are out of sync.
> > >
> > > I am new to logshipping and really don't know what is causing this - any
> > > help would be greatly appreciated!

logshipping mystery.

Hey All,
Hope this is the right place for this post.

I set up logshipping between two databases recently. everything looks fine. the backup, copy and restore jobs are all suceeding(from the job log). the problem is no Tlogs are being restored in secondary database. The restore job skips all the tlogs and says it did not find any tlog back up file to restore!! The jobs finishes with this:

007-07-17 14:40:30.59 Could not find a log backup file that could be applied to secondary database 'SaaSNet_dataStore'.
2007-07-17 14:40:30.59 The restore operation was successful. Secondary Database: 'SaaSNet_dataStore', Number of log backup files restored: 0
2007-07-17 14:40:30.59 Deleting old log backup files. Primary Database: 'SaaSNet_DataStore'
2007-07-17 14:40:30.59 The restore operation was successful. Secondary ID: '5808a414-2ada-41d2-a8a0-2cf84f85174a'

Appreciate your help

the problem was that we had a maintenance job running on the source sql server(doing hourly Tlog backups).

the job created gaps in the logshipping tlogs sequence numbers, which threw off the restore phase.

we resolved the issue by removing the specific databses from the tlog maintenance job(since logshipping is backing up the tlog for those databases anyway) and that did it. I had to redo the logshipping config afterwards to start fresh.

|||
the problem was that we had a maintenance job running on the source sql server(doing hourly Tlog backups).

the job created gaps in the logshipping tlogs sequence numbers, which threw off the restore phase.

we resolved the issue by removing the specific databses from the tlog maintenance job(since logshipping is backing up the tlog for those databases anyway) and that did it. I had to redo the logshipping config afterwards to start fresh.

logshipping mystery.

Hey All,
Hope this is the right place for this post.

I set up logshipping between two databases recently. everything looks fine. the backup, copy and restore jobs are all suceeding(from the job log). the problem is no Tlogs are being restored in secondary database. The restore job skips all the tlogs and says it did not find any tlog back up file to restore!! The jobs finishes with this:

007-07-17 14:40:30.59 Could not find a log backup file that could be applied to secondary database 'SaaSNet_dataStore'.
2007-07-17 14:40:30.59 The restore operation was successful. Secondary Database: 'SaaSNet_dataStore', Number of log backup files restored: 0
2007-07-17 14:40:30.59 Deleting old log backup files. Primary Database: 'SaaSNet_DataStore'
2007-07-17 14:40:30.59 The restore operation was successful. Secondary ID: '5808a414-2ada-41d2-a8a0-2cf84f85174a'

Appreciate your help

the problem was that we had a maintenance job running on the source sql server(doing hourly Tlog backups).

the job created gaps in the logshipping tlogs sequence numbers, which threw off the restore phase.

we resolved the issue by removing the specific databses from the tlog maintenance job(since logshipping is backing up the tlog for those databases anyway) and that did it. I had to redo the logshipping config afterwards to start fresh.

|||
the problem was that we had a maintenance job running on the source sql server(doing hourly Tlog backups).

the job created gaps in the logshipping tlogs sequence numbers, which threw off the restore phase.

we resolved the issue by removing the specific databses from the tlog maintenance job(since logshipping is backing up the tlog for those databases anyway) and that did it. I had to redo the logshipping config afterwards to start fresh.

logshipping mystery- SOLVED -

Hey All,
Hope this is the right place for this post.

I set up logshipping between two databases recently. everything looks fine. the backup, copy and restore jobs are all suceeding(from the job log). the problem is no Tlogs are being restored in secondary database. The restore job skips all the tlogs and says it did not find any tlog back up file to restore!! The jobs finishes with this:

007-07-17 14:40:30.59 Could not find a log backup file that could be applied to secondary database 'SaaSNet_dataStore'.
2007-07-17 14:40:30.59 The restore operation was successful. Secondary Database: 'SaaSNet_dataStore', Number of log backup files restored: 0
2007-07-17 14:40:30.59 Deleting old log backup files. Primary Database: 'SaaSNet_DataStore'
2007-07-17 14:40:30.59 The restore operation was successful. Secondary ID: '5808a414-2ada-41d2-a8a0-2cf84f85174a'

Appreciate your help

the problem was that we had a maintenance job running on the source sql server(doing hourly Tlog backups).

the job created gaps in the logshipping tlogs sequence numbers, which threw off the restore phase.

we resolved the issue by removing the specific databses from the tlog maintenance job(since logshipping is backing up the tlog for those databases anyway) and that did it. I had to redo the logshipping config afterwards to start fresh.

|||
the problem was that we had a maintenance job running on the source sql server(doing hourly Tlog backups).

the job created gaps in the logshipping tlogs sequence numbers, which threw off the restore phase.

we resolved the issue by removing the specific databses from the tlog maintenance job(since logshipping is backing up the tlog for those databases anyway) and that did it. I had to redo the logshipping config afterwards to start fresh.

LogShipping and Transaction Log Backup

We use veritas Backup Exec for Hourly Transaction Log
BAckup
Would it be possible to integrate Logshipping with the
Translog backup performed using veritas Backup Exec?
not really. What you would have to do is to extract the tlogs from the
backupexec media and then apply them. You will be probably better off to
ship the backup and logs natively.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Cyriac George" <cgeorge@.naturesplus.com> wrote in message
news:3d3501c4a595$25befc00$a301280a@.phx.gbl...
> We use veritas Backup Exec for Hourly Transaction Log
> BAckup
> Would it be possible to integrate Logshipping with the
> Translog backup performed using veritas Backup Exec?
>
|||Thanks Hilary for the reply
I was also considering using BackupExec Scripts to achieve the same result
I am not comfortable using native script for Backup since we have a SAN
managed
backup infrastructure. Right now I am doing restore to the Stand By Server
using
Transaction Log restore from the Tape
Thanks
Cyriac George
"Hilary Cotter" wrote:

> not really. What you would have to do is to extract the tlogs from the
> backupexec media and then apply them. You will be probably better off to
> ship the backup and logs natively.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Cyriac George" <cgeorge@.naturesplus.com> wrote in message
> news:3d3501c4a595$25befc00$a301280a@.phx.gbl...
>
>
|||I meant native NT or SQL commands to accomplish the backup.
Unless you have huge tlogs (for large databases you should be dumping
perhaps each minute), you should be dumping to disk using multiple backup
devices.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Cyriac George" <Cyriac George@.discussions.microsoft.com> wrote in message
news:9A620431-339C-4B69-A075-10DFF3886E96@.microsoft.com...[vbcol=seagreen]
> Thanks Hilary for the reply
> I was also considering using BackupExec Scripts to achieve the same result
> I am not comfortable using native script for Backup since we have a SAN
> managed
> backup infrastructure. Right now I am doing restore to the Stand By Server
> using
> Transaction Log restore from the Tape
> Thanks
> Cyriac George
> "Hilary Cotter" wrote:
|||Hilary,
Is Replication a better option in this scenario?
My concern with the replication is loss of performance
on the main (Primary) server. Also given the fact that My primary server
is Clustered, and We have hourly BAckup of Transaction Logs using Backup
Exec, Can I still maintain the standby server using Replication?
What is a rough estimate of the performance loss on the
Server?
Thanks
Cyriac George

LogShipping and Database Backup

Hello,

have a question about logshipping and full database backup: during a full database backup the log will be backed up too and emptied. If I run a full backup on databases which is setup for logshipping - does it mean I will loose data on the standby side ?

Thanks in advance,

BerndNo the full database backup will not affect the log shipping database. Transaction logs will continue to be applied normally at the target node. You will not lose any data.

Regards,

hmscottsql