1

I am configuring Log shipping from SQL Server 2008R2 to 2016 using NORECOVERY option with Log Shopping jobs. I have successfully configured Backup, Copy and Restore jobs.

When I perform a restore on the standby database, then its Database file location changes on standby site.

I was creating a standby database at D:\<FOLDER>, but after restoring the database on 2016 using Restore -> Database, the location changed to C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA.

Improve this question
5
  • Customize databases
    – safi qadri
    Commented Feb 19, 2019 at 10:48
  • Did you recovered the master database on your standby, that would overwrite all settings.
    – eckes
    Commented Feb 19, 2019 at 11:00
  • No. This is user database. When I am going to open database on STANDBY site... Right click on USER database -> Task -> Restore Database
    – safi qadri
    Commented Feb 19, 2019 at 11:23
  • It is picking the default settings on the instance properties--> database
    – Ramakant Dadhichi
    Commented Feb 19, 2019 at 11:43
  • Can you please check SQL server settings. You need to change the directory from SQL Server > Properties > Database Settings > Change DATA and LOG file directories. This might resolve the issue.
    – Shashank Tiwari
    Commented Feb 19, 2019 at 11:44

1 Answer 1

Reset to default
2

There are 2 ways you can fix this.

  1. As @ramakant mentioned, SQL Server is using the default paths setup on the server. To change this, right click on the server in Object Explorer>Properties>Database Settings>Database Default Locations. Changing these values will automatically restore all databases to those directories unless different paths are specified.

  2. You need to restore the database via a script, and that way you can specify where the files will be. That will restore a database from a local location to the D: drive. Alter the script as required with regards to the locations.

    RESTORE DATABASE [MyDatabase] FROM  
DISK = N'C:\Work\MyDatabase.bak' WITH  FILE = 1,  
MOVE N'MyDatabase' TO N'C:\MSSQL\Data\MyDatabase.mdf',  
MOVE N'MyDatabase_log' TO N'C:\MSSQL\Log\MyDatabase_log.ldf',  
NOUNLOAD,  STATS = 5
GO

More Info : RESTORE Statements (Transact-SQL)

Either one of these methods will work for you.

Improve this answer
5
  • One more question is: We finally configured 2 test instances using NORECOVERY option with Log Shopping jobs – Backup, Copy and Restore setup to run every hour between following servers: 1. MS SQL Server 2008R2 as a source instance 2. MS SQL Server 2016 as a target instance – as we need our current 2008R2 databases to be upgraded/migrated to 2016 instance Log history of these three jobs shows no error. i.e. Backup executes successfully on 2008R2 instance, Copy and Restore jobs execute fine on 2016
    – safi qadri
    Commented Feb 20, 2019 at 6:55
  • However, when we check the timestamp of data and log files of the configured databases on 2016, it shows the timestamp when this was configured. It seems transaction logs are not being applied. Also, when we try to open the database on 2016 using Restore -> Database, it reports some transaction log missing (because we configured to delete older than 2 days transaction logs and hence these were deleted after 2 days).
    – safi qadri
    Commented Feb 20, 2019 at 6:55
  • Hence, I have check from this command then it shows last_restored_file correctly but actually not. "select secondary_database,last_copied_file,last_restored_file from msdb..log_shipping_monitor_secondary" It seems that at the time of database open on 2016, it actually applies/restores transaction log files. Please confirm! Would you please show whether there is any way it can apply transaction logs while in this UPGRADE setup as explained above and let older than 2 days transaction log files deleted as per scheduled till we actually upgrade to 2016?
    – safi qadri
    Commented Feb 20, 2019 at 6:55
  • Your plan should work in theory, however i am unsure about the log file history that needs to be kept. normally just a period while it gets applied but i have not attempted this before. I would recommend asking a new question so that you can get the correct answers based on the topic.
    – WadeH
    Commented Feb 20, 2019 at 14:21
  • In addition, seeing as my answer did help and worked for your initial question, would you mind please marking it as the answer. Thank you
    – WadeH
    Commented Feb 20, 2019 at 22:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.