Tag: database

Creating A Contained Database – SQL 2012/2014

“A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. SQL Server 2014 helps user to isolate their database from the instance in 4 ways.

  • Much of the metadata that describes a database is maintained in the database. (In addition to, or instead of, maintaining metadata in the master database.)
  • All metadata are defined using the same collation.
  • User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL Server.
  • The SQL Server environment (DMV’s, XEvents, etc.) reports and can act upon containment information.”

MSDN Source reference – click here

T-SQL Code

EXEC sys.sp_configure N’contained database authentication’, N’1′

GO

RECONFIGURE WITH OVERRIDE

GO

USE [master]

GO

ALTER DATABASE [MyDemoDB] SET CONTAINMENT = PARTIAL WITH NO_WAIT

GO

 

Deprecated Database Features: SQL 2005 – SQL 2014 Links

Below are reference links to Microsoft documentation web pages listing deprecated database features from SQL 2005 to SQL 2014….  

2005   http://msdn.microsoft.com/en-us/library/ms143729(v=sql.90).aspx  

2008   http://msdn.microsoft.com/en-us/library/ms143729(v=sql.100).aspx  

2012  http://technet.microsoft.com/en-us/library/ms143729(v=sql.110).aspx

2014   http://msdn.microsoft.com/en-us/library/ms143729(v=sql.120).aspx  

Restore to SQL 2005 from SQL 2008

You cant restore a database backwards from SQL Server 2008 to a SQL 2005 Instance.

One trick you can use is to script the entire database, and ensure you select in the advanced options as shown below, SQL 2005.

Then run the script at the destination SQL 2005 instance.

Remember to change the database file paths in the output SQL script as needed.

e22

 

 

The database owner SID recorded

Issue:

A restored database from one instance to the other keeps showing below when attempting to say, alter a stored procedure.

The database owner SID recorded in the master database differs from the database owner SID recorded in database

Solution applied: Assuming XYZ is the name of the database and contosoadmin is the name of the sa or sysadmin domain account usually assigned as owner in the destination SQL instance. contoso here represents the domain name.

Run:

ALTER DATABASE XYZ SET TRUSTWORTHY ON

ALTER AUTHORIZATION ON Database::archive TO [contosoadmin]

Database Snapshot

Sample T-SQL Code for creating a database snapshot.

use AdventureWorks2012
go

sp_helpfile — used to get the logical database name needed for below

CREATE DATABASE AWSnapshot
ON (
NAME = ‘AdventureWorks2012_Data’,
FILENAME=’d:tlogAWSnapshot.ss’
)
AS SNAPSHOT OF AdventureWorks2012;

–using it
USE AWSnapshot;
GO
SELECT *
FROM Sales.SalesOrderDetail
WHERE ProductID = 53;

–reverting from snapshot
RESTORE DATABASE AdventureWorks2012
FROM DATABASE_SNAPSHOT=’AWSnapshot’;