SQL Server Performance Forum – Threads Archive

Change DBID

I want to order DBIDs in my Dev server same as my production. For example if dbid for DBOne in Production is 7, then it has the 7 in Dev server, too. I can detach the databases in my Dev server and reattach in order. Is there easier way? Can I just update the sysdatabases? CanadaDBA

---

Don’t ever update system tables directly. Other than that, I can’t think of a situation where DBIDs must be identical – you would only use a DBID if you’re calling SPs or functions that require it as a parameter, right? In that case you should use transparent code like … DECLARE @dbid INT
SET @dbid = DB_ID() … and work with @dbid. If the code is running in another database, then use SET @dbid = DB_ID(‘my_database’) etc., etc.

---

I am going to replay a production’s trace on my Test server. Please refer to the following link. http://www.databasejournal.com/features/mssql/article.php/10894_1479971_3
…After capturing the trace file we can now replay that trace file in our Development environment. There are several "gotchas" that you will want to keep in mind when replaying a trace on another server. Most of these are mentioned in bol (books online) but they bear repeating here:
…
Gotcha 3: The dbid of the database in Development must match the dbid of the database in Production. You can determine the dbid of your database with the following query:
…

Thanks, CanadaDBA

---

Gotcha indeed! If you restore a backup of the production master database, wouldn’t that kind of copy the DBIDs? And the other gotchas (login related) too?

---

Hummm! Copy the master is wise but then I have to be careful to have the same databases in my Test environment first.<br />Still detach and reattach in order is easier. [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Gotcha indeed!<br /><br />If you restore a backup of the production master database, wouldn’t that kind of copy the DBIDs? And the other gotchas (login related) too?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />CanadaDBA

---

Hummm again! If you restore master to an instance with no other user databases, then – I wonder – do the non-matching DBIDs get lost, or if you attach a database under that name, does it get the unclaimed DBID for the matching name?

---

Just remove the dbid column from the replay trace… Or make sure that when you run the replay you remove dbid from the column list.

---

Instead of messing with the server, how about a search and replace in the trace log? Keith Payne
Technical Marketing Solutions
www.tms-us.com

---

DBID DBName
5 DBOne
3 DBThree
1 DBTwo
2 DBFour If I add a database to the server, it will get 4 as DBID. If I delete/detach any of the databases then the next time that I add/attach a database, it will get the very first available DBID. But still I am not comfortable with copy only the master database to the target server. I think it will mess everything or makes the new server unstable. Do you have any experience of copying master database alone to another server for any reason?
CanadaDBA

---

Sounds good! I didn’t think about it. BTW, we decided to recreate our Test server with a production’s snap shut. I mean copy all physical files from production to Test. And of course by following the instructions for this work.
CanadaDBA

---

]]>