How to setup Microsoft SQL Server replication —09 June 2009
Boy, is this complicated? Yeah, but hey, replication is a serious business…
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