Restore backup of database (.bak) using Sql Server 2005

If you intend to create a back up of your database in Server A and restore it on a Server B, here's how to go about it. Create a .bak file (Open SSMS > Database > Right Click your database > Tasks > BackUp > Specify the destination).

To do it using script, check my blog over here.

Once the .bak is created, copy this file from Server A to a Server B. We will assume that the file has been copied at the location ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\" at Server B. It can any location that you desire.

How to restore the .bak file

Use this query:

RESTORE DATABASE [SouthWind]
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\South.BAK'
WITH REPLACE,
MOVE 'SouthWind_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SouthWind_Data.MDF',
MOVE 'SouthWind_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SouthWind_Log.LDF'

where FromDisk is where the .bak file is kept on Server B and MOVE specifies the location of the data and log file.

Find all tables with or without an IDENTITY column

The SYS.IDENTITY_COLUMNS is a quiet a handy view. Doing a SELECT * FROM SYS.IDENTITY_COLUMNS will return you all the columns that are identity columns for the current database. The object_id information returned from the SELECT query can be used to return the table name and using this, we can in turn return all the tables that are IDENTITY columns.

To find all tables with IDENTITY Columns

USE [YOURDBNAME]
SELECT (SCHEMA_NAME(schema_id) + '.' + name) as SchemaTable
FROM sys.tables
WHERE [name] IN
(
SELECT OBJECT_NAME(OBJECT_ID) AS TABLENAME
FROM SYS.IDENTITY_COLUMNS
)
ORDER BY SchemaTable;
GO

To find all tables without IDENTITY Columns

USE [YOURDBNAME]
SELECT (SCHEMA_NAME(schema_id) + '.' + name) as SchemaTable
FROM sys.tables
WHERE [name] NOT IN
(
SELECT OBJECT_NAME(OBJECT_ID) AS TABLENAME
FROM SYS.IDENTITY_COLUMNS
)
ORDER BY SchemaTable;
GO

We could have used only the view to find out the tables with or without the identity columns. The sys.tables was used to get the schema as well.

How to send an email using SQL Server 2005

With SQL Server 2005, there is no need to use MAPI client to send emails. Fellow developers who have used MAPI in the previous versions of SQL Server are well aware of the challenges it had. However in Sql Server 2005, we can now use the Database Mail to send emails.

[Note: I assume you have set up and configured Database Mail. If not, check this link Database Mail Configuration Stored Procedures to use various stored procedures required to configure Database Mail. As an alternative, you could also use the SQL Server Surface area configuration tool to configure Database Mail]

Use the following script to send a mail from your Sql Server

USE [YourDB]
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'admin@xyz.com; xyz@xyz.com; pqr@xyz.com',
@body = 'Just testing the mail',
@subject = 'Sending Mail using Database Mail' ;
GO

Find all tables in a database without a primary key

If you are performing a database audit and want to quickly find out the tables not having a primary key, use this query :

USE [Your DB]

GO
SELECT TABLE_SCHEMA as 'SCHEMA', TABLE_NAME as 'NO_Primary_Key'
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_NAME NOT IN
( SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
) AND TABLE_TYPE = 'BASE TABLE'

How to check if a date is a valid date in Sql Server 2005

At times, in our stored procedures or sql queries, we need to check if the date specified in the variables is a valid date.

Use the ISDATE() function.

The ISDATE() function determines whether the variable or the expression contains a valid date. It returns 1(true) if the input expression is a valid date; otherwise, it returns 0 (false).

For eg:
DECLARE @dt varchar(10)
SET @dt = '02/21/08'
SELECT ISDATE(@dt)
-- Returns 1

DECLARE @dt varchar(10)
SET @dt = '13/21/08'
SELECT ISDATE(@dt)
-- Returns 0 as 13 is not a valid month

Recommended books for Sql Server 2005

Do you want to get started with programming in SQL Server 2005 and master it? Here are some recommendations written by none other than Itzik Ben-Gan :

Inside Microsoft® SQL Server™ 2005: T-SQL Querying
A comprehensive, hands-on reference for database developers and administrators, this book focuses on advanced querying techniques using Transact-SQL (T-SQL), the database programming language built into SQL Server

Inside Microsoft® SQL Server™ 2005: T-SQL Programming

Get a detailed look at the internal architecture of T-SQL with this comprehensive programming reference. Database developers and administrators get best practices, expert techniques, and code samples to master the intricacies of the programming language—solving complex problems with real-world solutions.

Repeat group of characters 'n' number of times

You can use the rich in-built function set of SQL Server 2005 to perform a variety of tasks. Let us explore how to repeat a group of characters 'n' number of times and also insert space in between two strings.

Repeat a group of characters 'n' number of times

Use the REPLICATE() function to repeat a character expression for a specified number of times

SELECT REPLICATE('-',15) as Underline
displays: the character '-' 15 times ---------------

Insert space in between two strings

SPACE() function returns a string of repeated spaces depending on the number specified

SELECT 'Far' + SPACE(10) + 'Away'
displays: 'Far         Away'

Generate your own serial numbers for a table

There are times when you would want to generate your own serial numbers for a query. For eg: If we take the Customers table in Northwind database, the CustomerID is not numeric Eg: 'ALFKI'.

If you want to generate your own serial number for the Customers table while displaying the records, use the ROW_NUMBER(). As mentioned in BOL "ROW_NUMBER() returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition"

Here is a query that will help you generate your own serial numbers for the CustomerID

SELECT (ROW_NUMBER() OVER ( ORDER BY CustomerID)) AS ID ,
CustomerID, ContactName, CompanyName, ContactTitle
FROM Customers

Display the size of all tables in Sql Server 2005

sp_spaceused returns number of rows, disk space reserved, and disk space used by a table. However when you have to return the space used by all the tables in a database, you have two options: One is to loop through all the tables and then pass the table name to the sp_spaceused procedure. The second is to use the undocumented sp_MSforeachtable procedure. We will explore both of these over here:

The Lengthy Way

USE yourdbname
DECLARE @TblNames Table
(
COUNTER INT IDENTITY(1,1),
tbl_name nvarchar(100) NULL
)
DECLARE @ROWCOUNT INT
DECLARE @I INT
DECLARE @str nvarchar(100)
SET @I = 1
INSERT INTO @TblNames(tbl_name) SELECT name FROM sys.Tables
SET @ROWCOUNT = @@ROWCOUNT
WHILE @I <= @ROWCOUNT
BEGIN
SELECT @str = tbl_name FROM @TblNames WHERE COUNTER = @I
EXEC sp_spaceused @str
SET @I = @I +1
END

Note: The advantage in taking the lengthy approach is that you can create another temporary table and sort the tables based on the space used.

The Short Way

USE yourdbname
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

Note: sp_MSforeachtable is an undocumented stored procedure

References :
http://msdn2.microsoft.com/en-us/library/ms188776.aspx
http://www.msnewsgroups.net/group/microsoft.public.dotnet.languages.csharp/topic37975.aspx
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm

Get the nth highest salary using Sql Server 2005

A very common requirement is to get the 1st highest, 2nd highest salary and so on, of an employee in an organization. Let us see how to do it:

Sample Table


DROP TABLE #Employees
GO

CREATE TABLE #Employees (EmpID int, EmpName varchar(10), Salary int)
INSERT #Employees SELECT 1,'Tim',345345
INSERT #Employees SELECT 2,'Jill',76845
INSERT #Employees SELECT 3,'Kathy',234545
INSERT #Employees SELECT 4,'Jack',74564
INSERT #Employees SELECT 5,'Harry',56756456
INSERT #Employees SELECT 6,'Arnol',75675
INSERT #Employees SELECT 7,'Dave',76566
INSERT #Employees SELECT 8,'Zinnade',58776
INSERT #Employees SELECT 9,'Iflar',345567
INSERT #Employees SELECT 10,'Jimmy',76766

Highest Salary Of an Employee

SELECT EmpName, Salary
from
(
SELECT EmpName, Salary, Row_Number() OVER(ORDER BY SALARY DESC) AS 'Salaries'
FROM #Employees
) emp
WHERE Salaries = 1

2nd highest salary


SELECT EmpName, Salary
from
(
SELECT EmpName, Salary, Row_Number() OVER(ORDER BY SALARY DESC) AS 'Salaries'
FROM #Employees
) emp
WHERE Salaries = 2

Similarly you can now find the nth maximum from any table.

Format dates in SSRS reports

Displaying dates in different formats in your reports is a very common requirement that one faces while working with Sql Server Reporting Services (SSRS).

Let us see how we can display dates in different formats :

=Format(Fields!myDate.Value, “M/d/yy”) - 2/11/08
=Format(Fields!myDate.Value, “MM/dd/yyyy”) - 02/11/2008
=Format(Fields!myDate.Value, “d-MMMM-yy”) - 11-December-08
=Format(Fields!myDate.Value, “d-MMM-yyyy”) - 11-Dec-2008 =Format(Fields!myDate.Value, “M/d/yyyy H:mm”) - 2/11/2008 13:50
=Format(Fields!myDate.Value, “MMM-dd-yyyy”) - Feb-11-2008

Find duplicates in a table and delete them using SQL Server 2005

It is a very common requirement to find duplicates in table based on a column or set of columns and then delete them. Let us see how to do that:

Sample Table

DROP TABLE #Customers
GO

CREATE TABLE #Customers (CustID int, CustName varchar(10), PostalCode int)
INSERT #Customers SELECT 1,'A',100
INSERT #Customers SELECT 1,'B',100
INSERT #Customers SELECT 2,'C',100
INSERT #Customers SELECT 3,'D',100
INSERT #Customers SELECT 4,'E',100
INSERT #Customers SELECT 5,'F',100
INSERT #Customers SELECT 5,'G',100
INSERT #Customers SELECT 6,'H',200
INSERT #Customers SELECT 7,'I',300

Find Duplicates in CustID

SELECT CustID
FROM #Customers
GROUP BY CustID HAVING count(*) > 1

returns you the duplicate CustID

Delete Duplicates in the Customer table

SET ROWCOUNT 1
SELECT NULL
WHILE @@rowcount > 0
DELETE cust
FROM #Customers as cust
INNER JOIN
(SELECT CustID
FROM #Customers
GROUP BY CustID HAVING count(*) > 1)
AS c ON c.CustID = cust.CustID
SET ROWCOUNT 0

SELECT * FROM #Customers

Performance Monitoring with SQL Server 2008

There are some new performance monitoring tools for Microsoft SQL Server 2008. The SQL Server Performance Studio Data Collector feature is something that you would want to know about. Also there are some new counters introduced with a new feature called "Resource Governor".

Here are some good resources to get you started:

SQL Server 2008: New Performance Monitoring and Troubleshooting Using Management Studio

Performance Monitoring with SQL Server 2008

SQL Server 2008 Performance and Scalability Technologies

How to encrypt a stored procedure in SQL Server 2005

At times, it is needed that you encrypt the text of stored procedures containing sensitive information. SQL Server provides the WITH ENCRYPTION to encrypt the text of the stored procedure.

CREATE procedure [dbo].[Ten Most Expensive Products Encyrpt]
WITH ENCRYPTION AS
SET ROWCOUNT 10
SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC

Once the stored procedure has been created WITH ENCRYPTION, attempts to view the stored procedure returns a message specifying that the text is encrypted:

EXEC sp_helptext usp_SEL_EmployeePayHistory

'The text for object 'Ten Most Expensive Products Encyrpt' is encrypted.'

One note of caution. Save the original text of the stored procedure before encrypting it, as there is no straightforward way to decode the encrypted text. One hack is to attach a debugger to the server process and retrieve the decrypted procedure from memory at runtime.

How to find out the users logged in to a SQL Server 2005 database

In order to find out the users logged in to a database, use the system view called sys.sysprocesses.

select * from sys.sysprocesses

returns you a host of information about the login name, Windows user name, processes running on an instance of SQL Server etc.. Check out the hostname, nt_username, loginame, nt_domain columns returned on executing the above query.

Read more about sys.sysprocesses over here

http://msdn2.microsoft.com/en-us/library/ms179881.aspx

You can also try out the stored procedures sp_who

EXEC sp_who

EXEC sp_who2

sp_who provides information about current users, sessions, and processes in an instance of SQL Server

Using the Dedicated Administrator Connection in SQL SERVER 2005

SQL SERVER 2005 introduces the Dedicated Administrator Connection (DAC) which is a special diagnostic connection for administrators to be used when the server is slow or not responding. Using this connection, admins can access SQL server to perform diagnostic tasks; such as killing the Sql server process causing problems or running T-SQL queries.

DAC can only be run by members of the 'sysadmin' role.

There are two ways of using the DAC :

1. Through the sqlcmd utility using a special administrator switch (-A)

Eg:

sqlcmd -S servername -U username -P yourpassword -A

or

sqlcmd -A -d Northwind -E -S SUPROTIM\MYSERVER (where -E specifies Windows Authentication and -S specifies server\instance)

2. By prefixing 'admin:' to the instance name

Eg: sqlcmd -Sadmin:SUPROTIM\MYSERVER

You can also use SQL Server Management Studio by connecting to 'admin:SUPROTIM\MYSERVER'

By default, the DAC does not allow network connections. To enable remote connections, either use the Sql Server Surface Area Configuration Tool and enabling DAC. Alternatively you can use the 'sp_configure' procedure in the following manner:

sp_configure 'remote admin connections', 1;

GO

RECONFIGURE;

GO

Check the following : http://msdn2.microsoft.com/en-us/library/ms189595.aspx