Refreshing a development database with production data

Hello,

I am trying to refresh a test database with data from a production database. Both database structures are identical, e.g. constraints, stored procs, PK, etc. I am trying to create a package in SSIS that accomplishes this task and I am having extensive problems. The import export wizard is out of the question because the constaints are not carried over, plus when I try to refresh the data using the import export wizard, it fails on 1 specific table because of a column in that table named "Error code". I think "Error code" is a micrsoft keyword, so it fails on this column. Does anyone know a workaround that I can do to accomplish this simple task, that could be completed in minutes using DTS. I understand that SSIS is not as straight forward as DTS, but this task is something that DBA's do on a regular basis and therefore should not be this difficult.

Any help would be appreciated,

David

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1118044&SiteID=1

Refresh data from production to development

Hi all, I've been assigned a task of refreshing data from the
production env to development env.

what i got is a backup file of a db in the prod env, i now need to
make that into the development env.

I can restore it to the dev env no problem, but the warnings i got are
that the tables owner in prod and dev env need to be different, that
is, owner is A in prod env and owner is B in dev env.

So I need to:
1) restore the db in dev env
2) change table owner from A to B
3) change related triggers
4) change related views

Can anyone suggest me an approach that is most efficient?

Thanks a lot.Hiya Bosco,

I'd create a script that dynamically builds a "sp_changeobjectowner"
statement for all objects that user A owns.. It'll simlify the steps
to this

1. Restore db in dev
2. Create and run permissions script

Syntax is as follows:

EXEC sp_changeobjectowner '<table_name>', 'B'|||Bosco

Here are some aricles that should help you.

http://www.support.microsoft.com/?id=314546 Moving DB's between Servers

http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach

http://support.microsoft.com/?id=221465 Using WITH MOVE in a Restore

http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers

http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore

http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users

http://www.support.microsoft.com/?id=168001 User Logon and/or
Permission Errors After Restoring Dump

http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers

Hope this helps

John

refresh data best way?

Hi There

I have a production server and a training server.On a monthly basis the training server needs to have data refreshed from the production server.This consists of approximately 1000 tables, with a few hundered thousand rows from many of the tables.
I was wondering what would be the best way to accomplish this in integration services.Obviously i do not want to create a task for each of the 1000 tables, and the data needs to be copied as fast as possible (it is a substatial amount of data).

I was thinking of loading a table with all the table names and using a loop to dynamically go throught the tables, but then i dont want to use a sql task to do a "select * from linked server into" as this would be very inefficient.
There are no real transformations needed, just copying many objects from one server to another.

There are other ways but i cant help thinking it is not the best way.
Any ideas on the best practice for this?

ThanxWhat about the transfer objects task? It does exactly what it says on the tin and also exactly what you described above.

-Jamie|||Hi Jamie

Thanx for the assiatance.
Yes i did investigate the copy objects tasks. Sorry i should have mentioned that my concerns are that i do not want to copy all rows from these tables, is there a dynamic sql command one can use for object data extraction in this task ?
Also this list of over 100 tables may change, which would require me to change the package every time, is there a way to dynamically drive this task from perhaps a table holding the objects that need to be copied? Perhaps this is possible in the copy objects task ? But i doubt it.

Thanx again for your help.|||Hi Jamie

I have realized dynamically driving thorugh the table list will not work with a for each loop container using a data flow task , as the source can use dynamic sql but the ole db destination cannot be dynamic therefore i could change the destination with the source dynamically.

I tlooks like the only way is to loop through using a sql task with a linked server.

Any ideas ?

Thanx|||

The other thing that woul worry me with only a partial data transfer would be integrity. If you transfer only 100 rows per table for example, what ensures that any foreign key relationships will not be violated, when you do not get matching data?

I don't think a totally generic and automated solution is feasible, the type of data, static vs transactional will influence how they are treated. If the tables themselves are dynamic, with tables being added and removed, you will need to know what each table is, and therefore how to treat them.

|||Hi Darren Thanx for the assistance.

There are no foreign keys, so that should not be an issue.
I agree i do not think it is feasible for SSIS in this situation, i have opted to go for Snapshot replication, easier to add and articles etc, just thought there may be a cool way to do it it in SSIS.

But thanx for the feedback!

Cheers|||Did you try using the SMO enumerator with the transfer objects task? This seems ideally suited, though I admit I don't fully understand what you're trying to do with your data.sql

Reflecting changes in production Db onto staging DB

I have a database db1 on server1 and server2.The Db On server1 is a production db and the Db on server2 is a staging Db.All the new data will be coming into production Db.And i wanted to update the data and database structures on staging Db from production Db on weekly basis.So how can I reflect the data and datastructures on my staging Db from my production Db.

Thanks.I'd recommend backup and restore. I've never found anything else that completely satisfies me.

-PatP

Redundant DR Site Cluster Restore

Hello,
We are restoring our production SQL cluster to an isolated DR site. The
server and SAN hardware are the same, the domain is the same, all of the IP
addresses are the same because the network is isolated.
Typically, we back our production servers up to tape and restore them on top
of a vanilla OS using NetBackup for our Windows DR restores. This usually
works fine, but we are always required to remove the restored server to the
domain, add it to a workgroup, and re-add the server back to the domain to
get rid of the " The trust
relationship between this workstation and the primary domain failed" error.
I have restored the OS of both nodes of the cluster to servers in the DR
site. The cluster service obviously will not start because of the "trust
error" which can't be resolved until the server is removed from the domain
and re-added.
My question is, if I do this to a node in the server, what will happen to
the cluster service/quorum/etc after I remove it from the domain. I'm going
to readd it immediately, but I'm wondering if it will cause any issues or if
there are any precautions I need to take? Or will the cluster just come up
assuming no other changes have been made?
Just go in and take all of the resource groups offline. Then stop the
cluster service in the service control applet. Once that is done, get the
servers added back to the domain. Once added back in, start the cluster
service, connect to your cluster, and start up all other groups.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"jason" <jason@.discussions.microsoft.com> wrote in message
news:ADAEB407-C959-45A4-941D-A29629DB6533@.microsoft.com...
> Hello,
> We are restoring our production SQL cluster to an isolated DR site. The
> server and SAN hardware are the same, the domain is the same, all of the
> IP
> addresses are the same because the network is isolated.
> Typically, we back our production servers up to tape and restore them on
> top
> of a vanilla OS using NetBackup for our Windows DR restores. This usually
> works fine, but we are always required to remove the restored server to
> the
> domain, add it to a workgroup, and re-add the server back to the domain to
> get rid of the " The trust
> relationship between this workstation and the primary domain failed"
> error.
> I have restored the OS of both nodes of the cluster to servers in the DR
> site. The cluster service obviously will not start because of the "trust
> error" which can't be resolved until the server is removed from the domain
> and re-added.
> My question is, if I do this to a node in the server, what will happen to
> the cluster service/quorum/etc after I remove it from the domain. I'm
> going
> to readd it immediately, but I'm wondering if it will cause any issues or
> if
> there are any precautions I need to take? Or will the cluster just come
> up
> assuming no other changes have been made?

Reduce size allocated to a database

We have allocated 10GB to a production database in SQL Server 2000 and we
find that it only uses around 600MB in size.
Can we change the size allocated from 10GB to say 3GB ? Is there any steps
we have to perform before the change ? Does the benefit of changing the
size is only saving in disk space ?
Thanks
Mark
If you want to reduce a physical size if the database , so run DBCC
SHRINKFILE command
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:OjMhzkj%23FHA.3036@.TK2MSFTNGP09.phx.gbl...
> We have allocated 10GB to a production database in SQL Server 2000 and we
> find that it only uses around 600MB in size.
> Can we change the size allocated from 10GB to say 3GB ? Is there any
> steps we have to perform before the change ? Does the benefit of changing
> the size is only saving in disk space ?
> Thanks
>
|||> Does the benefit of changing the size is only saving in disk space ?
Yes. You pay no penalty for having a large database file with SQL Server. Some additional comments
about shrink: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:OjMhzkj%23FHA.3036@.TK2MSFTNGP09.phx.gbl...
> We have allocated 10GB to a production database in SQL Server 2000 and we find that it only uses
> around 600MB in size.
> Can we change the size allocated from 10GB to say 3GB ? Is there any steps we have to perform
> before the change ? Does the benefit of changing the size is only saving in disk space ?
> Thanks
>
|||Dear Uri,
Do you mean that after changing the Database file size, I have to run DBCC
SHRINKFILE ?
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OAOaG8j%23FHA.2452@.TK2MSFTNGP11.phx.gbl...
> Mark
> If you want to reduce a physical size if the database , so run DBCC
> SHRINKFILE command
>
>
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:OjMhzkj%23FHA.3036@.TK2MSFTNGP09.phx.gbl...
>

Reduce size allocated to a database

We have allocated 10GB to a production database in SQL Server 2000 and we
find that it only uses around 600MB in size.
Can we change the size allocated from 10GB to say 3GB ? Is there any steps
we have to perform before the change ? Does the benefit of changing the
size is only saving in disk space ?
ThanksMark
If you want to reduce a physical size if the database , so run DBCC
SHRINKFILE command
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:OjMhzkj%23FHA.3036@.TK2MSFTNGP09.phx.gbl...
> We have allocated 10GB to a production database in SQL Server 2000 and we
> find that it only uses around 600MB in size.
> Can we change the size allocated from 10GB to say 3GB ? Is there any
> steps we have to perform before the change ? Does the benefit of changing
> the size is only saving in disk space ?
> Thanks
>|||> Does the benefit of changing the size is only saving in disk space ?
Yes. You pay no penalty for having a large database file with SQL Server. Some additional comments
about shrink: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:OjMhzkj%23FHA.3036@.TK2MSFTNGP09.phx.gbl...
> We have allocated 10GB to a production database in SQL Server 2000 and we find that it only uses
> around 600MB in size.
> Can we change the size allocated from 10GB to say 3GB ? Is there any steps we have to perform
> before the change ? Does the benefit of changing the size is only saving in disk space ?
> Thanks
>|||Dear Uri,
Do you mean that after changing the Database file size, I have to run DBCC
SHRINKFILE ?
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OAOaG8j%23FHA.2452@.TK2MSFTNGP11.phx.gbl...
> Mark
> If you want to reduce a physical size if the database , so run DBCC
> SHRINKFILE command
>
>
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:OjMhzkj%23FHA.3036@.TK2MSFTNGP09.phx.gbl...
>> We have allocated 10GB to a production database in SQL Server 2000 and we
>> find that it only uses around 600MB in size.
>> Can we change the size allocated from 10GB to say 3GB ? Is there any
>> steps we have to perform before the change ? Does the benefit of
>> changing the size is only saving in disk space ?
>> Thanks
>

Reduce size allocated to a database

We have allocated 10GB to a production database in SQL Server 2000 and we
find that it only uses around 600MB in size.
Can we change the size allocated from 10GB to say 3GB ? Is there any steps
we have to perform before the change ? Does the benefit of changing the
size is only saving in disk space ?
ThanksMark
If you want to reduce a physical size if the database , so run DBCC
SHRINKFILE command
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:OjMhzkj%23FHA.3036@.TK2MSFTNGP09.phx.gbl...
> We have allocated 10GB to a production database in SQL Server 2000 and we
> find that it only uses around 600MB in size.
> Can we change the size allocated from 10GB to say 3GB ? Is there any
> steps we have to perform before the change ? Does the benefit of changing
> the size is only saving in disk space ?
> Thanks
>|||> Does the benefit of changing the size is only saving in disk space ?
Yes. You pay no penalty for having a large database file with SQL Server. So
me additional comments
about shrink: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:OjMhzkj%23FHA.3036@.TK2MSFTNGP09.phx.gbl...
> We have allocated 10GB to a production database in SQL Server 2000 and we
find that it only uses
> around 600MB in size.
> Can we change the size allocated from 10GB to say 3GB ? Is there any step
s we have to perform
> before the change ? Does the benefit of changing the size is only saving
in disk space ?
> Thanks
>|||Dear Uri,
Do you mean that after changing the Database file size, I have to run DBCC
SHRINKFILE ?
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OAOaG8j%23FHA.2452@.TK2MSFTNGP11.phx.gbl...
> Mark
> If you want to reduce a physical size if the database , so run DBCC
> SHRINKFILE command
>
>
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:OjMhzkj%23FHA.3036@.TK2MSFTNGP09.phx.gbl...
>

Reduce File Size

Everyone,
Here is what I am looking for. I have production databases (around 20GB
each). I need to have copies of these available for my developers. They
don't need the whole 20GB so what I do is delete all the records from the DB
except for, say, one month worth of records. Then I use the shrink command
to reduce the file size to few hundred MB. It works fine except it takes
forever on my lab system to shrink the DB. Is there any faster method to do
this without me having to buy a faster server?
Thank you.
It will probably be faster to backup the database and zip the file. But your developers need then
20GB to restore the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dragon" <baadil_nospam@.hotmail.com> wrote in message news:eOeiceHRGHA.5296@.tk2msftngp13.phx.gbl...
> Everyone,
> Here is what I am looking for. I have production databases (around 20GB each). I need to have
> copies of these available for my developers. They don't need the whole 20GB so what I do is delete
> all the records from the DB except for, say, one month worth of records. Then I use the shrink
> command to reduce the file size to few hundred MB. It works fine except it takes forever on my lab
> system to shrink the DB. Is there any faster method to do this without me having to buy a faster
> server?
> Thank you.
>
|||Tibor,
Thank but some of my developer systems may not be able to handle 20GBs on
their laptops. So I guess I am stuck here. :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTLVw6HRGHA.5908@.TK2MSFTNGP14.phx.gbl...
> It will probably be faster to backup the database and zip the file. But
> your developers need then 20GB to restore the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dragon" <baadil_nospam@.hotmail.com> wrote in message
> news:eOeiceHRGHA.5296@.tk2msftngp13.phx.gbl...
>
|||I see... I guess you could try the TRUNCATEONLY option of the SHRINKFILE command, and pray that the
page with the highest address isn't too high up in the database file...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dragon" <baadil_nospam@.hotmail.com> wrote in message news:uHrFdWIRGHA.1576@.tk2msftngp13.phx.gbl...
> Tibor,
> Thank but some of my developer systems may not be able to handle 20GBs on their laptops. So I
> guess I am stuck here. :-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OTLVw6HRGHA.5908@.TK2MSFTNGP14.phx.gbl...
>

Reduce File Size

Everyone,
Here is what I am looking for. I have production databases (around 20GB
each). I need to have copies of these available for my developers. They
don't need the whole 20GB so what I do is delete all the records from the DB
except for, say, one month worth of records. Then I use the shrink command
to reduce the file size to few hundred MB. It works fine except it takes
forever on my lab system to shrink the DB. Is there any faster method to do
this without me having to buy a faster server?
Thank you.It will probably be faster to backup the database and zip the file. But your
developers need then
20GB to restore the database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dragon" <baadil_nospam@.hotmail.com> wrote in message news:eOeiceHRGHA.5296@.tk2msftngp13.phx
.gbl...
> Everyone,
> Here is what I am looking for. I have production databases (around 20GB ea
ch). I need to have
> copies of these available for my developers. They don't need the whole 20G
B so what I do is delete
> all the records from the DB except for, say, one month worth of records. T
hen I use the shrink
> command to reduce the file size to few hundred MB. It works fine except it
takes forever on my lab
> system to shrink the DB. Is there any faster method to do this without me
having to buy a faster
> server?
> Thank you.
>|||Tibor,
Thank but some of my developer systems may not be able to handle 20GBs on
their laptops. So I guess I am stuck here. :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTLVw6HRGHA.5908@.TK2MSFTNGP14.phx.gbl...
> It will probably be faster to backup the database and zip the file. But
> your developers need then 20GB to restore the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dragon" <baadil_nospam@.hotmail.com> wrote in message
> news:eOeiceHRGHA.5296@.tk2msftngp13.phx.gbl...
>|||I see... I guess you could try the TRUNCATEONLY option of the SHRINKFILE com
mand, and pray that the
page with the highest address isn't too high up in the database file...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dragon" <baadil_nospam@.hotmail.com> wrote in message news:uHrFdWIRGHA.1576@.tk2msftngp13.phx
.gbl...
> Tibor,
> Thank but some of my developer systems may not be able to handle 20GBs on
their laptops. So I
> guess I am stuck here. :-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OTLVw6HRGHA.5908@.TK2MSFTNGP14.phx.gbl...
>

Reduce File Size

Everyone,
Here is what I am looking for. I have production databases (around 20GB
each). I need to have copies of these available for my developers. They
don't need the whole 20GB so what I do is delete all the records from the DB
except for, say, one month worth of records. Then I use the shrink command
to reduce the file size to few hundred MB. It works fine except it takes
forever on my lab system to shrink the DB. Is there any faster method to do
this without me having to buy a faster server?
Thank you.It will probably be faster to backup the database and zip the file. But your developers need then
20GB to restore the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dragon" <baadil_nospam@.hotmail.com> wrote in message news:eOeiceHRGHA.5296@.tk2msftngp13.phx.gbl...
> Everyone,
> Here is what I am looking for. I have production databases (around 20GB each). I need to have
> copies of these available for my developers. They don't need the whole 20GB so what I do is delete
> all the records from the DB except for, say, one month worth of records. Then I use the shrink
> command to reduce the file size to few hundred MB. It works fine except it takes forever on my lab
> system to shrink the DB. Is there any faster method to do this without me having to buy a faster
> server?
> Thank you.
>|||Tibor,
Thank but some of my developer systems may not be able to handle 20GBs on
their laptops. So I guess I am stuck here. :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTLVw6HRGHA.5908@.TK2MSFTNGP14.phx.gbl...
> It will probably be faster to backup the database and zip the file. But
> your developers need then 20GB to restore the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dragon" <baadil_nospam@.hotmail.com> wrote in message
> news:eOeiceHRGHA.5296@.tk2msftngp13.phx.gbl...
>> Everyone,
>> Here is what I am looking for. I have production databases (around 20GB
>> each). I need to have copies of these available for my developers. They
>> don't need the whole 20GB so what I do is delete all the records from the
>> DB except for, say, one month worth of records. Then I use the shrink
>> command to reduce the file size to few hundred MB. It works fine except
>> it takes forever on my lab system to shrink the DB. Is there any faster
>> method to do this without me having to buy a faster server?
>> Thank you.
>|||I see... I guess you could try the TRUNCATEONLY option of the SHRINKFILE command, and pray that the
page with the highest address isn't too high up in the database file...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dragon" <baadil_nospam@.hotmail.com> wrote in message news:uHrFdWIRGHA.1576@.tk2msftngp13.phx.gbl...
> Tibor,
> Thank but some of my developer systems may not be able to handle 20GBs on their laptops. So I
> guess I am stuck here. :-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OTLVw6HRGHA.5908@.TK2MSFTNGP14.phx.gbl...
>> It will probably be faster to backup the database and zip the file. But your developers need then
>> 20GB to restore the database.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Dragon" <baadil_nospam@.hotmail.com> wrote in message
>> news:eOeiceHRGHA.5296@.tk2msftngp13.phx.gbl...
>> Everyone,
>> Here is what I am looking for. I have production databases (around 20GB each). I need to have
>> copies of these available for my developers. They don't need the whole 20GB so what I do is
>> delete all the records from the DB except for, say, one month worth of records. Then I use the
>> shrink command to reduce the file size to few hundred MB. It works fine except it takes forever
>> on my lab system to shrink the DB. Is there any faster method to do this without me having to
>> buy a faster server?
>> Thank you.
>>
>

Re-do replication for new release

We alter the database schema that our production application uses with almost every release. Is there a way to basically remove the replication and re-do it for all tables / views at one time? Then we could just "rebuild" the replication with every release. We have about 200 tables and 200 views that have many dependencies. I'm sure it could be done with scripting and the stored procedures, but I'm new at this and not sure where to start. Any ideas? Thanks!

Yes, you could script out all the replication settings (publication, articles, jobs, subscriptions, etc) using the UI, then remove replication completely from the publisher as well as subscriber and after you do the schema changes to the databases, then use the scripts to setup replication again on the new release.

But however note that there are always differences between releases. There could be slight differences in the way they are scripted out and the way they are handled in the new release. With that in mind, you may have to slightly edit the scripts before running them on the newest version.

Also, I would first test it out on a test systems before trying it on the production servers.

Also note that With SQL Server 2005, schema changes to the tables are replicated to the subscriber. So for example, if you add a new column to one of the replicated tables, that new column will be replicated to the Subscriber when configured appropriately.|||Thank you Sir, for your suggestions. I will give them a try.