A command-line client for SQL Server with auto-completion and syntax highlighting
Source: dbcli/mssql-cli: A command-line client for SQL Server with auto-completion and syntax highlighting
My blogposts and bookmarking regarding Web development, C#, ASP.NET, EPiServer, CSS, Html
A command-line client for SQL Server with auto-completion and syntax highlighting
Source: dbcli/mssql-cli: 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:
Source: View or Change the Default Locations for Data and Log Files | Microsoft Docs
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)
Source: Import a BACPAC File to Create a New User Database | Microsoft Docs
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
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.
Run deployment center as administrator -> select create Sql 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)
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:
1 2 3 |
<location path="epi"> |
Comment out the <authorization> element entirely (removes the access protection). The same for location:
1 2 3 |
<location path="epi/CMS/admin"> |
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:
1 2 3 |
<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
1 2 3 |
<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 #countsThe 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 #countswill 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
…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
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.
Source: Deleting Data in SQL Server with TRUNCATE vs DELETE commands
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