MS SQL Server: How to copy a standby DB (logs shipped) to another instance

Why we need to copy a standby database? The main reasons are the location & time. Normally the online DB will be in a server on datacenter/clouding, while the standby DB (logs shipped) will be in a local server. So if you have a big size DB, you may take time & traffic to transfer the backup of online DB to a local server for doing your needs. However, if you can copy the standby DB, it will be faster and save your traffic. After copying, your standby DB can still receive logs shipped from the online DB.

Below are steps to do this on the standby server.

1. Take offline the standby DB
Run the following command:

USE MASTER
GO
ALTER DATABASE [DBNAME] SET OFFLINE

2. Disable restoring job
On MS SQL Server Management Studio, go to SQL Server Agent >> Jobs >> right click on the restoring job >> select Disable, see the following for example:

3. Copy data file (.mdf) and log file (.ldf) of the standby DB to another place
To know where, you can right click on DB >> select Properties >>  select tab Files >> see Path column.

4. Take online the standby DB
Now, we take it back for continuing receiving logs shipped. Run the following command:

USE MASTER
GO
ALTER DATABASE [DBNAME] SET ONLINE

5. Enable restoring job again

After this step, you standby DB will run normally again.

Now we can create new DB and copy files in step 3 above to. Below are steps on the server which will run this copied DB.

6. Create new DB (same name with standby DB)
Run the following command (make sure new DB same names DBNAME_Data and DBNAME_Log with standby DB):

CREATE DATABASE [DBNAME]
ON
(NAME = 'DBNAME_Data', FILENAME = 'C:\SQL-Data-Folder\DBNAME.mdf')
LOG ON
(NAME = 'DBNAME_Log', FILENAME = 'C:\SQL-Data-Folder\DBNAME.ldf')

7. Take this new DB offline

ALTER DATABASE [DBNAME] SET OFFLINE

8. Copy files in step 3 overwrite files of new DB created

9. Grant permission for MSSQLSERVER working on copied files
On file explorer, right click on the file name >> select Properties menu >> select tab Security >> click Advanced button:

Change the owner and add full permissions on these files copied for user running SQL service (normally it is NT Service\MSSQLSERVER). See red boxes on above picture for example.

10. Take this new DB online

ALTER DATABASE [DBNAME] SET ONLINE

OK, now you have a copied DB of standby DB running. Have fun.