Restore SQL Server 7 database on 2000

Posted on 2003-02-24
Medium Priority
Last Modified: 2012-08-13
I'm trying to restore a backup from a version 7 database onto a 2000 Server. All works until I get the following errors:

1> 2> 3> 4> 5> 6> Processed 39256 pages for database 'NEWDB', file 'data_01' on file 1.
Processed 1 pages for database 'NEWDB', file 'log_01' on file 1.
Msg 9002, Level 17, State 6, Server TESTSERVER, Line 2
The log file for database 'NEWDB' is full. Back up the transaction log for
the database to free up some log space.
Msg 3013, Level 16, State 1, Server TESTSERVER, Line 2
RESTORE DATABASE is terminating abnormally.
Converting database 'NEWDB' from version 515 to the current version 539.
Database 'NEWDB' running the upgrade step from version 515 to version 524.
Database 'NEWDB' running the upgrade step from version 524 to version 525.
Database 'NEWDB' running the upgrade step from version 535 to version 536.
Database 'NEWDB' running the upgrade step from version 536 to version 537.
The statement has been terminated.

Unfortunately, I can't dump the transaction log, or do anything else, because the DB is still at the wrong version - I get this error:

Cannot open database 'NEWDB' version 536. Upgrade the database to the latest version.

How do I upgrade the database to the correct version, and get the database restored?

I don't know whether this is a really difficult problem, as I'm pretty new to SQL Server - but I'm struggling with it now, so it's worth 300 points to me.
Question by:WendyJ99
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8012335
Hi WendyJ99,

I am not sure but I think you are running out of disk space on your Log directory.  Make sure that when you restore, you are pointing the log to a disk that has enough space for the upgrade.

This database is over 160M in size so you may need to have much more space than that to go from 515 to 539.


Expert Comment

ID: 8012471
try two ways:

1: Try to enlarge your log file size.

2: Use trunc. log on chkpt. database option.

trunc. log on chkpt.
Causes the inactive portion of the transaction log to be truncated (committed transactions are removed) every time the CHECKPOINT process (manual or automatic) occurs. For more information, see Checkpoints and the Active Portion of the Log.
When trunc. log on chkpt. is set to true, the database is in log truncate mode. When a database is in log truncate mode, an automatic checkpoint occurs in the database every time the log becomes either 70 percent full or the number of records in the transaction log reaches the number SQL Server estimates it can process during the time specified by the recovery interval server configuration option. For more information, see Truncating the Transaction Log and recovery interval Option.

SQL Server does not, however, issue the checkpoint if the log cannot be truncated because of an outstanding transaction. When trunc. log on chkpt. is set, SQL Server also attempts a checkpoint on a log full error. It can be useful to select this option while developing the database to prevent the log from growing.

While the trunc. log on chkpt. database option is set to true, a backup of the transaction log cannot be created because the truncated transaction logs in the backups cannot be used to recover from media failure. Issuing the BACKUP LOG statement produces an error message which instructs you to use the BACKUP DATABASE statement. For more information, see Creating and Applying Transaction Log Backups.

By default, trunc. log on chkpt. is true when using SQL Server Desktop Edition, and false for all other editions.

The tempdb database is always truncated by the checkpoint process even if the trunc. log on chkpt. database option is set to false.

The status of this option can be determined by examining the IsTruncLog property of the DATABASEPROPERTY function.


Expert Comment

ID: 8014053
Please post your backup command from the OSQL or ISQL including any command line options when you are running these programs.

It looks like you have 6 lines of code that you are typing into the utility. These would be helpfull to see how you are restoring the database.  Also, is this into a NEW database or over an existing database?

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Accepted Solution

ARSRINI earned 600 total points
ID: 8015466

Is the database in the 2000 server a new database or an existing one? If new, you could try to do the following which is really very simple.

1. Connect the two servers in the same network and register the SQL of the ver 7.0 server on the 2000 server.
2. Create a new database in the 2000 server with sufficient file sizes for the database and the log files.
3. Generate the scripts for the database by Generate Scripts option from All Tasks in the 7.0 server.
4. Copy the script to the 2000 server
5. Run the script on the newly created database (this can be done from the Querly Analyser)
6. Use the Export Data option from All tasks to copy the data from the 7.0 server to 2000 server.

Of course, if your requirement is necessarily to restore from a tape, then you need to follow Danielzt's suggestion.

Author Comment

ID: 8018138
Thanks for all your suggestions:

atplack: I've got 22GB free on that disk, so I don't think that disk space is the issue here.

danielzt: How can I enlarge the log size, while I'm in the middle of a restore? I already have the trunctate log on checkpoint option set to true.

atplack: Here's my command line for the restore:

WITH MOVE 'data_01' TO 'C:\db\sqlserver\data\NEWDB_data.mdf',
MOVE 'log_01' TO 'C:\db\sqlserver\log\NEWDB_log.ldf',

I'm tried creating a database, then restoring over it, and also restoring to a new database, and always got the same results.

ARSRINI - I'm going to see if I can find a SQL 7 server to connect to and test this out - if this works, I'll take this to be the answer.


Author Comment

ID: 8106887
This didn't really give me what I needed, it worked, but as it was a large database, (or a slow network), it took me 2 days to move everything across.  The main delay was in actually generating the scripts, but I discovered that I could have skipped this, as the export option allowed me to create the tables and procedures separately.

But thanks for all suggestions.


Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question