MS SQL

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:\temp\eSignatur.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:\tmp\sqlcertificate' WITH PRIVATE KEY ( FILE = 'c:\tmp\sqlprivatekey',
	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;
alter database SignProxy modify file (name = SignProxy,
	filename ='C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\SignProxy.mdf');
alter database SignProxy modify file (name = SignProxy_Log,
	filename ='C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\SignProxy_log.ldf');
alter database SignProxy set online;
					  
-- Verify
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'SignProxy');

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%l\Microsoft\Microsoft SQL Server Data

Restore alongside existing database

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

-- restore to alternate destination
RESTORE DATABASE eSignatur
	FROM DISK = 'c:\temp\eSignatur.bak'
	WITH MOVE 'eSignatur' TO 'c:\eSignatur\db\tt_eSignatur_Data.mdf',
	MOVE 'eSignatur_log' TO 'c:\eSignatur\db\tt_eSignatur_log.ldf',
	FILE = 1,
	RECOVERY;

Restore to new empty database

USE master;
RESTORE DATABASE eSignatur
	FROM DISK = 'c:\temp\eSignatur.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';