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

Grossnickle, Brenda 40 Reputation points
2024-03-07T13:56:37.7066667+00:00

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;

SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
41 questions
0 comments No comments
{count} votes

Accepted answer
  1. Greg Low 1,320 Reputation points Microsoft Regional Director
    2024-03-07T22:02:32.81+00:00

    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.


0 additional answers

Sort by: Most helpful