dbatools Required Filesystem Access for Database Restores

Andy Levy
dbatools, sqlserver, powershell
Page content

The Problem

While performing an instance migration this spring, I happened upon something I didn’t expect in dbatools. It should have been a simple backup/restore copy of the databases, with the backup files residing on a fileshare on the destination server after being copied there. I kept getting a warning that the backup files I was attempting to restore couldn’t be read, and the restores (via Restore-DbaDatabase) wouldn’t execute.

I checked permissions on the server over and over again. Both on the filesystem and for the share that I was attempting to read from. Even more curious, if I executed the restore database statements directly from within Management Studio, the databases restored without issue.

After doing quite a bit of digging, I managed to find the reason. The user under which you’re executing Restore-DbaDatabase must have filesystem access (read is all that’s required) for the file path(s) you’re attempting to restore from. The SQL Server instance having access to those files isn’t enough - the user in PowerShell has to be able to verify that the files exist before the restore is attempted. This is made more confusing by the fact that this permission is not required for Backup-DbaDatabase.

Why This Bit Me

In the course of our server migration, we wanted to leave application connection strings unchanged, so we created a DNS CNAME record that pointed the old server name to the new server. It turns out that while this works great for SQL Server, SMB doesn’t allow you to do this. Because I was using the CNAME for the UNC path to the backups I was attempting to restore, the check against the files that Restore-DbaDatabase was executing failed.

Resolution

Switching from Restore-DbaDatabase -Path \\CNAME\path\database.bak to Restore-DbaDatabase -Path \\Realname\path\database.bak solved my problem.

Where Else To Watch Out

If you are running a PowerShell script that performs similar tasks via SQL Server Agent, especially if you’re using a Proxy for that Agent Job, this could hit you as well. Again, it’s not enough for SQL Server to have permission on that path, the Proxy account will need it as well.