Freeze when Restore DB is selected

[ddeegan]

Hello

I am running SQL 2K on Windows NT.

I am having a problem restoring my database.

When I right click on the database and select "Restore Database", Enterprise Manager freezes (Cntrl Alt Delete to "fix")

I looked and the DB is 3,777 MB with a transaction log of 141 MB. This problem does not occur with a smaller db (1,327MB/ trans log also 141 MB)

Any suggestions would be appreciated.

Thanks
Dave

 

[SQLBill]

Are there any error messages in the SQL Server Error Log or the Windows Event Viewer logs?

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Also, why restore from Enterprise Manager?

Use the SQL Server RESTORE DATABASE command. Refer to the BOL for syntax.

-SQLBill


Posting advice: FAQ481-4875

 

[ddeegan]

I do not see any error messages in SQL Server Error
Log or the Windows Event Viewer logs.

I was able to get the restore process to run but at
99% comlete a "Microsoft SQL-DMO (ODBC SQLState: 42000)" message reads

"Cannot use the backup set in file ‘d:\MSSQL7\BACKUP\d_info_db_200306191930.BAK’ for this restore operation. Backup or restore operation terminating abnormally."

This was though EM - I am looking at the syntax for the RESTORE DATABASE command

If this info gives you any other ideas, please let me know.

Thanks
Dave

 

[SQLBill]

Are you sure it's a good backup?

How was the backup done?

-SQLBill

Posting advice: FAQ481-4875

 

[ddeegan]

It was through in a Database Maintenance Plan through Enterprise Manager and set up years ago - everything looks fine. I'm not sure how I could tell - I don't see error messages anywhere.

 

[Catadmin]

Cannot use the backup set" indicates to me that either you are trying to use a differential backup where you need a full backup (or vice versa).

Also, are you actually trying to restore to SQL 7 or SQL 2000? If SQL 2000, that is probably your problem since you've got what looks to be a SQL 7 backup file. Bill, do you know if a 7 db can be straight restored to the 2k server?

Having never done a 7 to 2000 backup, I'm not sure how you go about upgrading that database if that is your option.

The date on your backup file looks pretty old. Are you sure you want to restore a database from June 2003?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLBill]

Bill, do you know if a 7 db can be straight restored to the 2k server?

I've never used ver7, but I don't think you can. I know I've seen a post where a person can't restore 6.5 to 2000 without upgrading first.

-SQLBill


Posting advice: FAQ481-4875

 

[Catadmin]

I know I've seen a post where a person can't restore 6.5 to 2000 without upgrading first.

That one I remember. I just couldn't remember if 7 could go to 2000. I'm leaning towards the answer of "it can't", but I'm not 100% sure.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLBill]

I'm leaning that way also (and leaning so far that I might fall out of my chair). I even recall seeing posts where there were issues restoring a database from a 2000 SP2 database to 2000 SP3, SP3 to SP4, etc.

I think the rule of thumb is upgrade in place first, then backup and restore elsewhere. Or restore to the same setup and then upgrade.

-SQLBill

Posting advice: FAQ481-4875

 

[ddeegan]

Thank you for all of your posts. I was able to get a DBA to look at it and the difference in the way he restored was to select "from device" rather than the default "Database" - this may go with the earlier post about the differential

I was also cutting the amount of space I had on the server a little close.

As far as the SQL7/2K issue - I might be able to test it but would be fine with taking your guys word for it.

Thanks again for all the help. Have a great weekend.

Dave