Restore SQL databases with same logical name

Question

Hi all,

We have a lot of SQL servers where databases might have the same logical names but different file names in DATA and LOG folders.

For example, how it's visible from Restore pane:

Capture.JPG.jpg

So, when we restore all databases to a different location (we did this during our disaster recovery test) - the database "CA" was not restored.

avsql Error <9072>: sqlconnectimpl_smo::execute Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: The file 'D:\DATA\CA.mdf' cannot be overwritten. It is being used by database 'CA'. File 'CA' cannot be restored to 'D:\DATA\CA.mdf'. Use WITH MOVE to identify a valid location for the file. The file 'L:\LOG\CA_log.ldf' cannot be overwritten. It is being used by database 'CA'. File 'CA_log' cannot be restored to 'L:\LOG\CA_log.ldf'. Use WITH MOVE to identify a valid location for the file. Problems were identified while planning for the RESTORE statement. Previous messages provide details. RESTORE DATABASE is terminating abnormally.

Restore job was configured to redirect all databases to a different server with running MSSQL. New paths for DATA and LOGs were defiend with options "Alternate databsae location" and "Alternate log locaiton".

As far as I understend, Avamar did not try to restore databases with original file names, but it used Logical name of databases instead.

Do you know how to solve it? For us it's important to restore data during minimal time, so the way to restore such databases one by one with different names is not variant

Thanks!

pawankumawat · Answer

Hello,

Please try following steps, hope it will help you. If you face this issue even after implementing following steps, I would suggest you to open a case with EMC support.

There are two workarounds:

Workaround 1:

Restore the backup to a flat file, and then restore the database from the flat file via MSSQL Management Studio.

1. Select SQL backup to restore.
2. Specify/browse destination client.
3. In "Restore Plug-in" drop-down menu, select "Windows File System".
4. In "Restore Destination Choices" select "Restore everything to a differentlocation".
5. In "Items Marked for Restore" options group, click on "Set Destination" button and browse the destination path on destination client using "Windows File System" plug-in (or manually enter the destination path in "Save Target(s) in Directory", for example: C:\restore)
6. Click "OK" two times to start the restore.

After the restore successfully completes, on the destination server run SQL Management Studio and connect to it.
7. Right click on "Databases" folder in "Object Explorer" and select "Restore Database..."
8. In "To database" field enter the database name to be restored.
9. Select "From device" radio button and click on browse ("..." button).
10. Locate the backup file in the previously specified restore destination path (as specified in step 1.5).
11. In "Files of type" drop-down menu select "All Files (*)" option and select restore file (f-0 file).
12. Click "OK" button two times.
13. In "Select the backup sets to restore:" select backup by clicking on the checkbox in the "Restore" column.
14. Click on "Options" button in "Select a page" menu 2.9. In "Restore the database files as:" change the destination path for the database files, if required, by clicking on the browse button ("..." button).
15. Select "Recovery state" and select "RESTORE WITH RECOVERY" (if this is the last backup to be restored) or "RESTORE WITH NORECOVERY" (if other backups are planned to be restored on the same database, for example differential backups
d-1, d-2, ...).
16. Repeat the previous steps if other successive backups are required to be restored (for example differential backups d-1, d-2, ...).

Workaround 2:

Restore the backup with Avamar GUI, and add the following option:

--appendclauses=,MOVE 'sysft_ftcat_documentindex_bf4dfe6f1de7df118d46005056b9000f TO 'c:\ \ftcat_documentindex_bf4dfe6f1de7df118d46005056b9000f'

Michaylo_rus · Answer

Hi,

Could you please leave a little bit more details about Workaround2.. what does it mean? Should restore path be for DATA or LOG location?

Thank you very much for help! I will check workaround1 this evening

P.S. I already opened a case in EMC support and provided some logs...

Michaylo_rus · Answer

Hi,

Finally issue has been solved. EMC engineer suggested us avoiding to use the options for redirect restore which I used. ("Alternate databsae location" and "Alternate log locaiton). Instead of this new locations for *.mdf and *.ldf files must be defined like here: Restore to different SQL Server instance or location -> Select SQL instance -> Set Destination -> Select all records about database and via Browse define the needed path. Then select all records about logs and define new path for Logs.

In this case our databases have been restored with original file names.

I did not find any mentions about such issue in the EMC docs..

Avamar

Restore SQL databases with same logical name

Was this post helpful?