I’ve been using SQL virtual database console UI for a few years now and am exploring using the command line tool in order to automate virtualization of databases through jobs. I’m noticing that when I virtualize a database using the console UI, the virtual database properties in SQL Server Management Studio shows the MDF/LDF file path as C:\Program Files\Idera\SQL virtual database\FilterMetaData\<servername> and that we do not actually claim the original size of the MDF/LDF files. Besides small metadata files, the only space required is for the .safe backup file. (This is as expected,)
When I use the sqlvdbcmd command line tool to virtualize a database (sqlvdbcmd Create MyVirtualDatabase D:\DatabaseBackupName.safe), SQL Server Management Studio shows the MDF/LDF file path as D:\ (the original location of the backup folder) and actually recreates the MDF/LDF file in that location. So instead of simply requiring space only to support the .safe backup file, it requires space for the .safe, .mdf, and .ldf files. When I attempt to delete the .mdf/.ldf files, I am warned that they are in use by SSMS, which says to me that it didn’t actually virtualize the database (it isn’t reading the .safe file) it just restored the MDF/LDF files and is reading those.
Why does the sqlvdbcmd command line tool behave differently than the console UI when virtualizing databases? How can I use sqlvdbcmd command line to truly virtualize my databases? To me the benefit of this tool is being able to read the smaller .safe files and remove the .mdf/.ldf files from the server in order to reclaim space. And using the command line tool is not achieving that benefit – it does not seem to be working as advertised. I’d appreciate any support/guidance on this matter!
The problem that you described was reported previously with the release of SQLvdb 2.0. I’ll provide details regarding that issue below and the workaround that was suggested. Before that, I wanted to note that this issue should have been resolved in the 2.1 release of SQLvdb (click here for release notes).
When mounting a virtual database using the SQL virtual database command-line interface (CLI), the SQLvdb Filter Service attempts to create virtualized SQL Server database files in the original location(s) of the source database. This behavior can lead to errors when the database that was the source of the backup file still exists on the instance on which you are attempting to mount the virtual database on.
SQLvdbCmd create Northwind-VDB c:\backup\MYServer_Northwind_Full_20120513.safe
VIRTUAL DATABASE Northwind-VDB…FAILED.
An error was encountered while creating Virtual Database ‘Northwind-VDB’.
The file ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\northwnd.mdf’ cannot be overwritten. It is being used by database ‘Northwind’.
File ‘Northwind’ cannot be restored to ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\northwnd.mdf’. Use WITH MOVE to identify a valid location for the file. The file ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\northwnd.ldf’ cannot be overwritten. It is being used by database ‘Northwind’. File ‘Northwind_log’ cannot be restored to ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\northwnd.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.
Mounting the virtual database from the SQL virtual database console succeeds without any errors, but places the virtualized SQL Server database files in another directory (i.e. C:\Program Files\Idera\SQL virtual database\FilterMetaData\<TargetInstanceName> [<TargetInstanceName> should be replace with the name of the instance that the virtual database will be mounted on]).
During the mount process, SQL virtual database will create virtualized SQL Server database files. By default, the virtualized SQL Server database files will be created in the original location of the database files when the backup was performed on the source database. If the original database still exists on the target instance, the mount will fail as the original database files still exists and cannot be overwritten.
When mounting a virtual database using the SQL virtual database console, SQLvdb automatically supplies an alternate location for the virtualized SQL Server database files. For this reason, the mount succeeds when initiated from the SQL virtual database console.
To avoid this issue when ussing the command-line interfact, modify the command being used in the SQLvdb CLI to include the -Move parameter for each database file within the database, as you would when specifying a native SQL Server restore.
-Move <logical_filename> <target_filename>
Move the database logical database file <logical_filename> to the physical target file <target_filename>. This parameter corresponds to the WITH MOVE option in the RESTORE DATABASE T/SQL command.
Below is the modified command of the example above. This modified command includes the -Move parameter for each database file.
SQLvdbCmd create Northwind-VDB c:\backup\MYServer_Northwind_Full_20120513.safe -Move Northwind “C:\Program Files\Idera\SQL virtual database\FilterMetaData\<TargetInstanceName>\Northwind-VDB_data.mdf” -Move Northwind_log “C:\Program Files\Idera\SQL virtual database\FilterMetaData\<TargetInstanceName>\Northwind-VDB_log.ldf”
If you would like to create the virtualized SQL Server database files in the same directory that the SQL virtual database console uses, you can determine the console’s working directory by using the following steps:
1. Open the SQL virtual database console
2. Click ‘Help & More’
3. Click ‘Change Setup’
4. The directory the console will place the virtualized SQL Server database files in will be defined in the ‘Default Database File Location’ field
5. Modify your command to used the path shown in the ‘Default Database File Location’ field
Let us know if you have any questions.
Thanks for the tips! I tried the “-Move <logical_filename> <target_filename>” suggestion, but unfortunately this is still not working for me. The original server I was trying is version 126.96.36.199 but we have a different server running version 188.8.131.52 – I get the same experience on both servers even though they are different versions.
When I run a statement such as SQLvdbCmd create VirtualDBTest2 “O:\VirtualDBs\OriginalDB.safe” -Move OriginalDB â€œC:\Program Files\Idera\SQL virtual database\FilterMetaData\<instancename>\VirtualDBTest2.mdfâ€ -Move OriginalDB_log â€œC:\Program Files\Idera\SQL virtual database\FilterMetaData\<instancename>\VirtualDBTest2_log.ldfâ€
I get the error:
“VIRTUAL DATABASE VirtualDBTest2…FAILED.
An error was encountered while creating Virtual Database ‘VirtualDBTest2′. XBlade Mount failed.
Mount failed due to mdf data file or ldf file conflict with previous mount or restore. Check your with moves for conflict with other mounts or restores.
file: Refer to SQLvdbFilterService_XBlade.log for any further details.”
When I check the SQLvdbFilterService_XBlade.log I see the same message on both servers:
“Mount failed due to mdf data file or ldf file conflict with previous mount or restore.
Check your with moves for conflict with other mounts or restores.
The only difference between the messages on the 2 different servers is that one references Line:2383 the other references Line:2386.
I’ve confirmed that no files exist for this database in C:\Program Files\Idera\SQL virtual database\FilterMetaData\<instancename> and I’ve also taken care to use a backup that has not yet been restored and has not yet been mounted as a virtual database, so the message “Mount failed due to mdf data file or ldf file conflict with previous mount or restore” doesn’t make any sense to me.
I am attempting to give the virtual database a different name (the original database is still active on the server) and this is something that we do all the time using the console UI, so it seems like it shouldn’t be a problem via the command line tool.
Any additional thoughts/suggestions on how to get around this error? Thanks in advance for your help!
Sometimes when unmounting the SQLvdb not all files are removed properly. The next step is to manually clear all the unused files by SQLvdb. In order to so, open the SQLvdb console and navigate to Help & More > Change Setup > Cleanup Unused Files and click on the Clean Up button. After you have completed the clean up process, please attempt to remount the SQLvdb and let me know if you are still encountering any issues.
Thank you for the response! I tried using the Clean Up button, but I still get the same error.
My apologies for the delayed response on this post.
At this time it may be necessary to open a support ticket and we can collect additional information to further investigate the issue you are encountering. You can log into the Idera Customer Portal and create a support ticket and a Technical Support Engineer with contact you with the next step, for gather the logs for review. Thank you.
Powered by IDERA