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
LVL 17
MIKESoftware Solutions ConsultantAsked:
Who is Participating?
 
YveauConnect With a Mentor Commented:
:-)

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
 
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
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
 
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:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.