Tag: MS SQL Server 2005

Find your backup history in SQL Server

While managing multiple databases, it is often needed to check database backup history like when did we taken backup? Where did we take last few backups? What was the size of last few backup? And many other information.
I have setup maintenance plan for few database, few databases backup taken by third party software and all works on schedule on particular time even I keep one small T-SQL script handy to check all information I have mentioned above.
SELECT     
      bkSet.Backup_Start_Date as BackupStartDate,
      bkSet.Backup_Finish_Date as BackupEndDate,
    MedFam.Physical_Device_Name AS BackupPath,
    MedSet.Software_Name AS SoftwareUsedForBackup,
    bkSet.User_Name ASBackupTakenBy,
    bkSet.Server_Name AS ServerName,
    bkSet.Database_Name As DatabaseName,
    CASE bkSet.Type     
            WHEN ‘L’ THEN ‘TransactionLogBackup’
            WHEN ‘D’ THEN ‘FullBackup’
            WHEN ‘F’ THEN ‘FileBackup’
            WHEN ‘I’ THEN ‘DifferentialBackup’
        WHEN ‘G’ THEN ‘DifferentialFileBackup’
        WHEN ‘P’ THEN ‘PartialBackup’
        WHEN ‘Q’ THEN ‘DifferentialPartialBackup’
        ELSE NULL END AS BackupType,
    CAST((bkSet.Backup_Size/1048576) AS NUMERIC(10,2)) AS BackupSizeInMB
FROM       
      msdb..BackupMediaFamily MedFam
INNER JOIN 
      msdb..BackupMediaSet MedSet
ON
      MedFam.Media_Set_ID = MedSet.Media_Set_ID
INNER JOIN 
      msdb..BackupSet bkSet
ON
      bkSet.Media_Set_ID = MedSet.Media_Set_ID
WHERE     
      –keep your database name in condition
      bkSet.Database_Name = ‘Adventureworks’
AND       
      –put the date between which you want to find details of backup
      bkSet.Backup_Finish_Date BETWEEN ‘2011-07-01’ AND ‘2011-07-10’
ORDER BY   
      bkSet.Backup_Finish_Date DESC

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile”
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Generate SP or function definition in SQL Server 2005/2008

Well, today I would like to share one very quick and useful simple TSQL which can help you to generate script (definition) of stored procedure, trigger, functions etc. Generally you can do it with SSMS easily by right click on object and click on option like “Script Stored Procedure as->Create to->File”  or  “Script function as->Create to->File” etc. but if you want to generate script for many object at a time, there is one very small TSQL can come to your help which I myself used to use so many time.
Look at the TSQL below:
select so.name as ObjectName,sc.text as ObjectDefination from sysobjects so join syscomments sc on so.id=sc.id
–P for Stored Procedure
–FN for scalar function
–IF for Inlined table function
–TF for Table function
–TR for trigger
where so.type in (‘P’,‘FN’,‘IF’,‘TF’,‘TR’)
–if you want defination of specific function or SP, include below condition too
–and so.name=’YourSPorFunctionName’
Order by so.name,sc.colid
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Warning – some % of consumed workload has syntax errors. Check tuning log for more information in SQL Server 2008/2005

I have seen many SQL Developer used to use Database tuning advisor and many of them meet with warning like below.
30% of consumed workload has syntax errors. Check tuning log for more information.
You can see above error (percentage ratio may be changed in case to case) and think that your query is perfect and there is not error at all than why DTA (Database Tuning Advisor) showing syntax error?
Actually DTA is not much powerful to understand if you are using temp table in your stored procedure or calling sub stored procedure or may be using user define functions and it shows above warning. DTA works well only with simple Vanilla type plain SELECT statement.
So in that case, you have to find out the query on which you have doubts, run that query and records it in profiler and get help of DTA.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Upgrade from SQL Server 2000 to SQL Server 2005

There were major changes in architecture & engine of SQL Server 2005 as compare with SQL Server 2000. So as per my observation and experience in live environment, many different kind of error used to come while upgrading, I don’t mean that upgrade doesn’t work at all but personally I have came across so many different kind of error so rather than upgrading from SQL Server 2000 to SQL Server 2005, I would like to follow different approach which has been given below.

1.)    Detach all use databases (don’t need to have system databases with us)
2.)    Back up all the job if you have any in your SQL Server 2000
3.)    Create login transfer script so that all logins of your SQL Server 2000 works fine in SQL Server 2005 with same passwords. There are few different approaches for this but I would like to point out one of the Microsoft’s link for this task. http://support.microsoft.com/kb/246133
4.)    Now, this is the time to uninstall SQL Server 2000
5.)    Install SQL Server 2005 and upgrade it with latest hot fixes and patches
6.)    Attach all your databases which were detached from SQL Server 2000
7.)    Restore all jobs which were backed up from SQL Server 2000
8.)    Run the transfer login script which we have generated in step 3 above.

Isn’t it simple to go for? I find this easiest way to go for rather than upgrading the server. This is my personal point of view which I used to follow. The main reason of writing this article is, yesterday one of my friend was upgrading his SQL Server 2000 to 2005 and came across one weird error which he was not able to solve. I suggested him to follow these steps in future and also suggested few steps which might resolve the error he was facing. I used to get update from him today, If the way I suggested had helped him to resolve the error, I will provide full case study in my blog so everybody can take advantage of the same.

Reference: Ritesh Shah               

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Microsoft Baseline Security Analyzer (MBSA)

Microsoft Baseline Security Analyzer (MBSA) is an easy-to-use tool that helps small and medium businesses determine their security state in accordance with Microsoft security recommendations and offers specific remediation guidance. Improve your security management process by using MBSA to detect common security misconfigurations and missing security updates on your computer systems. Built on the Windows Update Agent and Microsoft Update infrastructure, MBSA ensures consistency with other Microsoft management products including Microsoft Update (MU), Windows Server Update Services (WSUS), Systems Management Server (SMS), System Center Configuration Manager (SCCM) 2007, and Small Business Server (SBS).

You can check following issues with your SQL Server 2005.

tWindows Administrative Vulnerabilities
tWeak passwords
tIIS administrative Vulnerabilities
tCheck for SQL administrative vulnerabilities
tCheck for security updates

You can download this useful utility from here. MBSA is available in English, German, Japanese and French languages for both 32 and 64 bit OS.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Microsoft SQL Server 2005 Best Practices Analyzer – BPA

I used to use BPA (Microsoft SQL Server 2005 Best Practices Analyzer) tool for all my SQL Server 2005 servers. It is really very helpful.

The SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment.

You can download this really very useful tool from here.

BTW, you can use this tool with Microsoft SQL Server 2005 only. BPA for SQL Server 2008 is not available yet.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Error Fix – A network-related or instance-specific error occurred while establishing a connection to SQL Server. (provider: SQL Network Interfaces, error: 28 – Server doesn’t support requested protocol) (Microsoft SQL Server, Error: -1)

After a long time I face above error when I have configured new Microsoft SQL Server 2005 instance in my one of the computer and tried to access it from other computer in network from Asp.Net web application. This is really annoying error but the solution for this error is really quite simple. I knew where to go and that is why I fixed it in even less than 2 minutes but I thought it might be helpful to my reader.

If you want to connect via TCP/IP to remote Microsoft SQL Server and it shows below given error:

Error Fix: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 28 – Server doesn’t support requested protocol) (Microsoft SQL Server, Error: -1)

You have to go to:

Start->All Programs->Microsoft SQL Server 2005->Configuration Tools-> SQL Server Surface area configurations

Once you click on “SQL Server surface area configuration”. It will open one dialog box, you have to click on “Surface are configurations for services and connections”.

Now, you are on track, select your instance of SQL Server,->Database Engine->Remote Connections.
You will find two options here.
1.)    Local connection only
2.)    Local and remote connection.

Select second option, click on “Apply” button and then “OK” button . That’s it,  You are done, now, restart your service and enjoy your connection.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Delete Duplicate records or Select Duplicate records in SQL Server 2008/2005/2000

People may face problem in selecting or deleting duplicate records from database. This is not a new problem; every SQL Developer may face this kind of situation at least once in his/her careers. Now a days, especially after SQL Server 2005, it is becoming really easy to find duplicate records and delete it with the help of CTE (Common Table Expression), basically CTE is introduced in SQL Server 2005 version so long way back in SQL Server 2000, there was no CTE and people were using logical trick to overcome this issues.

I have already written articles on that topic which I want to share with you today.

Click here to look at the trick which works in SQL Server 2000/2005/2008.
Click Here to look at the new method with CTE which should work in SQL Server 2005+ version only.

I see many of the .NET developer still now aware with these kinds of techniques and facing problem due to unawareness. This is the main reason I wrote follow up post for my past two articles.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Frequently Asked Question SQL Server Zone in Different Forums online

It’s been more than 6 months I am actively working in few different online forums like Experts-Exchange.com and forums.aspx.net etc. It has been observed that few questions are really coming repeatedly with different forms. So I thought to list out the articles I have already written for those questions for Microsoft SQL Server. It would be interesting to list many question answered in one post.

Let us see it one by one in no particular order:

Pivot is also one of the interesting as well as frequently asked question in every SQL Server forums. I have written approx 10 different articles on this topic but would like to list three of them here.

Generic Stored Procedure for PIVOT in SQL Server, you can use this SP with almost all PIVOT need.

CTE (Common Table Expression) is also one of the interesting topic which is being asked in forums very often. I have written few articles on this topic too but would like to list our only one here.

Apart from all these useful links, I would like to share two most important script which I used to use with all my servers and databases.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Cannot generate SSPI context (Microsoft SQL Server) in SQL Server 2005

You might face error “Cannot generate SSPI context” while login to your SQL Server 2005 from SQL Server Management Studio. This is really annoying error. Basically the root cause of this error is current windows user doesn’t have proper permission.

This error occurs only when you are trying to login with “Windows Authentication”. You have two options.
1.)    Login with your SQL Server authentication, if you have id/pass
2.)    Set proper permission for your user.

As a simple option, you can right away switch to SQL Server Authentication. For digging more about permission issue of your windows user, have a look at some of the following link from Microsoft website.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah