Attaching Database with mdf file

[Diesel]

Is there any way to attach a database in MS SQL SERVER if you do not know the original database name?

[Mykre]

Yes it is possible to do it but you will need to understand some TSQL,

Have a look in the Books online or go to SQLservercentral.com and search for sp_attachdb, this is the stored proce that you need to use.

 

If you have the client tools there is also a wizard that can attach the databases but It is esier and more stable to use the raw commnads to do the process.

 

If you have the MDF Files you do not need to know the SQL Server name that it was created on or the user permissions inside, becuase once you do attach it you can add users and permissions yourself

 

Is there any way to attach a database in MS SQL SERVER if you do not know the original database name?

[Diesel]

Have you accomlished this task? I tried using enterprise manager and sp_attach_db with no luck. The errors I receieve are LSN name not recognized.

[Joe Mamma]

-- create a db called blah on c:\
CREATE DATABASE blah
ON 
( NAME = blah_dat,
  FILENAME = 'c:\blah_dat.mdf'
)
LOG ON
( NAME = 'blah_log',
  FILENAME = 'c:\blah_log.ldf'
)
GO
-- create table 'blah' in blah
create table blah.dbo.blah(i int)
GO
-- insert a value into blah
insert into blah.dbo.blah values(1)
GO
-- select from blah to confirm the data is there
select * from blah.dbo.blah
GO
-- now detach blah
sp_detach_db @dbname='blah'
GO
-- now attach blah as newblah
sp_attach_db @dbname='newblah', @filename1='c:\blah_dat.mdf'
GO
-- select from newblah to confirm the data is there
select * from newblah.dbo.blah
GO
-- this command fails
select * from blah.dbo.blah

[Diesel]

Received this error when ran sp_attach_db command:

 

Server: Msg 1813, Level 16, State 2, Line 1

Could not open new database 'ASI'. Create Database is aborted.

Device activation error. The physical file name 'S:\Data\ASIFA_LOG.LDF' may be incorrect.

 

I have the old log file, but it's saved in c:. Im assuming if I create an S: drive and place the log file there the attach command will work. Why is it searching for the old log file?

[Joe Mamma]

are you using

 

EXEC sp_attach_db @dbname = 'newname',

@filename1 = '[MDF DATA FILE PATH HERE]',

@filename2 = '[LDF FILE PATH HERE]'

 

do you have both files???

 

 

try

 

EXEC sp_attach_single_file_db @dbname = 'newname',

@physname = '[MDF FILE HERE]'

 

it should create a new log file in the MDF's directory

Edited October 27, 2004 by Joe Mamma