DTS causes TMPDB to expand on host Server

The Copy SQL Server Objects Task doesn't have this
functionality, as far as I know, so unless you've
programmed it yourself (select * into xxx from
server.database.owner.view) I don't see how this is
possible. By default, only the TSQL code to create the
views will be transferred.
Is this DTS work being done so you can do a nosync
initialization? If so, and you are taking the whole
database, you might want to look at backing up and
restoring the database, which would be much quicker.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Explain what you mean by a NOSYNC initialization, I am still learning. I had
thought about the backup and then restore, but was unsure if it would be
faster. I see by your reply, and by the time it has taken me, that indeed it
would be much faster.
I sincerely appreciate your help. I have not physically looked at the
production server, or how it had been set up, thus the comment about server
space.
Thank you again,
LS
"Paul Ibison" wrote:

> The Copy SQL Server Objects Task doesn't have this
> functionality, as far as I know, so unless you've
> programmed it yourself (select * into xxx from
> server.database.owner.view) I don't see how this is
> possible. By default, only the TSQL code to create the
> views will be transferred.
> Is this DTS work being done so you can do a nosync
> initialization? If so, and you are taking the whole
> database, you might want to look at backing up and
> restoring the database, which would be much quicker.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||It might be useful what is the requirement for
your 'clone'. The reason I ask is that replication might
not be the correct solution for your needs. On
www.ReplicationAnswers.Com in the articles section I have
put an article which details the differences between
replication and log shipping for the purposes of
maintaining a standby server. My suspicion is that log
shipping may be more relevant for you. If not, then I'll
help you set up replication once you've posted back.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I am setting up a test SQL server for our programmers to use, as opposed to
the production server. They do not need all of the databases on the
production server on the test server, but there does need to be some type of
replication of the databases used. I hope this explains what I am attempting
to do?
Many Thanks,
Larry S
"Paul Ibison" wrote:

> It might be useful what is the requirement for
> your 'clone'. The reason I ask is that replication might
> not be the correct solution for your needs. On
> www.ReplicationAnswers.Com in the articles section I have
> put an article which details the differences between
> replication and log shipping for the purposes of
> maintaining a standby server. My suspicion is that log
> shipping may be more relevant for you. If not, then I'll
> help you set up replication once you've posted back.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||LS,
will they be editing the data, or just doing selects?
What latency can you accept between the production server
and the backup (developer) box? This will help narrow
down the options.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
A little of both, the idea is to have a enviroment that the programmers can
do whatever they want, and if the development database is so corrupt, that it
can be replaced the next day from the production server. That is what would
be ideal. And what my supervisor would like to have in place.
I sincerely appreciate all your help,
Regards,
Larry S
"Paul Ibison" wrote:

> LS,
> will they be editing the data, or just doing selects?
> What latency can you accept between the production server
> and the backup (developer) box? This will help narrow
> down the options.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||In that case I'd use a different strategy. I'd copy over
the production database backup to the developer's server
and restore it, on a nightly schedule. If you used log
shipping, the database would have to be treated as read
only, and essentially the same for transactional
replication, where developer's changes could 'break' it.
So, I'd ship the database. If it is large, you might want
to zip it up befiore transferring across the network,
otherwise a job to transfer and a job on the standby
server to restore should be enough. If you only want a
subset of the database however, then you might want to
take a look at snapshot replication.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Please clarify: "subset" of the databases? I hate to sound ignorant, but what
you are saying essentially is to do a backup and restore daily of the used
databases,correct?
I appreciate your time and patience with me.
Regards,
LS
"Paul Ibison" wrote:

> In that case I'd use a different strategy. I'd copy over
> the production database backup to the developer's server
> and restore it, on a nightly schedule. If you used log
> shipping, the database would have to be treated as read
> only, and essentially the same for transactional
> replication, where developer's changes could 'break' it.
> So, I'd ship the database. If it is large, you might want
> to zip it up befiore transferring across the network,
> otherwise a job to transfer and a job on the standby
> server to restore should be enough. If you only want a
> subset of the database however, then you might want to
> take a look at snapshot replication.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Yes - backup and restore would seem to fit your needs.
However the developers might only need to work against eg
10 tables and there are 100 other ones in the production
database. In that case, snapshot replication would
probably be more useful, assuming you can cope with the
necessary table locks when the snapshot is generated.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I think you are on target, as far as having to use table locks to backup
just the tables used should not be a problem. There will probably not be
enough of an area of concern to limit the back up / restore functionality.
I sincerely appreciate your help.
Regards,
Larry S
"Paul Ibison" wrote:

> Yes - backup and restore would seem to fit your needs.
> However the developers might only need to work against eg
> 10 tables and there are 100 other ones in the production
> database. In that case, snapshot replication would
> probably be more useful, assuming you can cope with the
> necessary table locks when the snapshot is generated.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>