Skip to main content
All CollectionsDatabases
Restoring MSSQL Databases Using SSMS
Restoring MSSQL Databases Using SSMS

Databases Microsoft SQL

Updated over 4 years ago

Restoring MSSQL Databases Using SSMS

If you are needing to restore your MSSQL database to a previous state, you can do this using SSMS. You can restore from a .bak file that you have already taken or you can restore using the .mdf and .ldf files. We take at least 1 daily backup of your database files for your dedicated server, but you can always choose to back up your system for more days than one.

Restore From MDF and LDF Files

If you have access the .mdf and .ldf files of your database from the previous database state that you are wanting and the database no longer exists on the MSSQL server, you can restore that database to the previous state by following the steps below:

  1. Upload the .mdf and .ldf files to the server, if they are not already on the server.

  2. Next, you will need to move the database files into the correct directory location to be read. This will generally be similar to the following path:C:\Program Files\Microsoft SQL Server\(your_version)\MSSQL\DATA

  3. With the files in the correct location, start the SSMS instance and log in with an appropriate user like using windows authentication or the SA user so that you have proper levels of access.

  4. Now, right click on the ‘Databases’ directory and then select the option ‘Attach…’.

  5. Then, select the database files you have just added to the MSSQL data directory and select ‘OK’.Note: If needed, you can now export the database into a .bak file from the newly attached database.

Alternatively, you can also run the following query. Please make modifications where needed.

Copy to clipboardCREATE DATABASE [dbName]
ON ( FILENAME = 'C:\Program Files\Microsoft SQL Server\(your_version)\MSSQL\DATA\[dbName].mdf' )
LOG ON ( FILENAME = 'C:\Program Files\Microsoft SQL Server\(your_version)\MSSQL\DATA\[dbName_log].ldf')
GO

Restore From A BAK File

The most common method of restoring your database is to use a .bak file taken from a replication or export process. The .bak file will contain a uniquely compressed version of the MDF and LDF files for the database. Using the SSMS native restore functionality you can overwrite the existing data from the data directory even with the database still present in the MSSQL server.

  1. Upload the .bak files to the server, if it isn’t already on the server.

  2. Next, start the SSMS instance and log in with an appropriate user like using windows authentication or the SA user so that you have proper levels of access.

  3. Expand the ‘Databases’ directory in the Object Explorer, and right-click on the appropriate database name.

  4. In the context menu, select the option to ‘Restore’ the database: Tasks > Restore > Database.

  5. In most cases, you’ll have the .bak file on hand, so change the option in the dialog box to ‘Device’ and select the ‘browse…’ button to find the backup file.Note: If the database was originally backed up on the same server and exists within the msdb backup history you can select it from the top drop-down menu. When you have the database you’re restoring from selected in the Destination drop-down menu it will tell you when the most recent backup was taken.

  6. You should now be able to restore the database using that .bak file. In some cases, you may be presented with errors that the restoration was unsuccessful.Note: It is important to pay attention to the error presented as it will likely correlate to a misconfigured option for the restore action.

Restore Options

Below is a brief explanation of the available restoration settings and a description of their impact during a restoration process:

  • Overwrite the existing database (WITH_REPLACE) - Allows the database to be overwritten without the need to drop the tables first, removing all data in the currenttables.

  • Preserve the replication settings (WITH KEEP_REPLICATION) - If the database is being restored to an alternate server, this setting would keep the replication settings attached from the previous instance. Usually not relevant unless the database was replicated when the backup was created.

  • Restrict access to the restored database (WITH RESTRICTED_USER) - Restricts access to the restored database to only db_owner, dbcreator, or the sa user.

  • Restore with recovery - Always select this option unless you have specific need to otherwise. Effectively allows the database to be used right out of the boxafter the restoration complete.

  • Restore with norecovery - This will leave the database in the restoring state. This will allow you to restore more than one backup file to the database if neededbut the database itself will need to be restored to RESTORE WITH RECOVERY once ready in order to recover it for use.

  • Restore with standby - Shouldn’t ever need to use this option, it requires a standby file which just puts the database into a standby state until it is recovered.

  • Close Existing Connections - If you’re restoring to a database currently in use the restoration will fail and you will receive “Exclusive access could not be obtained because the database is in use”. This option force closes all of the connections to ensure the database can be accessed exclusively.

Did this answer your question?