sp_attach_db has too many arguments specified

Question

sp_attach_db has too many arguments specified

Cory E.

SSCertifiable

Points: 7076
More actions
January 12, 2006 at 2:44 pm

#113067

Hey all
I have a database I am moving from one server to another, and am using the following as my way to do it:
Copy data from old server to new server - CHANGING the structure
There are over 16 files, so, I am instructed to use the "Create DB For Attach"
I did what the documentation says (CREATE DATABASE Database name
ON PRIMARY (FILENAME = 'Installation Drive:\Program Files\Microsoft SQL Server\Your Instance of SQL Server\Data\Primary file name.mdf')
FOR ATTACH)
However, it fails on every file stating:
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'J:\Database\Datafile\<DBNAME>_idx0504.ndf' may be incorrect.
And yes, that is incorrect, I wanted to change the location (drive as well)
Help???
SQL Server 2000 on W2k3

-- Cory

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

If the files are not found in the old location (which is stored in the mdf file) you need to specify each file in the CREATE DATABASE ... FOR ATTACH statement.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

jordanac Hall of Fame Points: 3653 More actions · Answer 2

jordanac

Hall of Fame

Points: 3653

January 12, 2006 at 11:04 pm

#614537

Also check your file extention .MDF vs .NDF

Andy.

Cory E. SSCertifiable Points: 7076 More actions · Answer 3

Thanks all, I think I got it figured out. Based on on Chris' comment, I just started naming all the files. The documentation, IMHO did not adaquatly explain this, and I felt that the "Create Database" was truly going to create the database. This "for attach" is a slick option. I may re-write my scripts to go this route as opposed to the sp_attach_db. it seems silly that the sp_attach_db has a limit of 16 files.

Again, thanks

-- Cory

tagudfaith SSC Enthusiast Points: 112 More actions · Answer 4

Hi,

May I ask how you manage to attach the database by using Create Database scripts?

I'm trying to attach the database using this script:

CREATE DATABASE StoreBO

ON PRIMARY (FILENAME = 'C:\MSSQL\Data\StoreBO_Data.MDF')

FOR ATTACH

But there's an error:

Server: Msg 9003, Level 20, State 1, Line 1

The LSN (1618:92:1) passed to log scan in database 'StoreBO' is invalid.

Connection Broken

I have 17 physical files to attach, how do I add/include them one by one on the script?

Pls. help.

Thanks and God Bless,

Faith

Bryansky SSCommitted Points: 1992 More actions · Answer 5

Hi Faith,

I think if you going to attach the DB files that is different from the source/original path or directory.. you have to put all your DB files in the CREATE DATABASE FOR ATTACH script...

something like this...

CREATE Database database name

ON PRIMARY(

FILENAME1 = 'new path\DBFile.mdf'),

(FILENAME2 = 'new path\DBFile.mdf'),

.

(FILENAME17 = ' ')

FOR ATTACH

This is what i did last time... it worked.

:-):cool:

tagudfaith SSC Enthusiast Points: 112 More actions · Answer 6

Hi,

Thanks for the reply...

Do you have any idea on this error:

Server: Msg 9003, Level 20, State 1, Line 1

The LSN (1618:92:1) passed to log scan in database 'StoreBO' is invalid.

Connection Broken

This is after I run this script:

CREATE DATABASE StoreBO

ON PRIMARY

(FILENAME = 'C:\MSSQL\Data\StoreBO_Data.mdf')

FOR ATTACH

Faith

Bryansky SSCommitted Points: 1992 More actions · Answer 7

do you have the transaction log file? better include it in the script.

:-):cool: