Re: DTS package to copy database
- From: "Allan Mitchell" <allan@xxxxxxxxxxxxxxxxxx>
- Date: Tue, 5 Apr 2005 21:03:59 +0100
Will the structure change?
If not then you may want to look at
1. Create a script that will drop DRI on the destination
2. Create a DTS package to pump all data from Source --> Destination
3. Apply DRI scripts
You can then permission each DB as you wish.
If the structure changes then you will need to apply those change using scripts.
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"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: no job history on scheduled job
- Next by Date: Re: Importing large file speeds up over time
- Previous by thread: Re: DTS package to copy database
- Next by thread: Re: DTS package to copy database
- Index(es):
Relevant Pages
|
