How to restore a Microsoft SQL database from backup with the Microsoft SQL Server Management Studio (MSSMS) user interface

There are multiple reasons to restore a database from backup. One of them can be disaster recovery, the other is to bring the production database to the developer machine. In both cases, the computer already has the old version of the database. In the new version of Microsoft SQL Server Management Studio (MSSMS) we cannot find the “Close connections” checkbox anymore, so we have to make sure all connections are closed, and we specify a unique database file name to restore the database to.

1. On the developer machine close all instances of Visual Studio to close the open database connections,
2. Close all Microsoft SQL Server Management Studio tabs that are connected to the database,
3. Move the backup file to the C:\Temp folder. Microsoft SQL Server Management Studio cannot see it in your Downloads folder.
4. Make sure your user account does not automatically connect to the database you want to restore:
   1. In Security, Logins right click your username and select Properties,
      
   2. Set the Default database to master
      
5.  Disconnect from the database server:
   1.  Right-click the database server and select Disconnect,
      
      
6. Connect to the database engine, but do not open the database you want to restore:
   1. Execute,

      USE Master

7. Import the database from production with the user interface:
   1. Right-click the database and select Tasks, Restore, Files and Filegroups…
      
   2. Select the From device radio button
      
   3. Click the … button to select the backup file
      
   4. In the Select backup devices window click the Add button to select the file
      
   5. Navigate to the C:\Temp folder and select the database backup file
   6. Click the OK button
      
   7. In the Select the backup sets section select the Restore check box in the file row, and click Options on the left side,
      
   8. On the Options tab select the Overwrite existing database (WITH REPLACE) check box, and click the … button to set the new unique database file name,
      
   9. Click OK to ignore the error message on the Locate Database Files – … message box
      
   10. Navigate to the current database file, click it, and append the date of the backup to the end of the file name to specify a unique data file name, and click OK,
       
   11. On the Restore Files and FileGroups window click the OK button to restore the database
       

Update the database to make it work at the new location

1. On a development machine set the databases to Simple recovery mode, so the log files do not grow out of bound.

   ALTER DATABASE my_database SET RECOVERY SIMPLE;

2. Delete and recreate the users in the restored database, because those have different internal IDs if the database was migrated from a different server

   USE my_database;
   GO
   spDropUser 'my_user', 'dbo'
   GO
   DROP SCHEMA my_user
   GO

   For some reason If I type DECLARE below, WordPress crashes. Please add the letter E to the end of the first word below.

   DECLAR @user_name varchar(50)
   SET @user_name = 'my_user'
   USE my_database; EXEC sp_grantdbaccess @user_name; EXEC sp_addrolemember @rolename = 'db_datareader', @membername = @user_name; EXEC sp_addrolemember @rolename = 'db_datawriter', @membername = @user_name; 
   GO