SQL SERVER – Attach mdf file without ldf file in Database

Background Story:
One of my friends recently called up and asked me if I had spare time to look at his database and give him a performance tuning advice. Because I had some free time to help him out, I said yes. I asked him to send me the details of his database structure and sample data. He said that since his database is in a very early stage and is small as of the moment, so he told me that he would like me to have a complete database. My response to him was “Sure! In that case, take a backup of the database and send it to me. I will restore it into my computer and play with it.”

He did send me his database; however, his method made me write this quick note here. Instead of taking a full backup of the database and sending it to me, he sent me only the .mdf (primary database file). In fact, I asked for a complete backup (I wanted to review file groups, files, as well as few other details).  Upon calling my friend,  I found that he was not available. Now,  he left me with only a .mdf file. As I had some extra time, I decided to checkout his database structure and get back to him regarding the full backup, whenever I can get in touch with him again.

Technical Talk:
If the database is shutdown gracefully and there was no abrupt shutdown (power outrages, pulling plugs to machines, machine crashes or any other reasons), it is possible (there’s no guarantee) to attach .mdf file only to the server. Please note that there can be many more reasons for a database that is not getting attached or restored. In my case, the database had a clean shutdown and there were no complex issues. I was able to recreate a transaction log file and attached the received .mdf file.

There are multiple ways of doing this. I am listing all of them here. Before using any of them, please consult the Domain Expert in your company or industry. Also, never attempt this on live/production server without the presence of a Disaster Recovery expert.

USE [master]
GO
-- Method 1: I use this method
EXEC sp_attach_single_file_db @dbname='TestDb',
@physname=N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf'
GO
-- Method 2:
CREATE DATABASE TestDb ON
(FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf')
FOR ATTACH_REBUILD_LOG
GO

Method 2: If one or more log files are missing, they are recreated again.

There is one more method which I am demonstrating here but I have not used myself before. According to Book Online, it will work only if there is one log file that is missing. If there are more than one log files involved, all of them are required to undergo the same procedure.

-- Method 3:
CREATE DATABASE TestDb ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TestDb.mdf')
FOR ATTACH
GO

Please read the Book Online in depth and consult DR experts before working on the production server. In my case, the above syntax just worked fine as the database was clean when it was detached. Feel free to write your opinions and experiences for it will help the IT community to learn more from your suggestions and skills.

PS: Do not try this on production server.

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

SQL SERVER – Retrieve and Explore Database Backup without Restoring Database – Idera virtual database

I recently downloaded Idera’s SQL virtual database, and tested it. There are a few things about this tool which caught my attention.

My Scenario

It is quite common in real life that sometimes observing or retrieving older data is necessary; however, it had changed as time passed by. The full database backup was 40 GB in size, and, to restore it on our production server, it usually takes around 16 to 22 minutes, depending on the load server that is usually present. This range in time varies from one server to another as per the configuration of the computer. Some other issues we used to have are the following:

  1. When we try to restore a large 40-GB database, we needed at least that much space on our production server.
  2. Once in a while, we even had to make changes in the restored database, and use the said changed and restored database for our purpose, making it more time-consuming.

My Solution

I have heard a lot about the Idera’s SQL virtual database tool.. Well, right after we started to test this tool, we found out that it really delivers what it promises. Using this software was very easy and we were able to restore our database from backup in less than 2 minutes, sparing us from the usual longer time of 16–22 minutes. The needful was finished in a total of 10 minutes. Another interesting observation is that there is no need to have an additional space for restoring the database. For complete database restoration, the single additional MB on the drive is not required anymore. We can use the database in the same way as our regular database, and there is no need for any additional configuration and setup.

Let us look at the most relevant points of this product based on my initial experience:

  • Quick restoration of the database backup
  • No additional space required for database restoration
  • virtual database has no physical .MDF or .LDF
  • The database which is restored is, in fact, the backup file converted in the virtual database. DDL and DML queries can be executed against this virtually restored database.
  • Regular backup operation can be implemented against virtual database, creating a physical .bak file that can be used for future use.
  • There was no observed degradation in performance on the original database as well the restored virtual database.
  • Additional T-SQL queries can be let off on the virtual database.

Well, this summarizes my quick review. And, as I was saying, I am very impressed with the product and I plan to explore it more. There are many features that I have noticed in this tool, which I think can be very useful if properly understood.

I had taken a few screenshots using my demo database afterwards. Let us see what other things this tool can do besides the mentioned activities. I am surprised with its performance so I want to know how exactly this feature works, specifically in the matter of why it does not create any additional files and yet, it still allows update on the virtually restored database. I guess I will have to send an e-mail to the developers of Idera and try to figure this out from them.

I think this tool is very useful, and it delivers a high level of performance way more than what I expected. Soon, I will write a review for additional uses of SQL virtual database.. If you are using SQL virtual database in your production environment, I am eager to learn more about it and your experience while using it.

The ‘Virtual’ Part of virtual database

When I set out to test this software, I thought virtual database had something to do with Hyper-V or visualization. In fact, the virtual database is a kind of database which shows up in your SQL Server Management Studio without actually restoring or even creating it. This tool creates a database in SSMS from the backup of the same database. The backup, however, works virtually the same way as original database.

virtualdb1 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database

Potential Usage of virtual database:

As soon as I described this tool to my teammate, I think his very first reaction was, “hey, if we have this then there is no need for log shipping.” I find his comment very interesting as log shipping is something where logs are moved to another server. In fact, there are no updates on the database from log; I would rather compare it with Snapshot Replication. In fact, whatever we use, snapshot replicated database can be similarly used and configured with virtual database. I totally believe that we can use it for reporting purpose. In fact, after this database was configured, I think the uses of this tool are unlimited. I will have to spend some more time studying it and will get back to you.

Click on images to see larger images.

vdb1 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database
virtual database Console
vdb2 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database
Harddrive Space before virtual database Setup
vdb3 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database
Attach Full Backup Screen
vdb4 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database
Backup on Harddrive
vdb5 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database
Attach Full Backup Screen with Settings
vdb6 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database
virtual database Setup – less than 60 sec
vdb7 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database
virtual database Setup – Online
vdb2 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database
Harddrive Space after virtual database Setup
vdb9 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database
Point in Time Recovery Option – Timeline View
vdb10 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database
virtual database Summary
vdb11 SQL SERVER   Retrieve and Explore Database Backup without Restoring Database   Idera virtual database
No Performance Difference between Regular DB vs Virtual DB

Please note that all SQL Server MVP gets free license of this software.

Reference: Pinal Dave (http://blog.SQLAuthority.com), Idera (virtual database)

SQL SERVER – 2008 – Introduction to Snapshot Database – Restore From Snapshot

Snapshot database is one of the most interesting concepts that I have used at some places recently.

Here is a quick definition of the subject from Book On Line:

A Database Snapshot is a read-only, static view of a database (the source database). Multiple snapshots can exist on a source database and can always reside on the same server instance as the database. Each database snapshot is consistent, in terms of transactions, with the source database as of the moment of the snapshot’s creation. A snapshot persists until it is explicitly dropped by the database owner.

If you do not know how Snapshot database work, here is a quick note on the subject. However, please refer to the official description on Book-on-Line for accuracy. Snapshot database is a read-only database created from an original database called the “source database”. This database operates at page level. When Snapshot database is created, it is produced on sparse files; in fact, it does not occupy any space (or occupies very little space) in the Operating System. When any data page is modified in the source database, that data page is copied to Snapshot database, making the sparse file size increases. When an unmodified data page is read in the Snapshot database, it actually reads the pages of the original database. In other words, the changes that happen in the source database are reflected in the Snapshot database.

Let us see a simple example of Snapshot. In the following exercise, we will do a few operations. Please note that this script is for demo purposes only- there are a few considerations of CPU, DISK I/O and memory, which will be discussed in the future posts.

  • Create Snapshot
  • Delete Data from Original DB
  • Restore Data from Snapshot

First, let us create the first Snapshot database and observe the sparse file details.

USE master
GO
-- Create Regular Database
CREATE DATABASE RegularDB
GO
USE RegularDB
GO
-- Populate Regular Database with Sample Table
CREATE TABLE FirstTable (ID INT, Value VARCHAR(10))
INSERT INTO FirstTable VALUES(1, 'First');
INSERT INTO FirstTable VALUES(2, 'Second');
INSERT INTO FirstTable VALUES(3, 'Third');
GO
-- Create Snapshot Database
CREATE DATABASE SnapshotDB ON
(Name ='RegularDB',
FileName='c:\SSDB.ss1')
AS SNAPSHOT OF RegularDB;
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO

Now let us see the resultset for the same.

snapshot1 SQL SERVER   2008   Introduction to Snapshot Database   Restore From Snapshot

snapshot2 SQL SERVER   2008   Introduction to Snapshot Database   Restore From Snapshot

snapshot3 SQL SERVER   2008   Introduction to Snapshot Database   Restore From Snapshot

Now let us do delete something from the Original DB and check the same details we checked before.

-- Delete from Regular Database
DELETE FROM RegularDB.dbo.FirstTable;
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO
snapshot4 SQL SERVER   2008   Introduction to Snapshot Database   Restore From Snapshot

When we check the details of sparse file created by Snapshot database, we will find some interesting details. The details of Regular DB remain the same.

snapshot6 SQL SERVER   2008   Introduction to Snapshot Database   Restore From Snapshot

It clearly shows that when we delete data from Regular/Source DB, it copies the data pages to Snapshot database. This is the reason why the size of the snapshot DB is increased.

Now let us take this small exercise to  the next level and restore our deleted data from Snapshot DB to Original Source DB.

-- Restore Data from Snapshot Database
USE master
GO
RESTORE DATABASE RegularDB
FROM DATABASE_SNAPSHOT = 'SnapshotDB';
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO
-- Clean up
DROP DATABASE [SnapshotDB];
DROP DATABASE [RegularDB];
GO

Now let us check the details of the select statement and we can see that we are successful able to restore the database from Snapshot Database.

snapshot1 SQL SERVER   2008   Introduction to Snapshot Database   Restore From Snapshot

We can clearly see that this is a very useful feature in case you would encounter a good business that needs it.

I would like to request the readers to suggest more details if they are using this feature in their business. Also, let me know if you think it can be potentially used to achieve any tasks.

Complete Script of the afore- mentioned operation for easy reference is as follows:

USE master
GO
-- Create Regular Database
CREATE DATABASE RegularDB
GO
USE RegularDB
GO
-- Populate Regular Database with Sample Table
CREATE TABLE FirstTable (ID INT, Value VARCHAR(10))
INSERT INTO FirstTable VALUES(1, 'First');
INSERT INTO FirstTable VALUES(2, 'Second');
INSERT INTO FirstTable VALUES(3, 'Third');
GO
-- Create Snapshot Database
CREATE DATABASE SnapshotDB ON
(Name ='RegularDB',
FileName='c:\SSDB.ss1')
AS SNAPSHOT OF RegularDB;
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO
-- Delete from Regular Database
DELETE FROM RegularDB.dbo.FirstTable;
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO
-- Restore Data from Snapshot Database
USE master
GO
RESTORE DATABASE RegularDB
FROM DATABASE_SNAPSHOT = 'SnapshotDB';
GO
-- Select from Regular and Snapshot Database
SELECT * FROM RegularDB.dbo.FirstTable;
SELECT * FROM SnapshotDB.dbo.FirstTable;
GO
-- Clean up
DROP DATABASE [SnapshotDB];
DROP DATABASE [RegularDB];
GO

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

SQL SERVER – Data and Page Compressions – Data Storage and IO Improvement

The performance of SQL Server is primarily decided by the disk I/O efficiency. Improving I/O definitely improves the performance. SQL Server 2008 introduced Data and Backup compression features to improve the disk I/O. Here, I will explain Data compression.

Data compression implies the reduction in the disk space reserved by data. Therefore, data compression can be configured for a table, clustered index, non-clustered index, indexed view or a partition of table or index.

Data compression is implemented at two levels: ROW and PAGE. Even page compression automatically implements row compression. Tables and indexes can be compressed when they are created by using the CREATE TABLE and CREATE INDEX statements. To change the compression state of a table, index, or partition, use the ALTER TABLE.. REBUILD WITH or ALTER INDEX.. REBUILD WITH statements. When compression state of a heap is changed, all non-clustered index are rebuilt (not with the compression type of table but there own).

Let me tell you what happens during the compression of data. In Row compression, the following four methods are used to remove unused space:

  1. The metadata overhead of the record is reduced.
  2. All numeric (for example integer, decimal, and float) and numeric-based (for example datetime and money) data type values are converted into variable length values. By default, the values of these data types are stored as fixed length like integer in 4 bytes datetime in 8 bytes, but after compression all unconsumed space is reclaimed. For example, a value 100 is stored in an integer-type column. We know an integer value between 0 and 255 can be stored in 1 byte. However, it reserves 4 bytes (integer type takes 4 bytes) on disk. Here, after compression, 3 bytes are reclaimed.
  3. CHAR and NCHAR type values are stored in variable length format. After compression, no blank character is stored with this type of data. For example, a value “DBMS” is stored in CHAR(10) type column. By default, this value will reserve 10 bytes on disk, but after compression, it will reserve only 4 bytes.
  4. NULL and 0 values across all data types are optimized and take no bytes.

Page compression is implemented through the following three methods:

  1. Row compression: as discussed above
  2. Prefix compression: In each page, for each column, a common value from all rows is identified and stored in a row below the header. After that, from all the rows, that common value is replaced with the reference of header row.
  3. Dictionary compression: Dictionary compression is same concept as that of Prefix compression implemented the second time. In Prefix compression, a separate common value is identified for each column, but in Dictionary compression, common values are identified from all columns in that page and stored in second row below the header. Then, these common values are replaced with the reference of values in new row.

Below is an example. Even it is not a good practical scenario, but sufficiently explains the compression functionality.

Review your database for performance and implement this strategies. Let us run the following different row level and page level compression and observe the size of the table once compression is in place.

USE tempdb
GO
CREATE TABLE TestCompression (col1 INT, col2 CHAR(50))
GO
INSERT INTO TestCompression VALUES (10, 'compression testing')
GO 5000
-- Original
EXEC sp_spaceused TestCompression
GO
-- DATA_COMPRESSION = ROW
ALTER TABLE TestCompression
REBUILD
WITH (DATA_COMPRESSION = ROW);
GO
EXEC sp_spaceused TestCompression
GO
-- DATA_COMPRESSION = PAGE
ALTER TABLE TestCompression
REBUILD
WITH (DATA_COMPRESSION = PAGE);
GO
EXEC sp_spaceused TestCompression
GO
-- DATA_COMPRESSION = NONE
ALTER TABLE TestCompression
REBUILD
WITH (DATA_COMPRESSION = NONE);
GO
EXEC sp_spaceused TestCompression
GO

datacompression SQL SERVER   Data and Page Compressions   Data Storage and IO Improvement

Do you use this compression on your production server? If yes, it will be interesting if you share your feedback with us over here.

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

SQLAuthority News – SQL Server Technical Article – The Data Loading Performance Guide

Note: SQL Server Technical Article – The Data Loading Performance Guide by Microsoft

The white paper describes load strategies for achieving high-speed data modifications of a Microsoft SQL Server database.

“Bulk Load Methods” and “Other Minimally Logged and Metadata Operations” provide an overview of two key and interrelated concepts for high-speed data loading: bulk loading and metadata operations.

After this background knowledge, white paper describe how these methods can be used to solve customer scenarios. Script examples illustrating common design pattern are found in “Solving Typical Scenarios with Bulk Loading” Special consideration must be taken when you need to load and read data concurrently in the same table. The section “Bulk Load, NOLOCK Queries, and Read Committed Snapshot Isolation” describes methods you can use to achieve concurrent loading and reading.

This white paper concludes with troubleshooting hints in “Optimizing Bulk Load”.

SQL Server Technical Article – The Data Loading Performance Guide

Abstract courtesy : Microsoft

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

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)

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.

sql mirrored backup1 SQL SERVER   Mirrored Backup and Restore and Split File Backup

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.

sql mirrored backup2 SQL SERVER   Mirrored Backup and Restore and Split File Backup

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.

sql mirrored backup3 SQL SERVER   Mirrored Backup and Restore and Split File Backup

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.

sql mirrored backup4 SQL SERVER   Mirrored Backup and Restore and Split File Backup

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.

sql mirrored backup5 SQL SERVER   Mirrored Backup and Restore and Split File 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.

sql mirrored backup6 SQL SERVER   Mirrored Backup and Restore and Split File Backup

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.

sql mirrored backup7 SQL SERVER   Mirrored Backup and Restore and Split File Backup

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

sql mirrored backup8 SQL SERVER   Mirrored Backup and Restore and Split File Backup

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)