Find the history of SQL database backups.

You need to add database name where I put DBNAME in this script. It will give you full, differential and log backup history. I know how important it is in DBA's daily routine.
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name



FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE


--(CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)

--and
msdb.dbo.backupset.database_name = 'DBNAME'
and msdb..backupset.type = 'D'
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date desc

Find out from SQL Management studio - Who is part of network group id?

I have script which tells you who is part of groupid. It helps to find some login issue.


declare @DomainGroupName varchar(50)

select @DomainGroupName = 'domain\GroupIDName'

exec xp_logininfo @DomainGroupName,'members'

Script User, Database and Object Permission in SQL Server 2005 and onward

Generate Create User Script on a Database

 

SELECT 'CREATE USER [' + name + '] for login [' + name + ']'

from sys.database_principals

where

 Type = 'U'

  and

name <>  'dbo'

Generate Database role permission on a Database

 

 

 

SELECT 'EXECUTE sp_AddRoleMember ''' + roles.name + ''', ''' + users.name + ''''

from sys.database_principals users

inner join sys.database_role_members link

on link.member_principal_id = users.principal_id

inner join sys.database_principals roles

on roles.principal_id = link.role_principal_id

 

 

Generate Object Level peremission on a Database

 

 

if object_id('dbo.tempPermissions') is not null

Drop table dbo.tempPermissions

Create table tempPermissions(ID int identity , Queries Varchar(255))

Insert into tempPermissions(Queries)

select 'GRANT ' + dp.permission_name collate latin1_general_cs_as

+ ' ON ' + s.name + '.' + o.name + ' TO ' + dpr.name

FROM sys.database_permissions AS dp

INNER JOIN sys.objects AS o ON dp.major_id=o.object_id

INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id

INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id

WHERE dpr.name NOT IN ('public','guest')

declare @count int, @max int, @query Varchar(255)

set @count =1

set @max = (Select max(ID) from tempPermissions)

set @query = (Select Queries from tempPermissions where ID = @count)

while(@count < @max)



begin
 exec(@query)

set @count += 1

set @query = (Select Queries from tempPermissions where ID = @count)



end
 select * from tempPermissions

drop table tempPermissions 

I have struggle a lot to find out those scripts but I always try to make like simple for other DBA's.
let me know if you have any questions.


How to send TEXT when sql job fails.

I was wondering how to prioritize failure jobs. DBA gets tons of emails everyday and we don't want to keep checking out emails for failing jobs. It would be lot better if we get text message when some high priority job fails. Read following

Most carriers have “SMS gateways” which take email messages from the Internet and deliver them to their customers’ cell phones as SMS text messages. The trick is that you need to know what carrier the recipient’s phone is on — it’s not enough to know their phone number. That’s because the carrier determines what the email address of the receiving phone is going to be. For example, Cingular phones’ address are all “something@cingularme.com” while Verizon phones are “something@vtext.com.”

Sound complicated? It’s not. All you really need to do is find your carrier in the list below, and then use the pattern shown there to figure out your email address. If you want to send email to a friend’s phone, just ask them which carrier they use, and off you go!

(Note: For the major carriers, I’ve included a link to a page with more detailed information on how SMS works with that carrier, how much it costs, and where you can find more information. In the list below, just click any carrier’s highlighted name to find out more.)

Alltel
[10-digit phone number]@message.alltel.com
Example: 2125551212@message.alltel.com

AT&T (formerly Cingular)
[10-digit phone number]@txt.att.net
Example: 2125551212@txt.att.net

For multimedia messages, use [10-digit-number]@mms.att.net
Example: 2125551212@mms.att.net

Boost Mobile
[10-digit phone number]@myboostmobile.com
Example: 2125551212@myboostmobile.com

Cricket Wireless
[10-digit phone number]@sms.mycricket.com
Example: 1234567890@sms.mycricket.com

For multimedia messages: [10-digit phone number]@mms.mycricket.com
Example: 1234567890@mms.mycricket.com

Nextel (now part of Sprint Nextel)
[10-digit telephone number]@messaging.nextel.com
Example: 7035551234@messaging.nextel.com

Sprint (now Sprint Nextel)
[10-digit phone number]@messaging.sprintpcs.com
Example: 2125551234@messaging.sprintpcs.com

T-Mobile
[10-digit phone number]@tmomail.net
Example: 4251234567@tmomail.net

Verizon
[10-digit phone number]@vtext.com
Example: 5552223333@vtext.com

Virgin Mobile USA
[10-digit phone number]@vmobl.com
Example: 5551234567@vmobl.com

Other U.S. and Canadian carriers:

Bell Canada: [10-digit-phone-number]@txt.bellmobility.ca

Centennial Wireless: [10-digit-phone-number]@cwemail.com

Cellular South: [10-digit-phone-number]@csouth1.com

Cincinnati Bell: [10-digit-phone-number]@gocbw.com

Metro PCS: [10-digit-phone-number]@mymetropcs.com or [10-digit-phone-number]@metropcs.sms.us

Qwest: [10-digit-phone-number]@qwestmp.com

Rogers: [10-digit-phone-number]@pcs.rogers.com

Suncom: [10-digit-phone-number]@tms.suncom.com

Telus: [10-digit-phone-number]@msg.telus.com

U.S. Cellular: [10-digit-phone-number]@email.uscc.net

thanks to http://sms411.net/how-to-send-email-to-a-phone/ for such a great article.

How to migrate MSDB SSIS packages from one server to another....

Coming soon..

How to script sql server jobs and migrate to another server ....

1. First of all open SSMS and connect to your sql server from where you want to copy jobs.

2. Then click on View on menu bar and then select Object Explorer Details

3.  Select all jobs from Object Explorer Details Windows
4.  Right Click on selected jobs and then select Script Job As -> Create To -> New Query window

5. Now copy script from new query window and then paste it to new server.

6. Before executing scripts you need to make sure followings.

1. Make sure email operator are exist on new server.
2. Make sure SSIS packages are exist on new server.
3. Make sure Security Credentials are created on new server.
4. Make sure users are present on new server as well.

7. Execute script on new server and now you have migrated jobs in new server.

Let me know if you have any comment.

SQL 2012 - Contained Database

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 2012 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.

Requirements of contained databases.

• It is required to enable contained databases on the instance.

• The contained database needs to be added to the connection string or specified when connecting via SQL Server Management Studio

Partial containment is enabled using the CREATE DATABASE and ALTER DATABASE statements or by using SQL Server Management Studio. 

How to configure contained database:

EXEC SP_CONFIGURE 'show advanced options',1

GO

RECONFIGURE

GO

EXEC SP_CONFIGURE 'contained database authentication',1

GO

RECONFIGURE

GO

USE [master]

GO

CREATE DATABASE [ContainedDB] CONTAINMENT = PARTIAL

Found some disadvantages of Contained Database:

Connection strings

Connection strings to a contained database must explicitly specify the database in the connection string. You can no longer rely on the login's default database to establish a connection; if you don't specify a database, SQL Server is not going to step through all the contained databases and try to find any database where your credentials may match.

---

Cross-db queries

Even if you create the same user with the same password in two different contained databases on the same server, your application will not be able to perform cross-database queries. The usernames and passwords may be the same, but they're not the same user. The reason for this? If you have contained databases on a hosted server, you shouldn't be prevented from having the same contained user as someone else who happens to be using the same hosted server. When full containment arrives (likely in the version after SQL Server 2012), cross-database queries will absolutely be prohibited anyway. I highly, highly, highly recommend that you don't create server-level logins with the same name as contained database users, and try to avoid creating the same contained user name in multiple contained databases.

---

Synonyms

Most 3- and 4-part names are easy to identify, and appear in a DMV. However if you create a synonym that points to a 3- or 4-part name, these do not show up in the DMV. So if you make heavy use of synonyms, it is possible that you will miss some external dependencies, and this can cause problems at the point where you migrate the database to a different server. I complained about this issue, but it was closed as "by design." (Note that the DMV will also miss 3- and 4-part names that are constructed via dynamic SQL.)

---

Password policy

If you have created a contained database user on a system without a password policy in place, you may find it hard to create the same user on a different system that does have a password policy in place. This is because the CREATE USER syntax does not support bypassing the password policy. I filed a bug about this problem, and it remains open. And it seems strange to me that on a system with a password policy in place, you can create a server-level login that easily bypasses the policy, but you can't create a database user that does so - even though this user is inherently less of a security risk.

---

Collation

Since we can no longer rely on the collation of tempdb, you may need to change any code that currently uses explicit collation or DATABASE_DEFAULT to use CATALOG_DEFAULT instead. See this BOL article for some potential issues.

---

IntelliSense

If you connect to a contained database as a contained user, SSMS will not fully support IntelliSense. You'll get basic underlining for syntax errors, but no auto-complete lists or tooltips and all the fun stuff. I filed a bug about this issue, and it remains open.

---

SQL Server Data Tools

If you are planning to use SSDT for database development, there currently isn't full support for contained databases. Which really just means that building the project won't fail if you use some feature or syntax that breaks containment, since SSDT currently doesn't know what containment is and what might break it.

---

ALTER DATABASE

When running an ALTER DATABASE command from within the context of a contained database, rRather than ALTER DATABASE foo you will need to use ALTER DATABASE CURRENT - this is so that if the database is moved, renamed, etc. these commands don't need to know anything about their external context or reference.

---

A few others

Some things you probably shouldn't still be using but nonetheless should be mentioned in the list of things that aren't supported or are deprecated and shouldn't be used in contained databases:

• numbered procedures
• temporary procedures
• collation changes in bound objects
• change data capture
• change tracking
• replication