UTC or GMT time in SQL Server 2005/2008

SQL Server 2005/2008 has a GETUTCDATE() function that retreives the local machine's date and time without the time zone offset, representing the current UTC time (Coordinated Universal Time) or GMT (Greenwich Mean Time).

If you need a more fractional seconds precision, use the SYSUTCDATETIME instead of GETUTCDATE()

Let's see the results when running some common date and time functions in SQL Server:

SELECT GETDATE() - 2009-03-28 13:08:37.553

SELECT SYSDATETIME() - 2009-03-28 13:08:37.5570000

SELECT GETUTCDATE() - 2009-03-28 07:38:37.557

SELECT SYSUTCDATETIME() - 2009-03-28 07:38:37.5570000

SELECT SYSDATETIMEOFFSET() - 2009-03-28 13:08:37.5570000 +05:30

As you can observe, the SYSDATETIME and SYSUTCDATETIME are used to display time with fractional second precision. The SYSDATETIMEOFFSET displays the time zone offset.

To find a difference between the Local time and UTC time use this query:

SELECT DATEDIFF(MINUTE,GetDate(),GetUTCDate());

Note: You may also want to checkout the SYSUTCDATETIME SWITCHOFFSET and TODATETIMEOFFSET functions

Verifying and Restoring a Differential Backup using T-SQL In SQL Server

In the previous post, we saw how to perform a Full and Differential Backup using T-SQL. In this post, let us quickly see how to restore a differential database using the backup.

Verifying BackUp

The first step is to verify the backup. Use the RESTORE command to verify the contents of the backup. There are certain arguments that can be used with the RESTORE command. The RESTORE FILELISTONLY lists the database and log files contained in the backup set.

RESTORE FILELISTONLY

FROM DISK = 'C:\MyBackUp\NW_Mar20_09.diff'

 

Output:

LogicalName    PhysicalName    Type    FileGroupName    Size    MaxSize    FileId    CreateLSN    DropLSN    UniqueId    ReadOnlyLSN    ReadWriteLSN    BackupSizeInBytes    SourceBlockSize    FileGroupId    LogGroupGUID    DifferentialBaseLSN    DifferentialBaseGUID    IsReadOnly    IsPresent    TDEThumbprint

Northwind    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\northwnd.mdf    D    PRIMARY    4456448    35184372080640    1    0    0    FEF51490-23C0-4472-A18D-E6E95B9FBF0E    0    0    393216    512    1    NULL    41000000033100113    F0FCC4D0-4C2A-40C3-941F-5934CA5E2939    0    1    NULL

Northwind_log    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\northwnd.ldf    L    NULL    1048576    2199023255552    2    0    0    37CE9E1A-E2ED-4706-8D08-3BE2C28EFF1A    0    0    0    512    0    NULL    0    00000000-0000-0000-0000-000000000000    0    1    NULL

I also find the RESTORE VERIFYONLY very useful to check if the backup set is complete and the entire backup is readable.

 

RESTORE VERIFYONLY

FROM DISK = 'C:\MyBackUp\NW_Mar20_09.diff'

WITH FILE = 1,

LOADHISTORY

 

Output:

The backup set on file 1 is valid.

You can read more about the other RESTORE Arguments over here.

Restoring Backup

Once we have verified the back up, we can now go ahead and restore the Full and Differential Backup using the following T-SQL

-- Restore the Full BackUp

RESTORE DATABASE Northwind

FROM DISK = 'C:\MyBackUp\NW_Mar20_09.bak'

WITH NORECOVERY, REPLACE

 

 

-- Now Restore the differential database backup

RESTORE DATABASE Northwind

   FROM DISK = 'C:\MyBackUp\NW_Mar20_09.diff'

   WITH FILE = 2,

   RECOVERY

GO

In a similar manner, you can even restore a transactions log backup.

How to do a Full and Differential Database Backup in SQL Server

Regular Backup's of your database is very important! A lot of people know how to do a Full and Differential backup using the SQL Server Management Studio (SSMS). It is as simple as opening SSMS > Right click Database > Tasks > BackUp. However not many are familiar doing the same operation using T-SQL.

Here's how to use T-SQL to do a Full and Differential Backup of your database in SQL Server 2005/2008

Note: In order to do a differential backup, a backup of the database needs to exists first since a differential backup copies all the data and log info that have changed since the last backup. So let us do a Full backup first

-- Full database backup needs to exist

-- before a Differential backup is taken

BACKUP DATABASE Northwind

   TO DISK = 'C:\MyBackUp\NW_Mar20_09.bak'

   WITH DESCRIPTION = 'First BackUp Of NW',

   INIT

GO

INIT parameter overwrites existing backups preserving the media header.
DESCRIPTION is for keeping notes about the backup

Note: The Folder 'MyBackUp' should exist before you

and now the Differential backup

-- Create a differential db backup

-- appending the backup to the full backup

BACKUP DATABASE Northwind

   TO DISK = 'C:\MyBackUp\NW_Mar20_09.diff'

   WITH DIFFERENTIAL,

   NOINIT,

   STATS= 50

GO

STATS gives additional info about the progress during a backup. A sample is shown below in bold:

66 percent processed.
Processed 48 pages for database 'Northwind', file 'Northwind' on file 3.
100 percent processed.
Processed 1 pages for database 'Northwind', file 'Northwind_log' on file 3.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 49 pages in 0.095 seconds (3.957 MB/sec).

A Simple Stored Procedure to Find Foreign Key Dependencies on Your Table in SQL Server 2005/2008

One of the criterias while dropping a table is that the ForeignKey Dependencies on the table should be dropped first. But how to you programmatically find out the Foreign Key Dependecies on your table using T-SQL. Here's a simple stored procedure that shows the dependencies of the table passed to it

USE Northwind

GO

CREATE PROCEDURE [dbo].[usersp_FindTableDependency]

@tblName varchar(50)

AS

SELECT fk.name, OBJECT_NAME(fk.parent_object_id) as DepTable

FROM sys.foreign_keys fk

inner join sys.tables st on st.[object_id] = fk.referenced_object_id

WHERE st.name = @tblName

In Order to test this procedure:

 

EXEC usersp_FindTableDependency 'Employees'

Produces the result

Name                                DepTable

FK_Orders_Employees                    Orders

FK_EmployeeTerritories_Employees    EmployeeTerritories

FK_Employees_Employees                Employees

Retrieve information about Database Restore for all Databases in SQL Server 2005/2008

If you want to find out details like when were all the databases last restored along with some more information like who restored it, recovery model etc., use the msdb..restorehistory and msdb..backupset tables as shown below:

SELECT rh.destination_database_name, rh.user_name, bk.first_lsn,

rh.restore_date, bk.backup_start_date, bk.recovery_model

FROM msdb..restorehistory rh

INNER JOIN msdb..backupset bk ON rh.backup_set_id = bk.backup_set_id

WHERE rh.destination_database_name IN

(SELECT name from sys.sysdatabases)

AND bk.type = 'D'

Output:

destination_database_name    user_name    first_lsn    restore_date    backup_start_date    recovery_model

Northwind    Suprotim-PC\Suprotim    41000000016800052    2009-03-14 17:58:36.653    2009-03-14 17:58:18.000    SIMPLE

The msdb..backupset is a very useful table and you can also get important details like the LSN (Log Sequence Number) used for a differential backup set.

Delete BackUp and Restore History for databases in SQL Server 2005/2008

The msdb database stores details of all backup and restore operations, in tables. If for some reason, you want to delete the backup and restore details of a particular database, use the sp_delete_database_backuphistory stored procedure.

The following query deletes all entries for the 'PictureAlbum' database in the backup-and-restore history tables

USE msdb;

GO

EXEC sp_delete_database_backuphistory 'PictureAlbum';

In order to delete backup and restore details for all the databases older than a specified date, use the sp_delete_backuphistory stored procedure. The query shown below deletes the historical data for all databases from the current datetime.

USE msdb;

GO

DECLARE @dt as datetime

SELECT @dt = GETDATE();

EXEC sp_delete_backuphistory @dt;

If you want to specify an earlier date, do it this way

USE msdb;

GO

EXEC sp_delete_backuphistory '03/10/09';

Set a database to read only mode using SQL Server 2005/2008

In this post, we will see how to set your SQL Server 2005/2008 database in a read only mode. Since there are two seperate ways of doing it for SQL Server 2005 as well as SQL Server 2008, we will create seperate queries for them.

SQL Server 2005

In order to set your SQL Server 2005 database in a read only mode, first make sure there are no users connected to the database. Once you are sure of that, use the sp_dboption stored procedure to modify database settings

Use the following query. In the query, we make the database read only after first checking if there are no users connected to the db. The Variable @CntConn = 0 means there are no users connected.

DECLARE @CntConn smallint

SELECT @CntConn = count(spid) FROM sys.sysprocesses

WHERE db_name(dbid) = 'Northwind'

 

if @CntConn < 1

EXEC sp_dboption "Northwind", "read only", "TRUE";

PRINT @CntConn

If someone now attempts to change any data in the database, he is encountered with the following error message



To set the database back to write only mode, use this query:

EXEC sp_dboption "Northwind", "read only", "FALSE";

Note: Now if there are a few users connected and you want to disconnect them forcefully, then check this post of mine Obtain Exclusive Access to Database While Performing Maintenance Tasks in SQL Server


SQL Server 2008

sp_dboption does work in SQL Server 2008, however it should not be used. According to BOL,

This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use ALTER DATABASE instead.

So to make a database read only in SQL Server 2008, use this query

USE master;

GO

ALTER DATABASE Northwind

SET READ_ONLY;

GO

Convert Number to Hours and Minutes in SQL Server

In one of my previous posts http://www.sqlservercurry.com/2009/03/convert-integer-to-date-and-time-in-sql.html, I had demonstrated how to convert integer to Date and Time. I got a mail from one of my readers who asked me if I could calculate hours and seconds from an integer field.

Here's how to do so:

DROP Table #SampleTable

CREATE Table #SampleTable (

[numasdate] int

);

 

Insert Into #SampleTable Values('9436');

Insert Into #SampleTable Values('537');

Insert Into #SampleTable Values('9323');

Insert Into #SampleTable Values('12525');

 

-- To calculate Total Hours and Minutes

Select numasdate,

Cast(numasdate / 60 as Varchar) + ' hours ' +

Cast(numasdate % 60 as Varchar) + ' minutes'

as [TotalHoursAndMinutes]

From

#SampleTable

Output:

9436    157 hours 16 minutes

537        8 hours 57 minutes

9323    155 hours 23 minutes

12525    208 hours 45 minutes

List All Tables with their Modification date in SQL Server

If you wish to find out when a table in a database was modified, use this query. This query lists all the tables in the database with the modification date

 USE Northwind

 SELECT name, modify_date FROM Sys.Tables

 Where object_id IN

 (SELECT object_id FROM sys.tables)

 AND type = 'U'

OUTPUT:

TableName            ModifyDate

Categories            2009-01-23 14:16:14.900

Customers            2009-01-23 14:16:15.103

Shippers            2009-01-23 14:16:14.790

Suppliers            2009-01-23 14:16:14.900

Orders                2009-01-23 14:16:14.790

Products            2009-01-23 14:16:14.900

Order Details        2009-01-23 14:16:13.653

CustomerCustomerDemo2009-01-23 14:16:15.107

CustomerDemographics2009-01-23 14:16:15.103

Region                2009-01-23 14:16:15.157

Territories            2009-01-23 14:16:15.180

EmployeeTerritories    2009-01-23 14:16:15.190

CustomersTemp        2009-02-20 17:37:34.083

Employees            2009-01-23 14:16:15.180

List all the Stored Procedures of a Database and their Definitions using T-SQL in SQL Server 2005/2008

One way to Script the Stored Procedures of your SQL Server 2008 database is to use the SQL Server Management Studio (SSMS). Open SSMS > Your Database > Programmability > Stored Procedures > Right Click Stored Procedure Name > 'Script Stored Procedure As' > 'Create To'

The other way to script objects of your database in SQL Server 2005/2008 is to use the Microsoft SQL Server Database Publishing Wizard 1.1.

However if you need to do the same task programmatically using T-SQL, then here's the T-SQL that will help you do so:

USE NORTHWIND

 

SELECT obj.Name as SPName,

modu.definition as SPDefinition,

obj.create_date as SPCreationDate

FROM sys.sql_modules modu

INNER JOIN sys.objects obj

ON modu.object_id = obj.object_id

WHERE obj.type = 'P'

This query will list down all the stored procedures and their definitions of your database. In order to save the results of this SELECT statement to a text file, check this post of mine

Save SELECT query output to a text file

Convert Integer to Date and Time in SQL Server

One of my users recently had a requirement where he had a field in the database that stored datetime as integers. The integers represented a date calculated keeping '1/1/2000 ' as the base date. He wanted to convert that integer back into date, however keeping '1/1/2000 00:00:00' as the base date. Here's how the requirement was achieved:

DROP Table #SampleTable

CREATE Table #SampleTable (

[numasdate] int

);

 

Insert Into #SampleTable Values('99933436');

Insert Into #SampleTable Values('55232337');

Insert Into #SampleTable Values('92323323');

Insert Into #SampleTable Values('111222525');

 

SELECT dateadd(ss,[numasdate],'1/1/2000') as ConvertToDate

FROM #SampleTable;

Output

2003-03-02 15:17:16.000

2001-10-01 06:18:57.000

2002-12-04 13:22:03.000

2003-07-11 07:08:45.000

Fetching all records for the previous day in SQL Server

Here's a query that lets you fetch all records for the previous day that falls between the time duration of 12 to 12

USE Northwind

DECLARE @PrevDate AS DateTime

SET @PrevDate = '1996-08-15'

SELECT OrderID, CustomerID, EmployeeID, OrderDate

FROM ORDERS

WHERE OrderDate < Cast(Convert(varchar,@PrevDate,101) as datetime)

and OrderDate >=  Cast(Convert(varchar,@PrevDate,101) as datetime) - 1

Results:

10280    BERGS    2    1996-08-14 00:00:00.000

10281    ROMEY    4    1996-08-14 00:00:00.000

Find the length of the longest text in a column

If you have a requirement to find out the longest text in a varchar, nvarchar or a text column, then here's how to do so:

 

USE PUBS

SELECT MAX(DATALENGTH(title)) as TextLength FROM titles

In order to print the longest text in a descending order of the length of text, here's how to do so:

 

USE PUBS

SELECT title, DATALENGTH(title) as TextLength FROM titles

group by title

order by TextLength desc

Generic SQL Server Stored Procedure to display Table Names of any Database passed to it

I was recently playing around with a generic stored procedures that can list the table names and stored procedure of any database name that is passed to it. Here's the procedure

CREATE PROCEDURE [dbo].[usersp_GetTablesSP]

@DatabaseName VARCHAR(50)

AS

BEGIN

DECLARE @DBSql NVARCHAR(200)

SET @DBSql = 'USE '+ @DatabaseName +' ; SELECT * FROM Sys.Objects WHERE Type IN (''U'', ''P'') ORDER BY type_desc'

 

EXEC sp_executesql @DBSql

END

To run it -

EXEC usersp_GetTablesSP 'Pubs'

or

EXEC usersp_GetTablesSP 'Northwind'

Running the procedure displays the list of stored procedure and tables in that database.

USE TOP Operator and Store Results in a Variable using SQL Server

A user had a query where he wanted to use the TOP Operator and store the results of this operation in a variable to be used later. Here's how to do so:

I am using the 'Customers' table of the Northwind database

DECLARE @CustomerID nchar(5)

SELECT TOP 1 @CustomerID = CustomerID

From Customers

Where Country = 'Germany'

ORDER BY CustomerID DESC

 

PRINT @CustomerID