Able to make a copy of a db?

[Guest]

I am trying to fix a mess of 4 databases. All 4 of them are query driven and
set up with the tables in SQL. Most are action queries and linked in some
way. Is there a way I can make a copy of these databases with the data so I
can run some test without messing with the live database? I ust want copies
that I can test on my desktop. Any help is appreciated.

 

[Guest]

Ideally get everyone off of the databases. Then just use Windows Explorer to
copy them to your computer. Before messing with the data, check each for
linked tables. If you find any, relink to those copies of the databases on
your computer.

There is also a very slight chance that someone wrote fancy code that does
things like running queries in the other databases. You might want to check
modules for any such code and point it to the proper copies of the database.

 

[Guest]

You may also start with a blank database and import all the forms, querys,
tables etc. and save it as something else. I do this from time to time when
I'm scared of messing something up on the "real" database.

 

[Guest]

Saving it as another database and importing all the tables etc. Would that
interfeer with the live records or would it be a strigh copy? Most of the
tables are either linked or set to SQL backend.

 

[Guest]

This would only make it a copy. You should be able to rename the database to
something different so you wont have to worry about interfering with the live
database. If you want to you can burn a copy of the db or e-mail it to
yourself
and open it on a computer that is not linked to the real database.

 

[Guest]

OK I just started with a blank db and named it something differnet then
inported all the queries tabales etc. I dont need to rename those do I? It
would still seem that I would be messing with the live data even though I
made a new db. This will not mess with my live data correct? Thanks again

 

[Guest]

If you imported the tables instead of linking, then you should not mess with
any data. Make sure that your queries are referancing the correct tables
(the ones you just copied). Also, if there are any pass through queries, you
will need to change these so they are not connected to the production tables.

 

[Norman Yuan]

Things may not be as simple as importing all tables to your *mdb file and
test, because your back end is SQL Server. It all depends on how your *.mdb
app (frontend) is designed.

Is the table are all on SQL Server? do you have local table? Does the app
also has linked table from other source than the SQL Server?
One important difference of using SQL Server backend from *.mdb back end is
that your app could use the server end power (UDF, SPs..) on the SQL Server.
If your app uses a lot server-side code, how could you test it your app by
importing data table to *.mdb file? On the other hand, if your app does not
use SQL Server's server-side power at all, it may well indicate that your
app is poorly designed.

So, idealy, you need to set up the similar SQL Server developing/testing
environment to test your app, if SQL Server is involved (in your case, yes).

Simply get SQL Server developer edition or MSDE/Sql Server Express set up
and create/attach the SQL Server database to it, and than do yuor test.

 

[Guest]

Yes very poorly designed. I was hired to fix this mess! It seems every table
is on SQL side and or the tables are linked in some ways to a database on the
SQL side. I dont see how to get the tables into my test database without
messing with the original data.

 

[Norman Yuan]

As I said, you need to set up a SQL Server/MSDE/SQL Server Express on your
develop machine, and then do the test. You can back up the production data
from the production SQL Server and restore it to your develop machine. This
way, you have real data to test without messing the production data. It is
fairly simple and easy thing to get a set data from production SQL Server
and restore/attach to develop SQL Server, if you know how to set up SQL
Server/MSDE. SQL Server Developer Edition only cost about $50, and MSDE is
free (I suppose you do know that). It is simply not imagineable to connect
your app to production SQL Server for testing.

 

[Albert D.Kallal]

Yes very poorly designed. I was hired to fix this mess! It seems every
table
is on SQL side and or the tables are linked in some ways to a database on
the
SQL side. I dont see how to get the tables into my test database without
messing with the original data.

When you say sql side...do you mean sql server? Most of the responders by
this point have concluded this..but to me..it still is not clear.

Often, a ms-access will be split into two parts

FE (front end) part - this is a application parts

BE (back end) part - this is the data part.

Now, is your data part sql server...or just a plain Jane mdb file?

If it is just a plane Jane file..then you can make a copy of it.

If it is actually sql server, then you will need to use the sql server tools
(assuming you have experience with sql server). Usually the tools of choice
is the Enterprise manager. You would use those tools to make a backup set of
your data..and then take that data to your pc, and THEN setup and install
sql server...

So, it just not clear if you are talking about sql server here, or actually
a simply linked mdb file. (there is a grand canyon of difference between
these two types of setups).

 

[Guest]

Well they gave me 3 weeks to fix it so do you think doing it live would be ok
if I was careful with the data? Pressed for time and lots of action queries!

 

[Norman Yuan]

Never ever do your fix (big or small) on live data without repeated tests on
test/develop environment. Install SQL Server/MSDE on your develop machine
would only take you 1 hour and off you go. If yuor knowledge on SQL Server
is somewhat limited (no offence, please), you really need someone with good
SQL Server skill to help, or you are risking your job potential. I really
suggest you only take this task if you know SQL Server well, although it
seems an Access front-end app fix. As I said in previous reply, if the
Access app does not use SQL Server's server-side power, it may well indicate
a very poor design. If you are to fix, you must consider what SQL Server
offers.

In any case, SQL Server test environment is a MUST if you do not want to
destroy the live data on the first try.

Here is what you need to do:

1 Get live data (the database on live SQL Server: a. get the lated full
back; or b. if access to the SQL Server database could be stopped for a few
minutes, detach the database and make a copy to the *.mdf and *.ldf files of
the database, then attach the database back to the SQL Server. Now you have
either a full back file of the database(s) or copies of *.mdf/*ldf files of
the databases, containning all data and server objected (tables, SPs, Views,
UFDs, ...).

2. Install a version of SQL Server. If the database file (*.mdf) is bigger
than 2G, then you need full version of SQL Server (get Developer Version for
$50). Otherwise, you can install MSDE (free for downloading from MS, and you
can also find it on Office 200x Pro version CD).

3. Configure SQL Server/MSDE properly (basically, creating a few logins is
the only thing to do)
4. Restore(if you got full backup) or attach (if you got dettached copy) the
database file to the installed SQL Server/MSDE.
5. Re-link your Access App to the database and you are ready to really mess
up the data as messy as you want.

All these steps would take 1 or 2 hours (unless you have huge database
(hundreds of G) that takes long time to copy).

 

[Guest]

OK thank you but one thing. Do I still need a copy of SQL if I disconnect the
links and then reconnect them when I am finished? I may be able to disconnect
them for about 10 minutes (small database) then reconnect. If that is the
case I would go that way. So I would disconnect then download then reconnect?
Thank you very mcuh for this info!

 

[Norman Yuan]

If you want to run the Access app (or other kind of apps that use data from
SQL Server database) on your develop computer before deploying to production
environment, you need a test SQL Server/NSDE available, either on your
computer or on a computer you can acess through network. You can even make
an identical database (name it differentlt from the libe database, of
course) on the production SQL Server and do your test there (if the your
test app/code does not hurt the SQL Server process too much due to code
error..., hopefully). Once you have your test database/SQL Server available,
you can use techniques, such as attach/detach, back/restore, or
improt/export to transfer data/server objects btween SQL Server.

The bottom line is to have good knowledge of SQL Server, even your main
focus is on the Access front end side.