Create development copy of Microsoft Project /SQL Sever database.

[Ken]

I am an Oracle Database Administrator but new to SQL Server. I have been
asked by a client to make changes to an existing sql sever database with
Microsoft Access front end. There is not currently a copy of the production
database to use for development. Could someone give me a quick best practice
approach for setting up a development/test enviornment.

Thanks in advance for your help.

 

[Tim Ferguson]

Could someone give me a quick best practice
approach for setting up a development/test enviornment.

You can go one of two ways:

Creating an ADP takes awy a lot of the work of the project; you can
interact directly with the SQL Server database to edit views and stored
procs etc. On the other hand, you have to use the ADO library which is
limited and nowadays poorly scalable.

You can create an ODBC link to the SQL Server and then use a normal
Access mdb front end using the DAO library, which is faster and more
capable than the other.

You can take the best of both worlds by using an ADP to interact with the
"back end" stuff; whilst actually developing the user interface in an
Access mdb. In the end, it depends on what you are used to and what you
are comfortable with.

Hope that helps


Tim F

 

[Ken]

Thanks.

An adp already exists connecting to a production database. Client wants to
modify forms, reports etc and add some additional funtionality. I had
planned to create copy of production adp and connect to a copy of production
database to avoid developing in production enviornment. Unsure whether to
use backukp, copy or transfer to create develpment database from existing
production database.

 

[Tim Ferguson]

An adp already exists connecting to a production database. Client
wants to modify forms, reports etc and add some additional
funtionality. I had planned to create copy of production adp and
connect to a copy of production database to avoid developing in
production enviornment. Unsure whether to use backukp, copy or
transfer to create develpment database from existing production
database.

Sorry; me being dumb and not reading original question carefully enough.
I am far from a SQL Server expert: I think there is a built in sp to copy
a database whole; or else you can create a back up and restore it to a
new database or a new server. MSDE is free and specifically intended for
that sort of purpose. There are also meant to be ways of generating a DDL
script from existing objects but I have never managed to make it work.

Yes, of course you are right about not developing against a working
production database.

Not sure if I've really helped very much. For more specific answers, you
might be better off trying something like m.p.a.adp.sqlserver

B Wishes

Tim F