Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

restore database Expand / Collapse
Author
Message
Posted Wednesday, January 6, 2010 10:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, February 27, 2014 10:41 AM
Points: 361, Visits: 1,781
I am trying to restore a database thru Management studio

restored database full backup with no recovery
Now trying to restore diff backup with recovery, throwing below error

The backup set holds a backup of a database other than the existing 'test' database.


Can anybody provide the tsql command to restore

Restore full backup with no recovery
restore diff backup with no recovery
restore diff 2 backup with recovery
Post #843011
Posted Wednesday, January 6, 2010 11:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:52 AM
Points: 39,867, Visits: 36,210
Are you sure the diff you're trying to restore belongs to that database? The error indicates that it doesn't


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #843047
Posted Wednesday, January 6, 2010 11:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 1:06 PM
Points: 2,827, Visits: 8,479
laddu4700 (1/6/2010)

Restore full backup with no recovery
restore diff backup with no recovery
restore diff 2 backup with recovery


You only need to restore the most recent diff, then all the transaction logs more recent than the diff.



Post #843067
Posted Wednesday, January 6, 2010 11:43 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:11 PM
Points: 17,601, Visits: 15,465
homebrew01 (1/6/2010)
laddu4700 (1/6/2010)

Restore full backup with no recovery
restore diff backup with no recovery
restore diff 2 backup with recovery


You only need to restore the most recent diff, then all the transaction logs more recent than the diff.


Yes - as homebrew said. Eliminate the middle diff backup and then add your tran logs more recent than the last diff.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #843073
Posted Wednesday, January 6, 2010 12:25 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, February 27, 2014 10:41 AM
Points: 361, Visits: 1,781
Thanks to all, i was selecting wrong back up file

So Now i want to verity the updated data

I have one full backup file 12/31/2009 12:20AM
Diff backup file 12/31/2009 06:35 AM
TLOG BACKUP 180 files between 6:35 am to 12:35pm

I want to restore each tlog with recovery and check the data..every time do I need to delete database and restore with tlog with recovery? or any other way to do it
Post #843122
Posted Wednesday, January 6, 2010 12:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:52 AM
Points: 39,867, Visits: 36,210
Restore it with Standby. Otherwise you'd have to drop the database and start from scratch after each log restore.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #843141
Posted Wednesday, January 6, 2010 8:59 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 15, 2014 5:34 AM
Points: 2,609, Visits: 3,903
Hi Gail,

How can we make a database in standby mode if we are specifying the WITH NORECOVERY option?

Can you please let me know how to restore the database in standby mode?What about the subsequent diff backup and log backup restoration?


TIA
Post #843287
Posted Thursday, January 7, 2010 1:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:52 AM
Points: 39,867, Visits: 36,210
Ratheesh.K.Nair (1/6/2010)
How can we make a database in standby mode if we are specifying the WITH NORECOVERY option?

You can't. If you want the database in Standby, you use WITH STANDBY instead of WITH NORECOVERY

Can you please let me know how to restore the database in standby mode?What about the subsequent diff backup and log backup restoration?


May I suggest a read of Books Online, specifically the sections on restoring a log.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #843358
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse