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

Follow

Get every new post delivered to your Inbox.

Join 279 other followers