cannot attach the 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

General SQL Server Forums

New to SQL Server Programming

cannot attach the database

Author

Topic

gtk
Starting Member

6 Posts

Posted - 2006-05-14 : 12:53:46

hi ,
i want to use a database that someone created in SQL SERVER 2000/WINDOWS SERVER 2003.My system is SQL SERVER 2000/XP SP2 PRO .The problem is that the database is an unknown file .I mean ,it isn't ,as usually, an .mdf or .ldf file ,so i cannot attach it to the server.
Is there a problem in compatibility between the different versions of windows?Or is missing something from the database?What can i do?

Thanx in advanced

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-14 : 13:02:09

>> Is there a problem in compatibility between the different versions of windows?
No.

Are you sure the database was detached and you have been sent the databaes files?
Do you know which is the data and which is the tr log?
Maybe you have a backup file rather than database files.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

gtk
Starting Member

6 Posts

Posted - 2006-05-14 : 13:08:38

firstly ,i'm newbie in databases.
i just have one unknown file without extension.Do you believe it's not the real database ?it's a backup file?What can i do now?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-05-14 : 13:10:29

Is the file a .bak file? That might answer the question Nigel asked about it being a backup file or not. If so, look up RESTORE DATABASE in Books Online.

Start/All Programs/Microsoft SQL Server/Books Online

Go to the Index tab and type in "RESTORE DATABASE". Read the section on "How to restore a database with a new name (Transact-SQL)".

If it's not a backup file, give us the extension of the file.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2006-05-14 : 13:13:42

You could try using:

RESTORE FILELISTONLY FROM DISK = 'x:\Mypath\MyFullBackupfile.BAK'

if the file IS a Backup file then you will get some information about the database it backed up. If not then it isn't a Backup file!

Kristen

gtk
Starting Member

6 Posts

Posted - 2006-05-14 : 14:06:18

I put the extension .bak in the file and i ran in the query analyzer the "RESTORE FILELISTONLY FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\myfile.BAK' ".

The output was the two logical names myfile_Data and myfile_Log that are existing in "physical name: C:\Program Files\Microsoft SQL Server\MSSQL\Data\ " .But i can't find them in this path.

I read the "How to restore a database with a new name (Transact-SQL)" and i run in query analyzer the next:

USE master
GO
-- First determine the number and names of the files in the backup.
-- MyNwind_2 is the name of the backup device.
RESTORE FILELISTONLY
   FROM myfile
-- Restore the files for MyNwind2_Test.
RESTORE DATABASE MyNwind2_Test
   FROM myfile
   WITH RECOVERY,
   MOVE 'myfile_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\data\MyNwind2_Test_data.mdf', 
   MOVE 'myfile_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\data\MyNwind2_Test_log.ldf'
GO

But it can't run .Any idea??

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-05-14 : 14:21:55

What was the error? You didn't run that Transact-SQL exactly like you posted it here did you???

Look up RESTORE in Books Online. Go to section E.

quote:

E. Make a copy of a database using BACKUP and RESTORE
This example uses both the BACKUP and RESTORE statements to make a copy of the Northwind database. The MOVE statement causes the data and log file to be restored to the specified locations. The RESTORE FILELISTONLY statement is used to determine the number and names of the files in the database being restored. The new copy of the database is named TestDB. For more information, see RESTORE FILELISTONLY.

BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY
FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'
GO

Think about what you are typing in, and what each of the values are for, before you execute in Query Analyzer. If you're still having issues after you try this, post the exact code you used and the exact error message.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-14 : 14:53:03

Sounds like your file is a backup file and is now
C:\Program Files\Microsoft SQL Server\MSSQL\Data\myfile.BAK

It's a bit odd that you have moved it to that destination and was it really called myfile?
Create a folder c:\sqldata and move the file into that so you have
c:\sqldata\myfile.BAK

now try
RESTORE FILELISTONLY FROM DISK = 'c'

This ahould give the same result as before with myfile_Data and myfile_Log. The paths given are from the source system where the physical files resided and are nothing to do with your machine.

Now run
RESTORE DATABASE mydb
FROM DISK = 'c:\sqldata\myfile.BAK'
WITH MOVE 'myfile_Data' TO 'c:\sqldata\mydb.mdf',
MOVE 'myfile_Log' TO 'c:\sqldata\mydb.ldf'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

gtk
Starting Member

6 Posts

Posted - 2006-05-14 : 16:37:59

ok ,i fixed it.thanx guys!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources