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

How to migrate the Visual Studio 2013 user database to SQL Server 2008

When you create a new web application in Microsoft Visual Studio 2013 the user database it creates is version 706. SQL Server 2008 can only open databases up to version 663 or earlier. To migrate the new Visual Studio 2013 databases to SQL Server 2008 script the database tables and create them again in the SQL Server 2008 Management Studio.

  • Start Visual Studio 2013
  • Create a new web application with user authentication
  • On the left side select the Server Explorer
  • Click the arrow next to the Data Connections
  • Click the arrow next to DefaultConnection
  • Click the arrow next to Tables
  • Right click each table and select Open Table Definition

visual studio script database table

At the bottom of the screen the table definition script appears

visual studio script database table result

You have to create the tables in the appropriate order, to satisfy the foreign key references. The script below creates the tables in the right order:

CREATE DATABASE your_database
GO
USE your_database
GO

--  ---------------------------------------------------------------------

CREATE TABLE [dbo].[__MigrationHistory] (
    [MigrationId]    NVARCHAR (150)  NOT NULL,
    [ContextKey]     NVARCHAR (300)  NOT NULL,
    [Model]          VARBINARY (MAX) NOT NULL,
    [ProductVersion] NVARCHAR (32)   NOT NULL,
    CONSTRAINT [PK_dbo.__MigrationHistory] PRIMARY KEY CLUSTERED ([MigrationId] ASC, [ContextKey] ASC)
);
GO            

--  ---------------------------------------------------------------------

CREATE TABLE [dbo].[AspNetRoles] (
    [Id]   NVARCHAR (128) NOT NULL,
    [Name] NVARCHAR (MAX) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetRoles] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO

--  ---------------------------------------------------------------------

CREATE TABLE [dbo].[AspNetUsers] (
    [Id]            NVARCHAR (128) NOT NULL,
    [UserName]      NVARCHAR (MAX) NULL,
    [PasswordHash]  NVARCHAR (MAX) NULL,
    [SecurityStamp] NVARCHAR (MAX) NULL,
    [Discriminator] NVARCHAR (128) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO

--  ---------------------------------------------------------------------

CREATE TABLE [dbo].[AspNetUserClaims] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [ClaimType]  NVARCHAR (MAX) NULL,
    [ClaimValue] NVARCHAR (MAX) NULL,
    [User_Id]    NVARCHAR (128) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUserClaims] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_User_Id] FOREIGN KEY ([User_Id]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
);
GO

--  ---------------------------------------------------------------------

CREATE TABLE [dbo].[AspNetUserLogins] (
    [UserId]        NVARCHAR (128) NOT NULL,
    [LoginProvider] NVARCHAR (128) NOT NULL,
    [ProviderKey]   NVARCHAR (128) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUserLogins] PRIMARY KEY CLUSTERED ([UserId] ASC, [LoginProvider] ASC, [ProviderKey] ASC),
    CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
);
GO
CREATE NONCLUSTERED INDEX [IX_UserId]
    ON [dbo].[AspNetUserLogins]([UserId] ASC);
GO

--  ---------------------------------------------------------------------

CREATE TABLE [dbo].[AspNetUserRoles] (
    [UserId] NVARCHAR (128) NOT NULL,
    [RoleId] NVARCHAR (128) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUserRoles] PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC),
    CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
);
GO
CREATE NONCLUSTERED INDEX [IX_RoleId]
    ON [dbo].[AspNetUserRoles]([RoleId] ASC);
GO
CREATE NONCLUSTERED INDEX [IX_UserId]
    ON [dbo].[AspNetUserRoles]([UserId] ASC);
GO

Execute the script in the SQL Server Management Studio

The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. (System.Data)

When you try to import Excel 2007 or later “.xlsx” files into an SQL Server 2008 database you may get the following error mesage :

The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. (System.Data)

sql server 2008 import excel xlsx

 

To solve this problem install the appropriate version of the Microsoft Access Database Engine.

 

On 32 bit Windows

On the 32 bit version of Windows Server 2003 download and install the 32 bit version of Microsoft Access Database Engine 2010 Redistributable from

http://www.microsoft.com/en-us/download/details.aspx?id=13255

 

On 64 bit Windows

On the 64 bit versions of Windows 7 and  Windows Server 2008 download and install the 64 bit version of

Microsoft Access Database Engine 2010 Redistributable from

http://www.microsoft.com/en-us/download/details.aspx?id=13255

and the

2007 Office System Driver: Data Connectivity Components from

http://www.microsoft.com/download/en/details.aspx?id=23734

 

You may also have to execute the following lines in the SQL Server Management Studio

  • Open a new query window
  • Select the master database
  • Execute the following lines

sp_configure ‘show advanced options’, 1;
RECONFIGURE;
sp_configure ‘Ad Hoc Distributed Queries’, 1;
RECONFIGURE;
GO

 

Why do we need to install the old 32 bit driver on the 64 bit operating system?

SQL Server Management Studio is a 32 bit application, most likely it can only use the 32 bit driver on the 64 bit operating system.

If you try to install the 32 bit version of the newer Microsoft Access Database Engine 2010 Redistributable and you already have the 64 bit version of Microsoft Office installed on your 64 bit computer you will get the following error message:

“You cannot install the 32-bit version of Microsoft Access Database Engine 2010 because you currently have 64-bit Office products installed. If you want to install 32-bit Microsoft Access Database Engine 2010, you will first need to remove the 64-bit installation of Office products. After uninstalling the following product(s), rerun setup in order to install 32-bit version of Microsoft Access Database Engine 2010: …”

sql server 2008 import excel xlsx 32 bit access database engine 64 bit office

 

The old 32 bit 2007 Office System Driver can be installed even if the 64 bit version of Microsoft Office 2013 is installed on the machine.

 

How to open SQL files in Visual Studio 2012

The default installation of Visual Studio 2012 cannot open SQL documents when you double click on them in Source Control Explorer.  To be able to do that you have to install the

SQL Server Data Tools  for Visual Studio 2012

For more information visit http://msdn.microsoft.com/en-US/data/tools.aspx

Create a backup of a Microsoft SQL Server database to a location outside of the database server

The Microsoft SQL Server Management Studio 2008 user interface only allows you to create a backup of a database to one of the local drives of the database server.

To create a backup of a Microsoft SQL Server database to a location outside of the database server execute the following SQL script:


BACKUP DATABASE MyDatabase TO
DISK = N'\\MyNas\MyDatabase.bak'
WITH NOFORMAT, NOINIT, NAME = N'MyDatabase Database Backup', NOSKIP, STATS = 10
GO

Change the bold values to match your environment.

How to bypass the “Saving changes is not permitted…” message in Microsoft SQL Management Studio

You made a change to a Microsoft SQL Server database table that requires the re-creation of the table you get the following error message when you try to save your changes:

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

Cause:

Microsoft SQL Server Management Studio doesn’t allow the re-creation of the table if the following option is set in the Microsoft SQL Management Studio configuration:

SQLServer_PreventSavingChangesTableRecreation

Solution:

  • In the Tools menu select “Options…”,
  • On the left pane select “Designers”,
  • Uncheck the “Prevent saving changes that require table re-creation” check box.