Take the 2-minute tour ×

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

share|improve this question

migrated from stackoverflow.com Sep 19 '09 at 5:01

This question came from our site for professional and enthusiast programmers.

4 Answers 4

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).

share|improve this answer
i tried after going through the article but no use i didnt get replication services –  SweetGangster Sep 15 '09 at 1:06

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.

share|improve this answer
ok so now i have backup all databases from one server to some network drive and restored them all from that drive but the problem is how can i restore all the bak files to the local machine i mean script...i used ola halgreens script to backup but i didnt find any good restore script –  SweetGangster Sep 15 '09 at 23:24
RESTORE DATABASE in Books Online: msdn.microsoft.com/en-us/library/ms186858.aspx (Were you looking for something more specific?) –  Aaron Alton Sep 18 '09 at 0:58

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.

share|improve this answer

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.

share|improve this answer

Your Answer

