Restore with multiple filegroups to different server problem
dterrie
Posts: 14
Hi,
I am trying to backup/restore a database with multiple filegroups to a 'MIRROR' server WITH NORECOVERY to set up mirroring for the 'PRINCIPAL' server db. The two servers have identical configurations. All of the filegroups, including the PRIMARY filegroup, reside on drive arrays (F=Data, G=Indexes, H=Logs). When I try to restore on the MIRROR server, SQL Backup insists on restoring all files to the D drive, where the SQL Server executables reside. The default file locations for the MIRROR server are on the D drive, but this should not override the locations in my source db, especially since the drive/folder layout on both servers is identical. If I change the paths to exactly match the original locations and try to restore, I get an error message stating that I have to use the WITH MOVE option. Yet clearly, it is SQL Backup that has already configured a move to the D drive, as no files from the source database are on this drive letter. What am I missing here? This should be a no-brainer.
I am trying to backup/restore a database with multiple filegroups to a 'MIRROR' server WITH NORECOVERY to set up mirroring for the 'PRINCIPAL' server db. The two servers have identical configurations. All of the filegroups, including the PRIMARY filegroup, reside on drive arrays (F=Data, G=Indexes, H=Logs). When I try to restore on the MIRROR server, SQL Backup insists on restoring all files to the D drive, where the SQL Server executables reside. The default file locations for the MIRROR server are on the D drive, but this should not override the locations in my source db, especially since the drive/folder layout on both servers is identical. If I change the paths to exactly match the original locations and try to restore, I get an error message stating that I have to use the WITH MOVE option. Yet clearly, it is SQL Backup that has already configured a move to the D drive, as no files from the source database are on this drive letter. What am I missing here? This should be a no-brainer.
Comments
What version of SQL Backup 5 are you using?
The issue with restoring to the "existing location" was a problem with SQL Backup 5.0 and 5.1; so if you are using these versions this will be fixed by upgrading to SQL Backup 5.2.
The reason for not restoring to the default location is that in many cases the disk structures have changed between the servers, and if we follow the "use existing locations" logic (as we did in SQL Backup 4), in many cases this would "fail by default".
Unfortunately it's one of those scenarios where people will have differing opinions, and we're not going to be able to satisfy everyone's wishes. I'll make a note of the suggestion and it's something we may re-evaluate for a future version of SQL Backup.
Thanks,
Jason
As is the norm in a mirrored configuration, I am simply trying to restore a replica of the principal db on the mirror server. In books on-line this is supposed to be accomplished via
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\AdventureWorks.bak'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks
FROM DISK = 'C:\ AdventureWorks.bak'
WITH FILE=1, NORECOVERY
GO
BOL shows the MOVE option, but implies I only need to do this if the drive letter or path differs, which is not the case here. In your Restore Wizard, you give the user the option to change the restore paths.
I presume I should just do a backup with the Management Studio and use T-SQL to restore?
Regarding your feature set, I don't see any difficulty in restoring to original locations while allowing the user to use your existing dialog to select alternates. You can easily enough test for the existence of the target default paths and guide the user if they're not found.
* Follow the wizard, leave the "default" file locations and continue to the final step (4 of 4)
* Select the "Scripts" tab, and copy/paste the script into management studio
* Strip out the "MOVE" sections as applicable (which will default back to the 'existing' disk location, as per SQL Server)
* Execute the restore.
As for the difficulty, it certainly isn't technically difficult - it's how SQL Backup 4.x functioned, but based on usability research and the problems many customers had with restoring it made more sense to do it this way for version 5.
I'll add a suggestion into our bug-tracking system to include an either/or option for a future version - that way you would be able to use the 'backup' file locations rather than the suggested defaults.
Thanks,
Jason
The bottom line in any app is that it should behave as expected. It's fine to default to the new server default location, but offering a dropdown with 'Current Locations' also seems quite sensible, as it could be used to pre-populate the path for each file being restored. If the user did not change these settings, and the paths exist, you can run the code I just did at your suggestion. If the user changes any of the paths, you can add a MOVE clause as required, and warn the user of the consequences.