Database Files show in master_files but not in database_files

Question

I have a few SQL databases, that I inherited.

I have a fresh install of SQL 2012

I have attached the databases to the server without an issue.

Yet, where I run Select * From sys.database_files; they are not in there, but when I run Select * From sys.master_files; they are.

This is messing up some code I am attempting to write by throwing the following errors:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'I:'.
Msg 8985, Level 16, State 1, Line 1
Could not locate file 'I:\SQL Databases\Cloud.CMS_log.ldf' for database 'master' in     sys.database_files. The file either does not exist, or was dropped. 

How can I fix it so they are there in sys.database_files so my code runs?

Declare @TempDBList Table
(
    Id int,
    DBName VarChar(250),
    FileType int,
    DBFile VarChar(1000)
);
Declare @BackupLocation VarChar(Max) = 'I:\SQL Databases\Backup';
Declare @FileLocation VarChar(Max);
Declare @DBName VarChar(250);
Declare @FileType Int;
Declare @DBBackup VarChar(Max);
Declare @LogBackup VarChar(Max);
Declare @Sql VarChar(Max);

Insert Into @TempDBList
(id, DBName, FileType, DBFile)
Select a.database_id DBid, a.Name , b.type FileType, b.physical_name As FileLocation
    From sys.databases a
    Inner Join sys.master_files b On b.database_id = a.database_id
    Where b.state = 0 AND a.database_id > 4;

Select @DBName = DBName, @FileType = FileType, @FileLocation = DBFile From @TempDBList Order By DBName;

While @@ROWCOUNT <> 0
Begin
    --- Set all databases to Simple Recovery
    Set @Sql = 'Alter DATABASE ' + QUOTENAME(@DBName) + ' Set RECOVERY SIMPLE';
    Exec(@Sql);

    Set @DBBackup = @BackupLocation + @DBName + '\' + @DBName + '_' + Convert(Varchar(500), GetDate(), 112)+ '.bak';
    Set @LogBackup = @BackupLocation + @DBName + '\' + @DBName + '_' + Convert(Varchar(500), GetDate(), 112)+ '.log.bak';

    If @FileType = 1
        Begin
            Set @Sql = 'Backup Database ' + QUOTENAME(@DBName) + ' To Disk = ' + @DBBackup;
            Exec(@Sql);
            Exec('DBCC SHRINKFILE(''' + @FileLocation + ''', TruncateOnly)');
        End
    Else If @FileType = 0
        Begin
            Set @Sql = 'Backup Log ' + QUOTENAME(@DBName) + ' To Disk = ' + @LogBackup;
            Exec(@Sql);
            Exec('DBCC SHRINKFILE(''' + @FileLocation + ''', TruncateOnly)');
        End
End

Screenshot for both sys.database_files & sys.master_files

http://prntscr.com/57p49b

Answer 1

sys.master_files is a system wide view and will show you all the files on the instance you are connected to (where you have sufficient permission), sys.database_files is a per database view, and will only show files in the specified database. You either need to connect to the correct database to see the files, e.g:

USE Master;
SELECT  *
FROM    sys.database_files;

Or use the 3 part object name:

SELECT  *
FROM    master.sys.database_files;

---

EDIT

I can only apologise for not explaining myself very well, but the above does point out why you cannot see files in sys.database_files that you can see in sys.master_files.

Look at the following screenshot:

You can see that after connecting to a different database (USE TestDB) different files are showing in sys.database_files, but the record count (and the actual records) in sys.master_files is the same regardless of database.

Now, looking at your actual error:

Could not locate file 'I:\SQL Databases\Cloud.CMS_log.ldf' for database 'master' in sys.database_files.

This explains the problem, you are connected to the database master, so sys.database_files would only show the files in the master database (master, and masterlog). You are looking for CMS_log which is presumably located in the database CMS, so to view this file in sys.database_files you would need to run:

USE CMS;
SELECT * FROM sys.database_files;

Or

SELECT * FROM CMS.sys.database_files;

Your actual error comes because you are trying to shrink the file CMS_Log while connected to the master database, which you cannot do, you would need to run:

USE CMS;
DBCC SHRINKFILE('CMS_Log', TRUNCATEONLY);

Hopefully this explains why you are getting the error.

---

FULL SCRIPT

DECLARE @BackupLocation VARCHAR(MAX) = '',
        @DBName SYSNAME,
        @DataFile SYSNAME,
        @LogFile SYSNAME,
        @SQL NVARCHAR(MAX);

DECLARE FileCursor CURSOR STATIC FORWARD_ONLY READ_ONLY
FOR
    SELECT  DBName = d.Name,
            DataFile = MAX(CASE WHEN f.Type = 0 THEN f.Name END),
            LogFile = MAX(CASE WHEN f.Type = 1 THEN f.Name END)
    FROM    sys.databases d
            INNER JOIN sys.master_files f
                ON d.database_id = f.database_id
    WHERE   d.Name NOT IN ('master', 'tempdb', 'model', 'msdb')
    AND     d.Name NOT LIKE 'ReportServer$%'
    GROUP BY d.Name;


OPEN FileCursor;
FETCH NEXT FROM FileCursor INTO @DBName, @DataFile, @LogFile;

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @SQL = '
        USE ' + QUOTENAME(@DBName) + ';
        ALTER DATABASE ' + QUOTENAME(@DBName) + ' SET RECOVERY SIMPLE;

        BACKUP DATABASE ' + QUOTENAME(@DBName) + ' TO DISK = ''' + @BackupLocation + 
            + @DBName + '_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak'';

        ALTER DATABASE ' + QUOTENAME(@DBName) + ' SET RECOVERY FULL;

        BACKUP LOG ' + QUOTENAME(@DBName) + ' TO DISK = ''' + @BackupLocation + 
            + @DBName + '_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '.log.bak'';

        DBCC SHRINKFILE(' + @DataFile + ', TRUNCATEONLY);
        DBCC SHRINKFILE(' + @LogFile + ', TRUNCATEONLY);';

    EXECUTE sp_executesql @SQL;

    FETCH NEXT FROM FileCursor INTO @DBName, @DataFile, @LogFile;
END

CLOSE FileCursor;
DEALLOCATE FileCursor;

---

This generates and executes a command like the following for each database:

USE [TestDB];
ALTER DATABASE [TestDB] SET RECOVERY SIMPLE;

BACKUP DATABASE [TestDB] TO DISK = 'I:\SQL Databases\Backup\TestDB_20141119.bak';

ALTER DATABASE [TestDB] SET RECOVERY FULL;

BACKUP LOG [TestDB] TO DISK = 'I:\SQL Databases\Backup\TestDB_20141119.log.bak';

DBCC SHRINKFILE(TestDB, TRUNCATEONLY);
DBCC SHRINKFILE(TestDB_log, TRUNCATEONLY);