question

RamdasThatte-2262 avatar image
0 Votes"
RamdasThatte-2262 asked Cathyji-msft commented

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

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-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @RamdasThatte-2262,

We have not received a response from you. Did the replies could help you? If the response helped, do "Accept Answer". If it is not, please let us know. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @RamdasThatte-2262,

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.