sp_attach_db 1

[abacaxi]

Hello All:
I posted a thread last week about this but just recently tried it.

I am on SQL Server 7.0 and need to attach a .mdb file. SQL Server 2000 is nice because it has a GUI option of attaching but not 7.0

So this is the procedure I used while logged in as 'sa' in MASTER:

EXEC sp_attach_db @dbname = N'FIRE',
@filename1 = N'c:\Fire_RMS\FIRE.mdf'

but I get this error:

Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'c:\Fire_RMS\FIRE.mdf' is not a valid database file header. The FILE SIZE property is incorrect.

I assume this sp will create a db named 'FIRE'? Or do I need to create the DB first? Actually I did that but another error said that the DB was already created.

Any ideas?

Thanks,
Todd

 

[tlbroadbent]

1) You don't create a database first if you want to attach it.

2) sp_attach_db requires at least two files - data )usually .mdf) and log (usually .ldf). Use sp_attach_single_file_db if you only want to attach one file. SQL Server will create a new transaction log file.

3) Was the backup file created on the same version of SQL Server where you are attempting to attaching? You cannot attach a SQL Server 2000 MDF in SQL Server 7.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[abacaxi]

Terry,

Thanks for your feedback.

The .mdf file I want to attach is from the same version...7.0

I tried using the sp_attach_single_file_db (obviously modifying the args that are specific to this sp) but I get the same error.

Todd

 

[SQLBill]

How did you copy/backup the .mdf file in the first place? Did you just copy the file or did you run the detach command and then copy/backup the .mdf file?

-SQLBill

 

[abacaxi]

Good point SQLBill...I just copied and pasted!

It sounds like I should detach first? If I run a detach, does it leave a copy on the source server or does it completely detach it?

 

[tlbroadbent]

If you detach the database, both the MDF and LDF files will remain on the server. However, the database will not exist in SQL Server. You shuold detach, copy the files and then attach the databse on the source server. An alernate method is to backup the database and restore the database on the other server. Check out the following links.

INF: Moving SQL Server Databases to a New Location with Detach/Attach

http://support.microsoft.com/default.aspx?kbid=224071

INF: Moving SQL Server 7.0 Databases to a New Server with BACKUP and RESTORE

http://support.microsoft.com/default.aspx?kbid=304692

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[SQLBill]

The issue with just copying and not detaching or using BACKUP commands is what's known as OPEN FILES. SQL Server databases are open files meaning that they are always available and being used right now. It's like having a MS WORD document open on your screen. A backup program may not be able to backup that Word document accurately because it doesn't know if you are making changes to it (the 'status' of the data is unknown). But once you close the WORD document, the backup can work because the status isn't changing. This is basically what happens with SQL Server. As long as the database is attached, the status is UNKNOWN - it could be changing. But detaching it lets the system know that nothing is happening to it so it's in a 'static' mode. SQL Server BACKUP commands enable the database to be backed up while the database is still being used.

So, in short:

BACKUP command lets you backup the database while it's still in use. Requires the RESTORE command to be used to restore the database. This creates backup files (.bak and others).

DETACH command lets you copy/backup the database, but the database is not available for use. Requires ATTACHing the database to restore it. This uses the actual database files (.mdf and .ldf).

Which is preferable? Depends...
Detaching is quicker, the copied/backed up files are the actual database files and are quicker to restore.
Backing up with the BACKUP command is longer, but the database can still be used while the backup is being done.

I will normally do BACKUP commands. But if I know I have to do something with the database/system that might cause me to do a restore, then I will detach the databases and move/copy them to another location.

-SQLBill

 

[abacaxi]

To all:

Thanks for your suggestions!


Todd