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
.
Best Answer
There are 2 ways you can fix this.
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.
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.
More Info : RESTORE Statements (Transact-SQL)
Either one of these methods will work for you.