Copy All tables

Hey I'm trying to simply copy all tables from one database to another on a
nightly basis.
If i select the database, choose export, and go through all the steps,
choosing the starting database, the destination database, the values to back
up and scheduling it, it all seems fine. But the job always fails. If i
select only a couple of tables, the job runs fine? anyone have any ideas
why this would be? Or advice on another way to backup the tables nightly?
Thanks
James,
I'm not sure if you're using replication or DTS for this but either way it
sounds like a locking issue. You may need to prevent users accessing the
tables when you do the copy eg setting the database to single user mode.
Alternatively, you could use transactional replication to synchronize the
data. The initial snapshot can be taken without requiring a table lock -
allowing concurrent access. This would perhaps be preferable as you won't be
duplicating data transfer each day.
Regards,
Paul Ibison
|||Are you deleteing all the rows in your destination table or appending? It sounds like you aren't. Go back in your package and delete the existing rows before appending.
Looking for a book on SQL Server replication
http://www.nwsu.com/0974973602.html
|||How do i lock the database?
This job is running at 3am, so there shouldn't be any users working, and the
backup seems to only run for about 5 minutes before failing?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u6twIeqNEHA.1456@.TK2MSFTNGP09.phx.gbl...
> James,
> I'm not sure if you're using replication or DTS for this but either way it
> sounds like a locking issue. You may need to prevent users accessing the
> tables when you do the copy eg setting the database to single user mode.
> Alternatively, you could use transactional replication to synchronize the
> data. The initial snapshot can be taken without requiring a table lock -
> allowing concurrent access. This would perhaps be preferable as you won't
be
> duplicating data transfer each day.
> Regards,
> Paul Ibison
>
|||James,
you can't lock a database, apart from making it single-user. I need to ask
some questions: Is your job runnung a dts package or replication or sps? Is
the structure of the tables changing each day or constant? How much of the
data is changing each day? How much data is there in the tables? What error
messages are returned? I need a bit more info before committing myself to
definite advice, although I suspect log-shipping or transactional
replication are what you need.
Regards,
Paul Ibison
|||Heres the error message:
Executed as user: CDC-EDB1\SYSTEM. ...Start: Copy SQL Server Objects
DTSRun OnProgress: Copy SQL Server Objects; Scripting objects for Transfer;
PercentComplete = 0; ProgressCount = 0 DTSRun OnProgress: Copy SQL Server
Objects; Scripting Logins; PercentComplete = 0; ProgressCount = 0 DTSRun
OnProgress: Copy SQL Server Objects; Scripting Users; PercentComplete = 0;
ProgressCount = 0 DTSRun OnProgress: Copy SQL Server Objects; Enumerating
objects for Transfer; PercentComplete = 0; ProgressCount = 0 DTSRun
OnProgress: Copy SQL Server Objects; Enumerating objects for Transfer : 0
Percent Completed; PercentComplete = 0; ProgressCount = 0 DTSRun
OnProgress: Copy SQL Server Objects; 0 Percent Completed; PercentComplete =
0; ProgressCount = 0 DTSRun OnProgress: Copy SQL Server Objects;
Enumerating objects for Transfer : 10 Percent Completed; PercentComplete =
10; ProgressCount = 0 DTSRun OnProgress: Copy SQL Server Objects; 12
Percent Completed; Pe... Process Exit Code 1. The step failed.
Its a DTS package thats running. The values in the tables are changing
daily, I want them copied to another server where we run reports on the
data. This server is old and slow, so I don't want it slowing down the live
server.
Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OaKmLUsNEHA.4060@.TK2MSFTNGP10.phx.gbl...
> James,
> you can't lock a database, apart from making it single-user. I need to ask
> some questions: Is your job runnung a dts package or replication or sps?
Is
> the structure of the tables changing each day or constant? How much of the
> data is changing each day? How much data is there in the tables? What
error
> messages are returned? I need a bit more info before committing myself to
> definite advice, although I suspect log-shipping or transactional
> replication are what you need.
> Regards,
> Paul Ibison
>
|||James,
the error message is not helpful and there is no way with the copy objects task of getting more info. It uses nolock , so locking is not the issue, and it is difficult to tell the exact cause of the problem. If you have constraints on the table and are us
ing row by row logging you can use the transform data task, which will give you a lot more info and possibly help you identify the cause of the problem. This will be more of a hit on your system though. For less of a hit, you can use log shipping, and the
n move the tables to a separate standby server and then copy them, or possibly overwrite the standby database completely if this suits your needs. Finally, there is transactional replication with a remote distributor which would also transfer the table da
ta.
Regards,
Paul Ibison