How to setup Microsoft SQL Server replication —09 June 2009

Boy, is this complicated? Yeah, but hey, replication is a serious business…

http://msdn.microsoft.com/en-us/library/ms151247(SQL.90).aspx

Anyway, I just managed to set it up, and the one thing that wasted most of my time was broken stored procedures. The lesson here is, before publishing a stored procedure for replication, recreate it at the source first! (Right click, Modify, F5)

Actually replication might be overkill. Instead of that, daily full restore from daily full dumps might be a viable alternative.

Dump database T-SQL hint:

BACKUP DATABASE [SameName] TO  DISK = N'D:the long long pathSameName_backup_200906091739.bak' WITH NOFORMAT, NOINIT, NAME = N'SameName_backup_20090609173911', SKIP, REWIND, NOUNLOAD,  STATS = 10 GO

Restore database T-SQL hint:

RESTORE DATABASE [SameName_Restore] FROM  DISK = N'D:the long long path200906091105.bak' WITH  FILE = 1, MOVE N'Dev' TO N'D:the long long pathSameName_Restore.mdf', MOVE N'Dev_log' TO N'D:the long long pathSameName_Restore_1.ldf', NOUNLOAD,  STATS = 10 GO