November 9, 2011 at 1:39 pm
I am moving a database from SQL Server 2000 to SQL Server 2008 R2 (10.50.1600.1) and it gets to 100% but then nothing happens. If I run it through the wizard, it says 100% and the green circle keeps spinning for hours, if I run it through QA it gets to 100% but the query keeps executing. I ran sp_who2 and i see the database, and the command says it is in DB STARTUP.
November 9, 2011 at 1:40 pm
script:
RESTORE DATABASE MyDB
FROM DISK = N'C:\InstallMedia\wsquared\MyDB.bak'
WITH FILE = 1,
MOVE N'MyDBDat.mdf' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.V10\MSSQL\DATA\MyDB.mdf',
MOVE N'MyDBLog.ldf' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.V10\MSSQL\DATA\MyDB_1.ldf',
NOUNLOAD, REPLACE, RECOVERY, STATS = 10
GO
November 9, 2011 at 1:49 pm
Anything in the SQL error log relating to this DB?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 9, 2011 at 2:09 pm
1 transaction rolled back in database MyDB. Recovery is writing a checkpoint in database.
The funny thing is that I dont get an error message when the recovery process is happening. I restart the instance and I see MyDb but it isnt accessible, these are the messages I get after the restart.
November 9, 2011 at 2:10 pm
the mdf and ldf files are the correct size as well.
November 9, 2011 at 2:12 pm
I am also getting a converting database MyDB from version 539 to the current version 661
November 9, 2011 at 2:14 pm
Can you just post the last few messages in the log?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 9, 2011 at 2:20 pm
2011-11-09 15:01:19.86 spid13s A new instance of the full-text filter daemon host process has been successfully started.
2011-11-09 15:01:19.89 spid13s Starting up database 'msdb'.
2011-11-09 15:01:19.89 spid14s Starting up database 'ReportServer$V10'.
2011-11-09 15:01:19.89 spid15s Starting up database 'ReportServer$V10TempDB'.
2011-11-09 15:01:19.94 spid10s Clearing tempdb database.
2011-11-09 15:01:19.96 Server A self-generated certificate was successfully loaded for encryption.
2011-11-09 15:01:19.99 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2011-11-09 15:01:19.99 Logon Error: 17187, Severity: 16, State: 1.
2011-11-09 15:01:19.99 Logon SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: 10.0.0.7]
2011-11-09 15:01:20.17 spid10s Starting up database 'tempdb'.
2011-11-09 15:01:20.21 spid21s The Service Broker protocol transport is disabled or not configured.
2011-11-09 15:01:20.21 spid21s The Database Mirroring protocol transport is disabled or not configured.
2011-11-09 15:01:20.23 spid21s Service Broker manager has started.
2011-11-09 15:01:21.99 spid18s 1 transactions rolled back in database 'MyDB' (9). This is an informational message only. No user action is required.
2011-11-09 15:01:21.99 spid18s Recovery is writing a checkpoint in database 'MyDB' (9). This is an informational message only. No user action is required.
2011-11-09 15:01:22.05 spid18s Converting database 'MyDB' from version 539 to the current version 661.
2011-11-09 15:01:22.05 spid18s Database 'MyDB' running the upgrade step from version 539 to version 551.
2011-11-09 15:01:35.71 spid53 Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
2011-11-09 15:01:35.71 spid53 Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
November 9, 2011 at 3:47 pm
This is the log as I am doing the restore, I had to restart the entire process but this is the log as I am restoring:
Date,Source,Severity,Message
11/09/2011 16:37:16,spid57,Unknown,Recovery is writing a checkpoint in database 'MyDB' (9). This is an informational message only. No user action is required.
11/09/2011 16:37:16,spid57,Unknown,Starting up database 'MyDB'.
11/09/2011 16:37:13,spid57,Unknown,The database 'MyDB' is marked RESTORING and is in a state that does not allow recovery to be run.
11/09/2011 16:37:12,spid57,Unknown,Starting up database 'MyDB'.
11/09/2011 16:34:11,Logon,Unknown,Login failed for user 'sa'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
11/09/2011 16:34:11,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 38.
11/09/2011 16:33:41,spid51,Unknown,Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
11/09/2011 16:33:41,spid51,Unknown,Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
November 9, 2011 at 7:56 pm
Applying Service Pack 1 for SQL Server 2008 R2...see if that helps.
November 9, 2011 at 9:04 pm
did not work....
November 9, 2011 at 9:58 pm
wanted to know if others have had issues restore a sql 2000 x86 database to a sql 2008 r2 x64 instance?
November 9, 2011 at 11:08 pm
patrick-511016 (11/9/2011)
wanted to know if others have had issues restore a sql 2000 x86 database to a sql 2008 r2 x64 instance?
Per MSDN there shouldn't be any issues, if:
The database being restored must be at least version 80 (SQL Server 2000) to restore to SQL Server 2008 R2. SQL Server 2000 or SQL Server 2005 databases that have a compatibility level less than 80 will be set to compatibility 80 when restored.
Note: After you restore a SQL Server 2005 or SQL Server 2000 database to SQL Server 2008 R2, the database becomes available immediately and is then automatically upgraded. If the database has full-text indexes, the upgrade process either imports, resets, or rebuilds them, depending on the setting of the upgrade_option server property. If the upgrade option is set to import (upgrade_option = 2) or rebuild (upgrade_option = 0), the full-text indexes will be unavailable during the upgrade. Depending the amount of data being indexed, importing can take several hours, and rebuilding can take up to ten times longer. When the upgrade option is set to import, the associated full-text indexes are rebuilt if no full-text catalog is available. To change the setting of the upgrade_option server property, use sp_fulltext_service.
November 9, 2011 at 11:34 pm
no full text....just sits at 100%
The last two entries in the log are:
(Last Entry)-Recovery is writing a checkpointin database 'MyDB' (9). This is an informational message only. No user action is required
(2nd to last entry)-Starting up database 'MyDB'
November 9, 2011 at 11:43 pm
patrick-511016 (11/9/2011)
no full text....just sits at 100%The last two entries in the log are:
(Last Entry)-Recovery is writing a checkpointin database 'MyDB' (9). This is an informational message only. No user action is required
(2nd to last entry)-Starting up database 'MyDB'
How big the DB is? How much time it took to get 100% message? If it’s feasible, would you mind giving it another try for fresh BACKUP and RESTORE?
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply