Replace database with same database id

Question

Replace database with same database id

Ashwin M N

Hall of Fame

Points: 3668
More actions
August 2, 2013 at 12:40 pm

#296457

I have to replace XX database from backup in production. The third party tool and reporting services have been configure to database id so I want to restore database without breaking database id. Can I know simple overwrite restores database with same database id? or any other method is there to restore keeping same database id.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Ashwin M N (8/2/2013)
I have to replace XX database from backup in production. The third party tool and reporting services have been configure to database id so I want to restore database without breaking database id. Can I know simple overwrite restores database with same database id? or any other method is there to restore keeping same database id.

wow never heard of an app locking down the database id.

say for example, you have a server with 14 databases, but you need database id 7 for your special application.

you'll need to

1.find the current database that has id 7, and either backup + drop, or detach the database.

2.restore or attach the database that needs to be #7,

3. restore or reattach the original #7, so it becomes #15.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

Yeah, that's crazy.

Is there any way you can "spoof" the database id so it can be something else in the real server but report back to the software what it expects.

Heaven help you when you have to disaster-recover that db!

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

WolfgangE SSCertifiable Points: 6328 More actions · Answer 3

ScottPletcher (8/2/2013)
Yeah, that's crazy.

indeed :w00t: never heard of this too.

I would never rely on the dabase ids beeing the same after restore.

Kick this third party tool and find another one or try to change the reports so that they are using the database name. Maybe you can use the system table master.sys.databases as kind of mapping table when changing the selects of the reporting tool.