Backup failure

All,
I am receiving the following error when I try to backup to tape from SQL using backup under the Management folder in Enterprise manager. Logged in as administrator. Can backup the pubs, Northwind and master databases with no problem.
The database is approx. 8.5GB and the tape can hold 40GB formatted. Using option to overwrite and initialize.
Microsoft SQL-DMO (ODBC SQLState: 42000)
Write on '\\.\Tape0' failed, status = 1117. See the SQL Server error log for more details, backup database is terminating abnormally.
In the error log;
Internal I/O request 0x075CB728: op: write, pBuffer: 0x07d3000, size: 65536, position: 11141120, UMS: internal: 0xC0000185, internalhigh: 0x10000, offset: 0xAA0000, offsethigh: 0x0, m_buf: 0x07d30000, m_len: 65536, m_actualbytes: 65536, m_errcode: 1117, b
ackupfile: \\.\Tape0
Any thought would be appreciated.
Regards,
Dave
can you back it up to disk instead of to tape?
can you backup a large file from disk to tape?
I will do it this way because backup done faster and eliminating the hassle
of dealing with the tape drive.
my 0.02
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:C1CCFEFC-AD93-4715-919F-C468273BD974@.microsoft.com...
> All,
> I am receiving the following error when I try to backup to tape from SQL
using backup under the Management folder in Enterprise manager. Logged in as
administrator. Can backup the pubs, Northwind and master databases with no
problem.
> The database is approx. 8.5GB and the tape can hold 40GB formatted. Using
option to overwrite and initialize.
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> Write on '\\.\Tape0' failed, status = 1117. See the SQL Server error log
for more details, backup database is terminating abnormally.
> In the error log;
> Internal I/O request 0x075CB728: op: write, pBuffer: 0x07d3000, size:
65536, position: 11141120, UMS: internal: 0xC0000185, internalhigh: 0x10000,
offset: 0xAA0000, offsethigh: 0x0, m_buf: 0x07d30000, m_len: 65536,
m_actualbytes: 65536, m_errcode: 1117, backupfile: \\.\Tape0
> Any thought would be appreciated.
> Regards,
> Dave

Backup failure

All,
I am receiving the following error when I try to backup to tape from SQL usi
ng backup under the Management folder in Enterprise manager. Logged in as ad
ministrator. Can backup the pubs, Northwind and master databases with no pro
blem.
The database is approx. 8.5GB and the tape can hold 40GB formatted. Using op
tion to overwrite and initialize.
Microsoft SQL-DMO (ODBC SQLState: 42000)
Write on '\\.\Tape0' failed, status = 1117. See the SQL Server error log for
more details, backup database is terminating abnormally.
In the error log;
Internal I/O request 0x075CB728: op: write, pBuffer: 0x07d3000, size: 65536,
position: 11141120, UMS: internal: 0xC0000185, internalhigh: 0x10000, offse
t: 0xAA0000, offsethigh: 0x0, m_buf: 0x07d30000, m_len: 65536, m_actualbytes
: 65536, m_errcode: 1117, b
ackupfile: \\.\Tape0
Any thought would be appreciated.
Regards,
Davecan you back it up to disk instead of to tape?
can you backup a large file from disk to tape?
I will do it this way because backup done faster and eliminating the hassle
of dealing with the tape drive.
my 0.02
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:C1CCFEFC-AD93-4715-919F-C468273BD974@.microsoft.com...
> All,
> I am receiving the following error when I try to backup to tape from SQL
using backup under the Management folder in Enterprise manager. Logged in as
administrator. Can backup the pubs, Northwind and master databases with no
problem.
> The database is approx. 8.5GB and the tape can hold 40GB formatted. Using
option to overwrite and initialize.
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> Write on '\\.\Tape0' failed, status = 1117. See the SQL Server error log
for more details, backup database is terminating abnormally.
> In the error log;
> Internal I/O request 0x075CB728: op: write, pBuffer: 0x07d3000, size:
65536, position: 11141120, UMS: internal: 0xC0000185, internalhigh: 0x10000,
offset: 0xAA0000, offsethigh: 0x0, m_buf: 0x07d30000, m_len: 65536,
m_actualbytes: 65536, m_errcode: 1117, backupfile: \\.\Tape0
> Any thought would be appreciated.
> Regards,
> Davesql

Backup display

I created some backups for my databases and they show in the Enterprise Jobs
as Uncategorized [local]. All the Maintenance Plans show as Database
Maintenance.
I have about 10 of them and you can't tell which is which without going into
the properties.
Is there an easier way to see what backups you have set up and what the
status is?
Thanks,
Tom
I am not quite sure what you are asking. You have the ability to put what
ever description you want for any job. You also have the ability to set the
category or to even create a new custom category. So between those two
options you should certainly be able to do something descriptive enough to
group your jobs appropriately.
Andrew J. Kelly SQL MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%232QEfvx5FHA.884@.TK2MSFTNGP14.phx.gbl...
>I created some backups for my databases and they show in the Enterprise
>Jobs as Uncategorized [local]. All the Maintenance Plans show as Database
>Maintenance.
> I have about 10 of them and you can't tell which is which without going
> into the properties.
> Is there an easier way to see what backups you have set up and what the
> status is?
> Thanks,
> Tom
>

Backup display

I created some backups for my databases and they show in the Enterprise Jobs
as Uncategorized [local]. All the Maintenance Plans show as Database
Maintenance.
I have about 10 of them and you can't tell which is which without going into
the properties.
Is there an easier way to see what backups you have set up and what the
status is?
Thanks,
TomI am not quite sure what you are asking. You have the ability to put what
ever description you want for any job. You also have the ability to set the
category or to even create a new custom category. So between those two
options you should certainly be able to do something descriptive enough to
group your jobs appropriately.
--
Andrew J. Kelly SQL MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%232QEfvx5FHA.884@.TK2MSFTNGP14.phx.gbl...
>I created some backups for my databases and they show in the Enterprise
>Jobs as Uncategorized [local]. All the Maintenance Plans show as Database
>Maintenance.
> I have about 10 of them and you can't tell which is which without going
> into the properties.
> Is there an easier way to see what backups you have set up and what the
> status is?
> Thanks,
> Tom
>

Backup display

I created some backups for my databases and they show in the Enterprise Jobs
as Uncategorized [local]. All the Maintenance Plans show as Database
Maintenance.
I have about 10 of them and you can't tell which is which without going into
the properties.
Is there an easier way to see what backups you have set up and what the
status is?
Thanks,
TomI am not quite sure what you are asking. You have the ability to put what
ever description you want for any job. You also have the ability to set the
category or to even create a new custom category. So between those two
options you should certainly be able to do something descriptive enough to
group your jobs appropriately.
Andrew J. Kelly SQL MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%232QEfvx5FHA.884@.TK2MSFTNGP14.phx.gbl...
>I created some backups for my databases and they show in the Enterprise
>Jobs as Uncategorized [local]. All the Maintenance Plans show as Data
base
>Maintenance.
> I have about 10 of them and you can't tell which is which without going
> into the properties.
> Is there an easier way to see what backups you have set up and what the
> status is?
> Thanks,
> Tom
>sql

Backup Databases

I am running SQL2000 SP4 on a Win2000 server and getting following error when
trying to perform a backup of a database within Enterprise Manager.
"Error 22272: Cannot load the DLL xpstart.dll, or one of the DLL's it
references. Reason 127(The specified procedure could not be found.). I have
looked any references to this error message, but yet to find anything on it.
Wondering if someone can assist me.
thanks,
JayHi
If backing up the datrabase from a different PC works, then I would think
re-installing the client tools may help solve this.
John
"Jay" wrote:
> I am running SQL2000 SP4 on a Win2000 server and getting following error when
> trying to perform a backup of a database within Enterprise Manager.
> "Error 22272: Cannot load the DLL xpstart.dll, or one of the DLL's it
> references. Reason 127(The specified procedure could not be found.). I have
> looked any references to this error message, but yet to find anything on it.
> Wondering if someone can assist me.
> thanks,
> Jay
>|||Jay wrote:
> I am running SQL2000 SP4 on a Win2000 server and getting following error when
> trying to perform a backup of a database within Enterprise Manager.
> "Error 22272: Cannot load the DLL xpstart.dll, or one of the DLL's it
> references. Reason 127(The specified procedure could not be found.). I have
> looked any references to this error message, but yet to find anything on it.
> Wondering if someone can assist me.
> thanks,
> Jay
>
Open up Query Analyzer and use the T-SQL BACKUP command - does it work?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Backup Databases

I am running SQL2000 SP4 on a Win2000 server and getting following error whe
n
trying to perform a backup of a database within Enterprise Manager.
"Error 22272: Cannot load the DLL xpstart.dll, or one of the DLL's it
references. Reason 127(The specified procedure could not be found.). I have
looked any references to this error message, but yet to find anything on it.
Wondering if someone can assist me.
thanks,
JayHi
If backing up the datrabase from a different PC works, then I would think
re-installing the client tools may help solve this.
John
"Jay" wrote:

> I am running SQL2000 SP4 on a Win2000 server and getting following error w
hen
> trying to perform a backup of a database within Enterprise Manager.
> "Error 22272: Cannot load the DLL xpstart.dll, or one of the DLL's it
> references. Reason 127(The specified procedure could not be found.). I ha
ve
> looked any references to this error message, but yet to find anything on i
t.
> Wondering if someone can assist me.
> thanks,
> Jay
>|||Jay wrote:
> I am running SQL2000 SP4 on a Win2000 server and getting following error w
hen
> trying to perform a backup of a database within Enterprise Manager.
> "Error 22272: Cannot load the DLL xpstart.dll, or one of the DLL's it
> references. Reason 127(The specified procedure could not be found.). I ha
ve
> looked any references to this error message, but yet to find anything on i
t.
> Wondering if someone can assist me.
> thanks,
> Jay
>
Open up Query Analyzer and use the T-SQL BACKUP command - does it work?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Backup command and getting progress like Enterprise/Management Studio

Sorry if this is the wrong place for this, but I couldn't find a better place...

I have a .net application that calls a TSQL command to backup the database when the user clicks a button. I would like to give the user incremental feedback on what's going on.

So I hooked into the connection's InfoMessage event.

It gets called at the end of the command, not during. I read somewhere that you're supposed to be able to add "WITH NOWAIT" to a TSQL command to have it return immediately and then use the InfoMessage for updates, but the Backup command doesn't support it.

I also set a trace on the SQL Server and told SQL Server to backup using Enterprise manager hoping to catch what it was doing special to get these commands, and didn't see anything unique.

Does anyone have suggestions? I really need to show the progress of what's going on.

Thanks!

SqlClient unfortunately does not support retrieving of messages asynchronously during command execution. You can do this however if you use System.Data.Odbc instead. The steps are pretty much the same. This can also be done if you use C/C++ and use ODBC/OLEDB/SNAC.

Backup command and getting progress like Enterprise/Management Studio

Sorry if this is the wrong place for this, but I couldn't find a better place...

I have a .net application that calls a TSQL command to backup the database when the user clicks a button. I would like to give the user incremental feedback on what's going on.

So I hooked into the connection's InfoMessage event.

It gets called at the end of the command, not during. I read somewhere that you're supposed to be able to add "WITH NOWAIT" to a TSQL command to have it return immediately and then use the InfoMessage for updates, but the Backup command doesn't support it.

I also set a trace on the SQL Server and told SQL Server to backup using Enterprise manager hoping to catch what it was doing special to get these commands, and didn't see anything unique.

Does anyone have suggestions? I really need to show the progress of what's going on.

Thanks!

SqlClient unfortunately does not support retrieving of messages asynchronously during command execution. You can do this however if you use System.Data.Odbc instead. The steps are pretty much the same. This can also be done if you use C/C++ and use ODBC/OLEDB/SNAC.

backup big db fail

It's probably talking about memory on the server that SQL Server is running
on. Why would you possibly have the need for Enterprise Edition and 4
processors with 190GB of data but only have 2GB of memory. My laptop has
more than that. Are you using any of the 3rd part backup & compression
tools or is this a native SQL backup? If not I would highly recommend
buying one of them and adding more memory.
Andrew J. Kelly SQL MVP
"906507N" <906507N@.10090.I20034> wrote in message
news:Xns998147FE8B8D0140507NA02619@.207.46.248.16.. .
>I everybody,
> I have a big (well, this is relative) db, when I do a full db backup, the
> .bak is arount 190G
> I run under windows std server 2000, 2G mem, 4 cpu,sql server 2000
> enterprise ed.
> I backup the database to a dedicated server via UNC name with a dedicated
> network card. I have not enought space on local disk
> I do a backup every day but perhaps 1 backup per week works.
> I get the following error message :
> Error 1450 is : Insufficient system resources exist to complete the
> requested service.
> what "ressource" is not enough ? this is not the disk space since the
> target server (windows 2003 R2) has 3T free space.
> hope some one has some advise.
> thanks in advance
> BackupMedium::ReportIoError :
> chec write sur l'unit de sauvegarde \\storage\backup\Backup_22\SD_db_
> 200707291858.BAK .
> Erreur du systme d'exploitation 1450
> (Ressources systme insuffisantes pour terminer le service demand.).
> La sauvegarde n'a pas russir achever la commande
> BACKUP DATABASE [SIMD] TO DISK = N'\\storage\backup\Backup_22\SD_db_
> 200707291858.BAK'
> WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
> Internal I/O request 0x05702918:
> Op: Write, pBuffer: 0x05710000, Size: 983040,
> Position: 137109051904, RetryCount: 10, UMS: Internal: 0x0,
> InternalHigh: 0xF0000, Offset: 0xEC561A00, OffsetHigh: 0x1F, m_buf:
> 0x05710000,
> m_len: 983040, m_actualBytes: 0, m_errcode: 1450,
> BackupFile: \\storage\backup\Backup_22\SD_db_200707291858.BAK
On Aug 2, 6:55 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> It's probably talking about memory on the server that SQL Server is running
> on. Why would you possibly have the need for Enterprise Edition and 4
> processors with 190GB of data but only have 2GB of memory. My laptop has
> more than that. Are you using any of the 3rd part backup & compression
> tools or is this a native SQL backup? If not I would highly recommend
> buying one of them and adding more memory.
> --
> Andrew J. Kelly SQL MVP
> "906507N" <9065...@.10090.I20034> wrote in message
> news:Xns998147FE8B8D0140507NA02619@.207.46.248.16.. .
>
>
>
>
>
> - Show quoted text -
That is a rather low amount of memory for a box with those
specifications and needs. I'm not sure if this problem is memory
related, but I've also read a lot of various posts in regards to
posting large files to a network location. Again, most of these will
give other errors than the ones you reported, but might be another
avenue to venture through.
|||Try it out first with a local storage - probably a USB drive that can
accommodate 190GB and dump your backup there and see what happens
"acorcoran" <acorcoran@.gmail.com> wrote in message
news:1186105163.744059.159720@.i38g2000prf.googlegr oups.com...
On Aug 2, 6:55 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> It's probably talking about memory on the server that SQL Server is
> running
> on. Why would you possibly have the need for Enterprise Edition and 4
> processors with 190GB of data but only have 2GB of memory. My laptop has
> more than that. Are you using any of the 3rd part backup & compression
> tools or is this a native SQL backup? If not I would highly recommend
> buying one of them and adding more memory.
> --
> Andrew J. Kelly SQL MVP
> "906507N" <9065...@.10090.I20034> wrote in message
> news:Xns998147FE8B8D0140507NA02619@.207.46.248.16.. .
>
>
>
>
>
> - Show quoted text -
That is a rather low amount of memory for a box with those
specifications and needs. I'm not sure if this problem is memory
related, but I've also read a lot of various posts in regards to
posting large files to a network location. Again, most of these will
give other errors than the ones you reported, but might be another
avenue to venture through.

Backup and restore question plz help!

If i have old full database back up of master and msdn and every other database that i had on a old sql server 2000 enterprise. can i use the back up file to recreate the same eviroment on a new installed fresh sql server 2000 enterprise boxYes you can restore to the original state.|||The instance must have the same Service-packs already applied however. For example, If the backups are from SQL with service pack 3. Master won't restore on an SQL without this service pack.

Backup across the Network

We are using SQL2000
Can I do a database backup onto a file on another server?
In Enterprise Manager, I can't seem to specify a device on another server.
Thanks for any help.Yes. Just type a UNC name.
Note: SQL must be running under a domain-level account with FULL CONTROL
rights to the share and underlying folders. Don't use administrative
(C$,D$, etc) shares.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"dschrier" <anonymous@.discussions.microsoft.com> wrote in message
news:72E05DAF-E254-4B13-926B-D183E9E95EAD@.microsoft.com...
> We are using SQL2000
> Can I do a database backup onto a file on another server?
> In Enterprise Manager, I can't seem to specify a device on another server.
> Thanks for any help.|||First create a backup device that points to a network share on the other
server, than backup to that backup device.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"dschrier" <anonymous@.discussions.microsoft.com> wrote in message
news:72E05DAF-E254-4B13-926B-D183E9E95EAD@.microsoft.com...
> We are using SQL2000
> Can I do a database backup onto a file on another server?
> In Enterprise Manager, I can't seem to specify a device on another server.
> Thanks for any help.|||Hi,
Can I do a database backup onto a file on another server?
Yes , follow the below steps. Enterprise manager will only show the local
disks.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
Thanks
Hari
MCDBA
"dschrier" <anonymous@.discussions.microsoft.com> wrote in message
news:72E05DAF-E254-4B13-926B-D183E9E95EAD@.microsoft.com...
> We are using SQL2000
> Can I do a database backup onto a file on another server?
> In Enterprise Manager, I can't seem to specify a device on another server.
> Thanks for any help.|||Hi ,
Just to add this you can map the remote share on you database server .
The mapped drive can be used to backup on .
Thanks
Ajay
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ObmCswLSEHA.904@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Can I do a database backup onto a file on another server?
> Yes , follow the below steps. Enterprise manager will only show the local
> disks.
> There are Few Pre requisites to do backup remotely;
>
> 1. You Should start SQL server using Domain user who got access to remote
> machine Share
> 2. Should have share in the remote machine
> 3. If you need to schedule this as a job then SQL Agent should use the
same
> Domain user in which SQL server was started
> 4. Restart the services
> Now you can execute the Backup script with UNC path
> BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak'
with
> init
> Note:
> Backup to remote machine will not work if you start SQL server using Local
> system account
> Thanks
> Hari
> MCDBA
> "dschrier" <anonymous@.discussions.microsoft.com> wrote in message
> news:72E05DAF-E254-4B13-926B-D183E9E95EAD@.microsoft.com...
server.[vbcol=seagreen]
>

Backup across the Network

We are using SQL2000
Can I do a database backup onto a file on another server?
In Enterprise Manager, I can't seem to specify a device on another server.
Thanks for any help.
Yes. Just type a UNC name.
Note: SQL must be running under a domain-level account with FULL CONTROL
rights to the share and underlying folders. Don't use administrative
(C$,D$, etc) shares.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"dschrier" <anonymous@.discussions.microsoft.com> wrote in message
news:72E05DAF-E254-4B13-926B-D183E9E95EAD@.microsoft.com...
> We are using SQL2000
> Can I do a database backup onto a file on another server?
> In Enterprise Manager, I can't seem to specify a device on another server.
> Thanks for any help.
|||First create a backup device that points to a network share on the other
server, than backup to that backup device.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"dschrier" <anonymous@.discussions.microsoft.com> wrote in message
news:72E05DAF-E254-4B13-926B-D183E9E95EAD@.microsoft.com...
> We are using SQL2000
> Can I do a database backup onto a file on another server?
> In Enterprise Manager, I can't seem to specify a device on another server.
> Thanks for any help.
|||Hi,
Can I do a database backup onto a file on another server?
Yes , follow the below steps. Enterprise manager will only show the local
disks.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
Thanks
Hari
MCDBA
"dschrier" <anonymous@.discussions.microsoft.com> wrote in message
news:72E05DAF-E254-4B13-926B-D183E9E95EAD@.microsoft.com...
> We are using SQL2000
> Can I do a database backup onto a file on another server?
> In Enterprise Manager, I can't seem to specify a device on another server.
> Thanks for any help.
|||Hi ,
Just to add this you can map the remote share on you database server .
The mapped drive can be used to backup on .
Thanks
Ajay
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ObmCswLSEHA.904@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Can I do a database backup onto a file on another server?
> Yes , follow the below steps. Enterprise manager will only show the local
> disks.
> There are Few Pre requisites to do backup remotely;
>
> 1. You Should start SQL server using Domain user who got access to remote
> machine Share
> 2. Should have share in the remote machine
> 3. If you need to schedule this as a job then SQL Agent should use the
same
> Domain user in which SQL server was started
> 4. Restart the services
> Now you can execute the Backup script with UNC path
> BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak'
with[vbcol=seagreen]
> init
> Note:
> Backup to remote machine will not work if you start SQL server using Local
> system account
> Thanks
> Hari
> MCDBA
> "dschrier" <anonymous@.discussions.microsoft.com> wrote in message
> news:72E05DAF-E254-4B13-926B-D183E9E95EAD@.microsoft.com...
server.
>

Backup Abort

I have a SQL Server 2000 Enterprise database on a Windows 2003 server. I
created a database-complete backup from SQL Server 2000 Enterprise GUI. When
the backup job finishes it displays the following message: The backup
operation has been completed sucessfully.
But SQL Server errorlog states that BACKUP failed to complete.
No backup file was created.
Please help me with this problem.
Thank You,
SQL Server errorlog
BACKUP failed to complete the command BACKUP LOG [DATABASE] TO DISK = N'K:\D_tlog_200412150700.TRN' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10,
NOFORMATJoe,
You need to check to see if you process has WRITE privs to the K: drive.
Also, look in the Event viewer to see what errors where logged there as
well.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:CCD38392-52DD-4FC5-AB65-F33C0FEF2D84@.microsoft.com...
> I have a SQL Server 2000 Enterprise database on a Windows 2003 server. I
> created a database-complete backup from SQL Server 2000 Enterprise GUI.
When
> the backup job finishes it displays the following message: The backup
> operation has been completed sucessfully.
> But SQL Server errorlog states that BACKUP failed to complete.
> No backup file was created.
> Please help me with this problem.
> Thank You,
>
> SQL Server errorlog
> BACKUP failed to complete the command BACKUP LOG [DATABASE] TO DISK => N'K:\D_tlog_200412150700.TRN' WITH INIT , NOUNLOAD , NOSKIP , STATS =10,
> NOFORMAT

Backup Abort

I have a SQL Server 2000 Enterprise database on a Windows 2003 server. I
created a database-complete backup from SQL Server 2000 Enterprise GUI. When
the backup job finishes it displays the following message: The backup
operation has been completed sucessfully.
But SQL Server errorlog states that BACKUP failed to complete.
No backup file was created.
Please help me with this problem.
Thank You,
SQL Server errorlog
BACKUP failed to complete the command BACKUP LOG [DATABASE] TO DISK =
N'K:\D_tlog_200412150700.TRN' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10,
NOFORMAT
Joe,
You need to check to see if you process has WRITE privs to the K: drive.
Also, look in the Event viewer to see what errors where logged there as
well.
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:CCD38392-52DD-4FC5-AB65-F33C0FEF2D84@.microsoft.com...
> I have a SQL Server 2000 Enterprise database on a Windows 2003 server. I
> created a database-complete backup from SQL Server 2000 Enterprise GUI.
When
> the backup job finishes it displays the following message: The backup
> operation has been completed sucessfully.
> But SQL Server errorlog states that BACKUP failed to complete.
> No backup file was created.
> Please help me with this problem.
> Thank You,
>
> SQL Server errorlog
> BACKUP failed to complete the command BACKUP LOG [DATABASE] TO DISK =
> N'K:\D_tlog_200412150700.TRN' WITH INIT , NOUNLOAD , NOSKIP , STATS =
10,
> NOFORMAT

Backup 19 gig 2 hours

Hi all.
I have a Windows Server Enterprise 2003 with SQL Server SP1 installed on it.
I take every night a full backup of my CRM database. This CRM database grow
every day and I'm now at 19 gig of size.
I backup this database on an another server (Backup to disk), a Windows
Server 2003 standard with 1 CPU (3.4gig) and 1 gig of ram. This server do
absolutely nothing during the time of the backup. The server is idle.
Both servers are connected with 2 NIC at 1 gig each, so .... both are
connected at 2 gig on the network.
Is it normal that the backup of a 19 gig database take 2 hours ?
Thanks.
JonathanHi,
I seriously doubt it, because I backup 45 Gb database in less than 20 min
time.
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:6803F80E-1369-4351-A3DD-7646B93F8ACA@.microsoft.com...
> Hi all.
> I have a Windows Server Enterprise 2003 with SQL Server SP1 installed on
> it.
> I take every night a full backup of my CRM database. This CRM database
> grow
> every day and I'm now at 19 gig of size.
> I backup this database on an another server (Backup to disk), a Windows
> Server 2003 standard with 1 CPU (3.4gig) and 1 gig of ram. This server do
> absolutely nothing during the time of the backup. The server is idle.
> Both servers are connected with 2 NIC at 1 gig each, so .... both are
> connected at 2 gig on the network.
> Is it normal that the backup of a 19 gig database take 2 hours ?
> Thanks.
> Jonathan|||I suspect that the disk subsystem of the target server is slow. Is it RAID?
If so, what level - 0, 2, 5 or 10? How many disks are in the RAID array for
the volume you are using? If you use PerfMon, what is the write queue
length?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:6803F80E-1369-4351-A3DD-7646B93F8ACA@.microsoft.com...
Hi all.
I have a Windows Server Enterprise 2003 with SQL Server SP1 installed on it.
I take every night a full backup of my CRM database. This CRM database grow
every day and I'm now at 19 gig of size.
I backup this database on an another server (Backup to disk), a Windows
Server 2003 standard with 1 CPU (3.4gig) and 1 gig of ram. This server do
absolutely nothing during the time of the backup. The server is idle.
Both servers are connected with 2 NIC at 1 gig each, so .... both are
connected at 2 gig on the network.
Is it normal that the backup of a 19 gig database take 2 hours ?
Thanks.
Jonathan|||Is your network configured properly? Is there other network traffic during
backup? How long does it take to backup to local disk? How long does it
take to copy a 19GB backup file on the backup server to the same drive? One
of these will likely tell you where the root cause is.
TheSQLGuru
President
Indicium Resources, Inc.
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:6803F80E-1369-4351-A3DD-7646B93F8ACA@.microsoft.com...
> Hi all.
> I have a Windows Server Enterprise 2003 with SQL Server SP1 installed on
> it.
> I take every night a full backup of my CRM database. This CRM database
> grow
> every day and I'm now at 19 gig of size.
> I backup this database on an another server (Backup to disk), a Windows
> Server 2003 standard with 1 CPU (3.4gig) and 1 gig of ram. This server do
> absolutely nothing during the time of the backup. The server is idle.
> Both servers are connected with 2 NIC at 1 gig each, so .... both are
> connected at 2 gig on the network.
> Is it normal that the backup of a 19 gig database take 2 hours ?
> Thanks.
> Jonathan|||Hello,
The recommended method is do the backup locally and copy the backup file to
other server. You can do this using a BAT file and schedule it using SQL
Server Agent
Thanks
Hari
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:6803F80E-1369-4351-A3DD-7646B93F8ACA@.microsoft.com...
> Hi all.
> I have a Windows Server Enterprise 2003 with SQL Server SP1 installed on
> it.
> I take every night a full backup of my CRM database. This CRM database
> grow
> every day and I'm now at 19 gig of size.
> I backup this database on an another server (Backup to disk), a Windows
> Server 2003 standard with 1 CPU (3.4gig) and 1 gig of ram. This server do
> absolutely nothing during the time of the backup. The server is idle.
> Both servers are connected with 2 NIC at 1 gig each, so .... both are
> connected at 2 gig on the network.
> Is it normal that the backup of a 19 gig database take 2 hours ?
> Thanks.
> Jonathan|||Thanks all for your answer.
For the target server, it's a RAID 5 with two partition and the one where I
backup my database has 453gig of free space. I have 6 disk at 10k rpm.
I have no other network communication during my SQL backup except 10 to 20
computer that can contact the target server randomly to verify if a new viru
s
definition is availaible.
I do not test to backup locally on the server to verify how much time it can
take to backup locally. I will try to test this.
Thanks.
Jonathan
"Hari Prasad" wrote:

> Hello,
> The recommended method is do the backup locally and copy the backup file t
o
> other server. You can do this using a BAT file and schedule it using SQL
> Server Agent
> Thanks
> Hari
> "Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
> news:6803F80E-1369-4351-A3DD-7646B93F8ACA@.microsoft.com...
>
>|||RAID5 is slow for writes, so that may be your problem. Also, 6 spindles are
not a lot, so you may have a disk bottleneck.
Run PerfMon and check the disk write queue length. If it is greater than 2
X the number of spindles - i.e. 12, in your case - then you have a disk
bottleneck. With 6 disks in RAID5, you have the effective storage of 5
disks. If you reconfigure as RAID10, that will drop to 3. If you can
handle the decreased capacity, then you should get better performance out of
a RAID10 set. That said, I would buy more disks. Spindles are your friend.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:C53A87AA-17B6-4157-81A5-7117353274D1@.microsoft.com...
Thanks all for your answer.
For the target server, it's a RAID 5 with two partition and the one where I
backup my database has 453gig of free space. I have 6 disk at 10k rpm.
I have no other network communication during my SQL backup except 10 to 20
computer that can contact the target server randomly to verify if a new
virus
definition is availaible.
I do not test to backup locally on the server to verify how much time it can
take to backup locally. I will try to test this.
Thanks.
Jonathan
"Hari Prasad" wrote:

> Hello,
> The recommended method is do the backup locally and copy the backup file
> to
> other server. You can do this using a BAT file and schedule it using SQL
> Server Agent
> Thanks
> Hari
> "Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
> news:6803F80E-1369-4351-A3DD-7646B93F8ACA@.microsoft.com...
>
>|||My target server is a RAID 5 with 6 disk at 10k RPM. I have 1 CPU (3.4gig)
and 1 gig of ram. On the partition where I backup the SQL Server, I have 453
gig of free space.
I have no other network traffic during the backup, except 10-20 computers
that can verify if a new virus definition is availaible. The Virus definitio
n
is download outside of the backup period once a day.
The network cards of my target server and my SQL server are configure at
1000/Full Duplex each and my target server File and Printer Sharing option i
s
configure for "Maximize for File Sharing".
I did the backup locally and it took 5 minutes to backup 19 gig. Much
faster. =) But I still have the problem if I need to transfer the backup on
the target server with a bat file.
What can be the problem ?
I need to solve this backup problem because my database will continue to gro
w.
Thanks guy.
Jonathan|||Have you got a large file on the destination server that you can copy to the
same (i.e. destination) server? I just want to confirm that it is a disk
issue and not a network issue. As I mentioned earlier, RAID5 is not your
friend when it comes to heavy writing.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:6F793F0F-D271-4032-955C-E04129246EEB@.microsoft.com...
My target server is a RAID 5 with 6 disk at 10k RPM. I have 1 CPU (3.4gig)
and 1 gig of ram. On the partition where I backup the SQL Server, I have 453
gig of free space.
I have no other network traffic during the backup, except 10-20 computers
that can verify if a new virus definition is availaible. The Virus
definition
is download outside of the backup period once a day.
The network cards of my target server and my SQL server are configure at
1000/Full Duplex each and my target server File and Printer Sharing option
is
configure for "Maximize for File Sharing".
I did the backup locally and it took 5 minutes to backup 19 gig. Much
faster. =) But I still have the problem if I need to transfer the backup on
the target server with a bat file.
What can be the problem ?
I need to solve this backup problem because my database will continue to
grow.
Thanks guy.
Jonathan|||Funny, I thought I mentioned that (copying file on destination server) 2
days ago . . . :-D
Given the network setup (1GB full duplex) it sure sounds like it will be
something on the destination server (antivirus, severe disk frag, very full
disk, etc) causing the problem.
TheSQLGuru
President
Indicium Resources, Inc.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23vMFLORqHHA.4132@.TK2MSFTNGP05.phx.gbl...
> Have you got a large file on the destination server that you can copy to
> the
> same (i.e. destination) server? I just want to confirm that it is a disk
> issue and not a network issue. As I mentioned earlier, RAID5 is not your
> friend when it comes to heavy writing.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
> news:6F793F0F-D271-4032-955C-E04129246EEB@.microsoft.com...
> My target server is a RAID 5 with 6 disk at 10k RPM. I have 1 CPU (3.4gig)
> and 1 gig of ram. On the partition where I backup the SQL Server, I have
> 453
> gig of free space.
> I have no other network traffic during the backup, except 10-20 computers
> that can verify if a new virus definition is availaible. The Virus
> definition
> is download outside of the backup period once a day.
> The network cards of my target server and my SQL server are configure at
> 1000/Full Duplex each and my target server File and Printer Sharing option
> is
> configure for "Maximize for File Sharing".
> I did the backup locally and it took 5 minutes to backup 19 gig. Much
> faster. =) But I still have the problem if I need to transfer the backup
> on
> the target server with a bat file.
> What can be the problem ?
> I need to solve this backup problem because my database will continue to
> grow.
> Thanks guy.
> Jonathan
>

Backup 19 gig 2 hours

Hi all.
I have a Windows Server Enterprise 2003 with SQL Server SP1 installed on it.
I take every night a full backup of my CRM database. This CRM database grow
every day and I'm now at 19 gig of size.
I backup this database on an another server (Backup to disk), a Windows
Server 2003 standard with 1 CPU (3.4gig) and 1 gig of ram. This server do
absolutely nothing during the time of the backup. The server is idle.
Both servers are connected with 2 NIC at 1 gig each, so .... both are
connected at 2 gig on the network.
Is it normal that the backup of a 19 gig database take 2 hours ?
Thanks.
Jonathan
I suspect that the disk subsystem of the target server is slow. Is it RAID?
If so, what level - 0, 2, 5 or 10? How many disks are in the RAID array for
the volume you are using? If you use PerfMon, what is the write queue
length?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:6803F80E-1369-4351-A3DD-7646B93F8ACA@.microsoft.com...
Hi all.
I have a Windows Server Enterprise 2003 with SQL Server SP1 installed on it.
I take every night a full backup of my CRM database. This CRM database grow
every day and I'm now at 19 gig of size.
I backup this database on an another server (Backup to disk), a Windows
Server 2003 standard with 1 CPU (3.4gig) and 1 gig of ram. This server do
absolutely nothing during the time of the backup. The server is idle.
Both servers are connected with 2 NIC at 1 gig each, so .... both are
connected at 2 gig on the network.
Is it normal that the backup of a 19 gig database take 2 hours ?
Thanks.
Jonathan
|||Is your network configured properly? Is there other network traffic during
backup? How long does it take to backup to local disk? How long does it
take to copy a 19GB backup file on the backup server to the same drive? One
of these will likely tell you where the root cause is.
TheSQLGuru
President
Indicium Resources, Inc.
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:6803F80E-1369-4351-A3DD-7646B93F8ACA@.microsoft.com...
> Hi all.
> I have a Windows Server Enterprise 2003 with SQL Server SP1 installed on
> it.
> I take every night a full backup of my CRM database. This CRM database
> grow
> every day and I'm now at 19 gig of size.
> I backup this database on an another server (Backup to disk), a Windows
> Server 2003 standard with 1 CPU (3.4gig) and 1 gig of ram. This server do
> absolutely nothing during the time of the backup. The server is idle.
> Both servers are connected with 2 NIC at 1 gig each, so .... both are
> connected at 2 gig on the network.
> Is it normal that the backup of a 19 gig database take 2 hours ?
> Thanks.
> Jonathan
|||Hello,
The recommended method is do the backup locally and copy the backup file to
other server. You can do this using a BAT file and schedule it using SQL
Server Agent
Thanks
Hari
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:6803F80E-1369-4351-A3DD-7646B93F8ACA@.microsoft.com...
> Hi all.
> I have a Windows Server Enterprise 2003 with SQL Server SP1 installed on
> it.
> I take every night a full backup of my CRM database. This CRM database
> grow
> every day and I'm now at 19 gig of size.
> I backup this database on an another server (Backup to disk), a Windows
> Server 2003 standard with 1 CPU (3.4gig) and 1 gig of ram. This server do
> absolutely nothing during the time of the backup. The server is idle.
> Both servers are connected with 2 NIC at 1 gig each, so .... both are
> connected at 2 gig on the network.
> Is it normal that the backup of a 19 gig database take 2 hours ?
> Thanks.
> Jonathan
|||Thanks all for your answer.
For the target server, it's a RAID 5 with two partition and the one where I
backup my database has 453gig of free space. I have 6 disk at 10k rpm.
I have no other network communication during my SQL backup except 10 to 20
computer that can contact the target server randomly to verify if a new virus
definition is availaible.
I do not test to backup locally on the server to verify how much time it can
take to backup locally. I will try to test this.
Thanks.
Jonathan
"Hari Prasad" wrote:

> Hello,
> The recommended method is do the backup locally and copy the backup file to
> other server. You can do this using a BAT file and schedule it using SQL
> Server Agent
> Thanks
> Hari
> "Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
> news:6803F80E-1369-4351-A3DD-7646B93F8ACA@.microsoft.com...
>
>
|||RAID5 is slow for writes, so that may be your problem. Also, 6 spindles are
not a lot, so you may have a disk bottleneck.
Run PerfMon and check the disk write queue length. If it is greater than 2
X the number of spindles - i.e. 12, in your case - then you have a disk
bottleneck. With 6 disks in RAID5, you have the effective storage of 5
disks. If you reconfigure as RAID10, that will drop to 3. If you can
handle the decreased capacity, then you should get better performance out of
a RAID10 set. That said, I would buy more disks. Spindles are your friend.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:C53A87AA-17B6-4157-81A5-7117353274D1@.microsoft.com...
Thanks all for your answer.
For the target server, it's a RAID 5 with two partition and the one where I
backup my database has 453gig of free space. I have 6 disk at 10k rpm.
I have no other network communication during my SQL backup except 10 to 20
computer that can contact the target server randomly to verify if a new
virus
definition is availaible.
I do not test to backup locally on the server to verify how much time it can
take to backup locally. I will try to test this.
Thanks.
Jonathan
"Hari Prasad" wrote:

> Hello,
> The recommended method is do the backup locally and copy the backup file
> to
> other server. You can do this using a BAT file and schedule it using SQL
> Server Agent
> Thanks
> Hari
> "Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
> news:6803F80E-1369-4351-A3DD-7646B93F8ACA@.microsoft.com...
>
>
|||My target server is a RAID 5 with 6 disk at 10k RPM. I have 1 CPU (3.4gig)
and 1 gig of ram. On the partition where I backup the SQL Server, I have 453
gig of free space.
I have no other network traffic during the backup, except 10-20 computers
that can verify if a new virus definition is availaible. The Virus definition
is download outside of the backup period once a day.
The network cards of my target server and my SQL server are configure at
1000/Full Duplex each and my target server File and Printer Sharing option is
configure for "Maximize for File Sharing".
I did the backup locally and it took 5 minutes to backup 19 gig. Much
faster. =) But I still have the problem if I need to transfer the backup on
the target server with a bat file.
What can be the problem ?
I need to solve this backup problem because my database will continue to grow.
Thanks guy.
Jonathan
|||Have you got a large file on the destination server that you can copy to the
same (i.e. destination) server? I just want to confirm that it is a disk
issue and not a network issue. As I mentioned earlier, RAID5 is not your
friend when it comes to heavy writing.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:6F793F0F-D271-4032-955C-E04129246EEB@.microsoft.com...
My target server is a RAID 5 with 6 disk at 10k RPM. I have 1 CPU (3.4gig)
and 1 gig of ram. On the partition where I backup the SQL Server, I have 453
gig of free space.
I have no other network traffic during the backup, except 10-20 computers
that can verify if a new virus definition is availaible. The Virus
definition
is download outside of the backup period once a day.
The network cards of my target server and my SQL server are configure at
1000/Full Duplex each and my target server File and Printer Sharing option
is
configure for "Maximize for File Sharing".
I did the backup locally and it took 5 minutes to backup 19 gig. Much
faster. =) But I still have the problem if I need to transfer the backup on
the target server with a bat file.
What can be the problem ?
I need to solve this backup problem because my database will continue to
grow.
Thanks guy.
Jonathan
|||Funny, I thought I mentioned that (copying file on destination server) 2
days ago . . . :-D
Given the network setup (1GB full duplex) it sure sounds like it will be
something on the destination server (antivirus, severe disk frag, very full
disk, etc) causing the problem.
TheSQLGuru
President
Indicium Resources, Inc.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23vMFLORqHHA.4132@.TK2MSFTNGP05.phx.gbl...
> Have you got a large file on the destination server that you can copy to
> the
> same (i.e. destination) server? I just want to confirm that it is a disk
> issue and not a network issue. As I mentioned earlier, RAID5 is not your
> friend when it comes to heavy writing.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
> news:6F793F0F-D271-4032-955C-E04129246EEB@.microsoft.com...
> My target server is a RAID 5 with 6 disk at 10k RPM. I have 1 CPU (3.4gig)
> and 1 gig of ram. On the partition where I backup the SQL Server, I have
> 453
> gig of free space.
> I have no other network traffic during the backup, except 10-20 computers
> that can verify if a new virus definition is availaible. The Virus
> definition
> is download outside of the backup period once a day.
> The network cards of my target server and my SQL server are configure at
> 1000/Full Duplex each and my target server File and Printer Sharing option
> is
> configure for "Maximize for File Sharing".
> I did the backup locally and it took 5 minutes to backup 19 gig. Much
> faster. =) But I still have the problem if I need to transfer the backup
> on
> the target server with a bat file.
> What can be the problem ?
> I need to solve this backup problem because my database will continue to
> grow.
> Thanks guy.
> Jonathan
>
|||I ran PerfMon and the result is very bad.
My Avg Queue Length at 6 and more and my Memory Pages/sec is at an average
of 225.
In summary, if I look at the graph, the Memory Pages/Sec and the Avg Disk
Queue Length is at 100%.
Is it a Memory problem or a disk problem ?
I have 1 gig of RAM and 6 disk at 10k rpm in Raid 5.
Thanks.

Backup 19 gig 2 hours

Hi all.
I have a Windows Server Enterprise 2003 with SQL Server SP1 installed on it.
I take every night a full backup of my CRM database. This CRM database grow
every day and I'm now at 19 gig of size.
I backup this database on an another server (Backup to disk), a Windows
Server 2003 standard with 1 CPU (3.4gig) and 1 gig of ram. This server do
absolutely nothing during the time of the backup. The server is idle.
Both servers are connected with 2 NIC at 1 gig each, so .... both are
connected at 2 gig on the network.
Is it normal that the backup of a 19 gig database take 2 hours ?
Thanks.
JonathanHi,
I seriously doubt it, because I backup 45 Gb database in less than 20 min
time.
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:6803F80E-1369-4351-A3DD-7646B93F8ACA@.microsoft.com...
> Hi all.
> I have a Windows Server Enterprise 2003 with SQL Server SP1 installed on
> it.
> I take every night a full backup of my CRM database. This CRM database
> grow
> every day and I'm now at 19 gig of size.
> I backup this database on an another server (Backup to disk), a Windows
> Server 2003 standard with 1 CPU (3.4gig) and 1 gig of ram. This server do
> absolutely nothing during the time of the backup. The server is idle.
> Both servers are connected with 2 NIC at 1 gig each, so .... both are
> connected at 2 gig on the network.
> Is it normal that the backup of a 19 gig database take 2 hours ?
> Thanks.
> Jonathan|||I suspect that the disk subsystem of the target server is slow. Is it RAID?
If so, what level - 0, 2, 5 or 10? How many disks are in the RAID array for
the volume you are using? If you use PerfMon, what is the write queue
length?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:6803F80E-1369-4351-A3DD-7646B93F8ACA@.microsoft.com...
Hi all.
I have a Windows Server Enterprise 2003 with SQL Server SP1 installed on it.
I take every night a full backup of my CRM database. This CRM database grow
every day and I'm now at 19 gig of size.
I backup this database on an another server (Backup to disk), a Windows
Server 2003 standard with 1 CPU (3.4gig) and 1 gig of ram. This server do
absolutely nothing during the time of the backup. The server is idle.
Both servers are connected with 2 NIC at 1 gig each, so .... both are
connected at 2 gig on the network.
Is it normal that the backup of a 19 gig database take 2 hours ?
Thanks.
Jonathan|||Is your network configured properly? Is there other network traffic during
backup? How long does it take to backup to local disk? How long does it
take to copy a 19GB backup file on the backup server to the same drive? One
of these will likely tell you where the root cause is.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:6803F80E-1369-4351-A3DD-7646B93F8ACA@.microsoft.com...
> Hi all.
> I have a Windows Server Enterprise 2003 with SQL Server SP1 installed on
> it.
> I take every night a full backup of my CRM database. This CRM database
> grow
> every day and I'm now at 19 gig of size.
> I backup this database on an another server (Backup to disk), a Windows
> Server 2003 standard with 1 CPU (3.4gig) and 1 gig of ram. This server do
> absolutely nothing during the time of the backup. The server is idle.
> Both servers are connected with 2 NIC at 1 gig each, so .... both are
> connected at 2 gig on the network.
> Is it normal that the backup of a 19 gig database take 2 hours ?
> Thanks.
> Jonathan|||Hello,
The recommended method is do the backup locally and copy the backup file to
other server. You can do this using a BAT file and schedule it using SQL
Server Agent
Thanks
Hari
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:6803F80E-1369-4351-A3DD-7646B93F8ACA@.microsoft.com...
> Hi all.
> I have a Windows Server Enterprise 2003 with SQL Server SP1 installed on
> it.
> I take every night a full backup of my CRM database. This CRM database
> grow
> every day and I'm now at 19 gig of size.
> I backup this database on an another server (Backup to disk), a Windows
> Server 2003 standard with 1 CPU (3.4gig) and 1 gig of ram. This server do
> absolutely nothing during the time of the backup. The server is idle.
> Both servers are connected with 2 NIC at 1 gig each, so .... both are
> connected at 2 gig on the network.
> Is it normal that the backup of a 19 gig database take 2 hours ?
> Thanks.
> Jonathan|||Thanks all for your answer.
For the target server, it's a RAID 5 with two partition and the one where I
backup my database has 453gig of free space. I have 6 disk at 10k rpm.
I have no other network communication during my SQL backup except 10 to 20
computer that can contact the target server randomly to verify if a new virus
definition is availaible.
I do not test to backup locally on the server to verify how much time it can
take to backup locally. I will try to test this.
Thanks.
Jonathan
"Hari Prasad" wrote:
> Hello,
> The recommended method is do the backup locally and copy the backup file to
> other server. You can do this using a BAT file and schedule it using SQL
> Server Agent
> Thanks
> Hari
> "Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
> news:6803F80E-1369-4351-A3DD-7646B93F8ACA@.microsoft.com...
> > Hi all.
> >
> > I have a Windows Server Enterprise 2003 with SQL Server SP1 installed on
> > it.
> >
> > I take every night a full backup of my CRM database. This CRM database
> > grow
> > every day and I'm now at 19 gig of size.
> >
> > I backup this database on an another server (Backup to disk), a Windows
> > Server 2003 standard with 1 CPU (3.4gig) and 1 gig of ram. This server do
> > absolutely nothing during the time of the backup. The server is idle.
> >
> > Both servers are connected with 2 NIC at 1 gig each, so .... both are
> > connected at 2 gig on the network.
> >
> > Is it normal that the backup of a 19 gig database take 2 hours ?
> >
> > Thanks.
> >
> > Jonathan
>
>|||RAID5 is slow for writes, so that may be your problem. Also, 6 spindles are
not a lot, so you may have a disk bottleneck.
Run PerfMon and check the disk write queue length. If it is greater than 2
X the number of spindles - i.e. 12, in your case - then you have a disk
bottleneck. With 6 disks in RAID5, you have the effective storage of 5
disks. If you reconfigure as RAID10, that will drop to 3. If you can
handle the decreased capacity, then you should get better performance out of
a RAID10 set. That said, I would buy more disks. Spindles are your friend.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:C53A87AA-17B6-4157-81A5-7117353274D1@.microsoft.com...
Thanks all for your answer.
For the target server, it's a RAID 5 with two partition and the one where I
backup my database has 453gig of free space. I have 6 disk at 10k rpm.
I have no other network communication during my SQL backup except 10 to 20
computer that can contact the target server randomly to verify if a new
virus
definition is availaible.
I do not test to backup locally on the server to verify how much time it can
take to backup locally. I will try to test this.
Thanks.
Jonathan
"Hari Prasad" wrote:
> Hello,
> The recommended method is do the backup locally and copy the backup file
> to
> other server. You can do this using a BAT file and schedule it using SQL
> Server Agent
> Thanks
> Hari
> "Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
> news:6803F80E-1369-4351-A3DD-7646B93F8ACA@.microsoft.com...
> > Hi all.
> >
> > I have a Windows Server Enterprise 2003 with SQL Server SP1 installed on
> > it.
> >
> > I take every night a full backup of my CRM database. This CRM database
> > grow
> > every day and I'm now at 19 gig of size.
> >
> > I backup this database on an another server (Backup to disk), a Windows
> > Server 2003 standard with 1 CPU (3.4gig) and 1 gig of ram. This server
> > do
> > absolutely nothing during the time of the backup. The server is idle.
> >
> > Both servers are connected with 2 NIC at 1 gig each, so .... both are
> > connected at 2 gig on the network.
> >
> > Is it normal that the backup of a 19 gig database take 2 hours ?
> >
> > Thanks.
> >
> > Jonathan
>
>|||My target server is a RAID 5 with 6 disk at 10k RPM. I have 1 CPU (3.4gig)
and 1 gig of ram. On the partition where I backup the SQL Server, I have 453
gig of free space.
I have no other network traffic during the backup, except 10-20 computers
that can verify if a new virus definition is availaible. The Virus definition
is download outside of the backup period once a day.
The network cards of my target server and my SQL server are configure at
1000/Full Duplex each and my target server File and Printer Sharing option is
configure for "Maximize for File Sharing".
I did the backup locally and it took 5 minutes to backup 19 gig. Much
faster. =) But I still have the problem if I need to transfer the backup on
the target server with a bat file.
What can be the problem ?
I need to solve this backup problem because my database will continue to grow.
Thanks guy.
Jonathan|||Have you got a large file on the destination server that you can copy to the
same (i.e. destination) server? I just want to confirm that it is a disk
issue and not a network issue. As I mentioned earlier, RAID5 is not your
friend when it comes to heavy writing.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:6F793F0F-D271-4032-955C-E04129246EEB@.microsoft.com...
My target server is a RAID 5 with 6 disk at 10k RPM. I have 1 CPU (3.4gig)
and 1 gig of ram. On the partition where I backup the SQL Server, I have 453
gig of free space.
I have no other network traffic during the backup, except 10-20 computers
that can verify if a new virus definition is availaible. The Virus
definition
is download outside of the backup period once a day.
The network cards of my target server and my SQL server are configure at
1000/Full Duplex each and my target server File and Printer Sharing option
is
configure for "Maximize for File Sharing".
I did the backup locally and it took 5 minutes to backup 19 gig. Much
faster. =) But I still have the problem if I need to transfer the backup on
the target server with a bat file.
What can be the problem ?
I need to solve this backup problem because my database will continue to
grow.
Thanks guy.
Jonathan|||Funny, I thought I mentioned that (copying file on destination server) 2
days ago . . . :-D
Given the network setup (1GB full duplex) it sure sounds like it will be
something on the destination server (antivirus, severe disk frag, very full
disk, etc) causing the problem.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23vMFLORqHHA.4132@.TK2MSFTNGP05.phx.gbl...
> Have you got a large file on the destination server that you can copy to
> the
> same (i.e. destination) server? I just want to confirm that it is a disk
> issue and not a network issue. As I mentioned earlier, RAID5 is not your
> friend when it comes to heavy writing.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
> news:6F793F0F-D271-4032-955C-E04129246EEB@.microsoft.com...
> My target server is a RAID 5 with 6 disk at 10k RPM. I have 1 CPU (3.4gig)
> and 1 gig of ram. On the partition where I backup the SQL Server, I have
> 453
> gig of free space.
> I have no other network traffic during the backup, except 10-20 computers
> that can verify if a new virus definition is availaible. The Virus
> definition
> is download outside of the backup period once a day.
> The network cards of my target server and my SQL server are configure at
> 1000/Full Duplex each and my target server File and Printer Sharing option
> is
> configure for "Maximize for File Sharing".
> I did the backup locally and it took 5 minutes to backup 19 gig. Much
> faster. =) But I still have the problem if I need to transfer the backup
> on
> the target server with a bat file.
> What can be the problem ?
> I need to solve this backup problem because my database will continue to
> grow.
> Thanks guy.
> Jonathan
>|||I ran PerfMon and the result is very bad.
My Avg Queue Length at 6 and more and my Memory Pages/sec is at an average
of 225.
In summary, if I look at the graph, the Memory Pages/Sec and the Avg Disk
Queue Length is at 100%.
Is it a Memory problem or a disk problem ?
I have 1 gig of RAM and 6 disk at 10k rpm in Raid 5.
Thanks.|||The disk queue length should be < 2 X the number of spindles = 12, in your
case. Thus, the disk queue length is fine. (Disk queue lengths are
absolute numbers, not percentages. I suspect that the scale for your
counter is set to the default.) Are these numbers from when the actual file
copy is taking place?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jonathan" <Jonathan@.discussions.microsoft.com> wrote in message
news:71B84BFD-943A-42E8-875B-FD2D2559928F@.microsoft.com...
I ran PerfMon and the result is very bad.
My Avg Queue Length at 6 and more and my Memory Pages/sec is at an average
of 225.
In summary, if I look at the graph, the Memory Pages/Sec and the Avg Disk
Queue Length is at 100%.
Is it a Memory problem or a disk problem ?
I have 1 gig of RAM and 6 disk at 10k rpm in Raid 5.
Thanks.

Backup / restore question

I had a SQL server with some harddrive problems. So, a full backup was made
(manually using Enterprise Manager) and restored in a new machine
successfully using Enterprise Manager (the option Leave Database
Operational. No Additional Transaction Logs Can Be Restored was selected)
If the only backup available is the last Full backup, is it possible to ever
roll back some of the last changes made in the old machine before the Full
Backup ?
Thanks for your help.If all you have is the full backup and the old db is toast then no. You
might be able to do something with third party tool help such as Lumigent
Log Explorer depending on a few factors.
Ray Higdon MCSE, MCDBA, CCNA
--
<--> wrote in message news:%23xrmzy6AEHA.3788@.TK2MSFTNGP10.phx.gbl...
> I had a SQL server with some harddrive problems. So, a full backup was
made
> (manually using Enterprise Manager) and restored in a new machine
> successfully using Enterprise Manager (the option Leave Database
> Operational. No Additional Transaction Logs Can Be Restored was selected)
> If the only backup available is the last Full backup, is it possible to
ever
> roll back some of the last changes made in the old machine before the Full
> Backup ?
> Thanks for your help.
>|||Hi,
You can do this only if your OLD database is in "FULL" recovery model. Below
steps are under the assumption
that database was set to FULL recovery model and the existing database is
still up.
1. In the new server Load the full database backup with NORECOVERY
2. Perform a transaction log backup in Old Server.
3. Copy the TX log backup file to new server
4. Restore the Transaction log WITH RECOVERY , STOPAT='DATE/TIME (refer
Books online)
This will allow you to go back to old data based on the date and time.
Note:
If it is Simple or Bulk_logged recovery model you cant rollback.
POINT-IN-TIME recovery is not supposed in both.
Thanks
Hari
MCDBA
<--> wrote in message news:#xrmzy6AEHA.3788@.TK2MSFTNGP10.phx.gbl...
> I had a SQL server with some harddrive problems. So, a full backup was
made
> (manually using Enterprise Manager) and restored in a new machine
> successfully using Enterprise Manager (the option Leave Database
> Operational. No Additional Transaction Logs Can Be Restored was selected)
> If the only backup available is the last Full backup, is it possible to
ever
> roll back some of the last changes made in the old machine before the Full
> Backup ?
> Thanks for your help.
>|||Hi Hari,
Unfortunately, my old server's HD is no longer is functioning. And all I
have is the last Full Backup. Looks like I may not be able to rollback any
changes...
Can you confirm?
Thanks.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:u4QLTU$AEHA.1456@.TK2MSFTNGP09.phx.gbl...
> Hi,
> You can do this only if your OLD database is in "FULL" recovery model.
Below
> steps are under the assumption
> that database was set to FULL recovery model and the existing database is
> still up.
> 1. In the new server Load the full database backup with NORECOVERY
> 2. Perform a transaction log backup in Old Server.
> 3. Copy the TX log backup file to new server
> 4. Restore the Transaction log WITH RECOVERY , STOPAT='DATE/TIME (refer
> Books online)
> This will allow you to go back to old data based on the date and time.
> Note:
> If it is Simple or Bulk_logged recovery model you cant rollback.
> POINT-IN-TIME recovery is not supposed in both.
> Thanks
> Hari
> MCDBA
>
>
>
> <--> wrote in message news:#xrmzy6AEHA.3788@.TK2MSFTNGP10.phx.gbl...
> made
selected)
> ever
Full
>|||You will not be able to rollback any changes, as Hari pointed out you can
only do point in time recovery by using the tran log
Ray Higdon MCSE, MCDBA, CCNA
--
<--> wrote in message news:O1pe8EFBEHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hi Hari,
> Unfortunately, my old server's HD is no longer is functioning. And all I
> have is the last Full Backup. Looks like I may not be able to rollback
any
> changes...
> Can you confirm?
> Thanks.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:u4QLTU$AEHA.1456@.TK2MSFTNGP09.phx.gbl...
> Below
is
was
> selected)
to
> Full
>|||Hi,
If you dont have the old database active, Sorry, you will not be able to
rollback the transactions (POINT-IN-TIME).
Thanks
Hari
MCDBA
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:#vsd8lFBEHA.1600@.tk2msftngp13.phx.gbl...
> You will not be able to rollback any changes, as Hari pointed out you can
> only do point in time recovery by using the tran log
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> <--> wrote in message news:O1pe8EFBEHA.2600@.TK2MSFTNGP09.phx.gbl...
> any
> is
(refer
> was
> to
the
>

Backup - Append vs Overwrite?

Hi,
If backing up a database to a disk file from within Enterprise Manager, it
seems you still get a choice between "Append" and "Overwrite". I don't
understand how these options work with disk files.
Does it mean that if you supply the exact same file name as an earlier
backup, the backup will be appended to the existing file (the file will
double in size but you'll still only have one file?).
Is it the case that if you supply a FileName that's never been used before,
it makes no difference whether you choose Append or Overwrite at this stage?
I've always used Maintenance plans for backup, and it seems you don't get a
choice about Append and Overwrite.
Thanks for any help.
Gerry Hickman
SSRU SysAdminSee inline.....
>If backing up a database to a disk file from within Enterprise Manager, it
>seems you still get a choice between "Append" and "Overwrite". I don't
>understand how these options work with disk files.
>Does it mean that if you supply the exact same file name as an earlier
>backup, the backup will be appended to the existing file (the file will
>double in size but you'll still only have one file?).
Yes it appends the backup to the file so you have one file
and two different backups in the file.
>Is it the case that if you supply a FileName that's never been used before,
>it makes no difference whether you choose Append or Overwrite at this stage?[/vbcol
]
There is nothing to append to as it will be making a new
backup.
[vbcol=seagreen]
>I've always used Maintenance plans for backup, and it seems you don't get a
>choice about Append and Overwrite.
The sqlmaint utility which the database maintenace plan uses
always does an append. You could always just write your own
backup in T-SQL using the with init or noinit options which
controls the append or overwrite behaviors. See BOL Backup
for more details on the options.
-Sue