Archive

Archive for the ‘Backup & Recovery’ Category

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 3 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

When did you backup? Where did you backup?

April 23, 2011 Leave a comment

If you are managing a lot of databases, you might need to scratch your head for a while to remember where and when did you backed up a database. Fortunately, this information is stored in MSDB database and can be easily retrieved using below tables:

1. backupmediafamily – contains one row for each media family.
2. backupset – contains a row for each backup set.
3. backupmediaset – contains a row for each backup media set.

You can join these tables to find out where/when a backup was made.

SELECT      BS.Backup_Start_Date, BS.Backup_Finish_Date,

            BMF.Physical_Device_Name AS [Backed-Up To],

            BMS.Software_Name AS [Performed Using],

            BS.Name AS [Backup Set Name],

            BS.User_Name AS [Performed By], BS.Server_Name,

            BS.Database_Name,

            CASE BS.Type      WHEN 'D' THEN 'Full'

                              WHEN 'I' THEN 'Differential'

                              WHEN 'L' THEN 'T-Log'

                              WHEN 'F' THEN 'File'

                              WHEN 'G' THEN 'Diff. File'

                              WHEN 'P' THEN 'Partial'

                              WHEN 'Q' THEN 'Diff. Partial'

                              ELSE 'Other' END AS [Backup Type],

            CAST(((BS.Backup_Size/1024)/1204) AS NUMERIC(10,2))

            AS [Size (MB)] ,

            CAST(((BS.Compressed_Backup_Size/1024)/1204) AS NUMERIC(10,2))

            AS [Compressed (MB)],

            CASE BS.Is_Password_Protected WHEN 1 THEN 'Yes'

                                          ELSE 'No' END AS [Password?],

            CASE BMS.Is_Compressed  WHEN 1 THEN 'Yes'

                                    ELSE 'No' END AS [Compressed?]

FROM        msdb..BackupMediaFamily BMF

INNER JOIN  msdb..BackupMediaSet BMS
            ON BMF.Media_Set_ID = BMS.Media_Set_ID

INNER JOIN  msdb..BackupSet BS
           
ON BS.Media_Set_ID = BMS.Media_Set_ID

–WHERE      BS.Database_Name = 'MyDatabaseName'

–AND        BS.Backup_Finish_Date BETWEEN 'StartDate' AND 'EndDate'

ORDER BY    BS.Backup_Finish_Date DESC

You can add a filter for a specific database name and backup duration to filter the result set.

The above query will return all information for all databases present on the current instance of SQL Server.

Below is the output received on my server:

image

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