MSSQL Survival Guide

Add column

ALTER TABLE [dbo].[SubscriptionProductVersions] ADD IsCreditRatingRequired BIT;
UPDATE [dbo].[SubscriptionProductVersions] SET IsCreditRatingRequired=0;
ALTER TABLE [dbo].[SubscriptionProductVersions] ALTER COLUMN IsCreditRatingRequired BIT NOT NULL;

Remove column

ALTER TABLE [dbo].[AmortizedLoans] DROP COLUMN IsCancelled;

Rename column

EXEC sp_rename 'old_table_name.[oldColumnName]', 'newColumnName', 'COLUMN'

Copy column

update dbo.Table set customerBaseId = customerId where customerBaseId is null;

Remove procedure

DROP (FUNCTION | PROCEDURE) [dbo].[GetAccountFirstConsumption];

Rename table

sp_rename 'old_table_name', 'new_table_name'

Constraints

ALTER TABLE [dbo].[XXX] NOCHECK CONSTRAINT ALL   -- disable constraints
ALTER TABLE [dbo].[XXX] CHECK CONSTRAINT ALL     -- enable constraints

Drop foreign key

ALTER TABLE [dbo].[AccountTable] DROP FK_AccountTableParentId_AccountTableId

Copy table

-- disable constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- copy table
insert into dbo.NewTable select * from [Mvno].[dbo].[bs_NewTable]

-- enable constraints
exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Backup

USE eSignatur;
BACKUP DATABASE eSignatur
	TO DISK = 'c:tempeSignatur.Bak' WITH FORMAT,
	MEDIANAME = 'C_SQLServerBackups',
	NAME = 'Full Backup of eSignatur';

Database encryption

See http://msdn.microsoft.com/en-us/library/cc278098(v=sql.100).aspx and http://ask.sqlservercentral.com/questions/28447/testing-tde-encryption-sql-2008.html..

USE master
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'B3ngal$kTig3r'
GO

CREATE CERTIFICATE SQLCertificate WITH SUBJECT = 'SQL Certificate'
GO

BACKUP CERTIFICATE SQLCertificate TO FILE = 'c:tmpsqlcertificate' WITH PRIVATE KEY ( FILE = 'c:tmpsqlprivatekey',
	ENCRYPTION BY PASSWORD = 'B3ngal$kTig3r' )
GO

USE eSignatur
GO

CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE SQLCertificate
GO

ALTER DATABASE eSignatur SET ENCRYPTION ON
GO

SELECT db_name(database_id), encryption_state, percent_complete, * FROM sys.dm_database_encryption_keys
GO

Database encryption_state 2 means that the encryption process has begun. Database encryption_state 3 means that the encryption process has completed.

Move database files

From http://technet.microsoft.com/en-us/library/ms345483.aspx.

ALTER DATABASE SignProxy SET OFFLINE
GO

ALTER DATABASE SignProxy MODIFY FILE (name = SignProxy,
	filename ='C:Program FilesMicrosoft SQL ServerMSSQL11.SQLEXPRESSMSSQLDATASignProxy.mdf')
GO

ALTER DATABASE SignProxy MODIFY FILE (name = SignProxy_Log,
	filename ='C:Program FilesMicrosoft SQL ServerMSSQL11.SQLEXPRESSMSSQLDATASignProxy_log.ldf');
GO

ALTER DATABASE SignProxy SET ONLINE
GO

-- Verify
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'SignProxy')
GO

If the database reports that it cannot open more instances

  1. Open Task Manager
  2. Stop sqlserver.exe
  3. Delete the SQLEXPRESS folder from %LOCALAPPDATA%lMicrosoftMicrosoft SQL Server Data

Restore alongside existing database

-- see files used (use below)
USE master;
RESTORE FILELISTONLY
	FROM DISK = 'c:tempeSignatur.bak';

-- restore to alternate destination
RESTORE DATABASE eSignatur
	FROM DISK = 'c:tempeSignatur.bak'
	WITH MOVE 'eSignatur' TO 'c:eSignaturdbtt_eSignatur_Data.mdf',
	MOVE 'eSignatur_log' TO 'c:eSignaturdbtt_eSignatur_log.ldf',
	FILE = 1,
	RECOVERY;

Restore to new empty database

USE master;
RESTORE DATABASE eSignatur
	FROM DISK = 'c:tempeSignatur.Bak' WITH FILE = 1,
	RECOVERY;

Login error after restore

You may have to rebind a login to a user in the database:

USE [database-name];
ALTER USER user WITH LOGIN = user;

Compute database size

select
	SQL_Server_Version = @@version,
	SQL_Server_Edition = serverproperty('Edition'),
	SQL_Server_Name = serverproperty('ServerName'),
	Database_Name = a.Name,
	Maximum_Database_Size_MB = case
	when serverproperty('EngineEdition') in (1, 4) then
		case
			when cast(serverproperty('ProductVersion') as nvarchar(128)) like '8.%' then '2048 MB'
			when cast(serverproperty('ProductVersion') as nvarchar(128)) like '9.%' then '4096 MB'
			when cast(serverproperty('ProductVersion') as nvarchar(128)) like '10.0%' then '4096 MB'
		else '10240 MB'
		end else
			'Effectively no limit' end,
		Data_Size_MB = convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
		Available_Growth_MB = case
		when serverproperty('EngineEdition') in (1, 4) then
		case
			when cast(serverproperty('ProductVersion') as nvarchar(128)) like '8.%' then 2048
			when cast(serverproperty('ProductVersion') as nvarchar(128)) like '9.%' then 4096
			when cast(serverproperty('ProductVersion') as nvarchar(128)) like '10.0%' then 4096
		else 10240
		end
		end - convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2))
	from
		sys.sysfiles a
	where
		a.name not like '%_log';

Trim database

use esignatur;

-- Size before
select
	Data_Size_MB = convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2))
from
	sys.sysfiles a
where
	a.name not like '%_log';

-- Trim
delete from CrmUpdates where SentToCrm is not null and created < GetDate() - 30;
delete from Emails where Status != 0 and created < GetDate() - 30;
delete from EventLogs where created < GetDate() - 30;
delete from ValidateDocumentEntities where created < GetDate() - 30;
delete from WebSessions where LastLogin < GetDate() - 30;
delete from XmlLogs where created < GetDate() - 30;

-- Size after
select
	Data_Size_MB = convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2))
from
	sys.sysfiles a
where
	a.name not like '%_log';

MSSQL 2017 for Linux in a Docker container

Make a backup of the database you want to install into the docker container. The install Docker, start and login.

In the steps below, the database from the file umbraco.bak is imported into to the database my.db. A user, MyUser is and is aligned with the login MyUser from the source-database.

docker pull microsoft/mssql-server-linux:2017-latest
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=YourStrong!Passw0rd" --name sql2017 -p 1433:1433 -v sql2017data:/var/opt/mssql -d microsoft/mssql-server-linux:2017-latest
docker exec -it sql2017 mkdir /var/opt/mssql/backup
docker cp umbraco.bak sql2017:/var/opt/mssql/backup
docker exec -it sql2017 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P YourStrong!Passw0rd

... and now some SQL magic. In the current prompt replace the '???' with the names from the FILELISTONLY command:

RESTORE FILELISTONLY FROM DISK = '/var/opt/mssql/backup/umbraco.bak'
go
RESTORE DATABASE [my.db] FROM DISK = '/var/opt/mssql/backup/umbraco.bak' WITH MOVE '???.umbraco' TO '/var/opt/mssql/data/???.umbraco.mdf', MOVE '???.umbraco_log' TO '/var/opt/mssql/data/???.umbraco.ldf'
go
SELECT Name FROM sys.Databases
go

... the last command just to check that the database was created succesfully.

Align user:

use [my.db]
go
create login MyUser with password = 'SomePassword', CHECK_POLICY=off
go
alter user MyUser with login = MyUser
go

... and that's it.