Re: DTS package to copy database
- From: "Allan Mitchell" <allan@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 6 Apr 2005 06:29:24 +0100
And another thing.
Why not BACKUP and RESTORE and then apply scripts to change any owners,
remove users etc etc
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"Uday Deo" <UdayDeo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:93806E9C-755C-48C1-8F92-76606892D1D6@xxxxxxxxxxxxxxxx
> BACKUP and RESTORE won't solve the issue 'cos I DON'T want to copy the
> users,
> roles and permissions. I just want to copy the data.
>
> One way to go about it is to copy each table but then it requires to
> manage
> the dependency as I can't copy the parent table before child table etc.
>
> If someone knows that u can just overwrite the data it would be great.
>
> What I can think of is
>
> 1. Create a SQL Script to remove the constraints
> 2. Create a SQL Script to add the constraints
> 3. Run the script for removing constraints on dest. DB
> 4. Copy the data over to each table of dest. DB from Source DB
> 5. Run the script for adding the constraints on dest. DB.
>
> Step 1 and 2 can be once in a while task but I will have to put Step 3, 4
> and 5 under a package and ensure that one runs after successful completion
> of
> another.
>
> Pl. let me know if this approach is useful.
>
> Thanks,
> Uday
> 3.
>
> "Allan Mitchell" wrote:
>
>> By far the best way is to use BACKUP and RESTORE. In my expereince and a
>> lot of others this is the cleanest way.
>>
>> --
>>
>>
>>
>> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
>> www.SQLDTS.com - The site for all your DTS needs.
>> www.SQLIS.com - You thought DTS was good. here we show you the new
>> stuff.
>> www.konesans.com - Consultancy from the people who know
>>
>>
>> "Uday Deo" <UdayDeo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:953D1B44-506E-49FB-9799-9FA947BB131F@xxxxxxxxxxxxxxxx
>> > Hi everyone,
>> >
>> > I am copying a database from one machine to another.
>> > The DB permissions are set for local users on each machine (including
>> > IUSER_WEBSERVER1 and IUSER_WEBSERVER2 etc).
>> >
>> > While copying the database I am using copy SQL Server Objects Task.
>> > Under Copy Tab,
>> > I am selecting create dest. objects (including drop destination objects
>> > first, include all dependent objects, Include extended properties),
>> > copy
>> > data
>> > (replace existing data) Use collation and Copy all objects.
>> >
>> > Under default options I am not copying DB users and roles, sql server
>> > logins, object level permissions since the users are diffenent on both
>> > machines.
>> >
>> > The basic aim is just transfer the data from one DB to another. I am
>> > having
>> > hard time to manage the permissions.
>> >
>> > Any thoughts about doing it efficiently without mapping permissions for
>> > the
>> > objects i.e. keeping the permissions as is and just clean the data and
>> > copy
>> > new / recent data from prod to staging.
>> >
>> > Uday
>>
>>
>>
.
- References:
- DTS package to copy database
- From: Uday Deo
- Re: DTS package to copy database
- From: Allan Mitchell
- Re: DTS package to copy database
- From: Uday Deo
- DTS package to copy database
- Prev by Date: Re: Appending to a text file in Sql DTS
- Next by Date: Re: How ActiveX Script Task return customized error message?
- Previous by thread: Re: DTS package to copy database
- Next by thread: no job history on scheduled job
- Index(es):
Relevant Pages
|
