Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

What is Original DB Name when attaching db

Posted on 2003-12-03
10
Medium Priority
?
534 Views
Last Modified: 2012-06-27
Hi All,  

We have an application that operates on a named instance of  MSDE.   In the instance there can be 1 -> many databases all with a standard structure.

Anyway,  when we ship a new database the MDF is sent on CD, the application then copies this to a specifed location and attaches it.    Hey presto...

However the snag is that we need to be able to attach the database as the original name of the database, simple enough however, on top of this requirement we need to effectively have the ability to display the original name of the database in an "Attach Database" dialog box (kind of like a file browser dialog which displays the original db name kind of like this:

Please select database to attach:

MyDb  (mydatabase.mdf)
TestDb (anotherdb.mdf)

etc

I am sure that somehow retrievable from unattached MDF since in EM when you select a file to attach it displays the original db name as default in the dialog box.  We dont want to resort to shipping and further files containing this info as that will lead to other problems.

One dirty option we have which we will take if we have no other alternative to is to open the file in a binary stream and append to the end the name, then strip this off before attaching.

Any help would be appreciated.
0
Comment
Question by:ShogunWade
  • 5
  • 4
10 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9867053
Not familiar with MSDE, but if the command:

RESTORE HEADERONLY
FROM < backup_device >

is available, you could run that within your app, capture the output, and extract the db name.  That command doesn't require you to know the db name up front.
0
 
LVL 18

Author Comment

by:ShogunWade
ID: 9867111
Trouble is it's not a backp, but rather the MDF file itself.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9867475
DOH!  Sorry about that.  Don't think I can help.

I've looked thru .MDFs on occassion and haven't see the db name in plain text that I recall.  Perhaps it's in DBCS or encoded in some way.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 18

Author Comment

by:ShogunWade
ID: 9867916
What method have you used to look into the MDF ?  A binany file viewer or something of that nature ?
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9868082
Mostly DBCC PAGE, which does do a binary print essentially, and also just using Word and Notepad.  Also tried doing a string search for the db name in the file ane couldn't find it.  

Of course, when you print using DBCC PAGE, each four bytes of data is "flipped", e.g, if the print out shows "001e0010" for the first four bytes, the actual values are "10001e00" (byte 4 -> 1, byte 3 -> 2, etc.).  Tricky to read.  Didn't verify if it's actually stored that way or just prints that way.
0
 
LVL 18

Accepted Solution

by:
ShogunWade earned 0 total points
ID: 9868426
YES YES YES.


Ive worked it out.  

I traced EM in Profiler and this is how it is done......



dbcc checkprimaryfile (N'C:\mydb.mdf', 2)

returns:

Database name      MyDatabaseName
Database version      539
Collation      872468488


with 0 as the parameter it returns whether it's an MDF or not

and with 3 as the parameter it returns the entire file group filename and virtual name.





0
 
LVL 18

Author Comment

by:ShogunWade
ID: 9868507
Thanks for your input scott hope you dont mind if I reclaim points .
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 9868973
No, not at all.  Glad you posted the solution, I'll try to remember that in the future.
0
 
LVL 18

Author Comment

by:ShogunWade
ID: 9873170
I like to share this sort of stuff.

what I forgot to mention is that the call only works on a non attached DB.  It errors on a sharing violation if it is aleady attached.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question