Attach Database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Attach Database

Author

Topic

hbradshaw
Starting Member

5 Posts

Posted - 2009-08-27 : 08:20:20

Hello:

I'm new to this forum and to SQL Server. I come from an Oracle environment.

I'm trying to attach a SQL Server database which is located on my local drive. These database files accompany a book I am using to learn about SQL Server.

Well, after attaching the database, I ran a simple SELECT statement to view the data. The query returns with zero records.

The database file size is 1,344KB. There are actually two files: UsersDB.mdf and UsersDB_log.ldf.

This is what I did to attach the DB:
1. Open SQL Server Management Studio
2. Open a connection
3. Right-click on Databases
4. Click Add
5. Browse my local drive and select the .mdf file
6. Click OK
7. Clicked Refresh

I then ran my SELECT query and came back with zero results.

Did I do something wrong? Why didn't the data import? The table structure is there.

Thank you in advance for the help.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-08-27 : 08:38:22

How many tables are there in your structure and what query did you run. I am guessing you have attached rather than added a new database. When you right click on databases, it should say Attach.., not Add?

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-27 : 08:39:51

Looks like you attached the database correctly.
So you see the database listed, and all it's objects (TABLES, VIEWS, etc)

Maybe the table you're query is just empty.

Run this to check if there is any data in the database:
USE UsersDB
go
exec sp_spaceused

Run this to check if any table has data:
select OBJECT_NAME(id), rowcnt
from sysindexes
where id > 99 and rowcnt > 0

hbradshaw
Starting Member

5 Posts

Posted - 2009-08-27 : 08:44:33

quote:
Originally posted by RickD

How many tables are there in your structure and what query did you run. I am guessing you have attached rather than added a new database. When you right click on databases, it should say Attach.., not Add?

Yes, when I right-click on Databases, I used Attach. Then a dialog box appeared and it was on that box that I clicked Add.

hbradshaw
Starting Member

5 Posts

Posted - 2009-08-27 : 08:46:05

quote:
Originally posted by YellowBug

Looks like you attached the database correctly.
So you see the database listed, and all it's objects (TABLES, VIEWS, etc)

Maybe the table you're query is just empty.

Run this to check if there is any data in the database:
USE UsersDB
go
exec sp_spaceused

Run this to check if any table has data:
select OBJECT_NAME(id), rowcnt
from sysindexes
where id > 99 and rowcnt > 0

Hi,

Yes, I do see the database listed along with all the objects.

As for running the checks, do I run these statements in Management Studio or do I have to go to a command prompt?

Thanks.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-27 : 08:55:44

In Management Studio, open a new query.

hbradshaw
Starting Member

5 Posts

Posted - 2009-08-27 : 09:16:50

quote:
Originally posted by YellowBug

In Management Studio, open a new query.

Hi,

Now this is interesting. In Management Studio, when I click Databases to expand my tree, I see the .mdf file that was attached. I then clicked on the + sign to expand some more and I click the + sign next to Tables. The table which appears is dbo.Users.

Question, when you attach a database file, is the name of the table going to be changed to something different? Or, should the table name be the same name as the .mdf file?

When I run the first query you gave me:
USE UsersDB
go
exec sp_spaceused,

I received the following error message:
Msg 911, Level 16, State 1, Line 1
Database 'UsersDB' does not exist. Make sure that the name is entered correctly.

This error makes sense since the table which appears in the Databases section doesn't contain the table UsersDB instead it has dbo.Users.

I went ahead and modified the above query to use dbo.Users, I receive the following message:
Msg 911, Level 16, State 1, Line 1
Database 'dbo' does not exist. Make sure that the name is entered correctly.

I'm somewhat at a loss. I don't understand why the Attach changed the name. And, I also don't quite understand why dbo isn't being recognized.

Doesn't "dbo" in dbo.Users represent the Owner or schema for this table?

I wish there was some way I can forward this .mdf file to have someone test it themselves and see if I'm doing something wrong or whether the file isn't any good.

Thank you for your time and help. I'm trying to make sense out of all this.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-27 : 09:21:11

There can be many tables in a database, of whatever names.
How many tables are there in this database?

What is the name of the database? You'll see that in the list of Databases in Management Studio?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-08-27 : 09:22:43

A database can have many tables of different names, it does not have to (or even usually) contain a table with the same name as the database. The .mdf and .ldf files are just physical files sitting on a disk somewhere.

If you open anew Query window and type SELECT * FROM Users while ensuring that the white box on the toolbar shows the name of your database.

hbradshaw
Starting Member

5 Posts

Posted - 2009-08-27 : 09:37:37

quote:
Originally posted by YellowBug

There can be many tables in a database, of whatever names.
How many tables are there in this database?

What is the name of the database? You'll see that in the list of Databases in Management Studio?

When I click on
Databases, this is what appears:
C:\USERS\OWNER\DOCUMENTS\ASP.NET 3.5 UNLEASHED\FINALCODE\CHAPTER03\CS\APP_DATA\USERSDB.MDF

I click on the + sign next to this database and then click on the + sign next to Tables. The table that appears is dbo.Users.

My confusion also lies in the path that is being shown. The file was not located in this path: C:\USERS\OWNER\DOCUMENTS\ASP.NET 3.5 UNLEASHED\FINALCODE\CHAPTER03\CS\APP_DATA\. The file was located in the following path C:\ASP\USERSDB.MDF.

I don't quite understand where this C:\USERS]OWNER\..... path is being derived.

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-27 : 11:38:20

Hmm.. Where is this? In Windows Explorer?
You will not see a path or even the mdf files in Management Studio.
Can you open the Object Explorer?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources