MSSQLTips - Latest SQL Server Tips

Find all database files for a drive and auto generate database attach and detach scripts for SQL Server

Fri, 3 Jul 2009 08:00:00 GMT

Often, you have a SQL Server instance containing many databases. It may happen that over time, you have added disk drives or LUNs and placed different database files on separate disk letters. Now you need to know which databases reside on which drive. You may require this because a drive should be replaced and you need to know which databases will be affected or maybe you need to detach all databases from a drive(s) and copy the contents of the entire drive letter to another server (for example, cloning of Production LUNs into a DEV/QA environment). Another reason may be that you need to map the database locations on the drives for DR purposes, in case you need to rebuild the server from scratch In this tip we look at how to find what d

SQL Server Filtered Indexes - What They Are, How to Use and Performance Advantages

Thu, 2 Jul 2009 08:00:00 GMT

SQL Server 2008 introduces Filtered Indexes which is an index with a WHERE clause. For example, you have a lot of NULL values in a column and you want to retrieve records with only non-NULL values (in SQL Server 2008, this is called Sparse Column). Or in another scenario you have several categories of data in a particular column, but you often retrieve data only for a particular category value. In this tip, I am going to walk through what a Filtered Index is, how it differs from other indexes, its usage scenario, its benefits and limitations.

Check the Last SQL Server Backup Date using Windows PowerShell

Wed, 1 Jul 2009 08:00:00 GMT

In a previous tip, Retrieve List of Databases and their Properties using PowerShell, you have seen how you can use Windows PowerShell to audit database properties and use Microsoft Excel to generate a report. Part of the daily SQL Server DBA tasks is to check the backups of all the databases in a SQL Server instance. How do we use Windows PowerShell to check for the last backup date of SQL Server databases and to confirm that they still meet our service level agreement?

June 2009 Tips - Recap

Tue, 30 Jun 2009 08:00:00 GMT

The June 2009 tips include the following: Analysis Services, Backup and Recovery, Database Administration, DBA Best Practices, Functions, Performance Tuning, Scripts, Security, Sharepoint, SQL Server 2008, SQL Server Integration Services, T-SQL and User Defined Functions (UDF).

Understanding Cross Database Ownership Chaining in SQL Server

Mon, 29 Jun 2009 08:00:00 GMT

I have a grasp on ownership chaining, but I'm wondering what cross-database ownership chaining is and how it works? How is the owner determined across databases if ownership is based on database users?