Find your backup history in SQL Server

While managing multiple databases, it is often needed tocheck database backup history like when did we taken backup? Where did we takelast few backups? What was the size of last few backup? And many other information.
I have setup maintenance plan for few database, fewdatabases backup taken by third party software and all works on schedule on particulartime even I keep one small T-SQL script handy to check all information I havementioned above.
SELECT     
      bkSet.Backup_Start_Date as BackupStartDate,
      bkSet.Backup_Finish_Date as BackupEndDate,
    MedFam.Physical_Device_NameAS BackupPath,
    MedSet.Software_NameAS SoftwareUsedForBackup,
    bkSet.User_Name ASBackupTakenBy,
    bkSet.Server_NameAS ServerName,
    bkSet.Database_NameAs 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 incondition
      bkSet.Database_Name = ‘Adventureworks’
AND       
      –put the date between which you wantto 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 butexamples and explanations prepared by Ritesh Shah, founder of
 http://www.SQLHub.com
Askme any SQL Server related question at my “ASK Profile

Find your backup history in SQL Server

While managing multiple databases, it is often needed tocheck database backup history like when did we taken backup? Where did we takelast few backups? What was the size of last few backup? And many other information.
I have setup maintenance plan for few database, fewdatabases backup taken by third party software and all works on schedule on particulartime even I keep one small T-SQL script handy to check all information I havementioned above.
SELECT     
      bkSet.Backup_Start_Date as BackupStartDate,
      bkSet.Backup_Finish_Date as BackupEndDate,
    MedFam.Physical_Device_NameAS BackupPath,
    MedSet.Software_NameAS SoftwareUsedForBackup,
    bkSet.User_Name ASBackupTakenBy,
    bkSet.Server_NameAS ServerName,
    bkSet.Database_NameAs 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 incondition
      bkSet.Database_Name = ‘Adventureworks’
AND       
      –put the date between which you wantto 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 butexamples and explanations prepared by Ritesh Shah, founder of
 http://www.SQLHub.com
Askme any SQL Server related question at my “ASK Profile

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

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

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               

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 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