SQL Server - How do you get a database into recovery mode.

Asked By Mark

03-Mar-10 10:53 PM

Here is the scenario.
I have a database on an equallogic box.  The equallogic box does a snapshot
at 10:00pm, on sql server i am doing transaction log backups every 10
minutes.  The database breaks at 10:30, so I restore to the 10pm
snapshot(this is not a sql snapshot).  How do I get the database into
recovery mode, so I can apply the transaction logs?

04-Mar-10 01:22 AM

Look in BOL's RESTORE command and locate the NORECOVERY option.

Basically, it is an option that tells the DB that you have logs to restore.

04-Mar-10 01:35 AM

... and it has to be done with a prior RESTORE command. So, whatever type of
snapshot this is, it need to support SQL Server so that SQL Server consider
reverting from this snapshot being a type of restore; allowing NORECOVERY
behavior so that log backups can be applied. The vendor of that snapshot
technology should have info on how to do this, assuming this product support
SQL Server and rolling forward log backups from such a snapshot.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

04-Mar-10 01:45 AM

Oh my, it is just before my bedtime and I messed that answer up. You do not
RESTORE to a snapshot, you revert to it and NORECOVERY is an option to
RESTORE.

My brain is too fuzzy and I was still thinking about the NOLOCK from the
other thread.

My apologies, I am going away now.

04-Mar-10 02:59 AM

No need to apology, nor go away, Jay. ;-)

Your post lead me on the right track, (hopefully) understanding the OPs
scenario. The OP scenario I believe is using some non-SQL Server snapshot
technology.  This technology need to behave like a SQL Server restore with
NORECOVERY (UNDO not performed).

Regarding SQL Server snapshot, you do use the RESTORE command to revert to
such a snapshot. Thing is that you cannot apply NORECOVERY to such a RESTORE
command. Technically, SQL Server cannot put the database in a state from
which log backups can be applied (like magically make the log as if from
some point in time only did REDO and not UNDO)...

Now, above being said from a pure reasoning standpoint, I just have to give
it a spin. Below code does that. What surprises me is that RESTORE from a
database snapshot accepts the NORECOVERY option, but the database is
accessible. It seems like the NORECVOERY option is a no-op; and IMO should
produce an error message...

USE master
GO

ALTER DATABASE Northwind SET RECOVERY FULL
GO

CREATE DATABASE n
ON  PRIMARY
( NAME = N'Northwind', FILENAME =
N'C:\DemoDatabases\DbFiles\a\Northwind.ss')
AS SNAPSHOT OF Northwind
GO

UPDATE Northwind.dbo.Categories
SET Description = 'Cheeeesus'
WHERE CategoryID = 4
GO

RESTORE DATABASE Northwind FROM DATABASE_SNAPSHOT = 'n' WITH NORECOVERY

--Above NORECOVERY is a no-op. Database is accessible:
SELECT * FROM Northwind.dbo.Categories
DROP DATABASE n



--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

04-Mar-10 10:02 AM

Much better after a nights sleep.

I see what you mean about the OP using a non-SQL Server snapshot. I never
heard of equallogic before, but Google and Dell have.

I suspect that the equallogic snapshots were being treated as backups, which
were being supplemented with SQL Server transaction log backups. Thus
compounding a mistake with another mistake.

A snapshot is only like a backup if there is no hardware failure. If it is a
SQL Server snapshot, it cannot even be backed up.

A transaction log may only be applied after restoring a SQL Server BACKUP.

08-Mar-10 05:40 PM

Here is their white paper
http://www.equallogic.com/uploadedFiles/Resources/White_Papers/WP911_SQL-Server_Advanced-Protection.pdf
it looks to me like they use this auto-snapshot manager, to take a snapshot
of the database, then do their own internal snapshot, with some intellegience
to go get all the data, so that they can do a revert and allow you to apply
the logs afterwords.

So here is the basic scenario, that I think would work, forget equallogic
for this.
Assume I am doing logs every 15 minutes
3:00pm I snapshot the database
3:20 I am told the database is corrupted . (of course the following depends
on the corruption)
Recovery -
Backup the tail
revert to the snapshot with norecovery
restore the two logs
That would work correct?
So
Then the equallogic smart copy snapshots might basically do the same thing,
except they are creating a physical copy of all the data.

08-Mar-10 08:57 PM

Mark,

A few points.

- Snapshots are not backups as they do not copy anything and the snapshots
themselves cannot be backed up.
- Snapshots work by making a physical copy of a page in the database when it
is changed, so that the original may be maintained, thus increasing I/O for
inserts, updates and deletes.
- While I would have to test it to make sure your scenario would work, if
you are correct about it being a SQL Server snapshot, then yes that
procedure should work.
- If you have a drive failure, your "backup" solution WILL NOT WORK and you
WILL LOSE your database.

I simply cannot stress enough that a snapshot is not a backup and should not be
treated like one.

Jay

--
Jay Konigsberg
SQL Server DBA in Sacramento, CA
http://www.linkedin.com/in/jaykonigsberg

Live in Sacramento, CA?
Join the Sacramento SQL Server User Group on LinkedIn
http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg

09-Mar-10 01:14 AM

Your scenario is basically the traditional scenario, but you introduce some
foreign backup technology for the database backup instead of SQL Server's
own backup (I have elaborated on this here:
http://www.karaszi.com/SQLServer/info_minimizing_data_loss.asp). The
whitepaper say that you can use this technology together with log backups,
but being a marketing flyer it does not say anything on how you do it. So,
you need to work out how you revert to such a snapshot with the same
functionality as the TSQL NORECOVERY option (database being in restoring
state). This has to be done in conjunction with a restore from their
snapshot, cannot be done afterwards.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi