{"id":32007,"date":"2018-11-03T13:55:03","date_gmt":"2018-11-03T08:25:03","guid":{"rendered":"https:\/\/www.wikitechy.com\/technology\/?p=32007"},"modified":"2018-11-03T13:55:03","modified_gmt":"2018-11-03T08:25:03","slug":"sql-databases-migrations-with-ssms","status":"publish","type":"post","link":"https:\/\/www.wikitechy.com\/technology\/sql-databases-migrations-with-ssms\/","title":{"rendered":"SQL Database Migration with SSMS"},"content":{"rendered":"<p><span style=\"color: #003300;\"><strong>SQL Database Migration with SSMS<\/strong><\/span><\/p>\n<p><strong>Migrating MSSQL<\/strong> between <a href=\"https:\/\/www.wikitechy.com\/tutorials\/apache\/apache-web-server\" target=\"_blank\" rel=\"noopener\">servers<\/a> can be challenging without the proper guidelines to keep you on track. In this article, I will be outlining the various ways to <strong>migrate\u00a0Microsoft SQL Server\u00a0databases<\/strong> between servers or instances. Whether you need to move a single database,\u00a0many <a href=\"https:\/\/www.wikitechy.com\/tutorials\/sqoop\/sqoop-list-database\" target=\"_blank\" rel=\"noopener\">databases<\/a>, logins or stored procedures and views we have you covered!<\/p>\n<p>There are many circumstances where you will need to move a database or restore databases. The most common reasons are:<\/p>\n<ul>\n<li><strong>Moving to an entirely new server.<\/strong><\/li>\n<li><strong>Moving to a different instance of SQL.<\/strong><\/li>\n<li><strong>Creating a development server or going live to a production server.<\/strong><\/li>\n<li><strong>Restoring databases from a backup.<\/strong><\/li>\n<\/ul>\n<p>There\u00a0square measure\u00a02\u00a0main\u00a0ways in which\u00a0to maneuver\u00a0SQL databases. Manually with <strong>Microsoft SQL Server Management Studio<\/strong> (SSMS) or with the\u00a0statement.\u00a0the tactic\u00a0you select\u00a0depends on what\u00a0you wish\u00a0to accomplish. If\u00a0you\u2019re\u00a0moving\u00a0one\u00a0info\u00a0or simply\u00a0some, manually <strong>backing up and restoring<\/strong> the databases with SSMS\u00a0are\u00a0the best\u00a0approach. If\u00a0you\u2019re\u00a0moving\u00a0loads\u00a0of databases (think\u00a0over\u00a010) then\u00a0victimization\u00a0the\u00a0statement\u00a0technique\u00a0can\u00a0speed up\u00a0the method. The\u00a0statement\u00a0technique\u00a0takes\u00a0additional\u00a0homework\u00a0work beforehand,\u00a0however\u00a0if\u00a0you\u2019re\u00a0transferring dozens of databases, then\u00a0it\u2019s\u00a0well\u00a0well worth the\u00a0time spent configuring the script\u00a0rather than\u00a0migrating\u00a0every\u00a0info\u00a0singly. If you aren\u2019t\u00a0positive\u00a0that\u00a0technique\u00a0to use,\u00a0attempt\u00a0the manual approach\u00a0initial\u00a0whereas\u00a0you get\u00a0snug\u00a0with\u00a0the method. I like to recommend\u00a0reading all the\u00a0manner\u00a0through for a deeper understanding of the methodology.<\/p>\n<h3 id=\"useful-references-for-terminology\"><span style=\"color: #0000ff;\"><strong>Useful References for Terminology<\/strong><\/span><\/h3>\n<ul>\n<li><strong><em>SSMS<\/em><\/strong>\u2013 An acronym for Microsoft\u00a0<strong>S<\/strong>QL\u00a0<strong>S<\/strong>erver\u00a0<strong>M<\/strong>anagement\u00a0<strong>S<\/strong><\/li>\n<li><strong><em>Source Server<\/em><\/strong>\u2013 The server or instance you are moving databases\u00a0<strong>from<\/strong>\u00a0or\u00a0<strong>off<\/strong>.<\/li>\n<li><strong><em>Destination Server<\/em><\/strong>\u2013 The server or instance you are moving databases\u00a0<strong>to<\/strong>.<\/li>\n<\/ul>\n<h2 id=\"the-manual-method\"><span style=\"color: #003300;\"><strong>The Manual Method<\/strong><\/span><\/h2>\n<p><strong>Moving SQL databases<\/strong> with the manual method can be very easy. It is the preferred process for transferring a few or smaller databases. To follow this part of the guide, you must have <strong>MSSQL, and Microsoft SQL Server Management Studio (SSMS) installed<\/strong>.<\/p>\n<h3 id=\"backing-up-databases-on-the-source-server\"><span style=\"color: #333300;\"><strong>Backing Up Databases on the Source Server<\/strong><\/span><\/h3>\n<p><span style=\"color: #339966;\"><strong>Step 1:<\/strong><\/span> Begin by logging into the Source server (the server you are moving databases from or off of). You will want to open <a href=\"https:\/\/www.wikitechy.com\/errors-and-fixes\/sql\/login-to-microsoft-sql-server-error-18456\" target=\"_blank\" rel=\"noopener\">Microsoft SQL Server<\/a> Management Studio by selecting\u00a0<strong><em>Start > Microsoft SQL Server > \u00a0Microsoft SQL Server Management Studio<\/em>.<\/strong><\/p>\n<p><strong><span style=\"color: #339966;\">Step 2:<\/span> Log into<\/strong> the SQL server using <a href=\"https:\/\/www.wikitechy.com\/technology\/windows-10-free-download-full-version-iso-32-bit-64-bit-2017\/\" target=\"_blank\" rel=\"noopener\">Windows<\/a> Authentication or SQL Authentication.<\/p>\n<p><span style=\"color: #339966;\"><strong>Step 3:<\/strong><\/span> Expand the server(in our case\u00a0<em>SQL01<\/em>), expand<strong>\u00a0<em>Databases<\/em><\/strong>, select the <strong>first database<\/strong> you want to move (pictured below).<\/p>\n<p><img fetchpriority=\"high\" decoding=\"async\" class=\"aligncenter size-full wp-image-32009\" src=\"https:\/\/www.wikitechy.com\/technology\/wp-content\/uploads\/2018\/11\/microsoft-sql-server.png\" alt=\"microsoft-sql-server\" width=\"612\" height=\"668\" srcset=\"https:\/\/www.wikitechy.com\/technology\/wp-content\/uploads\/2018\/11\/microsoft-sql-server.png 612w, https:\/\/www.wikitechy.com\/technology\/wp-content\/uploads\/2018\/11\/microsoft-sql-server-275x300.png 275w\" sizes=\"(max-width: 612px) 100vw, 612px\" \/><\/p>\n<p><span style=\"color: #339966;\"><strong>Step 4:<\/strong> <\/span>Right click on your database and select<strong>\u00a0<em>Tasks<\/em>\u00a0<\/strong>then click\u00a0<strong><em>Back Up<\/em><\/strong>.<\/p>\n<p><img decoding=\"async\" class=\"aligncenter size-full wp-image-32010\" src=\"https:\/\/www.wikitechy.com\/technology\/wp-content\/uploads\/2018\/11\/microsoft-sql-server-task.png\" alt=\"microsoft-sql-server-task\" width=\"652\" height=\"710\" srcset=\"https:\/\/www.wikitechy.com\/technology\/wp-content\/uploads\/2018\/11\/microsoft-sql-server-task.png 652w, https:\/\/www.wikitechy.com\/technology\/wp-content\/uploads\/2018\/11\/microsoft-sql-server-task-275x300.png 275w\" sizes=\"(max-width: 652px) 100vw, 652px\" \/><span style=\"color: #339966;\"><strong>Step 5:<\/strong><\/span> From here you are now at the\u00a0<strong><em>Back Up Database<\/em>screen<\/strong>. You can choose a Backup Type such as\u00a0<strong><em>Full<\/em>or\u00a0<em>Differential<\/em><\/strong>, make sure the correct database is selected, and set the destination for the SQL backup. For our example, we can leave the\u00a0<strong><em>Backup<\/em>\u00a0<em>Type<\/em>\u00a0as\u00a0<em>Full<\/em><\/strong>.<\/p>\n<p><span style=\"color: #339966;\"><strong>Step 6:<\/strong> <\/span>Under\u00a0<strong><em>Backup<\/em><em>Type<\/em><\/strong>, check the box for <strong>\u201c<em>Copy-only backup<\/em>.<\/strong>\u201d If you are running DPM or another form of server backup, backing up without the\u00a0<strong><em>Copy-Only<\/em>flag<\/strong> will cause a break in the backup log chain.<\/p>\n<p><span style=\"color: #339966;\"><strong>Step 7:<\/strong> <\/span>You will see a location under\u00a0<strong><em>Destination\u00a0<\/em><\/strong>for the path of the new backup. Typically you will\u00a0<em><strong>Remove<\/strong>\u00a0<\/em>this entry then\u00a0<em>Add<\/em>\u00a0a new one to select a folder that SQL has read\/write access. Adding a <strong>new\u00a0<em>Backup Destination<\/em><\/strong>\u00a0shows a path similar to the following: <strong><em>C:\\Program Files\\Microsoft SQL Server\\MSSQL13.MSSQLSERVER\\MSSQL\\Backup\\<\/em><\/strong><\/p>\n<p>This<strong> C:\\ path<\/strong> is where your stored database backup is. Note this location for later reference, as this is the default path to stored backups and will have to have proper read\/write access for SQL services.<\/p>\n<h3 id=\"note\"><span style=\"color: #993300;\"><strong>Note:<\/strong><\/span><\/h3>\n<p><strong><em>Advanced users may be comfortable leaving the destination as is, provided the permissions are correct on the output folder.<\/em><\/strong><\/p>\n<p><span style=\"color: #339966;\"><strong>Step 8:<\/strong><\/span> Next, append a filename to the end of this path such as\u00a0<em><strong>AdventureWorks2012-081418.bak<\/strong><\/em>\u2013 Be sure to end the filename with the extension<strong> .bak<\/strong> and select\u00a0<em>OK<\/em><\/p>\n<p><img decoding=\"async\" class=\"aligncenter size-full wp-image-32011\" src=\"https:\/\/www.wikitechy.com\/technology\/wp-content\/uploads\/2018\/11\/microsoft-sql-server-backup.png\" alt=\"microsoft-sql-server-backup\" width=\"653\" height=\"474\" srcset=\"https:\/\/www.wikitechy.com\/technology\/wp-content\/uploads\/2018\/11\/microsoft-sql-server-backup.png 653w, https:\/\/www.wikitechy.com\/technology\/wp-content\/uploads\/2018\/11\/microsoft-sql-server-backup-300x218.png 300w, https:\/\/www.wikitechy.com\/technology\/wp-content\/uploads\/2018\/11\/microsoft-sql-server-backup-74x55.png 74w\" sizes=\"(max-width: 653px) 100vw, 653px\" \/><\/p>\n<p><span style=\"color: #339966;\"><strong>Step 9:<\/strong> <\/span>Once you have pressed\u00a0<em>OK\u00a0<\/em>on the\u00a0<em>Select <strong>Backup Destination<\/strong>\u00a0<\/em>prompt, you are ready to <a href=\"https:\/\/www.wikitechy.com\/errors-and-fixes\/sql\/backup-to-url-fails-in-azure-resource-manager-arm-deployment-model-with-error-400-bad-request\" target=\"_blank\" rel=\"noopener\">back up<\/a> the database! All you need to do now is hit\u00a0<strong><em>OK<\/em><\/strong>, and the database will begin backing up.\u00a0You will see a progress bar in the bottom left-hand corner, and when the backup is complete, a window will appear saying <span style=\"color: #008000;\"><strong>\u2018<\/strong><\/span><em><span style=\"color: #008000;\"><strong>The backup of database \u2018AdventureWorks2012\u2019 completed successfully<\/strong><\/span>.<\/em>\u2018<\/p>\n<h3 id=\"restoring-databases-to-the-destination-server\"><span style=\"color: #003366;\"><strong>Restoring Databases to the Destination Server<\/strong><\/span><\/h3>\n<p>You should now have a <strong>.bak file<\/strong> of all your databases on the source server. These database files need to be transferred to the destination server. There are numerous ways to move your data to the destination server; you can use <strong>USB, Robocopy or FTP<\/strong>. After copying a database you can store it on your destination server,\u00a0 for our example, we have stored it on the C drive in a folder named\u00a0<em><strong>C:\\dbbackups<\/strong> .<\/em><\/p>\n<ol>\n<li>Open <strong>Microsoft SQL Server Management Studio.<\/strong><\/li>\n<li><strong>Log in<\/strong> to the SQL server using Windows Authentication or SQL Authentication.<\/li>\n<li>Expand the server and right click on\u00a0<strong><em>Databases\u00a0<\/em>and select\u00a0<\/strong><em><strong>Restore Database<\/strong>.<\/em><\/li>\n<li>The\u00a0<em><strong>Restore Database<\/strong>\u00a0<\/em>screen looks very similar to the\u00a0<em>Back Up Database\u00a0<\/em>screen. Under Source, you will want to select\u00a0<em>Device\u00a0<\/em>instead of\u00a0<em>Database<\/em>. Selecting\u00a0<em>Device<\/em>\u00a0allows you to restore directly from a file. Once you\u2019ve chosen\u00a0<em>Device<\/em>, click the browse icon [\u2026]<\/li>\n<li>Select\u00a0<strong><em>Add<\/em><\/strong>, then navigate to the folder in which your<strong> .bak files lives.<\/strong> (In this case,\u00a0<em>C:\\dbbackups<\/em>).<\/li>\n<li>Select the first database<strong> .bak<\/strong> you would like to restore and click OK<\/li>\n<\/ol>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-32012\" src=\"https:\/\/www.wikitechy.com\/technology\/wp-content\/uploads\/2018\/11\/microsoft-sql-server-restore.png\" alt=\"microsoft-sql-server-restore\" width=\"653\" height=\"450\" srcset=\"https:\/\/www.wikitechy.com\/technology\/wp-content\/uploads\/2018\/11\/microsoft-sql-server-restore.png 653w, https:\/\/www.wikitechy.com\/technology\/wp-content\/uploads\/2018\/11\/microsoft-sql-server-restore-300x207.png 300w\" sizes=\"(max-width: 653px) 100vw, 653px\" \/><\/p>\n<ol start=\"7\">\n<li>Click\u00a0<em>OK\u00a0<\/em>and now you are ready to import the database.\u00a0Before importing, let\u2019s take a look at the Options section on the left-hand side.\u00a0Under\u00a0<strong><em>Options,\u00a0<\/em><\/strong>you will see other configurations for restoring databases such as\u00a0<strong><em>Overwrite the Existing Database<\/em>,\u00a0<em>Preserve the Replication Settings<\/em>\u00a0and\u00a0<em>Restrict Access to the Restored Database<\/em><\/strong>.\u00a0In this case, we are not replacing an existing database so I will leave all these options unchecked. If you wanted to replace an existing database (for example, the backed up database has newer data than on the destination server or you are replacing a development or production database) then simply select\u00a0<strong><em>Overwrite the Existing Database<\/em>.<\/strong><\/li>\n<\/ol>\n<h3 id=\"note-2\"><span style=\"color: #993300;\"><strong>Note:<\/strong><\/span><\/h3>\n<p><strong><em>Advanced users may be comfortable leaving the destination as is, provided the permissions are correct on the output folder.<\/em><\/strong><\/p>\n<ol start=\"8\">\n<li>Clicking\u00a0<em>OK. b<\/em>egins the restore process as indicated by the popup window that reads \u2018<strong>Database \u2018AdventureWorks2012\u2032 restored successfully.\u2019<\/strong> You have migrated your database from the source to the destination server.<\/li>\n<\/ol>\n<p>Repeat this process for each database that you are migrating. You can then update path references in your scripts\/application to point to the new server, <span style=\"color: #003300;\"><strong>verify that the migration was successful<\/strong><\/span>.<\/p>\n<p>\u00a0<\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQL Database Migration with SSMS Migrating MSSQL between servers can be challenging without the proper guidelines to keep you on track. In this article, I will be outlining the various ways to migrate\u00a0Microsoft SQL Server\u00a0databases between servers or instances. Whether you need to move a single database,\u00a0many databases, logins or stored procedures and views we [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":31998,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23,28],"tags":[86146,86147,86148,86149,86150,86151,86152,86153,86154,86155,86156,86157,86158],"class_list":["post-32007","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database","category-sql-server","tag-database-migration-best-practices","tag-database-migration-in-sql-server-step-by-step","tag-database-migration-steps-in-sql-server","tag-how-to-migrate-a-sql-server-database-to-a-higher-version","tag-migrate-database-from-sql-server-2008-to-2014","tag-migration-in-sql-server-2012-step-by-step","tag-sql-server-2005-to-2012-migration-steps","tag-sql-server-2008-to-2016-migration-checklist","tag-sql-server-data-migration-best-practices","tag-sql-server-database-migration-best-practices","tag-sql-server-database-migration-checklis","tag-sql-server-migration-strategies-and-planning","tag-types-of-migration-in-sql-server"],"_links":{"self":[{"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/posts\/32007","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/comments?post=32007"}],"version-history":[{"count":0,"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/posts\/32007\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/media\/31998"}],"wp:attachment":[{"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/media?parent=32007"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/categories?post=32007"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wikitechy.com\/technology\/wp-json\/wp\/v2\/tags?post=32007"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}