Basic package is causing an error

I'm getting the following error message on a basic copy from a datareader (using an ODBC datasource) to a sqlnativeclient. There are no transformations or anything. Don't know what is going on. Any insights are appreciated.

[SQL Server Destination [361]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

SQL Server Destination only works if the server you are inserting to is the same server that you are running SSIS on. Otherise you get the message that you are seeing.

-Jamie

|||

Jamie Thomson wrote:

SQL Server Destination only works if the server you are inserting to is the same server that you are running SSIS on. Otherise you get the message that you are seeing.

-Jamie

jamie is correct. you can use the ole db destination instead.

btw, in her ssis webcast, joy mundy said that the ole db destination outperforms the sql server destination (in most cases).

|||

Duane Douglas wrote:

Jamie Thomson wrote:

SQL Server Destination only works if the server you are inserting to is the same server that you are running SSIS on. Otherise you get the message that you are seeing.

-Jamie

jamie is correct. you can use the ole db destination instead.

btw, in her ssis webcast, joy mundy said that the ole db destination outperforms the sql server destination (in most cases).

Really? Then Joy Mundy is wrong. Please could you send me a link to the webcast, I need to check this out?

Regards

Jamie

|||Thank you Jamie and Duane. Makes sense, but I didn't realize you had to use OLE DB for non-local connections. I'll go that route.|||

OLE DB Destination is the "default" destination for any relational database table. The SQL Server Destination is an optional extra that can be used in very specific circumstances.

Personally I think the name "SQL Server Destination" confuses people. They see it and automatically think it should be used when inserting into SQL Server and that is not the case. In Microsoft's defence it does say this very very clearly in the documentation.

-Jamie

|||I'm sure it is documented, but as often happens in life I have to fly by the seat of my pants learning as I go for a quick solution to a problem. No time to actually learn the product till later. Again, the help is greatly appreciated.|||

Jamie Thomson wrote:

Duane Douglas wrote:

Jamie Thomson wrote:

SQL Server Destination only works if the server you are inserting to is the same server that you are running SSIS on. Otherise you get the message that you are seeing.

-Jamie

jamie is correct. you can use the ole db destination instead.

btw, in her ssis webcast, joy mundy said that the ole db destination outperforms the sql server destination (in most cases).

Really? Then Joy Mundy is wrong. Please could you send me a link to the webcast, I need to check this out?

Regards

Jamie

jamie,

here's the link to joy's webcast: http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032297072&EventCategory=5&culture=en-US&CountryCode=US

|||

Jamie Thomson wrote:

Personally I think the name "SQL Server Destination" confuses people. They see it and automatically think it should be used when inserting into SQL Server and that is not the case. In Microsoft's defence it does say this very very clearly in the documentation.

-Jamie

i agree. "local sql server destination" might be a more appropriate title.sql

Basic Example of Insert, Update Trigger

Hello, Can someone please show me a basic example of trigger that will
work with two tables in this manner:
1. On INSERT for Table1, copy a particular column's value to another
column in Table2
2. On UPDATE for Table1, copy this particular column's value to
another column in Table2.
The key field for both tables is Invoice# and will always exist in
both tables.
It seems like there are a couple ways to do this, one with just using
a join and the other using the 'Inserted' table. Could someone please
show me the best approach to this solution? I would really be grateful
and name my next born after you.
Thanks in advance,
Buster
On Jun 13, 6:51 am, Buster Coder <dice_respo...@.hotmail.com> wrote:
> Hello, Can someone please show me a basic example of trigger that will
> work with two tables in this manner:
> 1. On INSERT for Table1, copy a particular column's value to another
> column in Table2
> 2. On UPDATE for Table1, copy this particular column's value to
> another column in Table2.
> The key field for both tables is Invoice# and will always exist in
> both tables.
> It seems like there are a couple ways to do this, one with just using
> a join and the other using the 'Inserted' table. Could someone please
> show me the best approach to this solution? I would really be grateful
> and name my next born after you.
> Thanks in advance,
> Buster
I think you require update in table2 in both the cases
CREATE TRIGGER employee_insupd
ON table1
FOR INSERT, UPDATE
AS
UPDATE T2 SET
col2 = a.col1
FROM table2 T2 , inserted a
WHERE T2.invoiceno = a.invoiceno

Basic Example of Insert, Update Trigger

Hello, Can someone please show me a basic example of trigger that will
work with two tables in this manner:
1. On INSERT for Table1, copy a particular column's value to another
column in Table2
2. On UPDATE for Table1, copy this particular column's value to
another column in Table2.
The key field for both tables is Invoice# and will always exist in
both tables.
It seems like there are a couple ways to do this, one with just using
a join and the other using the 'Inserted' table. Could someone please
show me the best approach to this solution? I would really be grateful
and name my next born after you.
Thanks in advance,
BusterOn Jun 13, 6:51 am, Buster Coder <dice_respo...@.hotmail.com> wrote:
> Hello, Can someone please show me a basic example of trigger that will
> work with two tables in this manner:
> 1. On INSERT for Table1, copy a particular column's value to another
> column in Table2
> 2. On UPDATE for Table1, copy this particular column's value to
> another column in Table2.
> The key field for both tables is Invoice# and will always exist in
> both tables.
> It seems like there are a couple ways to do this, one with just using
> a join and the other using the 'Inserted' table. Could someone please
> show me the best approach to this solution? I would really be grateful
> and name my next born after you.
> Thanks in advance,
> Buster
I think you require update in table2 in both the cases
CREATE TRIGGER employee_insupd
ON table1
FOR INSERT, UPDATE
AS
UPDATE T2 SET
col2 = a.col1
FROM table2 T2 , inserted a
WHERE T2.invoiceno = a.invoicenosql

Basic Example of Insert, Update Trigger

Hello, Can someone please show me a basic example of trigger that will
work with two tables in this manner:
1. On INSERT for Table1, copy a particular column's value to another
column in Table2
2. On UPDATE for Table1, copy this particular column's value to
another column in Table2.
The key field for both tables is Invoice# and will always exist in
both tables.
It seems like there are a couple ways to do this, one with just using
a join and the other using the 'Inserted' table. Could someone please
show me the best approach to this solution? I would really be grateful
and name my next born after you.
Thanks in advance,
BusterOn Jun 13, 6:51 am, Buster Coder <dice_respo...@.hotmail.com> wrote:
> Hello, Can someone please show me a basic example of trigger that will
> work with two tables in this manner:
> 1. On INSERT for Table1, copy a particular column's value to another
> column in Table2
> 2. On UPDATE for Table1, copy this particular column's value to
> another column in Table2.
> The key field for both tables is Invoice# and will always exist in
> both tables.
> It seems like there are a couple ways to do this, one with just using
> a join and the other using the 'Inserted' table. Could someone please
> show me the best approach to this solution? I would really be grateful
> and name my next born after you.
> Thanks in advance,
> Buster
I think you require update in table2 in both the cases
CREATE TRIGGER employee_insupd
ON table1
FOR INSERT, UPDATE
AS
UPDATE T2 SET
col2 = a.col1
FROM table2 T2 , inserted a
WHERE T2.invoiceno = a.invoiceno

backups through EM on remote registration

I have installed Sql Server 2000 evaluation copy on Windows XP, which I use to monitor other sql server 2000 installations (on Windows 2000) through Enterprise manager version 8.

What has been noticed is that if you try and set up a backup job through enterprise manager you do not get the option to remove inactive transaction log entries (yes the database's use full recovery models). Also if you select the verify backup option, it is not included within the job.

This is the same if you try the above on the local instance. This is not a problem as you can use the server installations, but what I am wondering is:

1. Has anyone come across this before?
2. Is it sql server 2000 on XP?
3. Is it the evaluation copy that is causing the problem?
4. Is it none of the above but indeed a far more complex reason which will mess with my head?

As always your help / knowledge is very much appreciated.As to verify, take a look at Database Maintanace Plans - there you can make it a part of your scheduled job. As to Inactive entries, actually it gets into your scheduled job, it is a default option when backing up transaction log - so you cannot see it in script that was generated by EM. Test for yourself - set up job without checking Remove inactive entries option and you can see that NO_TRUNCATE option is included in your command. Hope that it clears matters up a bit. Regards mojza|||Thanks for the reply mojza. I have no problems setting them up on the actual server, just on my PC through EM.

The verify problem is still an issue (i.e. You select it on your PC through EM but it is not part of the TSQL in the job).

However, the remove inactive transactions has got slightly stranger. On the server itself through EM, it is an option when creating a job and not touching the backup option (i.e. Database - complete is ticked by default). If you reselect it (click on it again or select another option and go back to complete) the option dissappears (which it should). Odd!!

As I said this is not really an issue but if anyone knows why I would appreciate the knowledge.|||I believe that Verify option in EM simply runs RESTORE VERIFYONLY command against backup set just completed so you can include this command plus some logic if you choose backup by T-SQL, otherwise you can set up Backup job in Database Maintanance Plan Wizard (it uses sqlmaint utility to perform its tasks which has -VrfyBackup switch to achieve backup set validation).

If 'remove inactive transactions' option is enabled in other backup type than transaction log, it's strange behavior and I've never seen that or heard about it. Evaluation edition or SQL on XP shouldn't be the cause. mojza|||The actual command it uses to berify if created on the server is:

select @.i = position from msdb..backupset where database_name='??' and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='??')
RESTORE VERIFYONLY FROM [??] WITH FILE = @.i

I would be interested to know if anyone else can reproduce the 'remove inactive transactions' with a complete backup problem. Essentially all I did was right-click on the database, select backup database and under the options tab the option is there.

Backups over a network

I am using the following syntax in sql 2000 in order to transfer the backup across the network to a backup machine:

xp_cmdshell N'copy /Y c:\mssql7\backup\bill_backup.bak \\Backup\backups\Bill\Backup'

The problem I am experiencing is that when I run this script, I get the response:

Access is denied.
0 file(s) copied

Can anyone tell me what I am doing wrong?Yea, your service account doesn't have rights to the box..

besides, backing up across the network can't be a good idea..

why not dump it locally and then move it?

Still, don't you think it's a security thing?|||Originally posted by Brett Kaiser
Yea, your service account doesn't have rights to the box..

besides, backing up across the network can't be a good idea..

why not dump it locally and then move it?

he is backing up locally and then trying to copy it over the network .. Brett

read the post again

xp_cmdshell N'copy /Y c:\mssql7\backup\bill_backup.bak \\Backup\backups\Bill\Backup'|||Ok, ive given up and having sql do the move for me. Not exactly sure whats causing the problem, but I have worked around it. I have just written a bat file that copies over a mapped drive that is run thru the schedule tasks interface. Not as clean as I would like, but it works.

Thanks again guys

Backups causing connectivity issues

I'm maintaining a database with an automated backup process.

Periodically, it does a "BACKUP DATABASE" to a local file and then do a file copy across the network to another system. This file is 2.7GB and when it is being copied it causes network problems; specifically ADO connections to the database get broken.

Does anyone have experience with this kind of issue?A DOS Copy is causing a network problem?

I wouldn't think so.

Where's the code, and what's the specific error message.|||A DOS Copy is causing a network problem?

I wouldn't think so.

Where's the code, and what's the specific error message.

I have a C++ ADO client application that gets:

Database COM exception.
HRESULT = -2147467259 (0x80004005)
[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.

This client application is running 24/7 on many different workstations. They all encounter this error and shutdown at the same time. It seems to only encounters this error during the file copy operation I mentioned. I assume that the 2.7GB file copy consumes so much bandwidth that it disrupts other network activity. CPU use on the server is low. I wouldn't have guessed this as the problem but that is certainly what the symptoms suggest.|||I still wouldn't put any money down on that...

But as check, why not copy the file over during periods of low activity...|||I still wouldn't put any money down on that...

But as check, why not copy the file over during periods of low activity...

We do. We copy it over at midnight. The C++ client app I mentioned needs to run 24/7/365 though and it can't lose network connectivity to the database (this is all on a LAN and has UPS).|||OK...out of the box thinking...is there anything else affected?

You sure it's not something to do with the way the app is constructed?

I'm no network guy, but can you put a sniffer on the network?|||DOS copy may be the same as Windows copy. As I understand things, Windows copy eats a lot of bandwidth in parity checks, and whatnot. Can you use FTP to deliver the database to an FTP server? That may be a lighter connection.|||Thanks guys. Those suggestions might work but the network guys agreed to just backup to a separate local hard drive. That will definitely spare the network.|||For files of these sizes to move over LAN people usually set up a dedicated NIC on a different subnet. If you spare a couple of bucks wisely (SQLLiteSpeed) you may shrink the size of the backup file down to around 500MB.

Backup/Restore from MSDE 1.0 to MSDE 2000

To anyone who can assist:
Everything I have read online today states that I should be able to take a
backup copy of my database in MSDE 1.0 and restore it to MSDE 2000. However,
I am experiencing problems with this. We are wanting to upgrade our customer
sites to MSDE 2000 (from MSDE 1.0), and we have as part of our product a
utility which they can backup and restore their databases.
The Backup utility calls this statement:
BACKUP DATABASE dbName TO DISK = fileName WITH BLOCKSIZE=2048
The Restore utility calls this statement:
RESTORE DATABASE dbName FROM DISK = fileName WITH REPLACE
I am able to uninstall MSDE 1.0 from our server, install MSDE 2000, copy our
DB (mdf & ldf files) from the original location to the Data directory for
MSDE 2000, and attach to the database. Everything works great until I
attempt to restore a copy of the database that was generated in MSDE 1.0.
Our Restore utility stops our Database server service, runs the RESTORE
command, then attempts to restart our DB service. This fails, and when I
attempt to look at the database in DbaMgr2K (a very nice tool, BTW), I
receive an error message stating that I need to upgrade this database.
Is there something I am overlooking? The only thing seems to be different
between the two installations is that we had to change our ODBC
configuration. Using MSDE 1.0, we could connect to the "SQL Server"
connection. Using MSDE 2000, we had to change this to our local server.
Thanks in advance for your help!
Becky
hi Becky,
BElders wrote:
> ...
> I am able to uninstall MSDE 1.0 from our server, install MSDE 2000,
> copy our DB (mdf & ldf files) from the original location to the Data
> directory for MSDE 2000, and attach to the database. Everything
> works great until I attempt to restore a copy of the database that
> was generated in MSDE 1.0. Our Restore utility stops our Database
> server service, runs the RESTORE command, then attempts to restart
> our DB service. This fails, and when I attempt to look at the
> database in DbaMgr2K (a very nice tool, BTW), I receive an error
> message stating that I need to upgrade this database.
it's not clear to me why are you stopping the service... if you stop it, how
do you perform the RESTORE operation?
the service of the restoring server must be running to do that , but
probably I misunderstood your words.. or this is can be the cause of the
restore operation failing...
restoring the MSDE 1.0 database on a MSDE 2000 instance will upgrade it to
2000 version, including the te database compatibility level, and DbaMgr2k
and DbaMgr are engine version specefic and not database version (thank you
for your interest in these prjs of mine), so you have to use DbaMgr2k for
MSDE 2000 instances, and DbaMgr for MSDE 1.0 instances..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Just to clear up the confusion...I am not stopping the SQL server service
when using our Restore utility. The service I mentioned is part of our
product, and this is really performed as a precaution. Also, the Restore
command appears to execute OK. The error occurs when our service is
attempting to restart.
That being said, I decided to play around with the Backup/Restore operations
within DbaMgr2k (I am pretty new at using the tool). I received an error
message when trying to run the Restore using the old copy because it could
not locate the mssql7\data directory (removed - by me - as part of the
uninstall of MSDE 1.0). I added this directory back, and the RESTORE
functioned correctly. One thing that I did not realize was that the MDF/LDF
files are relocated in this process. I attempted to mimic this using our
Restore utility, but the same error occurred.
My conclusion is that it looks as though the RESTORE statement in our
utility is not "robust" enough. So I am going to either have to modify our
utility, or just provide instructions to our clients of the steps needed to
perform the same operations using DbaMgr2K (I think I like the latter
solution .
Thanks for your help!!
Becky

Backup/copy to remote server

Hello,
I've spent the afternoon reading old newsgroup posts and BOL but am
still a bit confused about the best way to approach a new backup
scheme.
My organization would like to establish a single repository for backups
from our database servers, by either backing up databases on each
server and then moving them to the new, central server in some
automated fashion, or by simply pointing to the remote server as the
backup device.

>From a network performance standpoint I think backing up locally and
then copying the resulting files to a remote destination may be the
better option, as we have several servers and at least one of the
databases residing on them results in an almost 2GB backup file (with
most of the remainder smaller than that). I'm also unsure of exactly
how to go about this.
I believe SQL Server has been installed by default under the local
system account, so I'm wondering if this will limit my options with
regard to authentication across servers.
I've read about the BACKUP command, "log shipping" and other options,
but am not sure if any match our requirements.
If anyone could shine a light down the right path for me, my blood
pressure would be grateful. ;)vast55555@.yahoo.com wrote:
> Hello,
> I've spent the afternoon reading old newsgroup posts and BOL but am
> still a bit confused about the best way to approach a new backup
> scheme.
> My organization would like to establish a single repository for backups
> from our database servers, by either backing up databases on each
> server and then moving them to the new, central server in some
> automated fashion, or by simply pointing to the remote server as the
> backup device.
>
> then copying the resulting files to a remote destination may be the
> better option, as we have several servers and at least one of the
> databases residing on them results in an almost 2GB backup file (with
> most of the remainder smaller than that). I'm also unsure of exactly
> how to go about this.
> I believe SQL Server has been installed by default under the local
> system account, so I'm wondering if this will limit my options with
> regard to authentication across servers.
> I've read about the BACKUP command, "log shipping" and other options,
> but am not sure if any match our requirements.
> If anyone could shine a light down the right path for me, my blood
> pressure would be grateful. ;)
>
Create a share on your central server, configure SQL to run as a domain
user, grant that domain user permissions to the share, then use the
BACKUP command to backup directly to the UNC for that share. We move a
dozen backups around on our network each night, some of which are
approaching 200GB, there is no noticeable difference between backing up
locally and then copying vs. backing up over the network.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy,
Thanks for your quick response. That sounds like the way to go. One
potential problem is that this is a hosted situation--not in-house--and
I think there may be no domain, but rather isolated machines (I'm not a
network engineer by trade so I'm a bit ignorant here).
Tracy McKibben wrote:
> Create a share on your central server, configure SQL to run as a domain
> user, grant that domain user permissions to the share, then use the
> BACKUP command to backup directly to the UNC for that share. We move a
> dozen backups around on our network each night, some of which are
> approaching 200GB, there is no noticeable difference between backing up
> locally and then copying vs. backing up over the network.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Performance wise, backing up straight to a remote file share is often faster
than backing up locally and then copying the backup file to the same file
share. But there are other factors to consider. For instance, with backing u
p
straight to the remote file share, if it fails towards to the end due to som
e
network issue, the retry may take longer than just retrying the copy
operation.
Linchi
"vast55555@.yahoo.com" wrote:

> Hello,
> I've spent the afternoon reading old newsgroup posts and BOL but am
> still a bit confused about the best way to approach a new backup
> scheme.
> My organization would like to establish a single repository for backups
> from our database servers, by either backing up databases on each
> server and then moving them to the new, central server in some
> automated fashion, or by simply pointing to the remote server as the
> backup device.
>
> then copying the resulting files to a remote destination may be the
> better option, as we have several servers and at least one of the
> databases residing on them results in an almost 2GB backup file (with
> most of the remainder smaller than that). I'm also unsure of exactly
> how to go about this.
> I believe SQL Server has been installed by default under the local
> system account, so I'm wondering if this will limit my options with
> regard to authentication across servers.
> I've read about the BACKUP command, "log shipping" and other options,
> but am not sure if any match our requirements.
> If anyone could shine a light down the right path for me, my blood
> pressure would be grateful. ;)
>

Backup/copy to remote server

Hello,
I've spent the afternoon reading old newsgroup posts and BOL but am
still a bit confused about the best way to approach a new backup
scheme.
My organization would like to establish a single repository for backups
from our database servers, by either backing up databases on each
server and then moving them to the new, central server in some
automated fashion, or by simply pointing to the remote server as the
backup device.
>From a network performance standpoint I think backing up locally and
then copying the resulting files to a remote destination may be the
better option, as we have several servers and at least one of the
databases residing on them results in an almost 2GB backup file (with
most of the remainder smaller than that). I'm also unsure of exactly
how to go about this.
I believe SQL Server has been installed by default under the local
system account, so I'm wondering if this will limit my options with
regard to authentication across servers.
I've read about the BACKUP command, "log shipping" and other options,
but am not sure if any match our requirements.
If anyone could shine a light down the right path for me, my blood
pressure would be grateful. ;)vast55555@.yahoo.com wrote:
> Hello,
> I've spent the afternoon reading old newsgroup posts and BOL but am
> still a bit confused about the best way to approach a new backup
> scheme.
> My organization would like to establish a single repository for backups
> from our database servers, by either backing up databases on each
> server and then moving them to the new, central server in some
> automated fashion, or by simply pointing to the remote server as the
> backup device.
>>From a network performance standpoint I think backing up locally and
> then copying the resulting files to a remote destination may be the
> better option, as we have several servers and at least one of the
> databases residing on them results in an almost 2GB backup file (with
> most of the remainder smaller than that). I'm also unsure of exactly
> how to go about this.
> I believe SQL Server has been installed by default under the local
> system account, so I'm wondering if this will limit my options with
> regard to authentication across servers.
> I've read about the BACKUP command, "log shipping" and other options,
> but am not sure if any match our requirements.
> If anyone could shine a light down the right path for me, my blood
> pressure would be grateful. ;)
>
Create a share on your central server, configure SQL to run as a domain
user, grant that domain user permissions to the share, then use the
BACKUP command to backup directly to the UNC for that share. We move a
dozen backups around on our network each night, some of which are
approaching 200GB, there is no noticeable difference between backing up
locally and then copying vs. backing up over the network.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy,
Thanks for your quick response. That sounds like the way to go. One
potential problem is that this is a hosted situation--not in-house--and
I think there may be no domain, but rather isolated machines (I'm not a
network engineer by trade so I'm a bit ignorant here).
Tracy McKibben wrote:
> Create a share on your central server, configure SQL to run as a domain
> user, grant that domain user permissions to the share, then use the
> BACKUP command to backup directly to the UNC for that share. We move a
> dozen backups around on our network each night, some of which are
> approaching 200GB, there is no noticeable difference between backing up
> locally and then copying vs. backing up over the network.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Performance wise, backing up straight to a remote file share is often faster
than backing up locally and then copying the backup file to the same file
share. But there are other factors to consider. For instance, with backing up
straight to the remote file share, if it fails towards to the end due to some
network issue, the retry may take longer than just retrying the copy
operation.
Linchi
"vast55555@.yahoo.com" wrote:
> Hello,
> I've spent the afternoon reading old newsgroup posts and BOL but am
> still a bit confused about the best way to approach a new backup
> scheme.
> My organization would like to establish a single repository for backups
> from our database servers, by either backing up databases on each
> server and then moving them to the new, central server in some
> automated fashion, or by simply pointing to the remote server as the
> backup device.
> >From a network performance standpoint I think backing up locally and
> then copying the resulting files to a remote destination may be the
> better option, as we have several servers and at least one of the
> databases residing on them results in an almost 2GB backup file (with
> most of the remainder smaller than that). I'm also unsure of exactly
> how to go about this.
> I believe SQL Server has been installed by default under the local
> system account, so I'm wondering if this will limit my options with
> regard to authentication across servers.
> I've read about the BACKUP command, "log shipping" and other options,
> but am not sure if any match our requirements.
> If anyone could shine a light down the right path for me, my blood
> pressure would be grateful. ;)
>

Backup/copy to remote server

Hello,
I've spent the afternoon reading old newsgroup posts and BOL but am
still a bit confused about the best way to approach a new backup
scheme.
My organization would like to establish a single repository for backups
from our database servers, by either backing up databases on each
server and then moving them to the new, central server in some
automated fashion, or by simply pointing to the remote server as the
backup device.

>From a network performance standpoint I think backing up locally and
then copying the resulting files to a remote destination may be the
better option, as we have several servers and at least one of the
databases residing on them results in an almost 2GB backup file (with
most of the remainder smaller than that). I'm also unsure of exactly
how to go about this.
I believe SQL Server has been installed by default under the local
system account, so I'm wondering if this will limit my options with
regard to authentication across servers.
I've read about the BACKUP command, "log shipping" and other options,
but am not sure if any match our requirements.
If anyone could shine a light down the right path for me, my blood
pressure would be grateful. ;)
vast55555@.yahoo.com wrote:
> Hello,
> I've spent the afternoon reading old newsgroup posts and BOL but am
> still a bit confused about the best way to approach a new backup
> scheme.
> My organization would like to establish a single repository for backups
> from our database servers, by either backing up databases on each
> server and then moving them to the new, central server in some
> automated fashion, or by simply pointing to the remote server as the
> backup device.
> then copying the resulting files to a remote destination may be the
> better option, as we have several servers and at least one of the
> databases residing on them results in an almost 2GB backup file (with
> most of the remainder smaller than that). I'm also unsure of exactly
> how to go about this.
> I believe SQL Server has been installed by default under the local
> system account, so I'm wondering if this will limit my options with
> regard to authentication across servers.
> I've read about the BACKUP command, "log shipping" and other options,
> but am not sure if any match our requirements.
> If anyone could shine a light down the right path for me, my blood
> pressure would be grateful. ;)
>
Create a share on your central server, configure SQL to run as a domain
user, grant that domain user permissions to the share, then use the
BACKUP command to backup directly to the UNC for that share. We move a
dozen backups around on our network each night, some of which are
approaching 200GB, there is no noticeable difference between backing up
locally and then copying vs. backing up over the network.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi Tracy,
Thanks for your quick response. That sounds like the way to go. One
potential problem is that this is a hosted situation--not in-house--and
I think there may be no domain, but rather isolated machines (I'm not a
network engineer by trade so I'm a bit ignorant here).
Tracy McKibben wrote:
> Create a share on your central server, configure SQL to run as a domain
> user, grant that domain user permissions to the share, then use the
> BACKUP command to backup directly to the UNC for that share. We move a
> dozen backups around on our network each night, some of which are
> approaching 200GB, there is no noticeable difference between backing up
> locally and then copying vs. backing up over the network.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Performance wise, backing up straight to a remote file share is often faster
than backing up locally and then copying the backup file to the same file
share. But there are other factors to consider. For instance, with backing up
straight to the remote file share, if it fails towards to the end due to some
network issue, the retry may take longer than just retrying the copy
operation.
Linchi
"vast55555@.yahoo.com" wrote:

> Hello,
> I've spent the afternoon reading old newsgroup posts and BOL but am
> still a bit confused about the best way to approach a new backup
> scheme.
> My organization would like to establish a single repository for backups
> from our database servers, by either backing up databases on each
> server and then moving them to the new, central server in some
> automated fashion, or by simply pointing to the remote server as the
> backup device.
> then copying the resulting files to a remote destination may be the
> better option, as we have several servers and at least one of the
> databases residing on them results in an almost 2GB backup file (with
> most of the remainder smaller than that). I'm also unsure of exactly
> how to go about this.
> I believe SQL Server has been installed by default under the local
> system account, so I'm wondering if this will limit my options with
> regard to authentication across servers.
> I've read about the BACKUP command, "log shipping" and other options,
> but am not sure if any match our requirements.
> If anyone could shine a light down the right path for me, my blood
> pressure would be grateful. ;)
>

Backup/copy of sql server 2000 DB

Hi people!!
I was wondering how u can copy a sql server database (to make a backup) on to either a CD or floppy(dunno if theres enough disk space for that)?
ThanksHi,
you can make a backup of the database to disk and copy that to a cd
Command : backup database name of database to disk='c:\backup.bak'
or you can copy the fysical database file's to cd
then you have to copy the MDF and LDF files to cd

Lambik

backup/copy just a table

How can I perform a backup of a single table so that I
can then restore the table to another server with a new
table name?Unfortunatly there is no automatic way of doing this.
Instead I would use the export / import data from
Enterprice Manager.
Sumply right click your database, select all taskes and
there is an option for Export / Import data, from here its
fairly self explanitory.
Peter
"The best argument against democracy is a five-minute
conversation with the average voter."
Winston Churchill
>--Original Message--
>How can I perform a backup of a single table so that I
>can then restore the table to another server with a new
>table name?
>.
>|||Hi,
Backup a single table is not avalable directly from SQL 7, but it was there
in SQL 6.5. But you can perform a filegroup backup.
What you could do is you can put the tables which need frequent backup into
a seperate file group. After that you
can very well restore the file group backup.
Thanks
Hari
MCDBA
"Gio" wrote:
> How can I perform a backup of a single table so that I
> can then restore the table to another server with a new
> table name?
>|||Just be aware that the backup cannot be used alone, it has to be restored into the database from
where the backup was performed.
Also, the backup cannot be used to "go back in time" for that part of the database. All transaction
log backups taken since need to be applied until the database is usable.
Typically, when I see a request for table backup, filegroup backup is *not* the answer... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:9FC2795D-79A0-4339-9D80-5BE68C80D675@.microsoft.com...
> Hi,
> Backup a single table is not avalable directly from SQL 7, but it was there
> in SQL 6.5. But you can perform a filegroup backup.
> What you could do is you can put the tables which need frequent backup into
> a seperate file group. After that you
> can very well restore the file group backup.
> Thanks
> Hari
> MCDBA
>
> "Gio" wrote:
> > How can I perform a backup of a single table so that I
> > can then restore the table to another server with a new
> > table name?
> >|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u9TnxigoEHA.1088@.TK2MSFTNGP09.phx.gbl...
> Just be aware that the backup cannot be used alone, it has to be restored
into the database from
> where the backup was performed.
> Also, the backup cannot be used to "go back in time" for that part of the
database. All transaction
> log backups taken since need to be applied until the database is usable.
> Typically, when I see a request for table backup, filegroup backup is
*not* the answer... :-)
Funny enough, I was just looking at this issue today and came to the same
conclusion. ;-)
While it has its place, for waht I need, it's just not going to work. Which
is unfortunate.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/|||Greg,
*Maybe* the PARTIAL option of the RESTORE command can be useful for you. You restore into a new
database, but using PARTICAL, you don't have to restore the whole database, only PRIMARY and the one
containing the data you want. Then just copy that data over to your production database...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:cE45d.83103$Kt5.23453@.twister.nyroc.rr.com...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u9TnxigoEHA.1088@.TK2MSFTNGP09.phx.gbl...
>> Just be aware that the backup cannot be used alone, it has to be restored
> into the database from
>> where the backup was performed.
>> Also, the backup cannot be used to "go back in time" for that part of the
> database. All transaction
>> log backups taken since need to be applied until the database is usable.
>> Typically, when I see a request for table backup, filegroup backup is
> *not* the answer... :-)
> Funny enough, I was just looking at this issue today and came to the same
> conclusion. ;-)
> While it has its place, for waht I need, it's just not going to work. Which
> is unfortunate.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23RSexuuoEHA.1712@.tk2msftngp13.phx.gbl...
> Greg,
> *Maybe* the PARTIAL option of the RESTORE command can be useful for you.
You restore into a new
> database, but using PARTICAL, you don't have to restore the whole
database, only PRIMARY and the one
> containing the data you want. Then just copy that data over to your
production database...
As you said "maybe". :-)
There are other areas I may end up using filegroup backups, but this won't
be one of them.
But thanks for remindnig me of the PARTIAL command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>

backup/copy just a table

How can I perform a backup of a single table so that I
can then restore the table to another server with a new
table name?
Hi,
Backup a single table is not avalable directly from SQL 7, but it was there
in SQL 6.5. But you can perform a filegroup backup.
What you could do is you can put the tables which need frequent backup into
a seperate file group. After that you
can very well restore the file group backup.
Thanks
Hari
MCDBA
"Gio" wrote:

> How can I perform a backup of a single table so that I
> can then restore the table to another server with a new
> table name?
>
|||Just be aware that the backup cannot be used alone, it has to be restored into the database from
where the backup was performed.
Also, the backup cannot be used to "go back in time" for that part of the database. All transaction
log backups taken since need to be applied until the database is usable.
Typically, when I see a request for table backup, filegroup backup is *not* the answer... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:9FC2795D-79A0-4339-9D80-5BE68C80D675@.microsoft.com...[vbcol=seagreen]
> Hi,
> Backup a single table is not avalable directly from SQL 7, but it was there
> in SQL 6.5. But you can perform a filegroup backup.
> What you could do is you can put the tables which need frequent backup into
> a seperate file group. After that you
> can very well restore the file group backup.
> Thanks
> Hari
> MCDBA
>
> "Gio" wrote:
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u9TnxigoEHA.1088@.TK2MSFTNGP09.phx.gbl...
> Just be aware that the backup cannot be used alone, it has to be restored
into the database from
> where the backup was performed.
> Also, the backup cannot be used to "go back in time" for that part of the
database. All transaction
> log backups taken since need to be applied until the database is usable.
> Typically, when I see a request for table backup, filegroup backup is
*not* the answer... :-)
Funny enough, I was just looking at this issue today and came to the same
conclusion. ;-)
While it has its place, for waht I need, it's just not going to work. Which
is unfortunate.

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
|||Greg,
*Maybe* the PARTIAL option of the RESTORE command can be useful for you. You restore into a new
database, but using PARTICAL, you don't have to restore the whole database, only PRIMARY and the one
containing the data you want. Then just copy that data over to your production database...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:cE45d.83103$Kt5.23453@.twister.nyroc.rr.com...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u9TnxigoEHA.1088@.TK2MSFTNGP09.phx.gbl...
> into the database from
> database. All transaction
> *not* the answer... :-)
> Funny enough, I was just looking at this issue today and came to the same
> conclusion. ;-)
> While it has its place, for waht I need, it's just not going to work. Which
> is unfortunate.
>
>
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23RSexuuoEHA.1712@.tk2msftngp13.phx.gbl...
> Greg,
> *Maybe* the PARTIAL option of the RESTORE command can be useful for you.
You restore into a new
> database, but using PARTICAL, you don't have to restore the whole
database, only PRIMARY and the one
> containing the data you want. Then just copy that data over to your
production database...
As you said "maybe". :-)
There are other areas I may end up using filegroup backups, but this won't
be one of them.
But thanks for remindnig me of the PARTIAL command.

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>