A command-line client for SQL Server with auto-completion and syntax highlighting
Open SQL Server Management Studio and connect to the db server then follow these steps:
(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.
1. Access to a bacpac file locally / on azure storage
2. Use the UI wizard in Management Studio. (See below)
- Connect to the instance of SQL Server, whether on-premise or in SQL Database.
- In Object Explorer, right-click on Databases, and then select the Import Data-tier Application menu item to launch the wizard.
- Complete the wizard dialogs: Introduction Page. Import Settings Page.
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.
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.
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)
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:
<siteSettings pageRootId="1" pageStartId="1"
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
<siteSettings pageRootId="1" pageStartId="4"
(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.
The following SQL will get you the row count of all tables in a database:
1234567891011 CREATE TABLE #counts(table_name varchar(255),row_count int)EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESCDROP TABLE #counts
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:
123 SELECT SUM(row_count) AS total_row_count FROM #counts
will get you a single value for the total number of rows in the whole database.
…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.
I needed to search in all tables for a certain GUID, I used this SP in MS SQL Server 2016: Find a GUID (or anything) in ANY table in your database! | CSharpner.com
See heading: Using SQL Server Export / Import wizard
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.
SQL Server and the .NET Framework are based on different type systems. For example, the .NET Framework Decimal structure has a maximum scale of 28, whereas the SQL Server decimal and numeric data types have a maximum scale of 38.
Source: SQL Server Data Type Mappings