Problem to restore full databases in SQL Server 7.0

[gc]

The SQL server is in NT 4 and we have backup every day from NT on the tape. But we don't have backup in SQL server (only one backup in August). Now the SQL server is installed in another NT 4 server. What I need to do is copy all the databases(master, msdb, model,and user databases) into the new SQL server. Because the path is different, after I did restore by using the old master backup file, I can't start SQL Server. The error message is caould not find the SQL server. I tried to rebuild master databse but it failed.
Can anyone tell how to fix this problem? First use the database files on the tape if possible. Second use the old backup files to restore.
Thank you very much!
George

 

[aalmeida]

What is the Back up sw you used to back up to tape?
How the back was set? AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[Dilyias]

Ok, NT Server A has your current database.
You want to transfer that to NT Server B if I understand it.

What we do is have SQL server maintence plans that backup our production database every night. After this the backup files (saves the last 3 days worth) get backed up to tape so all I need to do is restore the most recent backup if needed.

Is it possible to make a backup(s) of your current server A databases? If so you can re-install SQL server on server B, then 'restore' each of the databases (or your custom ones on tape assumming they are backups and not mdf files), be sure to overwrite the existing database and specifify the new paths manually, where you want the database to go. If a database does not exist then you could create a new, blank one , and then restore over it.

Another method to transfer databases is to detach and re-attach the databases (mdf &/or ldf files).

To detach a database, all users must be logged out, and you must put the db in single usermode (sp_dboption 'database', 'single user', 'true' assuming SQL 7), then sp_detach_db 'database' . Copy these files to the new server and then run an 'sp_attach_db' or 'sp_attach_single_file_db' depending ..read Books Online on these sp's as they provide more details than I can here)

I suppose you could try to attach those db files on the tape as well..assumming they are mdf files and not actual backups.

I've done both methods to transfer databases from server to server - although I haven't done this to the master DB, only to our custom databases. Is there a reason you need to copy over the master as well?

Also, do you use veritas to backup? If so what method? Using SQL to create a backup, or plain NT files backup?

Hope this is helpful info!

 

[gc]

Hi! Al and Dilyias:
Thank you very much for your response. I'll try your suggestion and let you know the result.
We use Veritas for NT performing full backup. We don't have Veritas SQL option, only Veritas open file mgr because this was test project, that has now become critical.
Because the NT server A is crashed so we can't do rectore in SQl anymore. We have all the databases - master, model msdb, temp and appllication (both .mdf and .ldf) on the tape.
Thanks again!
George

 

[gc]

Hi! Dilyias:
I got problem when I use sp_attach in single user mode for copy the user database Main on the tape.
The procedure is as follows:
Create Main in the sql server, then replace the Main_Data.mdf and Main_log.ldf from the tape. Then run the sp_dettach which is fine.
When run the sp_Attach I got error message:
Could not be opened because some of files could not be activated.
How to activate these files? I even don't know what files are. Plaese help and thanks in advance!
George