Sql Backing up Tables: edition

Showing posts with label edition. Show all posts

Thursday, March 8, 2012

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.

Wednesday, March 7, 2012

Backup and restore to another database

We are running SQL Server 2000 Standard Edition
Every night, I want to take a backup of our production database and
restore it to another database that is used for reporting/DR
purposes.
My thinking was as follows:
(1) Setup a job on the source (production) to create the backup to a
named file
(2) Setup a job on the destination (DR) sometime later to restore the
backup
Is this the best way to do this?
Perhaps I could create 1 job to do both?Hi
Do you want to do that on dayly,weekly period?
Take a look at WITH MOVE option in the BOL
"PromisedOyster" <PromisedOyster@.hotmail.com> wrote in message
news:1173960621.133170.194740@.l77g2000hsb.googlegroups.com...
> We are running SQL Server 2000 Standard Edition
> Every night, I want to take a backup of our production database and
> restore it to another database that is used for reporting/DR
> purposes.
> My thinking was as follows:
> (1) Setup a job on the source (production) to create the backup to a
> named file
> (2) Setup a job on the destination (DR) sometime later to restore the
> backup
> Is this the best way to do this?
> Perhaps I could create 1 job to do both?
>|||Hello,
If your database size is not huge you could do the way you mentioned. If the
database is really big take a look into the LOGSHIPPING option.
In SQL 2000 standard you may need to manually configure logshipping. take a
look into the below URL:-
http://www.sqlmag.com/Article/ArticleID/23231/sql_server_23231.html
Thanks
Hari
"PromisedOyster" <PromisedOyster@.hotmail.com> wrote in message
news:1173960621.133170.194740@.l77g2000hsb.googlegroups.com...
> We are running SQL Server 2000 Standard Edition
> Every night, I want to take a backup of our production database and
> restore it to another database that is used for reporting/DR
> purposes.
> My thinking was as follows:
> (1) Setup a job on the source (production) to create the backup to a
> named file
> (2) Setup a job on the destination (DR) sometime later to restore the
> backup
> Is this the best way to do this?
> Perhaps I could create 1 job to do both?
>

Friday, February 10, 2012

Backing up to a different server

Dear All,
We are running Standard Edition 2000 SP3a on a 2003 Server.
We would like to backup onto the disk of a different 2003
Server.
However under the "Use this directory" of the Database
Maintenance Plan, it does not give the share directory we
set up, and typing the location i.e. \\OtherServer\Backups
results in an error.
All accounts we have tried are domain accounts, and they
have access to the directory.
Can anyone tell me what I'm missing ?
Thanks
PeterGive this a try in Query Analyser :-
BACKUP DATABASE pubs to DISK='\\server01\share\backupdir\backup.dmp'
--
HTH
Ryan Waight, MCDBA, MCSE
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:196c01c426ec$5fa7b420$a601280a@.phx.gbl...
> Dear All,
> We are running Standard Edition 2000 SP3a on a 2003 Server.
> We would like to backup onto the disk of a different 2003
> Server.
> However under the "Use this directory" of the Database
> Maintenance Plan, it does not give the share directory we
> set up, and typing the location i.e. \\OtherServer\Backups
> results in an error.
> All accounts we have tried are domain accounts, and they
> have access to the directory.
> Can anyone tell me what I'm missing ?
> Thanks
> Peter|||Thanks for your reply.
However we are trying to do this from the Database
Maintenance Plan, any pointers ?
Thanks
Peter
>--Original Message--
>Give this a try in Query Analyser :-
>BACKUP DATABASE pubs to DISK='\\server01
\share\backupdir\backup.dmp'
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:196c01c426ec$5fa7b420$a601280a@.phx.gbl...
>> Dear All,
>> We are running Standard Edition 2000 SP3a on a 2003
Server.
>> We would like to backup onto the disk of a different
2003
>> Server.
>> However under the "Use this directory" of the Database
>> Maintenance Plan, it does not give the share directory
we
>> set up, and typing the location i.e.
\\OtherServer\Backups
>> results in an error.
>> All accounts we have tried are domain accounts, and they
>> have access to the directory.
>> Can anyone tell me what I'm missing ?
>> Thanks
>> Peter
>
>.
>|||Check that if the account that runs the SQL Server
Services has access to the computer you are trying to
backup to..........
>--Original Message--
>Thanks for your reply.
>However we are trying to do this from the Database
>Maintenance Plan, any pointers ?
>Thanks
>Peter
>
>>--Original Message--
>>Give this a try in Query Analyser :-
>>BACKUP DATABASE pubs to DISK='\\server01
>\share\backupdir\backup.dmp'
>>--
>>HTH
>>Ryan Waight, MCDBA, MCSE
>>"Peter" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:196c01c426ec$5fa7b420$a601280a@.phx.gbl...
>> Dear All,
>> We are running Standard Edition 2000 SP3a on a 2003
>Server.
>> We would like to backup onto the disk of a different
>2003
>> Server.
>> However under the "Use this directory" of the Database
>> Maintenance Plan, it does not give the share directory
>we
>> set up, and typing the location i.e.
>\\OtherServer\Backups
>> results in an error.
>> All accounts we have tried are domain accounts, and
they
>> have access to the directory.
>> Can anyone tell me what I'm missing ?
>> Thanks
>> Peter
>>
>>.
>.
>|||Thanks it does.
It is not a problem with the account, but seems to be a
problem with the Database Maintenance Plan.
thanks
Peter
>--Original Message--
>Check that if the account that runs the SQL Server
>Services has access to the computer you are trying to
>backup to..........
>
>>--Original Message--
>>Thanks for your reply.
>>However we are trying to do this from the Database
>>Maintenance Plan, any pointers ?
>>Thanks
>>Peter
>>
>>--Original Message--
>>Give this a try in Query Analyser :-
>>BACKUP DATABASE pubs to DISK='\\server01
>>\share\backupdir\backup.dmp'
>>--
>>HTH
>>Ryan Waight, MCDBA, MCSE
>>"Peter" <anonymous@.discussions.microsoft.com> wrote in
>>message
>>news:196c01c426ec$5fa7b420$a601280a@.phx.gbl...
>> Dear All,
>> We are running Standard Edition 2000 SP3a on a 2003
>>Server.
>> We would like to backup onto the disk of a different
>>2003
>> Server.
>> However under the "Use this directory" of the Database
>> Maintenance Plan, it does not give the share
directory
>>we
>> set up, and typing the location i.e.
>>\\OtherServer\Backups
>> results in an error.
>> All accounts we have tried are domain accounts, and
>they
>> have access to the directory.
>> Can anyone tell me what I'm missing ?
>> Thanks
>> Peter
>>
>>.
>>.
>.
>|||You never did say what exactly the error is that you are
getting and that makes a difference but...backing up across
the network isn't necessarily the best idea. You may want to
backup to a local drive and then copy the backup to whatever
location you want to keep these on the network.
-Sue
On Tue, 20 Apr 2004 09:16:53 -0700, "Peter"
<anonymous@.discussions.microsoft.com> wrote:
>Thanks it does.
>It is not a problem with the account, but seems to be a
>problem with the Database Maintenance Plan.
>thanks
>Peter
>
>>--Original Message--
>>Check that if the account that runs the SQL Server
>>Services has access to the computer you are trying to
>>backup to..........
>>
>>--Original Message--
>>Thanks for your reply.
>>However we are trying to do this from the Database
>>Maintenance Plan, any pointers ?
>>Thanks
>>Peter
>>
>>--Original Message--
>>Give this a try in Query Analyser :-
>>BACKUP DATABASE pubs to DISK='\\server01
>>\share\backupdir\backup.dmp'
>>--
>>HTH
>>Ryan Waight, MCDBA, MCSE
>>"Peter" <anonymous@.discussions.microsoft.com> wrote in
>>message
>>news:196c01c426ec$5fa7b420$a601280a@.phx.gbl...
>> Dear All,
>> We are running Standard Edition 2000 SP3a on a 2003
>>Server.
>> We would like to backup onto the disk of a different
>>2003
>> Server.
>> However under the "Use this directory" of the Database
>> Maintenance Plan, it does not give the share
>directory
>>we
>> set up, and typing the location i.e.
>>\\OtherServer\Backups
>> results in an error.
>> All accounts we have tried are domain accounts, and
>>they
>> have access to the directory.
>> Can anyone tell me what I'm missing ?
>> Thanks
>> Peter
>>
>>.
>>.
>>.|||Thanks for all your help, it seems that you cannot do it
from the database maintence plan.
Sue the error was 'Directory was not found', and yes the
directory was there and we do have access rights to the
directory. It seems that the database maintenance plan
will only allow you to save to the server SQL is running
on.
Peter
>--Original Message--
>You never did say what exactly the error is that you are
>getting and that makes a difference but...backing up
across
>the network isn't necessarily the best idea. You may want
to
>backup to a local drive and then copy the backup to
whatever
>location you want to keep these on the network.
>-Sue
>On Tue, 20 Apr 2004 09:16:53 -0700, "Peter"
><anonymous@.discussions.microsoft.com> wrote:
>>Thanks it does.
>>It is not a problem with the account, but seems to be a
>>problem with the Database Maintenance Plan.
>>thanks
>>Peter
>>
>>--Original Message--
>>Check that if the account that runs the SQL Server
>>Services has access to the computer you are trying to
>>backup to..........
>>
>>--Original Message--
>>Thanks for your reply.
>>However we are trying to do this from the Database
>>Maintenance Plan, any pointers ?
>>Thanks
>>Peter
>>
>>--Original Message--
>>Give this a try in Query Analyser :-
>>BACKUP DATABASE pubs to DISK='\\server01
>>\share\backupdir\backup.dmp'
>>--
>>HTH
>>Ryan Waight, MCDBA, MCSE
>>"Peter" <anonymous@.discussions.microsoft.com> wrote
in
>>message
>>news:196c01c426ec$5fa7b420$a601280a@.phx.gbl...
>> Dear All,
>> We are running Standard Edition 2000 SP3a on a 2003
>>Server.
>> We would like to backup onto the disk of a
different
>>2003
>> Server.
>> However under the "Use this directory" of the
Database
>> Maintenance Plan, it does not give the share
>>directory
>>we
>> set up, and typing the location i.e.
>>\\OtherServer\Backups
>> results in an error.
>> All accounts we have tried are domain accounts, and
>>they
>> have access to the directory.
>> Can anyone tell me what I'm missing ?
>> Thanks
>> Peter
>>
>>.
>>.
>>.
>.
>|||That is not true. I have been backing up all my databases
on to a backup server for ages.
Can you ping the destination server from the source
server ' It may be a firewall issues e.t.c.
One of the best practice is to set the SQL Server service
and the SQL Server agent service be run by a domain
account. Make sure you include this domain account in
the 'Administrators' group of the both servers (You will
have to stop and restart the services). When you create
the maintenence plan, make the jobs owner this domain
account. But the most important thing is that make sure
you have connection between the two servers.
>--Original Message--
>Thanks for all your help, it seems that you cannot do it
>from the database maintence plan.
>Sue the error was 'Directory was not found', and yes the
>directory was there and we do have access rights to the
>directory. It seems that the database maintenance plan
>will only allow you to save to the server SQL is running
>on.
>Peter
>
>>--Original Message--
>>You never did say what exactly the error is that you are
>>getting and that makes a difference but...backing up
>across
>>the network isn't necessarily the best idea. You may
want
>to
>>backup to a local drive and then copy the backup to
>whatever
>>location you want to keep these on the network.
>>-Sue
>>On Tue, 20 Apr 2004 09:16:53 -0700, "Peter"
>><anonymous@.discussions.microsoft.com> wrote:
>>Thanks it does.
>>It is not a problem with the account, but seems to be a
>>problem with the Database Maintenance Plan.
>>thanks
>>Peter
>>
>>--Original Message--
>>Check that if the account that runs the SQL Server
>>Services has access to the computer you are trying to
>>backup to..........
>>
>>--Original Message--
>>Thanks for your reply.
>>However we are trying to do this from the Database
>>Maintenance Plan, any pointers ?
>>Thanks
>>Peter
>>
>>--Original Message--
>>Give this a try in Query Analyser :-
>>BACKUP DATABASE pubs to DISK='\\server01
>>\share\backupdir\backup.dmp'
>>--
>>HTH
>>Ryan Waight, MCDBA, MCSE
>>"Peter" <anonymous@.discussions.microsoft.com> wrote
>in
>>message
>>news:196c01c426ec$5fa7b420$a601280a@.phx.gbl...
>>> Dear All,
>>>
>>> We are running Standard Edition 2000 SP3a on a
2003
>>Server.
>>>
>>> We would like to backup onto the disk of a
>different
>>2003
>>> Server.
>>>
>>> However under the "Use this directory" of the
>Database
>>> Maintenance Plan, it does not give the share
>>directory
>>we
>>> set up, and typing the location i.e.
>>\\OtherServer\Backups
>>> results in an error.
>>>
>>> All accounts we have tried are domain accounts,
and
>>they
>>> have access to the directory.
>>>
>>> Can anyone tell me what I'm missing ?
>>>
>>> Thanks
>>> Peter
>>
>>.
>>.
>>.
>>
>>.
>.
>|||It will allow you to backup to a network directory when
using a unc path. In terms of who has access rights, it's
the SQL Server service account that needs rights. The
service account needs to be a domain account with
appropriate permissions to the other server, directory.
-Sue
On Wed, 21 Apr 2004 01:15:19 -0700, "Peter"
<anonymous@.discussions.microsoft.com> wrote:
>Thanks for all your help, it seems that you cannot do it
>from the database maintence plan.
>Sue the error was 'Directory was not found', and yes the
>directory was there and we do have access rights to the
>directory. It seems that the database maintenance plan
>will only allow you to save to the server SQL is running
>on.
>Peter
>
>>--Original Message--
>>You never did say what exactly the error is that you are
>>getting and that makes a difference but...backing up
>across
>>the network isn't necessarily the best idea. You may want
>to
>>backup to a local drive and then copy the backup to
>whatever
>>location you want to keep these on the network.
>>-Sue
>>On Tue, 20 Apr 2004 09:16:53 -0700, "Peter"
>><anonymous@.discussions.microsoft.com> wrote:
>>Thanks it does.
>>It is not a problem with the account, but seems to be a
>>problem with the Database Maintenance Plan.
>>thanks
>>Peter
>>
>>--Original Message--
>>Check that if the account that runs the SQL Server
>>Services has access to the computer you are trying to
>>backup to..........
>>
>>--Original Message--
>>Thanks for your reply.
>>However we are trying to do this from the Database
>>Maintenance Plan, any pointers ?
>>Thanks
>>Peter
>>
>>--Original Message--
>>Give this a try in Query Analyser :-
>>BACKUP DATABASE pubs to DISK='\\server01
>>\share\backupdir\backup.dmp'
>>--
>>HTH
>>Ryan Waight, MCDBA, MCSE
>>"Peter" <anonymous@.discussions.microsoft.com> wrote
>in
>>message
>>news:196c01c426ec$5fa7b420$a601280a@.phx.gbl...
>>> Dear All,
>>>
>>> We are running Standard Edition 2000 SP3a on a 2003
>>Server.
>>>
>>> We would like to backup onto the disk of a
>different
>>2003
>>> Server.
>>>
>>> However under the "Use this directory" of the
>Database
>>> Maintenance Plan, it does not give the share
>>directory
>>we
>>> set up, and typing the location i.e.
>>\\OtherServer\Backups
>>> results in an error.
>>>
>>> All accounts we have tried are domain accounts, and
>>they
>>> have access to the directory.
>>>
>>> Can anyone tell me what I'm missing ?
>>>
>>> Thanks
>>> Peter
>>
>>.
>>.
>>.
>>
>>.

Backing up the SQL server 2000 database using tape drives

Dear sir/madam,

I am from NTU, Singapore and we have a windows 2003 server edition installed on a DELL server and it also runs MS SQL server 2000 for a project of ours. We have installed a HP tape drive on the dell server and it works fine if ntbackup is used at the command prompt but then the SQL enterprise manager does not recognize the tape drive and backing up of our database is of high priority as it contains sensitive information. But the tape drive is installed properly as the device manager does not show any error and ntbackup also recognizes it. I cant seem to figure out why SQL server 2000 cannot recognize it. It would be very helpful if you could kindly guide me on this issue.

Thanking you

Regards

Lavanya Janardhanan

There are two things that you can try here:

The first is to create a logical backup device for your tape device.

From Enterprise Manager, open the Query Analyzer and use the following command:

USE master EXEC sp_addumpdevice 'tape', 'tapedump1', '\\.\tape0'

Where tapedump1 is the logical name for your tape drive, and \\.\tape0 is the physical path to your tape drive. You should use values for these parameters as appropriate to your configuration. You should then be able to use the logical device as a destination for your backups.

Alternatively, you can specify the tape path explicitly in TSQL, again in Query Analyzer:

BACKUP DATABASE MyDatabase TO TAPE = '\\.\Tape0' WITH FORMAT, NAME = 'My Backup Name';

This technique can help to determine where the issue is with recognizing your tape device.

Kevin Farlee

Thursday, March 8, 2012

backup big db fail

Wednesday, March 7, 2012

Backup and restore to another database

Friday, February 10, 2012

Backing up to a different server

Backing up to a different server

Backing up to a different server

Backing up the SQL server 2000 database using tape drives

Sql Backing up Tables

Blog Archive

About Me