meta data for this page
  •  

This is an old revision of the document!

MS SQL

move database files to optimized storage

Move MSSQL guest data files to dedicated VM disk.

First create new VM disk: 

zfs create nvmpool/data/vm-703-disk-3 -b 64k -V 16G
qm disk rescan

attach new storage to guest.

Format it under Windows guest to use 64kB cluster size. Verify cluster size: 

fsutil fsinfo ntfsinfo g:

move database files

Info based on 

ALTER DATABASE MY_DATABASE
    MODIFY FILE (NAME = MY_DATABASE, FILENAME = 'G:\MY_DATABASE.mdf');
GO
ALTER DATABASE MY_DATABASE
    MODIFY FILE (NAME = MY_DATABASE_log, FILENAME = 'G:\MY_DATABASE_log.ldf');  
GO
ALTER DATABASE MY_DATABASE SET OFFLINE;
GO

Stop MSSQL service.

Give new location rights for MSSQL service user (check what user is set in services).

Move database files to new location.

Start MSSQL service.