{"id":792,"date":"2022-09-02T11:36:01","date_gmt":"2022-09-02T11:36:01","guid":{"rendered":"https:\/\/bandittracker.com\/?p=792"},"modified":"2023-05-26T21:01:14","modified_gmt":"2023-05-26T21:01:14","slug":"where-are-sql-express-databases-located","status":"publish","type":"post","link":"https:\/\/bandittracker.com\/where-are-sql-express-databases-located\/","title":{"rendered":"Where Are SQL Express Databases Located?"},"content":{"rendered":"\n<p>There are three easy ways to find where your SQL Express database is located.<\/p>\n\n\n\n<p>Before we go through them step by step, I\u2019ll quickly explain how your databases are stored. You may be surprised to learn that a database has more than one file.<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_63 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<p class=\"ez-toc-title\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6601ab586ae58\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6601ab586ae58\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/bandittracker.com\/where-are-sql-express-databases-located\/#How_SQL_Express_Stores_Databases_In_Files\" title=\"How SQL Express Stores Databases In Files\">How SQL Express Stores Databases In Files<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/bandittracker.com\/where-are-sql-express-databases-located\/#Video_Walkthrough\" title=\"Video Walkthrough\">Video Walkthrough<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/bandittracker.com\/where-are-sql-express-databases-located\/#Method_1_Use_Management_Studio_To_Look_At_Database_Properties\" title=\"Method 1: Use Management Studio To Look At Database Properties\">Method 1: Use Management Studio To Look At Database Properties<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/bandittracker.com\/where-are-sql-express-databases-located\/#Method_2_Execute_A_System_Procedure_To_Find_The_Database_Location\" title=\"Method 2: Execute A System Procedure To Find The Database Location\">Method 2: Execute A System Procedure To Find The Database Location<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/bandittracker.com\/where-are-sql-express-databases-located\/#Method_3_Run_SQL_To_Find_The_Database_Location\" title=\"Method 3: Run SQL To Find The Database Location\">Method 3: Run SQL To Find The Database Location<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_SQL_Express_Stores_Databases_In_Files\"><\/span>How SQL Express Stores Databases In Files<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Each SQL Express database is stored in two or more files on your local disk.<\/p>\n\n\n\n<ul><li>data is stored in a file with the extension \u201c.mdf\u201d<\/li><li>logs for the database are stored in one or more files with the extension \u201c.ldf\u201d<\/li><\/ul>\n\n\n\n<p>Your database will probably have one log file, meaning that there are a total of two files for it. By default, the two files are stored in the same folder.<\/p>\n\n\n\n<p>If you have several databases, you will need to know your target database name in order to identify which files belong to which database.<\/p>\n\n\n\n<p>The file name will be the same as the database name. If you named your database \u201capples\u201d, the files will be called:<\/p>\n\n\n\n<ul><li>apples.mdf<\/li><li>apples.ldf<\/li><\/ul>\n\n\n\n<p>Now let\u2019s look at how to find the location of the files.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Video_Walkthrough\"><\/span>Video Walkthrough<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>If you&#8217;re prefer a visual walkthrough, then this video goes through the same three methods that I describe in the next sections of this article.<\/p>\n\n\n\n<figure class=\"wp-block-embed aligncenter is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-4-3 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe loading=\"lazy\" title=\"Where Are SQL Express Database Files Stored?\" width=\"1200\" height=\"900\" src=\"https:\/\/www.youtube.com\/embed\/qzXM0Xgnr9I?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Method_1_Use_Management_Studio_To_Look_At_Database_Properties\"><\/span>Method 1: Use Management Studio To Look At Database Properties<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<ol type=\"1\"><li>Launch SQL Server Management Studio and connect to your server.<\/li><li>Expand the list of databases<\/li><li>Right-click on the database you are looking for<\/li><li>Click on the \u201cFiles\u201d item in the left window<\/li><li>Expand the display in the main window<\/li><\/ol>\n\n\n\n<p>The picture below shows the Files window on my server. I\u2019ve expanded the window horizontally so I can see the full file location.<\/p>\n\n\n\n<p>You can also copy the path field to notepad to see it in full.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"749\" height=\"402\" src=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2022\/09\/ssms-file-location.jpg\" alt=\"\" class=\"wp-image-793\" srcset=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2022\/09\/ssms-file-location.jpg 749w, https:\/\/bandittracker.com\/wp-content\/uploads\/2022\/09\/ssms-file-location-300x161.jpg 300w\" sizes=\"(max-width: 749px) 100vw, 749px\" \/><\/figure><\/div>\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Method_2_Execute_A_System_Procedure_To_Find_The_Database_Location\"><\/span>Method 2: Execute A System Procedure To Find The Database Location<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>You can use this method in a query window in Management Studio.<\/p>\n\n\n\n<p>But you can also use the SQL command utility logged into the SQL Server instance. This is handy if you find that <a href=\"https:\/\/bandittracker.com\/fix-slow-start-up-of-sql-server-management-studio\/\">launching Management Studio is slow<\/a> (<em>click the link for ways to fix this<\/em>).<\/p>\n\n\n\n<p>You just need to know the database name.<\/p>\n\n\n\n<p>Run this SQL command:<\/p>\n\n\n\n<blockquote class=\"wp-block-quote\"><p><em>exec sp_helpdb [database name]<\/em><\/p><\/blockquote>\n\n\n\n<p>The output gives you several rows. The first row is a summary of the database size and creation date.<\/p>\n\n\n\n<p>The next lines show the file locations. Here is the output from my server:<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2022\/09\/sp_helpdb-file-location.jpg\" alt=\"\" class=\"wp-image-794\" width=\"771\" height=\"244\" srcset=\"https:\/\/bandittracker.com\/wp-content\/uploads\/2022\/09\/sp_helpdb-file-location.jpg 597w, https:\/\/bandittracker.com\/wp-content\/uploads\/2022\/09\/sp_helpdb-file-location-300x95.jpg 300w\" sizes=\"(max-width: 771px) 100vw, 771px\" \/><\/figure><\/div>\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Method_3_Run_SQL_To_Find_The_Database_Location\"><\/span>Method 3: Run SQL To Find The Database Location<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>This method queries the system tables to get the same information that is returned in the earlier methods.<\/p>\n\n\n\n<p>The file location is stored in the system table <em>sys.master_files<\/em>.<\/p>\n\n\n\n<p>This table has a numeric database_id field to identify which database you\u2019re looking at. You will probably want to join to sys.databases to use the database name.<\/p>\n\n\n\n<p>Here is a sample query where the name of the database is \u201caa\u201d. Simply substitute your own database name and the query will show you the database file locations.<\/p>\n\n\n\n<blockquote class=\"wp-block-quote\"><p><em>select f.type_desc, f.physical_name<\/em><\/p><p><em>from\u00a0\u00a0 sys.databases d<\/em><\/p><p><em>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 inner join sys.master_files f on d.database_id = f.database_id<\/em><\/p><p><em>where d.name = &#8216;aa&#8217;<\/em><\/p><p><\/p><\/blockquote>\n\n\n\n<p>Note that I\u2019m also pulling back the \u201ctype_desc\u201d field. This will show either \u201cROWS\u201d or \u201cLOG\u201d. <\/p>\n\n\n\n<p>The \u201cROWS\u201d record is the file that holds the data. The other file holds log information.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>There are three easy ways to find where your SQL Express database is located. Before we go through them step by step, I\u2019ll quickly explain how your databases are stored. You may be surprised to learn that a database has more than one file. How SQL Express Stores Databases In Files Each SQL Express database &#8230; <a title=\"Where Are SQL Express Databases Located?\" class=\"read-more\" href=\"https:\/\/bandittracker.com\/where-are-sql-express-databases-located\/\" aria-label=\"More on Where Are SQL Express Databases Located?\">Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[24],"tags":[],"_links":{"self":[{"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts\/792"}],"collection":[{"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/comments?post=792"}],"version-history":[{"count":2,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts\/792\/revisions"}],"predecessor-version":[{"id":797,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts\/792\/revisions\/797"}],"wp:attachment":[{"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/media?parent=792"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/categories?post=792"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/tags?post=792"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}