Restoring database in sql server is one of the important tasks database administrators have to do from time to time. Restoring a database just means to bring up a backed up copy of the database online. There are many scenarios that may require for the database to be restored from a backup such as when there is some issue with the current copy online. Take for example some data has been accidentally deleted and you want to go back to the state of the database it was when you took the last backup so you can have the deleted data back in the database. You may also restore a backed up copy of a database to another instance of sql server for other purposes such as testing or migrating to another server. I wrote a post on how to back up a database in case you are interested in backing up the database first before restoring it to another instance of sql server. As a general rule, most administrators have jobs that will back up the database on scheduled times daily or weekly or whatever. I will be restoring in this demo a mocked database dataannalytictips to the sql server running on my laptop. In a future post l will cover how to create a database from scratch.
Note that you must have access to the backup file and have the required permissions on the server to allow restoring a database. The sysadmin server role is required and db_owner role is required when restoring an existing database. Once you are connected to the server with SSMS, right click databases and click on Restore database. Below window will show.
The above is the general page for the Restore Database window. On this page, we have the source section where we can choose Device or Database as where we are restoring the database from. In our case we are restoring from a backup file so l choose Device. There is an ellipses at the end of the device option where you can click and open up a window which will allow you to choose the backup file to restore. Click on the ellipses and the window below will show up.
The Pop up window shown above is where you will add the backup file by clicking on the add button, navigate to the location of the file and click on it. Note the Backup Media type chosen is File. There is option for URL but I have never used that before. Once you clicked on it, the file path will be added to the backup media section. You can use the Remove button on the right to remove the file added and choose another one. The contents button will show you the backup information in the file. Click on the Add button and you should see below window where you can navigate to the backup location on the server you are restoring the database. If your backup file resides on a shared folder on your network ,you can paste that shared folder location into the backup File location box and press enter. Once you press enter, the files in that location will show up and you can choose the backup file by clicking on it in the white window in the middle right. By default when you click on Add, this window will take you to the backup location set up during sql server installation but you can navigate to anywhere on the server to locate the file using the directories in the left window.
Navigating to my backup file dataanalytictips.bak and selecting will get it added as shown below.
Click the OK button in the above file adding window to go back to the main database restore page below.
We can see details of the backup file. It has the database name, the last time the database backup was taken and it gives you the restore plan which details information no the backup set to restore. You have the option to verify the backup media you just selected to make sure its not corrupt. Note you can decide to give another name to the database during the restore procerss by changing the database name section. Lets take a look at the files page by clicking on files in the top left corner under select a page. You should see below.
The files page gives you information on the database files itself. We have the logical filename , file type, original file name and restore As option. The restore as option has ellipses where you can choose where you want to restore the files on the server instead of the default Data file location set up during the installation of the sql server. You can also choose the option Restore all data files to folder and using the data file folder and log file folder options to select where you want to restore those files. Lets go to the next page Options which is on the top left corner under the select a page and you will see below.
The page above is where some settings will cause the restore process to fail. For example, if the database is already existing on the server and you are restoring a backup, you must choose the option to overwrite the existing the base. Another important option is the Recovery state. In the snapshot it shows restore with recovery which is the default. This means once all the data has been restored, make the database available to be used or recover the database. It is also possible to restore the data without making the database available and this option is called restore with norecovery. This can be the case where you are recovering multiple files for the backup and you don’t want to make the database available until you have restored the last one and choose the option restore with recovery to make the database available for users to access. The last recovery state is restore with standby. This option restores to an offline database. You can read more on the restore options here https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-arguments-transact-sql?view=sql-server-20
Now that we have set up the restore, we just click on the ok button and the database will be restore to our server.
Hope this is helpful. Any suggestions welcome.
