{"id":676,"date":"2006-11-10T08:27:33","date_gmt":"2006-11-10T08:27:33","guid":{"rendered":"http:\/\/thekua.com\/rant\/?p=676"},"modified":"2006-11-10T08:27:33","modified_gmt":"2006-11-10T08:27:33","slug":"backing-up-sql-server-to-development","status":"publish","type":"post","link":"https:\/\/thekua.com\/rant\/2006\/11\/backing-up-sql-server-to-development\/","title":{"rendered":"Backing Up SQL Server to Development"},"content":{"rendered":"

On our current project, we try to keep testing as close to as what’s going in proudction as possible. We take frequent database backups (appropriately clean some of the data) and then each developer and the QAs restore them to local copies of SQL server. The DB in production is massive with its transaction file, so here’s a way of speeding it up for local copies. <\/p>\n

    \n
  1. Restore the DB into a copy of development<\/li>\n
  2. Trim what tables\/stored procedures\/etc you don’t need<\/li>\n
  3. Shrink the DB<\/li>\n
  4. Detach the DB and remove the log (LDF) file (our prod log file is about 2G and even though it’s empty when we back up, it takes an excessive time to restore)<\/li>\n
  5. Reattch the DB and back that up<\/li>\n
  6. Restore whenever you want and it should be faster<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"

    On our current project, we try to keep testing as close to as what’s going in proudction as possible. We take frequent database backups (appropriately clean some of the data) and then each developer and the QAs restore them to local copies of SQL server. The DB in production is massive with its transaction file, […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[39],"class_list":["post-676","post","type-post","status-publish","format-standard","hentry","category-technical","tag-work-related"],"yoast_head":"\nBacking Up SQL Server to Development - thekua.com@rest<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/thekua.com\/rant\/2006\/11\/backing-up-sql-server-to-development\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Backing Up SQL Server to Development - thekua.com@rest\" \/>\n<meta property=\"og:description\" content=\"On our current project, we try to keep testing as close to as what’s going in proudction as possible. We take frequent database backups (appropriately clean some of the data) and then each developer and the QAs restore them to local copies of SQL server. The DB in production is massive with its transaction file, […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/thekua.com\/rant\/2006\/11\/backing-up-sql-server-to-development\/\" \/>\n<meta property=\"og:site_name\" content=\"thekua.com@rest\" \/>\n<meta property=\"article:published_time\" content=\"2006-11-10T08:27:33+00:00\" \/>\n<meta name=\"author\" content=\"Patrick\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Patrick\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/2006\\\/11\\\/backing-up-sql-server-to-development\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/2006\\\/11\\\/backing-up-sql-server-to-development\\\/\"},\"author\":{\"name\":\"Patrick\",\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/#\\\/schema\\\/person\\\/6de103b28f96be3f1f4dc25db357c6ae\"},\"headline\":\"Backing Up SQL Server to Development\",\"datePublished\":\"2006-11-10T08:27:33+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/2006\\\/11\\\/backing-up-sql-server-to-development\\\/\"},\"wordCount\":144,\"commentCount\":1,\"publisher\":{\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/#\\\/schema\\\/person\\\/6de103b28f96be3f1f4dc25db357c6ae\"},\"keywords\":[\"Work-Related\"],\"articleSection\":[\"Work Related\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/thekua.com\\\/rant\\\/2006\\\/11\\\/backing-up-sql-server-to-development\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/2006\\\/11\\\/backing-up-sql-server-to-development\\\/\",\"url\":\"https:\\\/\\\/thekua.com\\\/rant\\\/2006\\\/11\\\/backing-up-sql-server-to-development\\\/\",\"name\":\"Backing Up SQL Server to Development - thekua.com@rest\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/#website\"},\"datePublished\":\"2006-11-10T08:27:33+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/2006\\\/11\\\/backing-up-sql-server-to-development\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/thekua.com\\\/rant\\\/2006\\\/11\\\/backing-up-sql-server-to-development\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/2006\\\/11\\\/backing-up-sql-server-to-development\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/thekua.com\\\/rant\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Backing Up SQL Server to Development\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/#website\",\"url\":\"https:\\\/\\\/thekua.com\\\/rant\\\/\",\"name\":\"thekua.com@rest\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/#\\\/schema\\\/person\\\/6de103b28f96be3f1f4dc25db357c6ae\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/thekua.com\\\/rant\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/#\\\/schema\\\/person\\\/6de103b28f96be3f1f4dc25db357c6ae\",\"name\":\"Patrick\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/wp-content\\\/uploads\\\/2025\\\/12\\\/pat-kua-head.png\",\"url\":\"https:\\\/\\\/thekua.com\\\/rant\\\/wp-content\\\/uploads\\\/2025\\\/12\\\/pat-kua-head.png\",\"contentUrl\":\"https:\\\/\\\/thekua.com\\\/rant\\\/wp-content\\\/uploads\\\/2025\\\/12\\\/pat-kua-head.png\",\"width\":400,\"height\":400,\"caption\":\"Patrick\"},\"logo\":{\"@id\":\"https:\\\/\\\/thekua.com\\\/rant\\\/wp-content\\\/uploads\\\/2025\\\/12\\\/pat-kua-head.png\"},\"sameAs\":[\"http:\\\/\\\/www.thekua.com\"],\"url\":\"https:\\\/\\\/thekua.com\\\/rant\\\/author\\\/patrick\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Backing Up SQL Server to Development - thekua.com@rest","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/thekua.com\/rant\/2006\/11\/backing-up-sql-server-to-development\/","og_locale":"en_US","og_type":"article","og_title":"Backing Up SQL Server to Development - thekua.com@rest","og_description":"On our current project, we try to keep testing as close to as what’s going in proudction as possible. We take frequent database backups (appropriately clean some of the data) and then each developer and the QAs restore them to local copies of SQL server. The DB in production is massive with its transaction file, […]","og_url":"https:\/\/thekua.com\/rant\/2006\/11\/backing-up-sql-server-to-development\/","og_site_name":"thekua.com@rest","article_published_time":"2006-11-10T08:27:33+00:00","author":"Patrick","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Patrick","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/thekua.com\/rant\/2006\/11\/backing-up-sql-server-to-development\/#article","isPartOf":{"@id":"https:\/\/thekua.com\/rant\/2006\/11\/backing-up-sql-server-to-development\/"},"author":{"name":"Patrick","@id":"https:\/\/thekua.com\/rant\/#\/schema\/person\/6de103b28f96be3f1f4dc25db357c6ae"},"headline":"Backing Up SQL Server to Development","datePublished":"2006-11-10T08:27:33+00:00","mainEntityOfPage":{"@id":"https:\/\/thekua.com\/rant\/2006\/11\/backing-up-sql-server-to-development\/"},"wordCount":144,"commentCount":1,"publisher":{"@id":"https:\/\/thekua.com\/rant\/#\/schema\/person\/6de103b28f96be3f1f4dc25db357c6ae"},"keywords":["Work-Related"],"articleSection":["Work Related"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/thekua.com\/rant\/2006\/11\/backing-up-sql-server-to-development\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/thekua.com\/rant\/2006\/11\/backing-up-sql-server-to-development\/","url":"https:\/\/thekua.com\/rant\/2006\/11\/backing-up-sql-server-to-development\/","name":"Backing Up SQL Server to Development - thekua.com@rest","isPartOf":{"@id":"https:\/\/thekua.com\/rant\/#website"},"datePublished":"2006-11-10T08:27:33+00:00","breadcrumb":{"@id":"https:\/\/thekua.com\/rant\/2006\/11\/backing-up-sql-server-to-development\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/thekua.com\/rant\/2006\/11\/backing-up-sql-server-to-development\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/thekua.com\/rant\/2006\/11\/backing-up-sql-server-to-development\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/thekua.com\/rant\/"},{"@type":"ListItem","position":2,"name":"Backing Up SQL Server to Development"}]},{"@type":"WebSite","@id":"https:\/\/thekua.com\/rant\/#website","url":"https:\/\/thekua.com\/rant\/","name":"thekua.com@rest","description":"","publisher":{"@id":"https:\/\/thekua.com\/rant\/#\/schema\/person\/6de103b28f96be3f1f4dc25db357c6ae"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/thekua.com\/rant\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/thekua.com\/rant\/#\/schema\/person\/6de103b28f96be3f1f4dc25db357c6ae","name":"Patrick","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/thekua.com\/rant\/wp-content\/uploads\/2025\/12\/pat-kua-head.png","url":"https:\/\/thekua.com\/rant\/wp-content\/uploads\/2025\/12\/pat-kua-head.png","contentUrl":"https:\/\/thekua.com\/rant\/wp-content\/uploads\/2025\/12\/pat-kua-head.png","width":400,"height":400,"caption":"Patrick"},"logo":{"@id":"https:\/\/thekua.com\/rant\/wp-content\/uploads\/2025\/12\/pat-kua-head.png"},"sameAs":["http:\/\/www.thekua.com"],"url":"https:\/\/thekua.com\/rant\/author\/patrick\/"}]}},"_links":{"self":[{"href":"https:\/\/thekua.com\/rant\/wp-json\/wp\/v2\/posts\/676","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/thekua.com\/rant\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thekua.com\/rant\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thekua.com\/rant\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/thekua.com\/rant\/wp-json\/wp\/v2\/comments?post=676"}],"version-history":[{"count":0,"href":"https:\/\/thekua.com\/rant\/wp-json\/wp\/v2\/posts\/676\/revisions"}],"wp:attachment":[{"href":"https:\/\/thekua.com\/rant\/wp-json\/wp\/v2\/media?parent=676"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thekua.com\/rant\/wp-json\/wp\/v2\/categories?post=676"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thekua.com\/rant\/wp-json\/wp\/v2\/tags?post=676"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}