SQL 7 datafile in SQL 2k

[cglisc]

Howdy,

We have an MSSQL 7 datafile on a win2k machine that crashed on us a couple of weeks ago (hardware problems).

We setup another machine, but we are know using SQL2K. Is there a way to get SQL2k to read/use SQL7 datafile?

Thanks,
Chris Lambrou

 

[ClaireHCM]

You can use sp_attach_db/sp_attach_single_file_db to attach the sql7 datafile.
After execute this ,the sql7 database will be upgraded to sql 2000 and creat a new database in sql 2000
Say your datafile and logfile of that database is located in
d:\test_data.mdf and d:\test_log.ldf.The query shoudl be

sp_attach_db 'dbname',
'd:\test_data.mdf',
'd:\test_log.ldf'

execute this in Sql2000 Query analizer.Natually it will create a database called 'dbname' with data file and log file.

 

[cglisc]

Thanks for the reply. I was able to do what I wanted to do by simply restoring a backup of the SQL 7 database into the SQL2k installation. But I was not aware of sp_attach_db system proc.

 

[ClaireHCM]

Yes,there are 2 ways you can do.

One is sp_attach_single_db and sp_attach_db.

The other is restore the backup file with replace in sql2000.
Say your database in sql7.0's mdf and ldf files are located in
D:\MSSQL7\data\x.mdf for databafile
D:\MSSQL7\data\x_log.LDF for lofgile
and the name of datafile is 'x' and name for log file is 'x_log'

You can first backup this database to a backupdevice or any disk path.Then in sql2000 query analyzer execute the following query

restore database x from disk='d:\xbackup.bak' with replace,
move 'x' to 'd:\x_data.mdf',
move 'x_log' to 'd:\x_log.ldf'

Here I backed up the database to disk = 'd:\xbackup.bak' and move the mdf and ldf file to new location for easy management.

 

[ClaireHCM]

Additional,replace should be used only if you create a database called x.

If you want to create the database on the fly when executing the resotore query.Remove the "replace," and execute it.