Database Files Down The Wrong Path

I manage a few servers used to host SQL Instances for development and test purposes. Each of those instances hosts databases covering multiple environments. So I’ve got multiple servers, with multiple instances, with multiple environments.

It’s important that issues in those environments don’t block development tasks, or invalidate or block testing cycles, so I like to keep them a little bit locked down. Code changes into those environments are generally made through builds and releases.

Equally though, sometimes developers need to be able to make changes that require sysadmin rights, without waiting for me to have time to help – or I might be on holiday.

Consequently, I’ve had to give elevated permissions to a few “trusted” devs. Result – bad things happen occasionally.

One common issue that bugs me is where databases have been moved from one instance to another, usually through backup and restore, and the files haven’t been moved as part of the restore so they get recreated in the data\log folders for the old instance.

This has caused me various problems, e.g. working out which instance is hammering the disk or using up all the space.

You can avoid this using the tick box in the Files page in the Restore dialog (if you’re restoring through the GUI):

FilesDownWrongPath

I’ve written a quick script to identify any database files that are suffering this and identifying the user responsible for the restore (where this is available). I thought I’d share in case anyone else suffers from the same problem:

--Check for database files down the wrong path
DECLARE @DefaultDataPath VARCHAR(512);
DECLARE @DefaultLogPath VARCHAR(512);

SET @DefaultDataPath = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS VARCHAR(512)) + '%';
SET @DefaultLogPath = CAST(SERVERPROPERTY('InstanceDefaultLogPath') AS VARCHAR(512)) + '%';

SELECT @DefaultDataPath AS CorrectDataPath, @DefaultLogPath AS CorrectLogPath;

SELECT 
   SUSER_SNAME(d.owner_sid) AS Culprit, 
   d.name, 
   f.type_desc, 
   f.physical_name
FROM sys.master_files f
INNER JOIN sys.databases d
   ON f.database_id = d.database_id
WHERE d.database_id > 4
AND d.name != 'SSISDB'
AND (
      (f.type_desc = 'ROWS' AND f.physical_name NOT LIKE @DefaultDataPath)
   OR (f.type_desc = 'LOG' AND f.physical_name NOT LIKE @DefaultLogPath)
);

System databases and the SSIS catalog are excluded.

For once I won’t show the results of the script in action – I am however off to have a quiet word with a few culprits!

One thought on “Database Files Down The Wrong Path

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s