What parameter to use when restoring a backup file of a replicated database, using LiteSpeed?
KEEP_REPLICATION parameter instructs the restore operation to keep the replication settings.You cannot specify this parameter with NORECOVERY.
You may see this SQL Server error message if restoring a backup of a replicated database without the parameter:
Database Northwind was restored, however an error was encountered while replication was being restored/removed. The database has been left offline. See the topic MSSQL_ENG003165 in SQL Server Books Online.
This error is raised if a problem occurs restoring a backup of a replicated database:
If the backup is being restored to the same database and server on which it was taken, the error indicates that replication settings could not be restored properly.
If the backup is being restored to a different database or server, the error indicates that replication settings could not be removed properly (by default, replication settings are removed if the database or server is different).
The error may be the result of a mismatch between the state of the restored database and one or more system databases that contain replication metadata: msdb, master, or the distribution database.
RESOLUTION:
KEEP_REPLICATION is passed using @with=KEEP_REPLICATION
Example:
EXEC master.dbo.xp_restore_database
@database = NORTHWIND
, @filename = C:\MSSQL\backup\NORTHWIND.Full
, @with=KEEP_REPLICATION
, @with=RECOVERY
This feature is available in SQL 2005 only.
