• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 597
  • Last Modified:

Restore SQL Server 7 database on 2000

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.
etc.............
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.
0
WendyJ99
Asked:
WendyJ99
1 Solution
 
atplackCommented:
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.

0
 
DanielztCommented:
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.

0
 
atplackCommented:
WendyJ99,
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?

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
ARSRINICommented:
WendyJ99

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.
0
 
WendyJ99Author Commented:
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:

RESTORE DATABASE NEWDB FROM DISK = 'C:\temp\olddb.dump'
WITH MOVE 'data_01' TO 'C:\db\sqlserver\data\NEWDB_data.mdf',
MOVE 'log_01' TO 'C:\db\sqlserver\log\NEWDB_log.ldf',
RECOVERY
go

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.



0
 
WendyJ99Author Commented:
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.

Wendy
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now