Database – How to restore to a SQL backup I made two days ago

databaserestoresql-server-2005

Database disasters only happen to other people, right? RIGHT?!?

I'm sure this has never happened to anyone since the beginning of computer history, but, believe it or not, I'm having a problem with a database restore. Perhaps you can help. I'm hoping it's something really simple.

Question: How do I restore to a backup I made two days ago?

The GUI doesn't seem to be working and I can't find the right TSQL to do it manually.

DETAILS

I took a backup of an SQL Server 2005 database on Monday, November 9th 2009 at around 9:30 AM using SQL Server Management Studio with the following options:

Recovery model: Simple
Backup type: Full
Backup component: database
Expires after: 0 days
Destination: Disk

I would like to restore this backup but I'm running into some problems.

Attempt #1: Use the GUI

Used the following options in the Restore Database dialog box:

To database: [MyDatabaseName]
To a point in time: '11/9/2009 12:00:00 AM 0 milliseconds
From database: [MyDatabaseName]
Overwrite the existing database: true.
Recovery state: Leave the database ready to use by rolling back…

Error message: Exclusive access could not be obtained because the database is in use. I tried to set to single_user, restart SQL Server service, etc. Never could figure out how to terminate existing connections in the Restore Database GUI.

Attempt #2: Use TSQL

ALTER DATABASE [MyDatabaseName]SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE [MyDatabaseName]SET RECOVERY FULL
GO

RESTORE DATABASE [MyDatabaseName]
FROM DISK = N'F:\DatabaseLocation\BackupName.bak'
WITH FILE = 1, NOUNLOAD, RECOVERY, REPLACE, STATS = 10, STOPAT = 'Nov 9, 2009'

Message: This backup set contains records that were logged before the designated point in time. The database is being left in the restoring state so that more roll forward can be performed.

I've tried different versions of the above with REPLACE, NORECOVERY, RECOVERY, etc.
Do I need to know the backup time to the exact millisecond? And if so, how do I find that?

Best Answer

You can't restore to point in time a SIMPLE recovery database. Only FULL or BULK recovery mode databases can be restored to point in time.

See Recovering to a Specific Point in Time for the specific syntax (the option is specified at RESTORE LOG time, not at RESTORE DATABASE time). Also see Restoring a Database to a Point Within a Backup for more generic points about STOP AT usage.