SQL SERVER – Find Location of Data File Using T-SQL

While preparing for the training course of Microsoft SQL Server 2005/2008 Query Optimization and & Performance Tuning, I needed to find out where my database files are stored on my hard drive. It is when following script came in handy.

SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf',
LOWER(physical_name)) - 1) DataFileLocation
FROM master.sys.master_files
WHERE database_id = 1 AND FILE_ID = 1

Resultset:

DataFileLocation
--------------------------------------------------------------------------
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\
(1 row(s) affected)

Above script provided exact location of where my data files of master database is. In fact, you can use it to find any other path of database as needed.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

SQLAuthority News – Data Compression Strategy Capacity Planning and Best Practices

Data Compression: Strategy, Capacity Planning and Best Practices
SQL Server Technical Article
Writer: Sanjay Mishra
Contributors: Marcel van der Holst, Peter Carlin, Sunil Agarwal
Technical Reviewer: Stuart Ozer, Lindsey Allen, Juergen Thomas, Thomas Kejser, Burzin Patel, Prem Mehra, Joseph Sack, Jimmy May, Cameron Gardiner, Mike Ruthruff, Glenn Berry (SQL Server MVP), Paul S Randal (SQLskills.com), David P Smith (ServiceU Corporation)
Published: May 2009

The data compression feature in SQL Server 2008 helps compress the data inside a database, and it can help reduce the size of the database. Apart from the space savings, data compression provides another benefit: Because compressed data is stored in fewer pages, queries need to read fewer pages from the disk, thereby improving the performance of I/O intensive workloads. However, extra CPU resources are required on the database server to compress and decompress the data, while data is exchanged with the application. Therefore, it is important to understand the workload characteristics when deciding which tables to compress.

This white paper provides guidance on the following:

  • How to decide which tables and indexes to compress
  • How to estimate the resources required to compress a table
  • How to reclaim space released by data compression
  • The performance impacts of data compression on typical workloads

Read Whitepaper: Data Compression Strategy Capacity Planning and Best Practices

Abstract courtesy : Microsoft

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Mirrored Backup and Restore and Split File Backup

Introduction

This article is based on a real life experience of the author while working with database backup and restore during his consultancy work for various organizations. We will go over the following important concepts of database backup and restore.

  1. Conventional Backup and Restore
  2. Spilt File Backup and Restore
  3. Mirror File Backup
  4. Understanding FORMAT Clause
  5. Miscellaneous details about Backup and Restore
Note: Before running all the examples, make sure that you have the required folders created on your drive. It is mandatory to create Backup folders prior to creating backup files using SQL Server.

In our example, we will require the following folders:

  • C:\Backup\SingleFile
  • C:\Backup\MultiFile
  • C:\Backup\MirrorFile

Conventional and Split File Backup and Restore

Just a day before working on one of the projects, I had to take a backup of one database of 14 GB. My hard drive lacked sufficient space at that moment. Fortunately, I had two 8 GB USB Drives with me. Now, the question was how to take a backup in two equal sizes, each of 7 GB, so I can fit them on each USB drive. Well, conventional backup takes one large backup in one file. However, SQL Server backup command can take backups in two or more split parts.

Let us see an example of a conventional one-file backup using the AdventureWorks database.

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\SingleFile\AdventureWorks.bak'
GO

The result is displayed below. Here, the backup is taken in a single file.

Now, let us see how we can split one database into two different database files. This method is very similar to taking a single-file backup. By simply adding an additional DISK option we can split the files backup files.

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\MultiFile\AdventureWorks1.bak',
DISK = 'C:\Backup\MultiFile\AdventureWorks2.bak',
DISK = 'C:\Backup\MultiFile\AdventureWorks3.bak'
GO

In the previous example, we can clearly see that backup is split into three equal parts of the original backup file size.

Restoring a backup from a single-file backup is quite easy. Let us go over an example where we restore the AdventureWorks database from a single backup file.

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backup\SingleFile\AdventureWorks.bak'
GO

Running the above script will give a successful message.

Now let us see an example where we restore a database from a split file. This method is very similar to restoring a database from a single file; just add an additional DISK option.

RESTORE DATABASE [AdventureWorks]
FROM DISK = N'C:\Backup\MultiFile\AdventureWorks1.bak',
DISK = N'C:\Backup\MultiFile\AdventureWorks2.bak',
DISK = N'C:\Backup\MultiFile\AdventureWorks3.bak'
GO

Running the above script will give a successful message as shown in the image below.

Make sure that while restoring database, the database is not in use, otherwise it will give an error of database in use. In the event of an error taking place, close all the connections and re-attempt to restore the database.

Mirror Backup of the file

It is quite a common practice to create an exact copy of the backup and store it to several places to deal with any catastrophes which might affect the place where the database is stored. Once a full backup is accomplished DBAs generally copy the database to another location in their network using a third party tools like robocopy or native DOS commands like xcopy.

In SQL Server 2005 and later versions, there is a Mirror command that makes a copy of the database backup to different locations while taking the original backup. The maximum limit of additional locations that can be specified with MIRROR clause is 3.

Mirrored backup can be taken in local computer system as well as in a local network. Let us now see two examples of mirror backup.

Example 1. Single File Backup to Multiple Locations using Mirror

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\SingleFile\AdventureWorks.bak'
MIRROR TO DISK = 'C:\Backup\MirrorFile\AdventureWorks.bak'
WITH FORMAT
GO

If this command is being run for the first time, it is mandatory to use the WITH FORMAT clause; but for sub sequential runs it is not required. WITH FORMAT reinitializes the backup.

When checked in both the folders ‘SingleFile’ and ‘MirrorFile’, backup files are exactly the same files. As mentioned earlier, four mirror backup can be specified in total.

Example 2. Split File Backup to Multiple Locations using Mirror

We have earlier seen an example where we can have multiple split files of large database backup files. SQL Server Mirror functionality also supports backup of the split files.

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\MultiFile\AdventureWorks1.bak',
DISK = 'C:\Backup\MultiFile\AdventureWorks2.bak',
DISK = 'C:\Backup\MultiFile\AdventureWorks3.bak'
MIRROR TO DISK = 'C:\Backup\MirrorFile\AdventureWorks1.bak',
DISK = 'C:\Backup\MirrorFile\AdventureWorks2.bak',
DISK = 'C:\Backup\MirrorFile\AdventureWorks3.bak'
WITH FORMAT
GO

All the mirror sets will need the same number of DISK clauses as the original backup media.

Mirrored database backup can be restored using the same method as the original backup. Mirrored backup is in fact an exact replica of the original backup.

Understanding the FORMAT Clause

The FORMAT clause is used to reinitiate a backup media. Although it is a very useful clause it should be used with caution. When the clause is used it erases everything present in backup media. I have noticed that some DBAs are confused while taking a backup on a local disk where they have SQL Server installed. They have a misconception that if the format command is used, it will erase the complete disk including the SQL Server installation. However, the fact is that SQL Server format clause is quite different from OS format. The effect of SQL Server format clause is limited to a folder or path specified in the DISK clause.

In our example, when the FORMAT clause is specified, it will format only folders like C:\Backup\MultiFile\ or C:\Backup\SingleFile.

Related Errors

Error 3010
Invalid backup mirror specification. All mirrors must have the same number of members.

This error can show up while taking a mirrored database backup along with a regular backup; and DISK and MIRROR TO DISK do not match accurately.

The following image demonstrates how the error takes place.

To fix the error, match the members of DISK and MIRROR TO DISK to each other.

Error 3215
Use WITH FORMAT to create a new mirrored backup set

This error can spring up when a new backup is initiated and an existing media header needs to be reset for all headers on the backup media. If there is already a backup on the media, it will display this error and prevent backup from being overwritten. To fix this error, use WITH FORMAT as shown in an earlier example.

Miscellaneous details about Backup and Restore

When no options are specified, BACKUP DATABASE takes only full backups. Before taking the first log backup, full database backup is necessary to take one full backup. Backups created on later versions of SQL Server cannot be restored to earlier versions of SQL Server. The user needs permissions of sysadmin or db_owner or db_backupoperator roles to perform backup operation.

Watch a 60 second video on this subject

Reference : Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Introduction to SQL Azure

What is SQL Azure?

In short, SQL Azure is simply a Microsoft branding change. SQL Services and SQL Data Services are now known as Microsoft SQL Azure and SQL Azure Database. There are a few changes, but fundamentally Microsoft’s plans to extend SQL server capabilities in cloud as web-based services remain intact. SQL Azure will continue to deliver an integrated set of services for relational databases. The reporting, analytics and data synchronization with end-users and partners also remains unchanged. This makes it most appealing to current users of SQL Server.

SQL Azure is going to be the Next Big Thing from Microsoft. It is just giving SQL Server a new way of doing operations it was doing for long time. Being on cloud, it keeps development relatively simple by keeping the heart of the programming almost the same. Additionally, SQL Azure keeps the goals of SQL Server, i.e. high availability, scalability and security in the core.

Developers can just start developing SQL Azure application locally using SQL Server Express version (http://www.microsoft.com/express/sql/download/), or the CTP version that will be available in August.

Read my complete article here.

Reference : Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SQL Server Express – A Complete Reference Guide

SQL Server Express is one of the most valuable products of Microsoft. Very often, I face many questions with regard to SQL Server Express. Today, we will be covering some of the most commonly asked questions.

Q: What is the cost of SQL Server Express?
A: SQL Server Express is a FREE product from Microsoft.

Q: Where can I find more details about SQL Server Express?
A: On official Microsoft Site: http://www.microsoft.com/express/sql/default.aspx

Q: Why should I use SQL Server Express when I have full version available?
A: Usually, I install only the license version product on my system. When I do not have to use all the features of SQL Server, I install SQL Server Express.

Q: What is the maximum size per database for SQL Server Express?
A: SQL Server Express supports a maximum size of 4 GB per database, which excludes all the log files. 4 GB is not a very large size; however, if database is properly designed and the tables are properly arranged in a separate database, this limitation can be resolved to a certain extent.

Q: Can MySQL be compared with SQL Server Express as both of them are free products?
A: Yes. Both are free and have their own space. However, thus far, I have never faced a situation wherein the task that can be accomplished by using MySQL is not accomplished by SQL Server Express.

Q: Does SQL Server have sufficient help available online?
A: Yes. There are so many online help resources available for SQL Server Express that user will find strongest community for any FREE product.

Let me give you quick list of the online help resources. Even though I took some time to build this reference list, I am sure that all of you will appreciate this and will share with others, which is worth the effort!

Overview

Getting Started

Installation

Quick Start

Misc

Blogs

Webcasts

Reference : Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Introduction to Change Data Capture (CDC) in SQL Server 2008

Simple-Talk.com has published my very first article on their site. This article is introducing Change Data Capture – the new concept introduced in SQL Server 2008. Change Data Capture records INSERTs, UPDATEs, and DELETEs applied to SQL Server tables, and makes a record available of what changed, where, and when, in simple relational ‘change tables’ rather than in an esoteric chopped salad of XML. These change tables contain columns that reflect the column structure of the source table you have chosen to track, along with the metadata needed to understand the changes that have been made.

Introduction to Change Data Capture (CDC) in SQL Server 2008

Please read my article and leave your valuable comments here. If you like the article do not forget to rate it.

Reference : Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Four Different Ways to Find Recovery Model for Database

Perhaps, the best thing about technical domain is that most of the things can be executed in more than one ways. It is always useful to know about the various methods of performing a single task. Today, we will observe four different ways to find out recovery model for any database.

Method 1

Right Click on Database >> Go to Properties >> Go to Option. On the Right side you can find recovery model.

Method 2

Click on the Database Node in Object Explorer. In Object Explorer Details, you can see the column Recovery Model.

Method 3

This is a very easy method and it gives all the database information in one script.

SELECT name AS [Database Name],
recovery_model_desc AS [Recovery Model]
FROM sys.databases
GO

Method 4

This method provides only one database at a time.

SELECT 'ADVENTUREWORKS' AS [Database Name],
DATABASEPROPERTYEX('ADVENTUREWORKS', 'RECOVERY')
AS [Recovery Model]
GO

My recommendation

Of all the above methods, I prefer Method 3. It is accurate and can be used in T-SQL code. Besides, I can use it for many other purposes as well.

I would like to know which one is your favorite method .

Reference : Pinal Dave (http://blog.SQLAuthority.com)