Feeds:
Posts
Comments

Archive for February, 2014

Last day of the last year, I came across a situation where I was stuck for a while even though we had backups. However later on found a solution and felt so excited ;).
Note : Please do not use this script for any negative purpose.

Problem :
As I mentioned earlier that last year on 31st Dec 2013, we were busy helping our client for yearend closing and suddenly we got an error from year closing procedure. Given below are the details.
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘tbl_Yeartodate_Value’.

Now, the problem is this table gets updated every evening and we do not why it is not available in the database. We do have each and everyday backup. However, we do not know when it was dropped and who dropped it. We started looking for the solution because it is not possible to restore all backups and check one by one.

Solution :
Some of our support engineers proposed the third party solution to recover the table directly because we do not know in which backup this table exists. I bought some time from the client and started doing my development for recovery and after few hours, I developed a script that could give the dropped object name along with the dropped date and dropped by name as well. However, I made sure that the client will not take any action against the person who dropped it.

Let me create few objects and drop it to demonstrate the solution.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
--Create Schema
CREATE SCHEMA HumanResources
GO

--Create Table
CREATE TABLE HumanResources.[tbl_Employee](
      [Sno] [int] NOT NULL,
      [ID] nvarchar(6) Not NULL ,
      [name] [varchar](50) NOT NULL,
      [Date of Birth]  datetime not null,
      [Weight] [int] NULL)

GO

--Create View
Create View HumanResources.[vw_Employee]
as
Select * from HumanResources.[tbl_Employee]
GO

--Create Procedure
Create Procedure HumanResources.[usp_Employee]
@EmployeeID nvarchar(6)
as
Select * from HumanResources.[tbl_Employee] Where [ID] =@EmployeeID
GO

--Create function
Create FUNCTION HumanResources.[fn_Employee](@EmployeeID nvarchar(6))
RETURNS int
AS
Begin
    Declare @Weight int
    Select  @Weight = [Weight]
        from HumanResources.[tbl_Employee] Where ID =@EmployeeID
    Return  @Weight
End
GO

--Create Trigger
CREATE TRIGGER HumanResources.trg_Employee
ON [tbl_Employee]
FOR INSERT
AS RAISERROR (50009, 16, 10)
GO

--Drop all objects
DROP TRIGGER HumanResources.trg_Employee
DROP VIEW HumanResources.[vw_Employee]
DROP PROCEDURE HumanResources.[usp_Employee]
DROP FUNCTION HumanResources.[Fn_Employee]
DROP TABLE HumanResources.[tbl_Employee]
GO

Once you dropped all object, you need to create the given below script to recover who dropped what object at what time.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
--DROP PROCEDURE Recover_Dropped_Objects_Detail_Proc
--GO
CREATE PROCEDURE Recover_Dropped_Objects_Detail_Proc
@Date_From DATETIME='1900/01/01',
@Date_To DATETIME ='9999/12/31'
AS

;WITH CTE AS (Select B.name AS [Schema Name]
,REPLACE(SUBSTRING(A.[RowLog Contents 0],14,LEN(A.[RowLog Contents 0])),0x00,0x) AS [Object Name]
,[Transaction ID]
,A.[RowLog Contents 0]
FROM fn_dblog(NULL,NULL) A
LEFT JOIN sys.schemas B
ON CONVERT(INT,SUBSTRING([RowLog Contents 0],2,2))= B.schema_id
WHERE A.[AllocUnitName] ='sys.sysschobjs.nc1'AND
A.[Transaction ID] IN (
SELECT DISTINCT [TRANSACTION ID] FROM  sys.fn_dblog(NULL, NULL)
WHERE Context IN ('LCX_NULL') AND Operation IN ('LOP_BEGIN_XACT')
AND [Transaction Name] LIKE '%DROP%'
AND CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To))

SELECT
[Schema Name]
,[Object Name]
,B.[Begin Time]  AS [Dropped Date & Time]
,C.[name] AS [Dropped By User Name]
FROM CTE A
INNER JOIN fn_dblog(NULL,NULL) B
ON A.[Transaction ID] =B.[Transaction ID]
AND Context IN ('LCX_NULL') AND Operation IN ('LOP_BEGIN_XACT')
AND [Transaction Name]LIKE '%DROP%'
INNER JOIN sys.sysusers  C ON B.[Transaction SID]=C.[Sid]
GO

Once you created the above stored procedure, lets execute it and get all the object names dropped during a particular interval, along with its dropped date and dropped by name.
Example :

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
EXEC Recover_Dropped_Objects_Detail_Proc  '2014/02/26','2014/02/26'
GO
--OUTPUT

who_dropped_what_object_at_what_time.1.1

Let me know if you came across this issue and its solution as well.

Read Full Post »

Sometimes lack of knowledge about the product irritates us. No matter, what product we are dealing with.
IntelliSence is one of the marvelous tools, that has been with us Since SQL Server 2008. It really helps us to speed up the development. However, sometimes it irritate as well ;). In SQL Server 2012, IntelliSence came up with two different modes inside to make our life more easier. Given below are the details.

  • Completion Mode
  • Suggestion Mode

Before proceeding with the explanation of these modes, I would like to enable Text Editor Toolbar to toggle between these two modes. Please note that it is not mandatory to enable text editor toolbar for toggling between modes but it is preferable to add because it helps us to see which mode we are in. Let me enable it step by step.

Step 1 :
First of all, you need to open SQL Server Management Studio (SSMS) and select Tools and Customize…  option as shown in the image below.

toggle suggestion and completion mode.2.1

Step 2 :
Once you select Customize… option, it will pop up Customize screen. You need to select Text Editor toolbar in it and press Close as shown below.

toggle suggestion and completion mode.2.2

Step 3 :
In this step, you can see that Text editor toolbar is available in SSMS and Toggles between suggestion and standard completion modes and mode button is highlighted as shown below.

toggle suggestion and completion mode.2.3

Once you enable the Text editor toolbar, let me explain what happens with each mode, with examples.

  • Completion mode : All of us are aware of completion mode because in earlier days completion mode was selected by default in IntelliSence. However, the problem with this mode is, if the object names are symmetrical in database (not necessary) and we search for desired object and by mistake if the criteria matches the other object name (not required), it will select the other object automatically and if this activity is continuous, it irritates us. Please note that, this mode is enabled by default in SQL Server. Given below is the screen image of completion mode in which Toggles between suggestion and standard completion modes button is switched OFF and it means that we are in completion mode.

toggle suggestion and completion mode.1.1

  • Suggestion Mode : This mode is good for the developers if they are working on symmetrical object name database, and this mode of intelliSence just suggest the object name for us, it does not select it automatically like completion mode. You can Press CTRL + ATL + Space bar to switch in Suggestion mode. Given below is the screen image of suggestion mode in which Toggles between suggestion and standard completion modes button is switched ON and it means you are in suggestion mode.

toggle suggestion and completion mode.1.2

Let me know if you used it in the real world examples and how it helped you.

Read Full Post »

Filetable is one of the new features shipped with SQL Server 2012. I have discussed its core functionality earlier in my articles. Today, I was trying to open one of the filetables using Explorer File Table Directory and it gave me given below error message.

file location cannot be opened.1.1

Let me explain the error and its solution in detail.

Error Message: The File location cannot be opened. Either access is not enabled or you do not have permissions for the same.

Solution:
If you look at the error description, it seems that either your access to this filetable is not enabled by the administrator or you do not have the permission to access this filetable. And I am a bit surprised because I logged in as an administrator, what next then ?

Let me explain the solution step by step.

Step 1 :
Click on the Start >>All Programs >>Microsoft SQL Server 2012 >>Configuration Tools >> SQL Server Configuration Manager, as shown in the image below.

filestream_disabled.1.1

Step 2 :
Once you click on SQL Server configuration manager, it will open SQL Server configuration manager options. It has two panes. Now you need to select SQL Server Services from the left hand pane and select the SQL Server Instance, right click on it and select Properties as shown in the image below.

filestream_disabled.1.2

Step 3 :
Once you select the Properties, it will open SQL Server Properties dialogue box. Now you need to find FILESTREAM Tab and select it.
Once you select it, you will find given below options as shown in the image.

    1. Enable FILESTREAM for Transact-SQL access.
    2. Enable FILESTREAM for file I/O access. 
    3. Allow remote clients to have streaming access to FILESTREAM data. 

file location cannot be opened.1.2

As you can see, the 2nd option (Enable FILESTREAM for file I/O access) has NOT been ENABLED and due to this reason, you cannot access filtable from SQL Server Management Studio (SSMS). Just enable this option and click Apply as shown in the picture below.

file location cannot be opened.1.3

Step 4 : 
Once you are done with SQL Server Configuration Manager, open SQL Server Management Studio and go to the same filetable and open it using Explorer FileTable Directory. This time it will not give you this error.

file location cannot be opened.1.4

Read Full Post »

Taking & maintaining back up is one of the core responsibilities of any Database Administrator. However, I came across some DBA’s that usually take the backup but not on a regular basis because it is a manual process. I strongly recommend that you start taking the backup even from day one after your deployment at the client site. Today, I handed over a SQL Server to the client after installation and immediately scheduled an automated backup as well. So thought of sharing this solution.

Let me schedule an automated backup to demonstrate step by step.

STEP 1 :
First of all, open the SQL Server Management Studio (SSMS) and check if SQL Server Agent is running or not. In my case it is not running due to this, it is showing red color sign in the icon of SQL Server Agent. Just right click on SQL Server Agent and select Start as shown in the image below.

schedule autobackup.1.1

Once you start, SQL Server Agent icon will be changed to green as shown below.

schedule autobackup.1.2

STEP 2 :
Remain in the SSMS, drill down Management and select Maintenance Plans, further right click on Maintenance Plans and select Maintenance Plan Wizard as shown in the image below.

schedule autobackup.1.3

STEP 3 :
Once you click on Maintenance Plan Wizard, it will pop up an information dialogue box. Click Next Button as shown below.

schedule autobackup.1.4

STEP 4 :
Once you click on Next Button, Plan Properties screen will open as shown below. Enter the maintenance plan name and press Change Button as shown in the image below.

schedule autobackup.1.5

STEP 5 :
Once you press Change Button, it will open a New Job Schedule Window, where you need to define the frequency, schedule date/ time & start date/ end date (no end date) as shown in the image below. Once you are done with the job schedule window press OK button and Next Button respectively.
Note : Always select the off peak time (after office working hours) for scheduler.

schedule autobackup.1.6

STEP 6 :
Now, you are at maintenance task window, here you need to select what type of maintenance you need to do. In our case we need to schedule a FULL database backup. Select the Back Up Database (Full) and press Next button as shown in the image below.

schedule autobackup.1.7

STEP 7 :
It is task order screen but you only select one task (Back Up Database (Full)). There is no need to do any thing in this screen. Just press Next button as shown below.

schedule autobackup.1.8

STEP 8 :
In this step you need to define the parameters of back up; mainly selection of database, location of database & verify integrity of database and press Next button as shown below.

schedule autobackup.1.9

STEP 9 :
Now, you are at report option that you probably need to view the results of this scheduler. This option is very helpful when it comes to debugging as shown below. Press Next Button.

schedule autobackup.1.10

STEP 10 :
Once you press Next button, the summary window will pop up and displays the summary of all configurations as shown below.

schedule autobackup.1.11

Once you press Finish Button, it will process your maintenance plan and will give you the status as shown below.

schedule autobackup.1.12

In addition to this, you can find your maintenance plan on your left hand side as shown below.

schedule autobackup.1.13

STEP 11 :
It seems that your maintenance plan has been done. Only one last step and that is, you need to rename the job. Just select SQL Server Agent and browse jobs, you will find one Backup.Maintenance.Subplan_1, right click on it and select properties as shown below.

schedule autobackup.1.14

STEP 12 :
You are in the job property window, you just need to rename your job name and press OK button as shown below.

schedule autobackup.1.15

Once you are done with all 12 steps you will find a daily backup at the defined time in a dedicated folder. In fact, it took me 5 minutes to configure but eventually it will make my life easier. Let me know how you guys are handling back ups.

Read Full Post »

Last Sunday, my support team received a backup from one of our customers and customer requested some activities for their backup. Once we saved the backup it was named testing backup.  The problem was, we deployed lots of databases for different applications at this client and we did not know that which database backup is he referring to. First of all it is not a best practice to name a backup like this, it must be proper naming conversion. However if you come across situation like this, there is a workaround to find the details about the backup without restoring it. But how ?

Given below is the script that will give you the details about the backup without restoring it to any database.

--This script is compatible with SQL Server 2005 and above.
USE master
GO
RESTORE FILELISTONLY
FROM DISK = N'C:\data\Testing Backup.bak'
GO

metadata of backup.1.1

In addition to the above information, if you need further details about the backup without restoring, you can use RESTORE HEADERONLY & RESTORE LABELONLY

Reference : MSDN

Read Full Post »

Today, I was searching for some script from my library and I came across this script (How do I find all the tables that do not have a primary key?) . I usually use this script, once I complete my deployment on any SQL Server to check if I missed to create primary key on any table.  There are multiple ways to achieve it. However, I will discuss three of them in this article.

METHOD 1 :
This is the shortest method to find all the tables that do not have a primary key. In this method, we need to use OBJECTPROPERTY to check each table property and filter it.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
SELECT
  SCHEMA_NAME(schema_id) AS [Schema name]
, name AS [Table name]
FROM sys.tables
WHERE
OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
GO
--OUPUT

donot have primary key.1.1

METHOD 2 :
This is one of my favorite methods because I do not need to remember any other view name, I can browse all the data from sys.objects.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
SELECT
  SCHEMA_NAME(schema_id) AS [Schema name]
, name AS [Table name]
FROM
sys.objects
WHERE [type]='U' AND object_id
NOT IN (
SELECT parent_object_id FROM sys.objects
WHERE [type]='PK'
)
GO
--OUPUT

donot have primary key.1.1

METHOD 3 :
In this method, we need to use two system views (sys.tables & sys.key_constraints) to achieve it.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
SELECT
 SCHEMA_NAME(schema_id) AS [Schema name]
, name AS [Table name]
FROM sys.tables
WHERE object_id NOT IN
(
SELECT parent_object_id
FROM sys.key_constraints
WHERE type = 'PK'
);
GO
--OUPUT

donot have primary key.1.1

Read Full Post »

Older Posts »