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.
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.
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?
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.
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?
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
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'?
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?
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.
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.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.