Archive

Archive for the ‘Backup & Recovery’ Category

SQL Server – How to Detach a Database

June 3, 2013 Leave a comment

To move a database from one location to other you fist need to detach the database from server. In this article we will learn different ways to detach a database from server.

There are two different methods available to detach a database from a server.

Method 1. Detach a Database using "Detach" Task in SQL Server Management Studio:

To detach a database using SQL Server Management Studio:

1. Right Click on Database you want to detach and Select "Tasks" > "Detach" Option.

image

2. Check the "Drop Connections" checkbox and click "OK" to detach the database

image

Now the database is detached from server and you can move .mdf and .ldf files related to database.

 

Method 2: Detach database using T-SQL:

To detach a database from a server using T-SQL you can use below code:

USE [master]

GO

 

ALTER DATABASE [SqlAndMe] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

EXEC master.dbo.sp_detach_db @dbname = N'SqlAndMe'

GO

Result Set:

Command(s) completed successfully.

This detaches database from server.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – Attach Database with MDF file only

January 30, 2013 1 comment

Earlier on my blog I posted about how you can attach a database using T-SQL when no log file is available. You can catch that here. In this post we will see how it can be attached using GUI.

To attach a database with no .ldf file, follow below steps:

1. Open Attach Database dialog box by selecting "Attach" from context menu of database node in Object Explorer:

image

2. Click "Add" button to locate the .mdf file, and click "OK":

image

image

3. Once you locate the .mdf file for database, it will automatically fill details for log file name and path, also a message will be displayed if .ldf is not available where it should be:

image

4. Since we do not have .ldf file available, select the row for log file and click on "Remove", and then click on "OK" to attach the database:

image

5. This will force SQL Server to rebuild log file for the database. Once the database is attached you can verify that log file has been rebuilt using following T-SQL:

USE [SqlAndMe]

GO

 

SELECT      name, physical_name

FROM        sys.database_files

GO

Results:

name          physical_name
———–   ———————–
SqlAndMe      C:\Database\Data\SqlAndMe.mdf
SqlAndMe_log  C:\Database\Log\SqlAndMe_log.LDF

(2 row(s) affected)

 

You can also do this using T-SQL, read Attach a single MDF file – Database for details.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – Enabling backup CHECKSUM with Trace Flag 3023

January 15, 2013 2 comments

You can use WITH CHECKSUM option to perform checksum when backup is created. When used this verifies each page for checksum and torn page.

You can use it in a BACKUP command as below:

BACKUP DATABASE [SqlAndMe]

TO DISK = N'C:\SqlAndMe.bak'

WITH CHECKSUM

GO

 

Once the backup is created you can check the header to verify if backup was created with checksum.

RESTORE HEADERONLY

FROM DISK = N'C:\SqlAndMe.bak'

GO

 

Result Set:

image

 

However, if you do not have control over underlying T-SQL for backup then you can enable backup checksums using Trace Flag 3023. When this Trace Flag is turned on WITH CHECKSUM option is applied automatically for a backup.

In the following example I have run a backup before enabling Trace Flag 3023, and after enabling Trace Flag 3023 to see it’s behavior:

– Run without Trace Flag 3023

BACKUP DATABASE [SqlAndMe]

TO DISK = N'C:\SqlAndMe.bak'

GO

 

 

DBCC TRACEON(3023)

GO

 

– Run after enabling Trace Flag 3023

BACKUP DATABASE [SqlAndMe]

TO DISK = N'C:\SqlAndMe.bak'

GO

 

DBCC TRACEOFF(3023)

GO

 

Result Set:

Processed 240 pages for database 'SqlAndMe', file 'SqlAndMe' on file 1.

Processed 1 pages for database 'SqlAndMe', file 'SqlAndMe_Log' on file 1.

BACKUP DATABASE successfully processed 241 pages in 0.213 seconds (8.839 MB/sec).

 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

Processed 240 pages for database 'SqlAndMe', file 'SqlAndMe' on file 2.

Processed 1 pages for database 'SqlAndMe', file 'SqlAndMe_Log' on file 2.

BACKUP DATABASE successfully processed 241 pages in 0.376 seconds (5.007 MB/sec).

 

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

Now, we can look at the header of the backup file to verify if the backup has been created with checksum:

RESTORE HEADERONLY

FROM DISK = N'C:\SqlAndMe.bak'

GO

 

Result Set:

image

 

As we can see from the output, First backup was created without checksums and second backup (after enabling Trace Flag 3023) is created with checksums without explicitly specifying WITH CHECKSUM option.

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – When will my backup finish?

November 18, 2011 5 comments

The sys.dm_exec_requests is a great way to find out how long the BACKUP will take to complete. If you are doing a backup WITH STATS or by GUI, you will not need to use the DMV. But, if someone else is running a backup (or a Job), you can find the percent of backup completed and estimated completion time using this DMV. Another easy way is to ask the person who is taking the backup :), but it’s not always possible.

The same thing happened to me recently (yesterday in fact!). I had to deploy a CR for an Application, and a Full database backup was to be taken in case a rollback is required. Now, if things were simple, I will take the backup and then proceed with CR deployment, but the database server is maintained by a different team about 4772 miles away! (managed by customer), and it’s not easy for people like me to keep staring at Outlook while waiting for backup completion notification from customer’s team.

When a backup is running, you can use the below query to check the progress, total_elapsed_time and estimated_completion_time returns milliseconds:

SELECT      command, percent_complete,

            'elapsed' = total_elapsed_time / 60000.0,

            'remaining' = estimated_completion_time / 60000.0

FROM        sys.dm_exec_requests

WHERE       command like 'BACKUP%'

Result Set:

command              percent_complete     elapsed       remaining

BACKUP DATABASE      50.75982             44.594500     41.207166

For a complete list of operation that reports percent_complete, check BOL for sys.dm_exec_requests.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – Creating and using Dump Devices for Backups

September 26, 2011 Leave a comment

A dump device is simply a logical device which redirects to a specified physical device. The main advantage of using a dump device for backups is it reduces the change required to backup code. For example, if you are backing up to a Network location. If you have hard-coded the backup location then the network location change will require a code change as well.

 

You can create a dump device using SSMS or T-SQL.

To create a dump device using SSMS:

1. Expand "Server Objects" in Object Explorer,

2. Right Click on "Backup Devices" and choose "New Backup Device"

image

3. Provide a logical Device name and the physical backup file location for dump device

image

4. Click "OK" to create the dump device.

 

To create a dump device using T-SQL:

To create a dump device using T-SQL you can use system stored procedure sp_addumpdevice.

USE [master]

GO

EXEC master.dbo.sp_addumpdevice

@devtype          = N'disk',

      @logicalname      = N'MyBackups',

      @physicalname     = N'C:\Database\Backup\BackupDisk.bak'

GO

Result Set:

(1 row(s) affected)

 

View all dump devices:

You can get the list of existing dump devices on server using sys.backup_devices catalog view:

SELECT      *

FROM        sys.backup_devices

GO

Result Set:

Name          type   type_desc     physical_name

———–   —–  ———–   ——————————–

MyBackups     2      DISK          C:\Database\Backup\BackupDisk.bak

 

(1 row(s) affected)

 

Using the dump device for BACKUP:

Once the dump device is created you can use it to store backups, The dump device will be listed in "Select Backup Destination" dialog box:

image

You can use it in T-SQL as below:

/*

      BACKUP DATABASE [SqlAndMe]

      TO DISK = N'C:\Database\Backup\SqlAndMeBackup.bak'

      WITH NOFORMAT, NOINIT, NAME = N'SqlAndMe – Full Backup',

      SKIP, NOREWIND, NOUNLOAD,  STATS = 10

      GO

*/

 

BACKUP DATABASE [SqlAndMe]

TO [MyBackups]

WITH NOFORMAT, NOINIT, NAME = N'SqlAndMe – Full Backup',

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

Result Set:

10 percent processed.

20 percent processed.

30 percent processed.

40 percent processed.

50 percent processed.

60 percent processed.

70 percent processed.

80 percent processed.

90 percent processed.

Processed 44680 pages for database 'SqlAndMe', file 'SqlAndMe' on file 2.

100 percent processed.

Processed 1 pages for database 'SqlAndMe', file 'SqlAndMe_log' on file 2.

BACKUP DATABASE successfully processed 44681 pages in 23.542 seconds (14.827 MB/sec).

 

Changing Physical File location for dump devices:

To change the physical file location for dump device, you need to drop and recreate the dump device. If you need to move the current physical file to a new location, it can be done using Windows Explorer or any other File Manager. The physical file is not locked unless a BACKUP/RESTORE is in progress.

USE [master]

GO

EXEC master.dbo.sp_dropdevice

      @logicalname = N'MyBackups'

GO

 

EXEC master.dbo.sp_addumpdevice

      @devtype          = N'disk',

      @logicalname      = N'MyBackups',

      @physicalname     = N'C:\NewLocation\BackupDisk.bak'

GO

 

 

SELECT      *

FROM        sys.backup_devices

GO

Result Set:

Device dropped.

 

 

Name          type   type_desc     physical_name

————  —— ————  ———————–

MyBackups     2      DISK          C:\NewLocation\BackupDisk.bak

 

(1 row(s) affected)

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – sys.dm_db_persisted_sku_features tells you about edition-specific features

August 22, 2011 Leave a comment

sys.dm_db_persisted_sku_features lists all features which are utilized by the database. Features specific to Enterprise/Developer edition are:

- Compression,
- Partitioning,
- TDE and CDC

These features are available only on Enterprise/Developer editions of SQL Server. You cannot attach or restore databases utilizing these features to a "lower" edition, such as Standard or Express edition.

For example, You can check features utilized by your database using:

USE SqlAndMe

 

SELECT      *

FROM        sys.dm_db_persisted_sku_features

Result Set:

feature_name  feature_id

Compression   100

Partitioning  200

 

(2 row(s) affected)

 

Since this database uses partition and compression it cannot be attached to an instance of "lower" edition,

If you try to attach database to a "lower" edition, you will run into below error:

Msg 909, Level 21, State 1, Line 1

Database 'SqlAndMe' cannot be started in this edition of SQL Server because part or all of object 'PageCompressionTest' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.

 

Msg 905, Level 21, State 1, Line 1

Database 'SqlAndMe' cannot be started in this edition of SQL Server because it contains a partition function 'PartFunc01'. Only Enterprise edition of SQL Server supports partitioning.

 

Msg 933, Level 21, State 1, Line 1

Database 'SqlAndMe' cannot be started because some of the database functionality is not available in the current edition of SQL Server.

These error messages tell you exactly why the database cannot be started. 1st error is due to compression feature, and 2nd is for partitioning feature. Notice the last message, it says, "Database cannot be started.", the database does attach to server, but it cannot be started.

In case of restore also, you will receive same error messages, the database will be restored but, it cannot be started:

20 percent processed.

40 percent processed.

60 percent processed.

80 percent processed.

100 percent processed.

Processed 29976 pages for database 'SqlAndMe', file 'SqlAndMe' on file 1.

Processed 3 pages for database 'SqlAndMe', file 'SqlAndMe_log' on file 1.

 

Msg 3167, Level 16, State 1, Line 1

RESTORE could not start database 'SqlAndMe'.

 

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

 

Msg 909, Level 21, State 1, Line 1

Database 'SqlAndMe' cannot be started in this edition of SQL Server because part or all of object 'PageCompressionTest' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.

 

Msg 905, Level 21, State 1, Line 1

Database 'SqlAndMe' cannot be started in this edition of SQL Server because it contains a partition function 'PartFunc01'. Only Enterprise edition of SQL Server supports partitioning.

 

Msg 933, Level 21, State 1, Line 1

Database 'SqlAndMe' cannot be started because some of the database functionality is not available in the current edition of SQL Server.

In order to avoid this issue, it must be planned ahead. Before using any edition-specific features ask – "Will the database need to be moved between editions?"

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

SQL Server – Denali – Management Studio – Restore Database

July 19, 2011 Leave a comment

In Management Studio 2008 R2, restore database dialog is as below:

image

Management Studio 2008 R2

You need to select the backups which you need to restore from the backup set list. This dialog box is tweaked in "Denali":

image

Management Studio "Denali"

First, thing to notice here is, it takes a tail-log backup of the source database. In earlier version, we needed to *remember* to do the same before restoring the database.

The Backup Sets list does not list all backup sets available in the media, it only lists backups of the selected database:

image

Also, it is showing only the latest backups since last FULL database backup, just like selecting database as source in earlier versions. Older backup can be restored via "Timeline…", which is self-explanatory:

image

And to make it complete, it also has a "Verify Backup Media" button. :)

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Attach a single MDF file – Database

April 29, 2011 2 comments

If you want to attach a database to a SQL Instance you need both the data file (.mdf) and the log file (.ldf). Without both files you cannot attach a database. Let’s try doing that…

To attach a database from Management Studio:

Right click on Database node and choose attach:

image

This will open the "Attach Databases" dialogue box, where you can select the .mdf file using "Add…" button:

image

Note, that is says "Not Found" for log file, if you click on "OK", it will raise an error:

image

The detailed error message can be seen by click on “Message” link:

image

Moral of the story, you can’t attach single .mdf  using GUI. You need to use T-SQL to achieve this:

– © 2011 – Vishal (http://SqlAndMe.com)

 

CREATE DATABASE [TestDB]

ON (FILENAME = 'C:\123\TestDB.mdf')

FOR ATTACH_REBUILD_LOG ;

On successful completion, you will get the below message:

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\TestDB_1.ldf" may be incorrect.

New log file 'C:\123\TestDB_log.ldf' was created.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Msg 4208 / Msg 4214 / Msg 3013 – BACKUP LOG is terminating abnormally – Backup & Recovery

April 25, 2011 1 comment

Recently, I encountered this message in one of our production servers. When I checked the SQL Agent Log I found below error message.

BACKUP LOG cannot be performed because there is no current database backup.
BACKUP LOG is terminating abnormally.

I searched for a solution and found a KB article which describes the issue, KB928317.

The KB article describes only one scenario that a Full backup must exist before a Log backup can be taken. Which is depicted below:

– © 2011 – Vishal (http://SqlAndMe.com)
CREATE DATABASE [TestDB]

ALTER DATABASE [TestDB] SET RECOVERY FULL

BACKUP
LOG [TestDB] TO
DISK = N'C:\TestDBLog.bak'
WITH NOFORMAT, NOINIT,
NAME = N'TestDB-Transaction Log  Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

The BACKUP LOG statement will throw and error since there is no Full backup:

Msg 4214, Level 16, State 1, Line 1

BACKUP LOG cannot be performed because there is no current database backup.

Msg 3013, Level 16, State 1, Line 1

BACKUP LOG is terminating abnormally.

 

To resolve this error, you must take a full backup before you execute the BACKUP LOG statement. The KB article did not seem to apply to my situation as a Full backup of our production database is taken daily.

The root cause in my situation was the recovery model of the database, which was set to SIMPLE !

Let’s try this on our TestDB

– © 2011 – Vishal (http://SqlAndMe.com)
ALTER DATABASE [TestDB] SET RECOVERY SIMPLE

BACKUP LOG [TestDB] TO
DISK = N'C:\TestDBLog.bak'
WITH NOFORMAT, NOINIT,
NAME = N'TestDB-Transaction Log  Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

 

The error message you receive is self-explanatory:
Msg 4208, Level 16, State 1, Line 1
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use
BACKUP DATABASE or change the recovery model using ALTER DATABASE.

Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

 

But, if the statement is executed by a SQL Job, you will only receive below in error message:
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

 

This can lead to confusion in identifying correct issue.

 

When I investigated further to find out why recovery model was set to SIMPLE, I found that a Re-indexing Job was scheduled which set the recovery model to SIMPLE to avoid Log file growth due to indexing operations. The Job will change the recovery model to SIMPLE, execute for around 14 hours and then it will set the recovery model to FULL.

 

A better approach is to perform indexing operations under BULK-LOGGED recovery model, since CREATE/ALTER and DROP INDEX operations are minimally logged under this model.

 

BOL : Backup Under the Bulk-Logged Recovery Model

 

 

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Follow

Get every new post delivered to your Inbox.

Join 247 other followers