DATABASE Replication using SQL Server Mgmt Studio - **** EXPRESS 2005 version ****

take a backup and restore it

Here's what I want..we have a local REPORTING DB setup by a 3rd party vendor....that is a replication of our MAIN LIVE DB that is offsite in another state.

I want to REPLICATE or copy our local REPORTNIG DB....onto my personal development workstation for query and analysis purposes ONLY. I'd like for it to be scheduled and automated..so that I don't manually have to go and do it...

Is this advisable / possible...?

MikeV
 

Here is a quote from the BOL regarding SQL Server Express SSE

You cannot configure SSE as a Publisher or Distributor for any type of replication. However, SSE can be a Subscriber, and merge replication allows changes to be replicated in both directions between a Publisher and Subscriber

Looks like you need to change the local reporting DB to be a publisher/subscriber and then you can take the changes from there.

So you mainly want an overnight copy of the reporting db, which is a replicated from the big bad live corporate db ?
Create a job that will:
1. copy the daily full backup of the reporting db to your local machine (using a shell command or xp_cmdshell)
2. restore the full backup to your local machine, overwriting the current local reporting database.

I think that is the quickest and safest way to go. That way, you won't interrupt any replication scenario's etc. Only thing is that you have to do a daily full backup of the replicated reporting db ... ?!

Hope this helps ...

Are there any WIZARDS in the EXPRESS EDITION that do this..or will I have to create the entire JOB by writing the SQL CODE myself...???

Thanks
MikeV

FYI....the DB that I want to copy...is a COPY of the LIVE DB......NOT the LIVE DB itself.

Our LIFE DB is in Maryland....we are in TEXAS and have our reporting DB tables "REFRESHED" every 3 hours.

I want to make a copy of the COPY DB and place it onto my WORKSTATION....

The most important Q is: is there a backup at hand from the 'copy db' ?
If not, the whole copy thing is getting problematic, because then you have to make one as well. And that would mean disrupting the current situation. I think that is not what you would want to do ... If the daily backup is made op the 'copy db' then the above scenario should work fine. Copying an existing backup won't do any harm ...

Hope this helps ...

Glad I could be of any help and thanks for the grade !