Hi,

We have installed SQL Server R2 Express Edition on our Windows Dedicated Server, we have another server with same configuration and SQL same version.

Our primary server SQL updated on daily basis we need to update our backup server SQL Database on daily basis only updated records that has been updated in Primary Server SQL. Is there any simple solution for it.


Thanks

Tahir Atique

First, you'll be better served if you move this to the programming forum as this is really a programming question. And second, there is not going to be an easy do this. The various replication methods that could do this are not a part of SQL Server Express (you have to pay them).

Perhaps somebody will be able to recommend a third-party utility, but I believe you will need to create a program to do this. How complex this program will need to be will depend on structure of your database.

I might be misunderstanding your question. The topic is "backup" but your description almost sounds like replication? I'd have to check, but I think replication is included with SQL 2008 R2 Express.

If you just want backup, then an excellent application for SQL Express: http://sqlbackupandftp.com/

Free for one database, $89 unlimited. Considering you're paying nothing for monthly SQL licensing, it's a steal for a one time fee. ;)

You can certainly do this for free, but it's more involved. Google "SQL 2008 express backup script" and you'll find plenty of info. IMHO, buy the app and get a ton more functionality including compression and FTP'ing the backups to another location.

Wayne

I know about the software SQLBackupandFTP but my requirement is that i need replicate updated data from one SQL Express to other SQL Express hosted in remote location.

I know about the software SQLBackupandFTP but my requirement is that i need replicate updated data from one SQL Express to other SQL Express hosted in remote location.

Ok, then you need to use SQL's replication functionality. I took a quick look and I don't believe you can replicate between two Express versions. It looks like you'd need one version such as Workgroup Edition and then could replicate that to a 2008 R2 Express version acting as a subscriber. You'd need to verify that to make sure.

http://www.sql-server-performance.com/articles/dba/sql_server_2008_r2_p1.aspx

I'm not a DBA, but I don't think you have much choice otherwise. It would be a complex sequence of backing up the data, transferring it to the backup server then restoring it. Since Express doesn't have any agent functionality it'd have to be done all through external code (probably SQL CLI). That process is still 'point in time' and not per transaction, so it's really not much different than using sqlbackupandftp once every X minutes and doing a manual restore if needed.

You could use a combination of the two w/ sqlbackupandftp handling the backup (possibly data transfer too), then have a SQL CLI script running as a Windows scheduled task to restore the DB.

osql.exe -S MyServer -E -Q"RESTORE DATABASE MyDB FROM disk = '\\MyServer
\my Backups\the backup'"

Taken from: http://bytes.com/topic/sql-server/answers/143923-restore-command-line

Google "SQL CLI restore database"

What does the second server do? If it's just a developer server, just do a daily back up and restore.

SQL Server Express only supports subscriber for replication.

There is log shipping with express edition. If you give us more details as what server 2 does, we might be able to help you.

I meant to say "There is NO log shipping with express.