January 23, 2009 at 2:55 pm
Hey Guys,
I am trying to restore databases from other DB servers and the path used for data and log files was different for different servers.
I start of by right clicking on databases and then restore.
Next I would select the device and add the backup file and then select the restore database and give the name for that database.
Next, when I go to options tab, I then see that path for data and log is the one using which it was created on that particular database. Is there a way of setting some path and it does not change depending on the path on which it was created. I dont want to edit the path location for data and log everytime. I changed database settings in server properties and went in and looked in registry entries as well but I could not change it.
Any help would be appreciated
January 25, 2009 at 1:10 pm
I don't think that you can do this.
I think that SQL Server will always default to the file locations recorded in the backup - which will be the file locations from the server that the backup was originally taken from.
If you are regularly doing a lot of restores and this is becoming a pain, how about scripting it and taking care of the different paths that way?
January 26, 2009 at 10:30 am
Thanks a lot for the reply. so there is no way of doing this.... apart from using a script..... I could find registry entries for changing default locations for folders (backups) and I thought if you have reg entries for backups, there should be for restore as well.... anyways thanks lot for your help
Nikhil
January 26, 2009 at 3:07 pm
No I don't think it's possible to change the MDF and LDF location when using Management Studio. There is a default location, but that's only used as default path for creating databases.
A T-SQL RESTORE command WITH MOVE option if the only way to do this.
__________________
MS-SQL / SSIS / SSRS junkie
Visit my blog at dba60k.net
January 26, 2009 at 3:54 pm
in ssms in the options tab you can overwrite the locations to the directories you want to restore to. This is equivalent to using the 'with move' option.
the first time you restore a database to another server (i.e. the database does not yet exist) you will have to use the with move option because restore will use the file locations held in the backup file.
for subsequent restores of the same database once it exists you can use the replace option as the restore will overwrite the in-situ database files of the target database as long as the logical file names and numbers match.
You have hit upon as good reason to standardise directory structures across your SQL servers where possible.
---------------------------------------------------------------------
January 27, 2009 at 8:23 am
thank you guys for all the replies.
After a lot of r & d, I thought there was no solution for my problem but to either change the path directly when restoring or script it. I was thinking I might be wrong, but thank s for helping guys,
Nikhil
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply