Archive

Archive for December, 2012

2012 in review

December 31, 2012 Leave a comment

The WordPress.com stats helper monkeys prepared a 2012 annual report for this blog.

Here’s an excerpt:

About 55,000 tourists visit Liechtenstein every year. This blog was viewed about 190,000 times in 2012. If it were Liechtenstein, it would take about 3 years for that many people to see it. Your blog had more visits than a small country in Europe!

Click here to see the complete report.

Using FullText search with FileTables in SQL Server 2012

December 21, 2012 5 comments

In my previous post sometime back I talked about FILETABLES, [link]. This is a new feature introduced in SQL Server 2012, which is built on top of FILESTREAM.

I also talked about how we can use them to store files and retrieve the information from them.

Here, in this post we will see how we can search in the files stored in FileTables using an existing feature i.e. “FullText Search”.

So, let’s check this by an example. I’ll be using the same code from my previous post to create a new Database and Filetables.

–> You will need to create a directory to allocate a location in any of your drive, lets’s create it in D: Drive, D:\SQL_Server2012\FileTables
Now execute the code below, which will create a new DataBase and a FileTable to store documents:

USE [master]
GO

-- Create a new Database with Filestream enabled:
CREATE DATABASE [newFileStreamDB]
 CONTAINMENT = NONE
 ON  PRIMARY ( 
	NAME = N'newFileStreamDB', 
	FILENAME = N'D:\SQL_Server2012\DATA\newFileStreamDB.mdf' , 
	SIZE = 5120KB , 
	MAXSIZE = UNLIMITED, 
	FILEGROWTH = 1024KB 
	),
 FILEGROUP newFileStreamGroup CONTAINS FILESTREAM DEFAULT (
	NAME = newFileStreamGroupFiles,
	FILENAME= 'D:\SQL_Server2012\FileTables\Files', 
	MAXSIZE = UNLIMITED 
	)
 LOG ON ( 
	NAME = N'newFileStreamDB_log', 
	FILENAME = N'D:\SQL_Server2012\DATA\newFileStreamDB_log.ldf' , 
	SIZE = 1024KB , 
	MAXSIZE = 2048GB , 
	FILEGROWTH = 10% 
	)
 WITH FILESTREAM (
	NON_TRANSACTED_ACCESS = FULL,
	DIRECTORY_NAME = N'FileTables' 
	)
GO

-- Use the new Database:
USE [newFileStreamDB]
GO

-- Creating a new FileTable
CREATE TABLE [dbo].[firstFileTable] 
	AS FILETABLE ON [PRIMARY] 
	FILESTREAM_ON [newFileStreamGroup]
WITH (
	FILETABLE_DIRECTORY = N'myfirstFileTable', 
	FILETABLE_COLLATE_FILENAME = Latin1_General_CI_AI
)
GO

To check the FileTables link with Drive:
– Goto database in Object Explorer, Right click on the FileTable and click on “Explore FileTable Directory” option. This will open up a shared folder which is on the Virtual Network Name of you computer.
– Copy-Paste some documents/files in the folder above where FileTable is configured.

Check details of files maintained in the FileTable:

SELECT * FROM [firstFileTable]

–> Now we will see how we can search inside those files stored in Folder and in the FileTables:
We will be using the FullText feature of SQL Server to configure it.

-- Creating FullText Catalog:
CREATE FULLTEXT CATALOG CATG_firstFileTable AS DEFAULT;

-- Creating FullText Index on the table and column:
CREATE FULLTEXT INDEX ON dbo.firstFileTable
    (name LANGUAGE 2057,
    file_stream TYPE COLUMN file_type LANGUAGE 2057)
    KEY INDEX PK__firstFil__5A5B77D5C6F38A8E				-- REPLACE this with new PK created
    ON CATG_firstFileTable
    WITH
        CHANGE_TRACKING AUTO,
        STOPLIST = SYSTEM;

Now let’s search inside those files by using FullText Search Functions:

-- Using FREETEXT() function:
SELECT *
FROM dbo.firstFileTable
WHERE
FREETEXT (file_stream, '<Search_String>') -- Put the search string here, which is in your document/file.

-- Using CONTAINS() function:
SELECT *
FROM dbo.firstFileTable
WHERE
CONTAINS (file_stream, '<Search_String>') -- Put the search string here, which is in your document/file.
-- Final Cleanup
USE [master]
GO

DROP DATABASE [newFileStreamDB]
GO

NOTE: You also might need to check what all Document-Filters are currently supported in your SQL Server.
If not, then you can also download some filters from following [link].

-- Check existing Document-Filters:
select * from sys.fulltext_document_types

-- Load Services after installing missing Document-Filters
EXEC sp_fulltext_service @action='load_os_resources', @value=1;
EXEC sp_fulltext_service 'restart_all_fdhosts';

For more on FullText Search check following MSDN link: http://msdn.microsoft.com/en-us/library/ms142571.aspx

New THROW statement in SQL Server 2012 (vs RAISERROR)

December 20, 2012 7 comments

Newly introduced THROW keyword in SQL server 2012 is an improvement over the existing RAISERROR() statement. Yes, it’s single ‘E’ in RAISERROR.

Both RAISERROR & THROW can be used in T-SQL code/script to raise and throw error within a TRY-CATCH block. Check my previous post for TRY-CATCH block, [link].

–> With RAISERROR developers had to use different ERROR_xxxx() system functions to get the error details to pass through the RAISERROR() statement, like:
– ERROR_NUMBER()
– ERROR_MESSAGE()
– ERROR_SEVERITY()
– ERROR_STATE()

let’s see an example:

-- Using RAISERROR()
DECLARE  
	@ERR_MSG AS NVARCHAR(4000)
	,@ERR_SEV AS SMALLINT
	,@ERR_STA AS SMALLINT

BEGIN TRY
	SELECT 1/0 as DivideByZero
END TRY
BEGIN CATCH
	SELECT @ERR_MSG = ERROR_MESSAGE(),
		@ERR_SEV =ERROR_SEVERITY(),
		@ERR_STA = ERROR_STATE()
	SET @ERR_MSG= 'Error occurred while retrieving the data from database: ' + @ERR_MSG

	RAISERROR (@ERR_MSG, @ERR_SEV, @ERR_STA)  WITH NOWAIT
END CATCH
GO

Output:
(0 row(s) affected)
Msg 50000, Level 16, State 1, Line 15
Error occurred while retrieving the data from database: Divide by zero error encountered.

The RAISERROR() can take first argument as message_id also instead of the message. But if you want to pass the message_id then it has to be in sys.messages

–> With THROW the benefit is: it is not mandatory to pass any parameter to raise an exception.
Just using the THROW; statement will get the error details and raise it, as shown below:

-- Using THROW - 1
BEGIN TRY
	SELECT 1/0 as DivideByZero
END TRY
BEGIN CATCH
	THROW;
END CATCH
GO

Output:
(0 row(s) affected)
Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.

As you see in the Output above, the error message thrown is the default one. But you can also add your customized message, we will see below.

IMP NOTE: THROW will show the exact line where the exception was occurred, here the line number is 2. But RAISERROR will show the line number where the RAISERROR statement was executed i.e. Line 15, but not the actual exception position.

Also passing the message_id won’t require it to be stored in sys.messages, let’s check this:

-- Using THROW - 2
DECLARE  
	@ERR_MSG AS NVARCHAR(4000)      
	,@ERR_STA AS SMALLINT      

BEGIN TRY
	SELECT 1/0 as DivideByZero
END TRY
BEGIN CATCH
	SELECT @ERR_MSG = ERROR_MESSAGE(),
		@ERR_STA = ERROR_STATE()

	SET @ERR_MSG= 'Error occurred while retrieving the data from database: ' + @ERR_MSG;

	THROW 50001, @ERR_MSG, @ERR_STA;
END CATCH
GO

Output:
(0 row(s) affected)
Msg 50001, Level 16, State 1, Line 14
Error occurred while retrieving the data from database: Divide by zero error encountered.

But if you parameterize the THROW statement as above it will not show the actual position of exception occurrence, and the behavior will be same as RAISERROR(). As with RAISERROR() you’ve to provide mandatory params, so there is no way to get the actual position of Line where the error occurred.

As per MSBOL following are the difference between RAISERROR & THROW:

RAISERROR statement

THROW statement

If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.

The error_number parameter does not have to be defined in sys.messages.

The msg_str parameter can contain printf formatting styles.

The message parameter does not accept printf style formatting.

The severity parameter specifies the severity of the exception.

There is no severity parameter. The exception severity is always set to 16.

NOTE: As per MS BOL for exception handling in new development work THROW must be used instead of RAISERROR.

SQL DBA – Move user Database (.mdf & .ldf files) to another drive

December 17, 2012 3 comments

 
Here in this post we will see how to move user created Database to an another drive from the default drive.
 

–> The first 2 ways I’ve discussed in my other blog posts, please check the links below:

1. Detach & Attach task, video

2. Backup & Restore task, link, video
 

3. By using ALTER command, Syntax below:

ALTER DATABASE SET OFFLINE;
GO

— Manually move the file(s) to the new location.

ALTER DATABASE Database name
MODIFY FILE (
NAME = ‘Database name’,
FILENAME = ‘New Location’
)
GO

ALTER DATABASE Database name SET ONLINE;
GO

 


 

–> Script used in above video:

Step #1. Create a new Database in SSMS with name “ManDB”.

Step #2. Check the current location of database:

select * from ManDB.sys.database_files

--ManDB		D:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ManDB.mdf
--ManDB_log	D:\Program Files (x86)\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ManDB_log.ldf

Step #3. Take the database offline, so that we can move files from one drive to another:

ALTER DATABASE ManDB SET OFFLINE;
GO

Step #4. Move the file(s) to the new location manually.

Step #5. Change the system catalog settings:

ALTER DATABASE ManDB
MODIFY FILE (
	NAME = 'ManDB',
	FILENAME = 'E:\SQLDBs\ManDB.mdf' 
)
GO

ALTER DATABASE ManDB
MODIFY FILE (
	NAME = 'ManDB_log',
	FILENAME = 'E:\SQLDBs\ManDB_log.ldf' 
)
GO

Step #6. Take the database back online:

ALTER DATABASE ManDB SET ONLINE;
GO

Step #7. Check the new location:

select * from ManDB.sys.database_files

--ManDB		E:\SQLDBs\ManDB.mdf
--ManDB_log	E:\SQLDBs\ManDB_log.ldf

SQL DBA – When was the Database restored (Query)?

December 13, 2012 Leave a comment

 
When you are working on your DEV database system, very often you restore databases from PRODUCTION systems in order to get fresh and latest data.
 

Sometimes questions crops in our mind like:

1. When exactly the backups were restored on DEV system.

2. Which user restored the backup on DEV.

3. When the backups were taken on PROD system.

4. What was the backup process start time and end time, so that you are sure you have correct snapshot of data.

5. What was the corresponding database name on PROD, as on DEV you can restore with a different name.

6. What was the backup file location while restoring the databases.
 

All these questions can be answered by the below sample query:

USE msdb
GO

SELECT 
	rs.destination_database_name AS TargetDBName, 
	rs.restore_date				 AS RestoreDate, 
	rs.user_name				 AS RestoreBy,
	bs.backup_start_date		 AS	BackupStartDate, 
	bs.backup_finish_date		 AS BackupFinishDate, 
	bs.database_name			 AS SourceDBName, 
	bmf.physical_device_name	 AS SourceDBFiles
FROM msdb.dbo.restorehistory rs
INNER JOIN msdb.dbo.backupset bs
	ON rs.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily bmf 
	ON bs.media_set_id = bmf.media_set_id 
ORDER BY rs.restore_date DESC

db-backup-restore-details

You can use the above query and tweak as per your needs.


Categories: DBA Stuff