3

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?

Improve this question
0

5 Answers 5

Reset to default
2

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.

Improve this answer
1
1

Sounds like you have it fixed, but I was typing my answer over on StackOverflow then discovered that the qustion had been closed and moved here, so here's my normal database restore command sequence in T_SQL if you need them

You will need to use

RESTORE FILELISTONLY FROM DISK = 'X:\path\MyBackupFilename.BAK'

to get the names of the logical devices (which will be unchanged if restoring to the same database that the backup was made from, but otherwise may be different)

USE master  -- (Can't sit in the database whilst its being restored!)
GO

-- ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE MyDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE MyDatabase
    FROM DISK = 'X:\path\MyBackupFilename.BAK'
    WITH
        REPLACE,
        NORECOVERY, -- Allow recovery of more DIFF/TLogs
        STATS = 10, -- Show progress (every 10%)
    MOVE 'MyLogicalName_data' TO 'X:\MSSQL\path\MyDatabaseFile.mdf', 
    MOVE 'MyLogicalName_log' TO 'X:\MSSQL\path\MyDatabaseFile.ldf'
GO

-- IF need to restore a later DIFF backup (made after this FULL backup):

RESTORE DATABASE MyDatabase
    FROM DISK = 'X:\path\MyDatabaseBackup_yyyymmdd_hhmm_Diff.BAK'
    WITH
        STATS = 10, -- Show progress (every 10%)
        NORECOVERY  -- Allow recovery of more TLog
GO


-- Optional restore of additional TRANSACTION LOGs
-- Repeat the following for each transaction log, in order

RESTORE LOG MyDatabase
    FROM DISK = 'X:\path\MyDatabaseBackup_yyyymmdd_hhmm_Trans.BAK'
    WITH
        NORECOVERY  -- Allo recovery of more TLogs
GO

-- When finished restoring all backup files "activate" the database
RESTORE DATABASE MyDatabase WITH RECOVERY
GO

-- **IF** Database was made with different Logical Names "rename" those now
ALTER DATABASE MyDatabase MODIFY FILE 
(NAME = 'LogicalNameFromBackup_data', NEWNAME = 'NewLogicalName_data')
GO
ALTER DATABASE MyDatabase MODIFY FILE 
(NAME = 'LogicalNameFromBackup_log', NEWNAME = 'NewLogicalName_log')
GO

-- If backup was made when DB in single / restricted user 
-- change access back to "normal"
-- ALTER DATABASE MyDatabase SET MULTI_USER, READ_WRITE WITH ROLLBACK IMMEDIATE
GO

If database has been restored onto a different server it will also be necessary to re-sync the database users with the server logins.

Improve this answer
1

You can kill the existing connections easily enough from the GUI. Just go to Management -> Activity Monitor. Sort by database and right-click and kill those who are in the db you wish to restore.

[edit]

This all assumes that your account isn't using that same database as the default, of course! If so, you'll want to change that and reconnect first.

Improve this answer
0

Have you tried to

1- Stop Sql Server.

2- Delete or move to another place physical files of the crashed database (.mdf and .log).

3- Start Sql Server.

4- Load backup

Improve this answer
0

Which database are you running the restore from? Make sure it's from one other than the db that you're restoring, and that you don't have any active queries in the Sql Management Studio.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .