SQL Server 2016 - Two issues when restoring database in standby mode on a test server

Ramdas Thatte 1 Reputation point
2021-04-13T13:55:16.397+00:00

Looking for some help regarding following two issues -

Issue 1. Source database has replication setup
When it is restored on a test server in standby mode sp_helpreplicationdb shows it as replicated but there is no publication or anything.
sp_removedbreplication does not work because database is in read-only mode
can not update sysdatabases to set Category to zero
Can not drop database unless I take it offline
When restored without standby mode it does not show it as replicated and can drop it without any issues.

Issue 2. When it is restored with move
database files correctly get moved to proper location but sys.database_files.physical_name has the original path.

Thanks in advance

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,642 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.8K Reputation points MVP
    2021-04-13T21:55:41.197+00:00

    I would assume that this is because the database is restored from a full backup + applying of transaction logs. This means that the only changes that can be made to the database are those that come with the transaction logs.

    Thus, things like removing the traces of replication or updating sys.database_files cannot happen, because in that case the database would go out of sync with the source. So these are operations that the RESTORE command performs in the recovery phase.

    0 comments
  2. CathyJi-MSFT 21,081 Reputation points Microsoft Vendor
    2021-04-14T09:57:35.39+00:00

    Hi @Ramdas Thatte ,

    > restoring database in standby mode

    The standby file allows a database to be brought up for read-only access between transaction log restores and can be used with either warm standby server situations or special recovery situations in which it is useful to inspect the database between log restores.

    > When it is restored with move database files correctly get moved to proper location but sys.database_files.physical_name has the original path.

    MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name'. Specifies that the data or log file whose logical name is specified by logical_file_name_in_backup should be moved by restoring it to the location specified by operating_system_file_name.

    Refer to MS document RESTORE Statements - Arguments (Transact-SQL) and the blog StandBy option while restoring database to get more information.

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.

    0 comments