• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 149
  • Last Modified:

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

It is possible to Replicate or copy/mirror a database using....the EXPRESS EDITION of SQL Server 2005..???

And if so.....HOW...?

Thanks
MikeV
0
MIKE
Asked:
MIKE
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
take a backup and restore it
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
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
 
0
 
CragCommented:
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
YveauCommented:
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 ...
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
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
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
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....
0
 
YveauCommented:
:-)

First one is easy:
    copy \\server\path\to\file.bak x:\local\path\for\file.bak

Second one becomes:
    restore database LocalReport
    from disk='<x:\local\path\for\file.bak>'
    with move '<replication_data>' to '<x:\local\path\for\repl_data.mdf>'
    , '<replication_log>' to '<x:\local\path\for\repl_log.ldf>'

check for the 'logical_file_name_in_backup' for the move section in the restore statement.

Hope this helps ...
0
 
YveauCommented:
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 ...
0
 
YveauCommented:
Glad I could be of any help and thanks for the grade !
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now