sql server 2005 Replication

Hi friends,

We have a sql server 2005 which contains 4 to 5 databases which are updated everyday externally, Currently we are backing up the Database and attaching to a different server and working on it in order to make sure nothing is deleted or changed in the original database.

But This backup and restore process has become hassle so i have looked up couple of options like replcation services but i dont seem to have Replication components installed i tried to install them but i could only see Subcription services not the publisher servcies any ways we use sql server 2005 express edition is replication the best bet or do u suggest any other ways?

If so how do we get the replication components? And if not what are the other ways ?

Thanks in Advance

From serverfault $pageUsers[$entry.posts[0].author].name

• You might want to check out this article at databasejournal.com. Of particular note is this paragraph:

  You should keep in mind that replication functionality is not incorporated by default in the SQL Server 2005 Express Edition installation. The option controlling this behavior is accessible by expanding the Database Services node on the Feature Selection page of the setup wizard and can be modified by assigning "Will be installed on local hard drive" value to its Replication entry. In addition, if you intend to take advantage of the connectivity and Replication Management Objects (RMO), you should apply the same setting to the Connectivity Components subnode of the Clients Components node on the same page of the wizard. In case you missed these steps during initial setup, simply launch SQLEXPR32.EXE (or SQLEXPR.EXE for 64-bit systems) to modify an existing instance (for the background information regarding this process, refer to our earlier article).

  From C-Pound Guru

• Replication would really be like shooting a fly with an elephant gun.

  Why not just restore your database with a different name? For instance, you could have "MyProductionDB" and "MyTestDB". Then script out the backup and restore, and you need only run the script when you want to "refresh" the test instance (note that there is a "Generate Script" button at the top of the backup and restore dialogs).

  Alternatively, do the same scripted backup/restore, but create a test instance if you can't change tha database name. The test instance could even be on the same physical machine - MyComputer\SQLExpress and MyComputer\SQLExpressTest.

  Aaron Alton : RESTORE DATABASE in Books Online: http://msdn.microsoft.com/en-us/library/ms186858.aspx (Were you looking for something more specific?)
  From Aaron Alton

• Replication services on publisher's side are not available with SQL Express. This is a licensing issue. Sql Express allows you only to subscribe to an existing publication.

  Instead of replication, you could decide to set a scheduled backup job. As job management is not available in sql express (and I guess you don't want to pay for the full Sql Server license), you can find third party software that will allow you to manage such tasks. I guess you should google for 'sql express job scheduler' or similar request.

  From $pageUsers[$post.author].name

• Replication may be overkill here, since it's designed more for continuous transfer of data than just a working backup copy. Obviously there are periodic replication methods (snapshot), but I still think it's more than you need for what you're trying to do.

  If both database instances are on the same server (or have visibility to a common network location), then you can just schedule a daily backup on the first and a daily restore 30 minutes later on the second one (or whatever delay is feasible). This way, it happens automatically.

  I just set up a more complicated process, in case you don't have a common network location. A batch file on the source creates a backup through OSQL, then zips it and uses an FTP script to send it across the internet. Another script on our destination sees the file in the FTP receive folder, extracts it, and then restores it using OSQL, sending an email notification when it's done. Definitely more complicated, but also an option if you need the flexibility.

  From rwmnau