SQL Server DBA

Friday, April 4, 2014

Finding Last Backup Time for All Database – Last Full, Differential and Log Backup

SET NOCOUNT ON
GO
SET quoted_identifier OFF
DECLARE @dbname AS VARCHAR(80)
DECLARE @msgdb AS VARCHAR(100)
DECLARE @dbbkpname AS VARCHAR(80)
DECLARE @dypart1 AS VARCHAR(2)
DECLARE @dypart2 AS VARCHAR(3)
DECLARE @dypart3 AS VARCHAR(4)
DECLARE @currentdate AS VARCHAR(10)
DECLARE @server_name AS VARCHAR(30)
SELECT @server_name = @@servername
SELECT @dypart1 = DATEPART(dd,GETDATE())
SELECT @dypart2 = DATENAME(mm,GETDATE())
SELECT @dypart3 = DATEPART(yy,GETDATE())
SELECT @currentdate= @dypart1 + @dypart2 + @dypart3
PRINT "#####################################################################"
PRINT "# SERVERNAME : "+ @server_name + " DATE : "+ @currentdate +"#"
PRINT "#####################################################################"
PRINT "DatabaseName Full Diff TranLog"
PRINT "##########################################################################################################################################"
SELECT SUBSTRING(s.name,1,50) AS 'DATABASE Name',
b.backup_start_date AS 'Full DB Backup Status',
c.backup_start_date AS 'Differential DB Backup Status',
d.backup_start_date AS 'Transaction Log Backup Status'
FROM MASTER..sysdatabases s
LEFT OUTER JOIN msdb..backupset b
ON s.name = b.database_name
AND b.backup_start_date =
(SELECT MAX(backup_start_date)AS 'Full DB Backup Status'
FROM msdb..backupset
WHERE database_name = b.database_name
AND TYPE = 'D') -- full database backups only, not log backups
LEFT OUTER JOIN msdb..backupset c
ON s.name = c.database_name
AND c.backup_start_date =
(SELECT MAX(backup_start_date)'Differential DB Backup Status'
FROM msdb..backupset
WHERE database_name = c.database_name
AND TYPE = 'I')
LEFT OUTER JOIN msdb..backupset d
ON s.name = d.database_name
AND d.backup_start_date =
(SELECT MAX(backup_start_date)'Transaction Log Backup Status'
FROM msdb..backupset
WHERE database_name = d.database_name
AND TYPE = 'L')
WHERE s.name <>'tempdb'
ORDER BY s.name

Wednesday, January 22, 2014

Contained Databases in SQL Server 2012

Contained databases are a new feature in SQL Server 2012 and are defined on MSDN Library as ”a database that is isolated from other databases and from the instance of SQL Server that hosts the database”.

The containment setting of a database can be NONE, PARTIAL or FULL. But only NONE and PARTIAL are supported on SQL Server 2012.

There are three types of containment hinted at in SQL Server 2012:

NONE: This default containment mode is what exists prior to SQL Server 2012. There are no boundaries defined or visible.
PARTIAL: With partially contained databases, you have the ability to define clearer boundaries between databases and the server, making it easier for metadata to be hosted within the databases. This, in turn, makes SQL Server databases more portable and less dependent on underlying hosts.
FULL: Fully contained databases are only alluded to in SQL Server 2012 BOL in a few locations and aren't currently available as an option. It's assumed, however, that full containment will enable greater database portability and potentially allow for strict enforcement of containment boundaries as a means of fully isolating databases from each other and from the underlying host.

Benefits and characteristics.

The following are some of the benefits and characteristics that contained databases have:

1. They make easier to migrate databases from one server to another. Errors related to orphan users are no longer an issue with contained databases, since a contained database user can now be created without an associated login.

2. Authentication can now occur at the database level.

3. Contained database users can be Windows and SQL Server authentication users.

4. A contained database user can access only contained database objects. They cannot access system databases and cannot access server objects.

5. Metadata is stored on the contained database and not stored on system databases. This makes contained databases more portable than the databases we know.

Disadvantages and limitations.

Some disadvantages and limitations are the following:

1. There are some security concerns. A database owner can create contained database users without the permission of a DBA. The possibility of denial of service attacks exist with contained databases using AUTO_CLOSE option. For security best practices about contained databases, please see the references shown at the end of the article.2. Partially contained databases cannot use replication, change data capture, change tracking, numbered procedures, schema-bound objects that depend on built-in functions with collation changes. You may find more limitations on the references shown at the end of this article.Requirements of contained databases.
1 It is required to enable contained databases on the instance.2. The contained database needs to be added to the connection string or specified when connecting via SQL Server Management Studio.Step-by-step instructions on how to create a contained database.

To be able to create contained databases on a SQL Server 2012 instance, we need to enable the contained database authentication option on the instance.

Open SQL Server 2012 Management Studio, connect to the instance, make a right click on the name of the instance on Object Explorer, select the Advanced page on the "Select a page" panel, and set to true the "Enable Contained Databases" option.

Alternatively, you can use sp_configure system stored procedure to enable contained databases on the instance, as shown below.

EXEC sp_configure 'show advanced', 1
GO
RECONFIGURE
GO

EXEC sp_configure 'contained database authentication', 1GO
RECONFIGURE
GO

When a database is created the "Containment type" should be set to "partial" to make the database a contained database, as shown below.

This can be done using T-SQL too, as shown below.

A contained database allows the creation of a database user that is not associated to an instance login. A contained database user can be created expanding the Security folder on the contained database, making a right click on the Users folder and selecting the "New User" option.

Next, set the user type to "SQL user with password", assign a user name, set the password for the database user and specify the default schema for the user.

Specify the roles for this user on the database and click OK.

If you would like to create the contained database user using T-SQL, please see the example below.CREATE USER [MorilloCD2User]
WITH PASSWORD =N'p@ssw0rd123',
DEFAULT_SCHEMA=[dbo]GO

To connect a contained database user to a contained database, the database name should be specified on the connection string. If you are using SQL Server Management Studio, on the Connect to Server dialog specify the authentication, specify the user name, provide the password, click on the Options button and specify the database name on the "Connect to database" combo box.

Thursday, June 20, 2013

Display the size of all tables in a database

Below is the query to get the list of tables with sizes in a database:

CREATE PROCEDURE getAllTablesSize
AS
BEGIN
      DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS;
      CREATE TABLE
            #temp (
                   [name] varchar(250),
                   [rows] varchar(50),
                 [reserved] varchar(50),
                 [data] varchar(50),
                 [index_size] varchar(50),
                 [unused] varchar(50)
                   );
      INSERT #temp EXEC ('sp_msforeachtable ''sp_spaceused ''''?''''''');
      UPDATE #temp
      SET
            [rows] = LTRIM(RTRIM(REPLACE(t.rows,'KB',''))),
            [reserved] = LTRIM(RTRIM(REPLACE(t.reserved,'KB',''))),
            [data] = LTRIM(RTRIM(REPLACE(t.data,'KB',''))),
            [index_size] = LTRIM(RTRIM(REPLACE(t.index_size,'KB',''))),
            [unused] = LTRIM(RTRIM(REPLACE(t.unused,'KB','')))
      FROM #temp AS t
      SELECT
            SUM(CAST([reserved] as decimal))/1024 AS 'Total reserved MB',
            SUM(CAST([data] as decimal))/1024 AS 'Total data MB',
            SUM(CAST([index_size] as decimal))/1024 AS 'Total index_size MB',
            SUM(CAST([unused] as decimal))/1024 AS 'Total unused MB'
      FROM
            #temp
      SELECT
            [name] ,
            CAST([rows] as INT)'rows' ,CAST([reserved] as INT)/1024 'reserved MB',
            CAST([data] as INT)/1024 'data MB' ,
            CAST([index_size]/1024 as INT)'index_size MB',
            CAST([unused] as INT)/1024 'unused MB'
      FROM
            #temp
    ORDER BY name
      DROP TABLE #temp;
END
GO
--EXECUTE getAllTablesSize

Thursday, May 30, 2013

SQL DBA reference websites

SQL Server DBA

Friday, April 4, 2014

Finding Last Backup Time for All Database – Last Full, Differential and Log Backup

Wednesday, January 22, 2014

Contained Databases in SQL Server 2012

Contained Databases in SQL Server 2012

Thursday, June 20, 2013

Display the size of all tables in a database

Thursday, May 30, 2013

SQL DBA reference websites

Blog Archive

Followers