Need help recovering SQL 7 database

[demoman]

First of all, my skill is in database design/SQL, rather than DBA stuff. A standalone workstation installation of SQL 7 crashed on Friday. I could not restart the engine. I tried re-installing SP3, it could not find the server. Finally, I performed a complete new install of SQL 7 and SP3. The service starts fine, but SQL server will not recognize 4 of the databases (2 .mdf, 2 .ldf). All I get is the standard stuff (master, pubs, northwind, etc). I am unsure how to proceed. I would be greateful for any advice.

Oh yeh, the user's last back-up was a month ago (and so it goes).

 

[codestorm]

So the 2 user databases aren't appearing any more ?

Search the SQLBOL for 'attach'. <insert witticism here>
codestorm

 

[tlbroadbent]

You should be able to use the system stored procedure sp_attach_db.

exec sp_attach_db
@dbname=N'dbname',
@filename1=N'<path>\filename.mdf',
@filename2=N'<path>\filename.ldf' Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[malkyb]

I have a very similar problem to 'demoman'!
I have had to reinstall SQL Server 7 and SP3 on an NT4 Server (SP6a) and set up new (different) locations for the 10 existing databases. Both ~.mdf, and ~ldf files are to be relocated to new seperate folders.
Prior to uninstalling, I performed full backups, and then ran sp_detach_db. The detached db ~.mdf and ~.ldf files were then moved to their new locations. SQL Server reinstalled cleanly.
I am not able now either to reattach any dbs, or to restore from the previous backups.

The following SQL:-
use master
go
exec sp_attach_db
@dbname=N'atest',
@filename1=N'f:\data\databases\atest\atest.mdf',
@filename2=N'e:\SQLTransactionLogs\atest\atest.ldf'

produces the following message

Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name 'f:\data\databases\atest\atest.mdf' may be incorrect.


Attempts to restore from ~.BAK using the following SQL

Restore database atest
from
DISK = 'E:\Apps\mssql7\Appstest\atest_db_200111071900.BAK'
with
MOVE 'atest_Data.MDF' to 'F:\SQLDatabases\atest\atest.mdf',
MOVE 'atest_Log.LDF' to 'e:\SQL7TransactionLogs\atest\atest.ldf',
RECOVERY,
REPLACE


result in:-

Server: Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'E:\Apps\mssql7\Appstest\atest_db_200111071900.BAK'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
Backup or restore operation terminating abnormally.


SQL Server Error log contains the following

BackupDiskFile::OpenMedia: Backup device 'E:\Apps\mssql7\Appstest\atest_db_200111071900.BAK' failed to open. Operating system error = 3(The system cannot find the path specified.).

(The file and path do exist, and can be viewed by NT Explorer. File and owning folders security Permissions are currently 'Everyone- Full control')

SQL Server errorlog also contains:

udopen: Operating system error 5(Access is denied.) during the creation/opening of physical device \\IS-DEV3\DATA\Databases\atest.mdf.

and
udopen: Operating system error 3(The system cannot find the path specified.) during the creation/opening of physical device f:\data\databases\atest\atest.mdf.

The same results occur whether I am logged in as sa, dbo, or Local NT system administrator.


Any advice would be very welcome.

Thanks

 

[Guest_imported]

LOL

 

[tlbroadbent]

Malkyb,

Are the drives where the data exists MAPPED network drives or are they actually physical drives on the server? SQL Server runs in its own set of credentials. SQL Server doesn't recognize drives MAPPED in a Windows session. If you are attempting to use drives on another server, I'd recommend that you not do so. See the SQL Server FAQ at

http://www.windows2000faq.com/Articles/Index.cfm?ArticleID=14285.

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[malkyb]

Hi Terry, thanks for getting back to me.
The data drives are all physical drives on the local NT server on which SQL Server is running

 

[chuckberetz]

I have a similar issue, but I'm probably screwed.

I backed up a SQL 7 db called &quot;backupfile&quot;. MS SQL Server as you know generates a file with no extension. I then moved &quot;backupfile&quot; to a storage server for savekeeping. Unfortunately, I did not move the .MDF and .LDF files.

The original server is toast; it's been completely wiped. Without the .MDF and .LDF files, is there any way to restore this db? It's essentially irreplaceable.

Thanks for your help!

Chuck

 

[tlbroadbent]

Chuck,

Add an extension to the backup file and try to retore it. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[malkyb]

Terry, I think I've resolved my problem - It seems to have been a permissions issue - by setting file and folder permissions to 'Everyone/Full', at least, I've restored my databases to a working state.
I'll worry about the security issue seperately!

Many thanks for your time, and for a great site.


MalkyB

 

[demoman]

sp_attach did work and I thank you for pointing me in the right direction!