How do you move SQL Server MDF and LDF files? Detach and attach is the old way, so here is the step-by-step method I use for my clients today.
Rename Physical File Name of a SQL Server Database
To rename physical file name entries for a SQL Server database, take it offline, change the catalog with MODIFY FILE, rename the files in Windows and bring the database online. This post builds a demo, shows the error a missing file causes, and gives a way to undo the change.
Rename Logical File Name of a SQL Server Database
To rename logical file name entries in SQL Server, use ALTER DATABASE with MODIFY FILE and NEWNAME. This post builds a demo database, renames both files and checks the result. It also covers names with dots and the two errors that stop a rename.
SQL SERVER – UDF – User Defined Function to Extract Only Numbers From String – Number Table Method
I rewrote my User Defined Function to extract only numbers from a string. The new one uses a numbers table, also called a Tally table, instead of a WHILE loop.
SQL SERVER – Unable to Attach Database Files – The PageAudit Property is Incorrect – Ransomware Attack
After a ransomware attack, a client could not attach database files and saw: The PageAudit property is incorrect. Here is what the error means.
SQL SERVER- High CPU and Transaction Type WorkFileGroup_fake_worktable and Workfile
Workfile and WorkFileGroup_fake_worktable were clues in a customer high-CPU investigation. I correlated them with queries and plan evidence.
SQL SERVER – How to DROP or DELETE Suspect Database?
A client’s production database was stuck in suspect state and they could not even drop it. Here is how I helped them drop the suspect database.