Creating a local copy of a database

Hi all,

I'm having a problem right now. I'm trying to create an offline copy of a database(live) in my local machine. The database is maintained in a different server machine and I want a copy of the particular DB as an offline copy.

The problem is there's DTS feature in SQL Server 2000 but I don't find any such tool in SQL Server 2005 edition. Can anyone please guide me on how to do this process?

Thanks & Regards,

ThunderRock

hi thunder,

Dts is now known as SSIS and it is found on VS2005

for the solution.

1. you can detach the database and copy the datafiles and attach it to the destination servers

2. you can use the database import export wizrd

|||

Hi Joeydj,

Thanks for the reply.

Now I've already done that but I wanted to know if there was some other method by which the complete Db can be imported. Because if we go by that method, we don't get the stored procedures as well as triggers and functions present in the source DB. So I want to know if there is any method by which the SPs, triggers as well as functions can be imported. We can write a query script and do it manually. But is there any features provided in Sql Server 2005 by which we can do it directly?

Another thing I noticed in the new database is that the data which get imported into the local machine(i.e the target DB) doesn't reflect the dependencies of the DB Objects present in the DB. So how do we go about solving this issue also?

Can you please guide on this? Anyone who have done this before and have some idea, please guide.

Thanks,

ThunderRock

|||

ThunderRock,

I just completed setting up database replication scenario that uses an SQL2005 db as a 'gold standard', and replication to a subscriber to an SqlExpress database. The subscriber db can be used offline for inquires in our case. We use the database snapshot to provide inquiry services for the subscriber when there is a network or preferred application downtime.

With replication options setup appropriately, you can have the replicated database include whatever objects you wish. Our case is only using snapshot replication, as that is all we require, but from what I understand other types of replication would work in like manner.

We get our source data from a foreign host system as a huge text file, and we use a custom app to transform it, and bulk copy it into our Sql2005 db. Then, we've setup jobs to perform the replication. On the subscriber end, we installed SqlExpress with Advanced Tools, and set it up as a subscriber to the Sql2005 db. We developed a simple windows form application to use for inquiry on the local database, and deployed the app to the subscriber desktop. It is functioning as intended and allows our users to inquire on the mission critical data during those odd periods of network or preferred application downtime.

Now, there may be some issues causing the issues you mention, there are some limitations on the amount of data, and supported memory configs, but our db has about 500,000 records, which is not huge, and not very many stored procedures at this point. So, we have not run into any showstoppers at this point.