Easiest way to verify that SQL has access to a folder that would contain mdf and ldf files

Question

Re: Easiest way to verify that SQL has access to a folder that would contain mdf and ldf files

I am writing a script to move ldf and/or mdf files. Doing the basic ALTER DATABASE commands below, but writing them in dynamic SQL so that multiple database files can be done at once. The person doing the manual cut and paste of the files is a server admin so they will be able to paste to almost any location. Do not want to find out after pasting 100 files that SQL cannot bring the databases online because SQL does not have access to the folder. How is the easiest way to verify SQL access to a folder? The user will be remoted into the server. If they start a TASK -> BACKUP (or RESTORE) and can navigate to the folder, does that mean that SQL has permissions to the folder?

ALTER DATABASE bank07_M1 MODIFY FILE (NAME = Bank07_M1_Log, FILENAME = 'F:\SQLData\MSSQL11.MSSQLSERVER\Bank07_M99.ldf');

ALTER DATABASE Bank07_M1 SET OFFLINE WITH ROLLBACK IMMEDIATE;

-- cut and paste file

ALTER DATABASE Bank07_M1 SET ONLINE;

Answer 1

The issue isn't whether the user can navigate to the folder. The issue is whether the service account for SQL Server can work with the folder.

Trying to do a simple backup to that folder seems like quite a good test. You should be able to trap failures on that pretty easily.

Another left-field approach might be to put these into an Agent job, where you use Powershell job steps to make sure the folder exists, has permissions for the SQL Server service account, etc. And do that before the T-SQL steps that move the metadata, etc. The job could read details of the databases to modify from a table in some DB. Clearly you could do that with xp_cmdshell commands from the DB but we try to avoid enabling that.