Back up

I have 2 instances of SQL running, a production and a development environment
that are almost identical. Every night I do a full back up of each database
in both environments. I have noticed that the development instance takes
considerably longer to back up than the production instance. The back up is
executed as a job on each instance (staggerd so as not to clash), a step for
each database using the following tsql code
BACKUP DATABASE [db name] TO TAPE = N'\\.\Tape0'
WITH NOINIT ,
NOUNLOAD , NAME = N'Data_Migration',
NOSKIP , STATS = 10, NOFORMAT.
the final back up to run uses the UNLOAD keyword to eject the tape. Am i
correct in presuming that the databases in the development instance take
longer to back up because as they run after the production instance, the tape
takes longer to rewind and ffwd between backups?
Also the final backup on the development insance often fails with the
following error
Request for device '\\.\tape0' timed out.
The backup seems to be successful I would just like to know why this occurs
The back up device is an ultrium 200GB, there are 5 databases on each
insance totaling 50GB
Cheers
Hi,
Your hardware (CPU Count or speed) or Tape Drive in production must be
better than the development environemnt.Thats is the reason the Backup
in Production is much faster than development server.
One recommendation is it is always best to backup to a Hard disk (Local
drive / SAN / NAS) and then copy to tape. This will ensure that
you have a good backup in your hard disk itself. This probably will remove
the timeout issue as well.
Thanks
Hari
SQL Server MVP
"mat" <mat@.discussions.microsoft.com> wrote in message
news:695DBCE2-2132-4BC7-B762-537736202ECE@.microsoft.com...
>I have 2 instances of SQL running, a production and a development
>environment
> that are almost identical. Every night I do a full back up of each
> database
> in both environments. I have noticed that the development instance takes
> considerably longer to back up than the production instance. The back up
> is
> executed as a job on each instance (staggerd so as not to clash), a step
> for
> each database using the following tsql code
> BACKUP DATABASE [db name] TO TAPE = N'\\.\Tape0'
> WITH NOINIT ,
> NOUNLOAD , NAME = N'Data_Migration',
> NOSKIP , STATS = 10, NOFORMAT.
> the final back up to run uses the UNLOAD keyword to eject the tape. Am i
> correct in presuming that the databases in the development instance take
> longer to back up because as they run after the production instance, the
> tape
> takes longer to rewind and ffwd between backups?
> Also the final backup on the development insance often fails with the
> following error
> Request for device '\\.\tape0' timed out.
> The backup seems to be successful I would just like to know why this
> occurs
> The back up device is an ultrium 200GB, there are 5 databases on each
> insance totaling 50GB
> Cheers
>
|||Do you have any other services kicking off at the time of the second backup;
for instance a virus protection scan?
"mat" wrote:

> I have 2 instances of SQL running, a production and a development environment
> that are almost identical. Every night I do a full back up of each database
> in both environments. I have noticed that the development instance takes
> considerably longer to back up than the production instance. The back up is
> executed as a job on each instance (staggerd so as not to clash), a step for
> each database using the following tsql code
> BACKUP DATABASE [db name] TO TAPE = N'\\.\Tape0'
> WITH NOINIT ,
> NOUNLOAD , NAME = N'Data_Migration',
> NOSKIP , STATS = 10, NOFORMAT.
> the final back up to run uses the UNLOAD keyword to eject the tape. Am i
> correct in presuming that the databases in the development instance take
> longer to back up because as they run after the production instance, the tape
> takes longer to rewind and ffwd between backups?
> Also the final backup on the development insance often fails with the
> following error
> Request for device '\\.\tape0' timed out.
> The backup seems to be successful I would just like to know why this occurs
> The back up device is an ultrium 200GB, there are 5 databases on each
> insance totaling 50GB
> Cheers
>
|||I am a network admin without too much SQL (read NONE!) experience. Can I
configure the backup using SQL 2000 Enterprise Manager, SQL Server Backup
option for the individual databases and then NTBackup to back up the SQL
backup from disk to tape? Can I do any harm to the databases by scheduling
the SQL backup process?
Thanks for your help - Aidan
"SteveA" wrote:
[vbcol=seagreen]
> Do you have any other services kicking off at the time of the second backup;
> for instance a virus protection scan?
> "mat" wrote:

Back up

I have 2 instances of SQL running, a production and a development environmen
t
that are almost identical. Every night I do a full back up of each database
in both environments. I have noticed that the development instance takes
considerably longer to back up than the production instance. The back up is
executed as a job on each instance (staggerd so as not to clash), a step for
each database using the following tsql code
BACKUP DATABASE [db name] TO TAPE = N'\\.\Tape0'
WITH NOINIT ,
NOUNLOAD , NAME = N'Data_Migration',
NOSKIP , STATS = 10, NOFORMAT.
the final back up to run uses the UNLOAD keyword to eject the tape. Am i
correct in presuming that the databases in the development instance take
longer to back up because as they run after the production instance, the tap
e
takes longer to rewind and ffwd between backups?
Also the final backup on the development insance often fails with the
following error
Request for device '\\.\tape0' timed out.
The backup seems to be successful I would just like to know why this occurs
The back up device is an ultrium 200GB, there are 5 databases on each
insance totaling 50GB
CheersHi,
Your hardware (CPU Count or speed) or Tape Drive in production must be
better than the development environemnt.Thats is the reason the Backup
in Production is much faster than development server.
One recommendation is it is always best to backup to a Hard disk (Local
drive / SAN / NAS) and then copy to tape. This will ensure that
you have a good backup in your hard disk itself. This probably will remove
the timeout issue as well.
Thanks
Hari
SQL Server MVP
"mat" <mat@.discussions.microsoft.com> wrote in message
news:695DBCE2-2132-4BC7-B762-537736202ECE@.microsoft.com...
>I have 2 instances of SQL running, a production and a development
>environment
> that are almost identical. Every night I do a full back up of each
> database
> in both environments. I have noticed that the development instance takes
> considerably longer to back up than the production instance. The back up
> is
> executed as a job on each instance (staggerd so as not to clash), a step
> for
> each database using the following tsql code
> BACKUP DATABASE [db name] TO TAPE = N'\\.\Tape0'
> WITH NOINIT ,
> NOUNLOAD , NAME = N'Data_Migration',
> NOSKIP , STATS = 10, NOFORMAT.
> the final back up to run uses the UNLOAD keyword to eject the tape. Am i
> correct in presuming that the databases in the development instance take
> longer to back up because as they run after the production instance, the
> tape
> takes longer to rewind and ffwd between backups?
> Also the final backup on the development insance often fails with the
> following error
> Request for device '\\.\tape0' timed out.
> The backup seems to be successful I would just like to know why this
> occurs
> The back up device is an ultrium 200GB, there are 5 databases on each
> insance totaling 50GB
> Cheers
>|||Do you have any other services kicking off at the time of the second backup;
for instance a virus protection scan?
"mat" wrote:

> I have 2 instances of SQL running, a production and a development environm
ent
> that are almost identical. Every night I do a full back up of each databas
e
> in both environments. I have noticed that the development instance takes
> considerably longer to back up than the production instance. The back up i
s
> executed as a job on each instance (staggerd so as not to clash), a step f
or
> each database using the following tsql code
> BACKUP DATABASE [db name] TO TAPE = N'\\.\Tape0'
> WITH NOINIT ,
> NOUNLOAD , NAME = N'Data_Migration',
> NOSKIP , STATS = 10, NOFORMAT.
> the final back up to run uses the UNLOAD keyword to eject the tape. Am i
> correct in presuming that the databases in the development instance take
> longer to back up because as they run after the production instance, the t
ape
> takes longer to rewind and ffwd between backups?
> Also the final backup on the development insance often fails with the
> following error
> Request for device '\\.\tape0' timed out.
> The backup seems to be successful I would just like to know why this occur
s
> The back up device is an ultrium 200GB, there are 5 databases on each
> insance totaling 50GB
> Cheers
>|||I am a network admin without too much SQL (read NONE!) experience. Can I
configure the backup using SQL 2000 Enterprise Manager, SQL Server Backup
option for the individual databases and then NTBackup to back up the SQL
backup from disk to tape? Can I do any harm to the databases by scheduling
the SQL backup process?
Thanks for your help - Aidan
"SteveA" wrote:
[vbcol=seagreen]
> Do you have any other services kicking off at the time of the second backu
p;
> for instance a virus protection scan?
> "mat" wrote:
>

Back up

I have 2 instances of SQL running, a production and a development environment
that are almost identical. Every night I do a full back up of each database
in both environments. I have noticed that the development instance takes
considerably longer to back up than the production instance. The back up is
executed as a job on each instance (staggerd so as not to clash), a step for
each database using the following tsql code
BACKUP DATABASE [db name] TO TAPE = N'\\.\Tape0'
WITH NOINIT ,
NOUNLOAD , NAME = N'Data_Migration',
NOSKIP , STATS = 10, NOFORMAT.
the final back up to run uses the UNLOAD keyword to eject the tape. Am i
correct in presuming that the databases in the development instance take
longer to back up because as they run after the production instance, the tape
takes longer to rewind and ffwd between backups?
Also the final backup on the development insance often fails with the
following error
Request for device '\\.\tape0' timed out.
The backup seems to be successful I would just like to know why this occurs
The back up device is an ultrium 200GB, there are 5 databases on each
insance totaling 50GB
CheersHi,
Your hardware (CPU Count or speed) or Tape Drive in production must be
better than the development environemnt.Thats is the reason the Backup
in Production is much faster than development server.
One recommendation is it is always best to backup to a Hard disk (Local
drive / SAN / NAS) and then copy to tape. This will ensure that
you have a good backup in your hard disk itself. This probably will remove
the timeout issue as well.
Thanks
Hari
SQL Server MVP
"mat" <mat@.discussions.microsoft.com> wrote in message
news:695DBCE2-2132-4BC7-B762-537736202ECE@.microsoft.com...
>I have 2 instances of SQL running, a production and a development
>environment
> that are almost identical. Every night I do a full back up of each
> database
> in both environments. I have noticed that the development instance takes
> considerably longer to back up than the production instance. The back up
> is
> executed as a job on each instance (staggerd so as not to clash), a step
> for
> each database using the following tsql code
> BACKUP DATABASE [db name] TO TAPE = N'\\.\Tape0'
> WITH NOINIT ,
> NOUNLOAD , NAME = N'Data_Migration',
> NOSKIP , STATS = 10, NOFORMAT.
> the final back up to run uses the UNLOAD keyword to eject the tape. Am i
> correct in presuming that the databases in the development instance take
> longer to back up because as they run after the production instance, the
> tape
> takes longer to rewind and ffwd between backups?
> Also the final backup on the development insance often fails with the
> following error
> Request for device '\\.\tape0' timed out.
> The backup seems to be successful I would just like to know why this
> occurs
> The back up device is an ultrium 200GB, there are 5 databases on each
> insance totaling 50GB
> Cheers
>|||Do you have any other services kicking off at the time of the second backup;
for instance a virus protection scan?
"mat" wrote:
> I have 2 instances of SQL running, a production and a development environment
> that are almost identical. Every night I do a full back up of each database
> in both environments. I have noticed that the development instance takes
> considerably longer to back up than the production instance. The back up is
> executed as a job on each instance (staggerd so as not to clash), a step for
> each database using the following tsql code
> BACKUP DATABASE [db name] TO TAPE = N'\\.\Tape0'
> WITH NOINIT ,
> NOUNLOAD , NAME = N'Data_Migration',
> NOSKIP , STATS = 10, NOFORMAT.
> the final back up to run uses the UNLOAD keyword to eject the tape. Am i
> correct in presuming that the databases in the development instance take
> longer to back up because as they run after the production instance, the tape
> takes longer to rewind and ffwd between backups?
> Also the final backup on the development insance often fails with the
> following error
> Request for device '\\.\tape0' timed out.
> The backup seems to be successful I would just like to know why this occurs
> The back up device is an ultrium 200GB, there are 5 databases on each
> insance totaling 50GB
> Cheers
>

AWE on 4GB multi-instance box?

Hi
We have a SQL server 2000 cluster, built on Windows 2000 Advanced server.
We are using an Active / Passive configuration which has 4 instances of SQL
Enterprise, and 1 Instance of IIS.
Both boxes are identical, and clustering works just fine.
However, in order to use the 4Gb in each box, should we enable the /3Gb
switch in Boot.Ini?
How then do we configure each instance of SQL Server? I believe that once
the /3Gb switch is enabled, that SQL will grab all the memory for itself,
leaving only 128Mb for the OS. We would probably need to specify a maximum
memory usage requirement, per instance, if that was to be the case.
There seems to be 2 options to me. Can anyone offer advice on whether we
should take:
1). Disable the AWE, via the removal of the /3Gb switch, and let SQL Server
manage its own memory
2). Enable AWE, via the use of the /3Gb switch, but then specify the maximum
memory each instance of SQL Server can use.
Does anyone have a view?
Thanks
Paul Aspinall
MCSD, MCDBA
Paul,
The /3GB switch does not enable or disable AWE. That is done via the AWE
option of sp_configure. If you have 4 actual instances of SQL Server plus
IIS on a single box with only 4GB of memory you certainly do not want to use
/3GB. You must share that 4GB with all 5 instances plus the OS and do not
want anything to swap to the swap file. Is there a particular reason to
have 4 separate instances vs. one with all the db's in it? It's hard to say
what the proper memory config should be given I know nothing about any of
your instances or how they are used. You might simply want to stay with
dynamic memory config on all 4 with no /3GB. AWE is only really useful with
more than 4GB anyway. It's IIS that I would be most worried about since it
doesn't play as well with others as SQL Server does.
Andrew J. Kelly SQL MVP
"Paul Aspinall" <paul@.aspy.co.uk> wrote in message
news:oG4Be.28164$O22.20831@.fe1.news.blueyonder.co. uk...
> Hi
> We have a SQL server 2000 cluster, built on Windows 2000 Advanced server.
> We are using an Active / Passive configuration which has 4 instances of
> SQL Enterprise, and 1 Instance of IIS.
> Both boxes are identical, and clustering works just fine.
> However, in order to use the 4Gb in each box, should we enable the /3Gb
> switch in Boot.Ini?
> How then do we configure each instance of SQL Server? I believe that once
> the /3Gb switch is enabled, that SQL will grab all the memory for itself,
> leaving only 128Mb for the OS. We would probably need to specify a maximum
> memory usage requirement, per instance, if that was to be the case.
> There seems to be 2 options to me. Can anyone offer advice on whether we
> should take:
> 1). Disable the AWE, via the removal of the /3Gb switch, and let SQL
> Server manage its own memory
> 2). Enable AWE, via the use of the /3Gb switch, but then specify the
> maximum memory each instance of SQL Server can use.
>
> Does anyone have a view?
> Thanks
>
> Paul Aspinall
> MCSD, MCDBA
>

AWE and multiple instance

I have a SQL Server 2000 Enterprise server with 16GB of RAM. I have
installed SP4 and the AWE hotfix.
I have 3 instances running on the box:
If I have 1 instance using 7GB and the other 2 are using 4GB each, does
AWE need to be enabled on the instancese which are set to 4GB?
Thanks!
They won't be able to use the 4GB unless you enable AWE for them.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"traceable1" <tracykc@.gmail.com> wrote in message
news:1141139567.679350.285320@.z34g2000cwc.googlegr oups.com...
I have a SQL Server 2000 Enterprise server with 16GB of RAM. I have
installed SP4 and the AWE hotfix.
I have 3 instances running on the box:
If I have 1 instance using 7GB and the other 2 are using 4GB each, does
AWE need to be enabled on the instancese which are set to 4GB?
Thanks!
|||Unless of course they are running on Win2K3 x64. In that case, the SQL2000
instance should be able use up to 4GB without enabling AWE.
Linchi
"Tom Moreau" wrote:

> They won't be able to use the 4GB unless you enable AWE for them.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "traceable1" <tracykc@.gmail.com> wrote in message
> news:1141139567.679350.285320@.z34g2000cwc.googlegr oups.com...
> I have a SQL Server 2000 Enterprise server with 16GB of RAM. I have
> installed SP4 and the AWE hotfix.
> I have 3 instances running on the box:
> If I have 1 instance using 7GB and the other 2 are using 4GB each, does
> AWE need to be enabled on the instancese which are set to 4GB?
> Thanks!
>
|||I just assumed he was on 32-bit hardware, but you raise a good point.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:07C2D7BC-D085-4C5A-BA3D-ED599E571785@.microsoft.com...
Unless of course they are running on Win2K3 x64. In that case, the SQL2000
instance should be able use up to 4GB without enabling AWE.
Linchi
"Tom Moreau" wrote:

> They won't be able to use the 4GB unless you enable AWE for them.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "traceable1" <tracykc@.gmail.com> wrote in message
> news:1141139567.679350.285320@.z34g2000cwc.googlegr oups.com...
> I have a SQL Server 2000 Enterprise server with 16GB of RAM. I have
> installed SP4 and the AWE hotfix.
> I have 3 instances running on the box:
> If I have 1 instance using 7GB and the other 2 are using 4GB each, does
> AWE need to be enabled on the instancese which are set to 4GB?
> Thanks!
>
|||One interesting question at that point...
What if had 16GB of RAM and would like to run first instance with 10GB RAM
and another 4 instances with 1GB of RAM each. (all 32 bit).
It's clear that the first one would need AWE, but what about the other 4?
2 of them wouldn't need AWE and they could alocate RAM dynamically right (3
with /3GB in boot.ini)?
But witch two? Is that important?
Would the instances without AWE have to start first and then the rest or
something like that?
Am I thinking in the right way here?
Tom K.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uObHIyKPGHA.1040@.TK2MSFTNGP12.phx.gbl...
>I just assumed he was on 32-bit hardware, but you raise a good point.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:07C2D7BC-D085-4C5A-BA3D-ED599E571785@.microsoft.com...
> Unless of course they are running on Win2K3 x64. In that case, the SQL2000
> instance should be able use up to 4GB without enabling AWE.
> Linchi
> "Tom Moreau" wrote:
>
|||That's an odd requirement. IIRC, up to about 12GB (per instance), you'd go
with both /3GB and /PAE. However, if you have those others that want 1GB,
I'd be sure to limit them explicitly to 1GB. Since they don't use AWE, then
if they start first and then the big instance starts up, I believe the
smaller ones would give up memory (if they're not already limited) to the
one requesting the AWE memory. Once it has its AWE memory, it won't give it
up.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Tom" <mcseman@.hotmail.com> wrote in message
news:u40kZ9KPGHA.648@.TK2MSFTNGP14.phx.gbl...
One interesting question at that point...
What if had 16GB of RAM and would like to run first instance with 10GB RAM
and another 4 instances with 1GB of RAM each. (all 32 bit).
It's clear that the first one would need AWE, but what about the other 4?
2 of them wouldn't need AWE and they could alocate RAM dynamically right (3
with /3GB in boot.ini)?
But witch two? Is that important?
Would the instances without AWE have to start first and then the rest or
something like that?
Am I thinking in the right way here?
Tom K.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uObHIyKPGHA.1040@.TK2MSFTNGP12.phx.gbl...
>I just assumed he was on 32-bit hardware, but you raise a good point.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
> news:07C2D7BC-D085-4C5A-BA3D-ED599E571785@.microsoft.com...
> Unless of course they are running on Win2K3 x64. In that case, the SQL2000
> instance should be able use up to 4GB without enabling AWE.
> Linchi
> "Tom Moreau" wrote:
>