Restoring a Database 2

[fiber0pti]

I had a database running. SQL Server was on my o/s drive but the actually db_Data file was on another drive. When I reinstall SQL server, how do I restore the database from that file? I can't goto restore database becuase there is no backups to restore.

 

[redlam]

pretty sure you can just 'attach' that file.
in EM, right click on the database directory and select 'all tasks/attach database'.

 

[tlbroadbent]

If you have SQL 7 or 2000, use sp_attach_db. See topic in SQL BOL or article at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_52oy.asp.

WIth SQL 6.5 or earlier, it may be possible but will certainly be more difficult. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[fiber0pti]

How do I use the sp_attach_db? Where do I go in SQL 7.0 to type that in? Is there any other way?

 

[redlam]

i'm not sure if there was a shortcut for this in SQL 7 like there is in SQL2000... in either case, you can run sp_attach_db from query analyzer. i'm pasting the syntax from BOL:
sp_attach_db [ @dbname = ] 'dbname'
, [ @filename1 = ] 'filename_n' [ ,...16 ]

Arguments
[@dbname =] 'dbname'

Is the name of the database to be attached to the server. The name must be unique. dbname is sysname, with a default of NULL.

[@filename1 =] 'filename_n'

Is the physical name, including path, of a database file. filename_n is nvarchar(260), with a default of NULL. There can be up to 16 file names specified. The parameter names start at @filename1 and increment to @filename16. The file name list must include at least the primary file, which contains the system tables that point to other files in the database. The list must also include any files that were moved after the database was detached.

 

[redlam]

This example attaches two files from pubs to the current server.

EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'

 

[fiber0pti]

That didn't work. I get an error about how the file name may be incorrect. Is there any other way? I noticed in SQL 2000 that there is an attach database feature but it is dimmed therefore I can't use it. Any other way to restore the database?

 

[fiber0pti]

I've done this from stored procedures:
EXEC sp_attach_db @dbname = N'vizeon',
@filename1 = N'e:\Mssql7\Data\vizeon_Data.MDF',
@filename2 = N'e:\Mssql7\Data\vizeon_Log.LDF'

I get an error when doing this, is there any other way?
The error reads: Error 5105: Device activation error. The Physical name 'e:\Mssql7\Data\vizeon_Data.MDF' may be incorrect.

 

[tlbroadbent]

Try this command in Query Analyzer. No need to create a stored procedure.

exec sp_attach_single_file_db N'vizeon', N'e:\Mssql7\Data\vizeon_Data.MDF'

If it fails then answer the following questions so we can get some idea why it is failing.

Is the E drive a local or a network drive?
Can you access the database files in Windows Explorer?
Do they look complete having correct sizes?
Can you copy them to another location such as the C drive?
What result do you get if you run the following command?

exec xp_cmdshell 'dir e:\Mssql7\Data\vizeon_Data.MDF'

The sp_attach_db procedure is about the easiest procedure you will find. However, if the files have been corrupted and you have no backup, then you are probably out of luck. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[redlam]

the error suggests that the file name or path is incorrect but i'm sure you've already verified this and the only other option that i am aware of is what you described above from sql2000. if the 'attach database' option is dimmed, you most likely do not have the necessary permissions to perform this function. is it possible for you to log in as 'sa'?

 

[lhotta]

Also make sure that the E drive is not compressed.

 

[Cathryn]

I have a question along these lines. Our server went down so we weren't able to 'detach' the database files. Can I copy them to another server and create a database for them?

 

[tlbroadbent]

Yes. As long as you are attaching to the same version or higher of SQL Server.

SQL 7 can attach SQL 7 files.
SQL 2000 can attach SQL 7 and 2000 files. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.

 

[Pelman]

Similar problem to Cathryn's.
The db wasn't detached, but we had two log files.
Now I am unable to attach the db using either sp_attach_db or sp_attach_single_file_db.
Is there any trickery that I can use to re-attach this db?
We have no way to recover the log files.