DB Migration and dbid Mismatch

[craigfw]

I'm migrating 4 db's off the machine from Hell (development server thrust into production) onto a permanent production sql server on another file server. The current db dbids are in the left column and the new db's dbids to migrate to are in the right column:

Asset 7 7
GFPB 10 6
Location Users 9 8
Recruiting 14 9

The Assetdb is fine, but the others are... I have two approaches which I am considering. First I was thinking of changing the dbid's in the new SQL Server by updating the dbid in sysdatabases and performing a "forced load" or bcping the data using DTS. The second idea is to create and then drop databases until I arrived at the appropriate dbid for each of the migrating db's. The create and drop approach sounds foolish to me, but the SQL Server's system tables would have the correct dbids and other entries thoughout their tables wouldn't they?
Any suggestions or comments?

 

[JamesLean]

First question: why is it so important that you get the same dbids in the new server?

--James

 

[craigfw]

Approx. 7 years ago when I was a full-time, large systems dba, dbid's had to match or the system catalogs wouldn't link the db to the system's objects and nothing would recover (this was Sybase: MS SQL Servers source code).

If this isn't the case, then how does the SQL Server function currently?

 

[JamesLean]

All the databases objects are stored within the database itself. It really doesn't matter what the dbid is.

What method are you using to transfer the dbs? Using either detach/attach or restoring a backup should work fine.

--James

 

[craigfw]

I'm using DBArtisan's DataUpload wizard and I have moved the dumps from the other machine to the new SQL Server's local machine .

The reason I had to move the dumps (memory key migration, the new "sneaker net") is that a non-sa/dba web designer kept moving everything around before I arrived in my current postion and now the box has degraded in status, can't be in any domain, but is in a Workgroup and has two bound IP's pointing to two different websites.

Would you suggest that I use Enterprize Manager to load the databases? What success have you

 

[mrdenny]

I'd recommend backing up and restoring the databases to the new server, and calling it a day. Then move over any user accounts that need to be moved.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[SQLSister]

I'm with mr denny except I'd create the users first, then when you retore the databse, they just automatically inherit all the permissions they had before and you don't have the orphaned user issue.

Questions about posting. See faq183-874

 

[mrdenny]

Good point. The company I work at now, the DBs are such a mess that I'm constently restoring dbs, and having to deal with orphaned users. I've just got a script that I run after every restore that finds all the users and resynces them all.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)