Sql-server – the files

alter-databasesql server

The DBA run a file relocation script to move the physical Database file from where it was running to I:.

ALTER DATABASE [cro01] MODIFY FILE ( name=cro01,filename=N'i:\SQLData\cro01.MDF')

He take the Database offline ad copy the file.
But for some strange reason he don't finnish the job correctly. We have now mdf files duplicated and we dont know which one is running. How do I know the "real" running location of the files via a Tsql query? Is it posible? I want to avoid checking manually each disk file location against each sys.master_files entry.

Best Answer

dbatools to the rescue - use Find-DbaOrphanedFile

This command searches all directories associated with SQL database files for database files that are not currently in use by the SQL Server instance.

By default, it looks for orphaned .mdf, .ldf and .ndf files in the root\data directory, the default data path, the default log path, the system paths and any directory in use by any attached directory.

You can specify additional filetypes using the -FileType parameter, and additional paths to search using the -Path parameter.

e.g. Below command will - Connect to sqlserver2014a, authenticating with SQL Server authentication, and searches for orphaned files. Returns server name, local filename, and unc path to file.

$cred = get-credential -UserName yourUserThatHasAccessToYourServer -Message "Enter correct Password"
#above will pop up message box to enter the password !
Find-DbaOrphanedFile -SqlInstance sqlserver2014a -SqlCredential $cred

Related Solutions

Sql-server – Where is the information about what files each database stored for SQL Server 2008 R2

SQL Server maintains this in sys.master_files, after you have restored the master database
When SQL Server starts up, you'll see a lot of suspect databases.

You can attach the MDFs and LDFs using the info in sys.master_files

Sql-server – What Are the Log File Names

There's no way to find those files with a detached database .mdf. As Aaron suggests, you can create the database with ATTACH_REBUILD_LOG. Another option is if you have an "old" backup file of the database, you can use RESTORE FILELISTONLY to interrogate the backup file for the state of the database files at the time of that backup. This will give you a starting point to track down your files.

Edit Because I like Powershell, here's a script that will read through all the full backup files in a directory and build an attachdbs.sql in your Documents folder:

param([parameter(Mandatory=$true)][string] $dir,
        [parameter(Mandatory=$true)][string] $server)


#load assemblies
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
$smosrv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $server


#get backup files
$files = gci $dir | where {$_.name -like "*.bak"}

$output=([Environment]::GetFolderPath("MyDocuments")) + "\attachdbs.sql"

"/*****************************************" > $output
"Attach script based off of backup files" >> $output
"*****************************************/" >> $output
foreach($file in $files){
    $rs=new-object("Microsoft.SqlServer.Management.Smo.Restore")
    $rs.Devices.AddDevice($file.FullName, "File")
    $hd=$rs.ReadBackupHeader($smosrv)
    $dbname=$hd.Rows[0].DatabaseName

    $dbfiles=$rs.ReadFileList($smosrv)

    "CREATE DATABASE $dbname ON" >> $output
    $filewrite=@()
    foreach($dbfile in $dbfiles){
        $filewrite+="(FILENAME='"+$dbfile.PhysicalName+"')"
    }

    $filewrite -join ",`n" >> $output

    "FOR ATTACH; `n--------------------------" >> $output
    }

Best Answer

Related Solutions

Sql-server – Where is the information about what files each database stored for SQL Server 2008 R2

Sql-server – What Are the Log File Names

Related Question