WendyJ99
asked on
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.
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.
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.
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.
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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\NEWD
MOVE 'log_01' TO 'C:\db\sqlserver\log\NEWDB
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.
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\NEWD
MOVE 'log_01' TO 'C:\db\sqlserver\log\NEWDB
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.
ASKER
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
But thanks for all suggestions.
Wendy
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.