Category: SQL

Import a BACPAC File to Create a New Database

I used a SQL Express 2016 to import a bacpac file from Azure.
(Export the db from the Azure Portal first). And the SQL Server Management studio 17.

Initially imported into SQL Express 2014 but got some warnings/errors regarding the import, so SQL 2016 works better.

Steps:
1. Access to a bacpac file locally / on azure storage
2. Use the UI wizard in Management Studio. (See below)

To launch the wizard, use the following steps:
  1. Connect to the instance of SQL Server, whether on-premise or in SQL Database.
  2. In Object Explorer, right-click on Databases, and then select the Import Data-tier Application menu item to launch the wizard.
  3. Complete the wizard dialogs: Introduction Page. Import Settings Page.

    Source: Import a BACPAC File to Create a New User Database | Microsoft Docs

I got this error when starting up the website:
System.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – The certificate chain was issued by an authority that is not trusted.

Solved the error by setting TrustServerCertificate=false in the connectionstring.

More info here:
https://stackoverflow.com/questions/17615260/the-certificate-chain-was-issued-by-an-authority-that-is-not-trusted-when-conn

Import existing EPiServer users into an empty database – EPiServer 7.5

Here is a short guide for importing users from an existing EPiServer site into an empty EPiServer site. And then setup the default user groups and access rights. This is for EPiServer 7.5 using the standard Sql Membership but will probably work fine with newer EPiServer versions as well.

Create empty db

Run deployment center as administrator -> select create Sql db.

Importing users from old/other db:

How to copy table data, see this guide: https://www.sqlshack.com/how-to-copy-tables-from-one-database-to-another-in-sql-server/
The tables are named like: aspnet_Applications

Marked in bold are the tables that was used by my EPiServer 7.5 installation (the other tables where just empty). This is the order used, (see the link above), making sure the Users table is uploaded early on is important.

1. Application
2. Users
3.Membership
4.Paths
5.PersonalizationAllUsers
6.PersonalizationPerUser
7.Profile
8.Roles
9.Events
10.UsersInRoles (watch out for FK constraints to Users and Roles)
11.SchemaVersions (identical between same EPiServer db versions)

Create users and roles from scratch

Override the access protection to EPiServer admin until valid admin user and user groups are created:

Find location elements in web.config and comment out:

Comment out the <authorization> element entirely (removes the access protection). The same for location:

Goto http://mysite.local/epi/CMS/Admin/Default.aspx or similar for epi admin area.

Create new roles in admin: (these are EPiServer defaults)
WebEditors
WebAdmins

Add your admin user  to WebEditors and WebAdmins group.

Goto admin “Set access rights”

Set correct access rights for editors and admin groups.
Check lower checkbox to make descendant content inherit the rights.
Also “Everyone” group should have Read access on root and downwards.

Set this in web.config:

PagestartId should be root id.

Uncomment the authorization for /epi and /epi/cms/admin (enable the authorization again).

Login with you admin user
Create a start page or import an episerver export xml file.
Run  [mysite.local]/epi/CMS/Admin/IndexContent.aspx to update search index if imported content.

Goto site settings in admin and point out the page as start page. And/or set in web.config

(usually becomes 4)

Goto site settings in admin and point out the all site hosts (dev, test, prod etc).
Add more users such as editors and adminstrators that need access to site.

How to fetch the row count for all tables in a SQL SERVER database

The following SQL will get you the row count of all tables in a database:

The output will be a list of tables and their row counts.

If you just want the total row count across the whole database, appending:

will get you a single value for the total number of rows in the whole database.

Source: How to fetch the row count for all tables in a SQL SERVER database – Stack Overflow

How to migrate a SQL Server database to a lower version

…there are a few options that can help us to downgrade the database from a higher version of SQL Server to a lower version SQL Server. These options include:

  • Generate Scripts wizard of SQL Server Management Studio
  • SQL Server Integration Services
  • Custom scripting and BCP

In this tip we will use the Generate Scripts wizard of SQL Server Management Studio.

Source: How to migrate a SQL Server database to a lower version

Deleting Data in SQL Server with TRUNCATE vs DELETE commands

TRUNCATE TABLE is a statement that quickly deletes all records in a table by deallocating the data pages used by the table. This reduces the resource overhead of logging the deletions, as well as the number of locks acquired; however, it bypasses the transaction log, and the only record of the truncation in the transaction logs is the page deallocation. Records removed by the TRUNCATE TABLE statement cannot be restored. You cannot specify a WHERE clause in a TRUNCATE TABLE statement, it is all or nothing. The advantage to using TRUNCATE TABLE is that in addition to removing all rows from the table it resets the IDENTITY back to the SEED, and the deallocated pages are returned to the system for use in other areas.

In addition, TRUNCATE TABLE statements cannot be used for tables involved in replication or log shipping, since both depend on the transaction log to keep remote databases consistent.

TRUNCATE TABLE cannot used when a foreign key references the table to be truncated, since TRUNCATE statements do not fire triggers. This could result in inconsistent data because ON DELETE/UPDATE triggers would not fire. If all table rows need to be deleted and there is a foreign key referencing the table, you must drop the foreign key and then recreate it.

Source: Deleting Data in SQL Server with TRUNCATE vs DELETE commands