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 Microsoft SQL Server databases between servers or instances. Whether you need to move a single database, many databases, logins or stored procedures and views we have you covered!

There are many circumstances where you will need to move a database or restore databases. The most common reasons are:

• Moving to an entirely new server.
• Moving to a different instance of SQL.
• Creating a development server or going live to a production server.
• Restoring databases from a backup.

There square measure 2 main ways in which to maneuver SQL databases. Manually with Microsoft SQL Server Management Studio (SSMS) or with the statement. the tactic you select depends on what you wish to accomplish. If you’re moving one info or simply some, manually backing up and restoring the databases with SSMS are the best approach. If you’re moving loads of databases (think over 10) then victimization the statement technique can speed up the method. The statement technique takes additional homework work beforehand, however if you’re transferring dozens of databases, then it’s well well worth the time spent configuring the script rather than migrating every info singly. If you aren’t positive that technique to use, attempt the manual approach initial whereas you get snug with the method. I like to recommend reading all the manner through for a deeper understanding of the methodology.

Useful References for Terminology

• SSMS– An acronym for Microsoft SQL Server Management S
• Source Server– The server or instance you are moving databases from or off.
• Destination Server– The server or instance you are moving databases to.

The Manual Method

Moving SQL databases 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 MSSQL, and Microsoft SQL Server Management Studio (SSMS) installed.

Backing Up Databases on the Source Server

Step 1: Begin by logging into the Source server (the server you are moving databases from or off of). You will want to open Microsoft SQL Server Management Studio by selecting Start > Microsoft SQL Server >  Microsoft SQL Server Management Studio.

Step 2: Log into the SQL server using Windows Authentication or SQL Authentication.

Step 3: Expand the server(in our case SQL01), expand Databases, select the first database you want to move (pictured below).

Step 4: Right click on your database and select Tasks then click Back Up.

Step 5: From here you are now at the Back Up Databasescreen. You can choose a Backup Type such as Fullor Differential, make sure the correct database is selected, and set the destination for the SQL backup. For our example, we can leave the Backup Type as Full.

Step 6: Under BackupType, check the box for “Copy-only backup.” If you are running DPM or another form of server backup, backing up without the Copy-Onlyflag will cause a break in the backup log chain.

Step 7: You will see a location under Destination for the path of the new backup. Typically you will Remove this entry then Add a new one to select a folder that SQL has read/write access. Adding a new Backup Destination shows a path similar to the following: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\

This C:\ path 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.

Note:

Advanced users may be comfortable leaving the destination as is, provided the permissions are correct on the output folder.

Step 8: Next, append a filename to the end of this path such as AdventureWorks2012-081418.bak– Be sure to end the filename with the extension .bak and select OK

Step 9: Once you have pressed OK on the Select Backup Destination prompt, you are ready to back up the database! All you need to do now is hit OK, and the database will begin backing up. You will see a progress bar in the bottom left-hand corner, and when the backup is complete, a window will appear saying ‘The backup of database ‘AdventureWorks2012’ completed successfully.‘

Restoring Databases to the Destination Server

You should now have a .bak file 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 USB, Robocopy or FTP. After copying a database you can store it on your destination server,  for our example, we have stored it on the C drive in a folder named C:\dbbackups .

1. Open Microsoft SQL Server Management Studio.
2. Log in to the SQL server using Windows Authentication or SQL Authentication.
3. Expand the server and right click on Databases and select Restore Database.
4. The Restore Database screen looks very similar to the Back Up Database screen. Under Source, you will want to select Device instead of Database. Selecting Device allows you to restore directly from a file. Once you’ve chosen Device, click the browse icon […]
5. Select Add, then navigate to the folder in which your .bak files lives. (In this case, C:\dbbackups).
6. Select the first database .bak you would like to restore and click OK

7. Click OK and now you are ready to import the database. Before importing, let’s take a look at the Options section on the left-hand side. Under Options, you will see other configurations for restoring databases such as Overwrite the Existing Database, Preserve the Replication Settings and Restrict Access to the Restored Database. In 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 Overwrite the Existing Database.

Note:

Advanced users may be comfortable leaving the destination as is, provided the permissions are correct on the output folder.

8. Clicking OK. begins the restore process as indicated by the popup window that reads ‘Database ‘AdventureWorks2012′ restored successfully.’ You have migrated your database from the source to the destination server.

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, verify that the migration was successful.