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.
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
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?