Back Up and Restore Your SQL Database

Occasionally a developer needs to go through the process of safely creating a new version of their existing database or updating/refreshing their development server every few months with production data. As a Junior developer, this was one of the most terrifying things my tech lead has asked me to do because I felt like I had the potential to mess up all of our customer data and ruin the company! Of course, we have backups and this was a silly fear; all went well with a little time and practice and I did not destroy my company. This handy little guide will walk you through SQL Server Management Studio 2012's Back Up and Restore tools if perhaps this is your first time or if it's just been awhile and you want to make sure you get it right.

For this guide, I will assume you already have an existing database (or two, if you are restoring one with the data of another) and you are currently the only person accessing the database you want to backup or restore. If other computers/users may be connected to this database, you must make sure you are the only one accessing it at this time (via SQL or potentially a .NET or other application). If you are unsure whether other users are connected, execute sp_who in a new SQL query to see a list of all hosts and users logged in to the server(s) and what their statuses are. Sleeping and runnable statuses can both cause a hangup in the backup and restore processes, so they must all be disconnected.

Right click the Database you'd like to back up in the Object Explorer, navigate to Tasks, and select Back Up (if the Object Explorer isn't open, do so through the toolbar View > Object Explorer).

In the Back Up Database window General page you want to make sure the correct Database is selected in the dropdown, likely you will want the Full Backup Type and Database Backup Component, and rename the database backup set if applicable along with any description and expiration (optional). Remove any Destinations currently in the textbox and click Add, then click the "..." button beside the Destination File name box. Choose the location of your backup and give it a File name. Note: Be sure to append the File name with a .bak extension or it will be more difficult to find later! Then click OK until you are back on the main Back Up Database window. Under the Options page, I typically leave these settings as they are, but if you'd like the current backup to overwrite existing backups, verification of your backup, or for it to continue through an error, select these as necessary. Click OK when finished and SQL will begin execution of the Back Up process. Assuming the process completed successfully, you should soon get a pop up stating as much.

For Restoring our database, I am covering two different scenarios:
 1. Restore an existing database with its own backup from an earlier time (probably due to an error of some kind that you want to fix).
 2. Restore an existing database with a backup from a different database (e.g. You have a production server and a development server, and you want to make sure the development server has the latest production data).

Both of these scenarios will work similarly, but the end result will be slightly different for each.

In either case, we are going to right-click the database we want to restore with our back up file, then navigate to Tasks > Restore > Database. In the General page you want to change the Source to Device, click the "..." button, and find the .bak file you created a few moments ago. If you forgot to use the .bak extension, you will need to navigate to the path you used and choose Files of type "All Files" to find it. Click OK until you get back to the main Restore Database window. Under Destination be sure the database you want to restore using the backup is selected from the Database dropdown. If you are restoring the database with its own backup, the database names should match. If you are restoring a database with the backup of a different database, they will not match, and this should reflect the name of the database to be "overwritten" with the backup data.

Next in the Files page, change the three Restore As selections to match similarly to the last portion - either the same database as the backup or as the one that will be overwritten in the case of a different database being updated with another database's backup data. If you are relocating all files or need specific Data and Log file folders used, check this box and choose your folders respectively. In the Options page, be sure to select the Restore option "Overwrite the existing database" so the new data will overwrite the old. The other two check boxes are as-needed, preserving replication settings and restricting access to the restored database, so use those as applicable. The final selection - Take tail-log* backup before restore - is one that I typically de-select, but may be something you want to keep depending on your Restore situation. You may choose to prompt before restoring each backup, but this is not necessary.

*Tail-log: In the case of any log records that have not yet been backed up, the tail-log captures these and carries them along to ensure that no work has been lost, and to keep the log chain intact. Not all situations require the tail-log, such as if the recovery point is located in an earlier log backup or in a case like our example where we are overwriting a database and don't need to restore it to a point after the most recent backup.
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/tail-log-backups-sql-server?view=sql-server-2017

Click OK. If data was not corrupt and you are still the only person connected to the server being overwritten/restored, you should soon get a confirmation window stating that the Restore was successful. Your database should reflect the data of the backup and be in working order!