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

restore a sql 2000 file to a sql 2008 database

Hi guys, we are currently running SQL 2000 Standard and are going to go SQL 2008 in the near future.  We want to do some testing on a test box.  I have two questions.  The first is can someone direct me to a link that covers the upgrade from 2000 to 2008?  Secondly, for the purposes of testing, I installed a fresh box with just SQL 2008.  Can I restore databases from a SQL 2000 backup to 2008 OR just make a copy of the existing mdb files and attach them in SQL 2008 or would this screw things up.  Also, if you know off the top of your heads, what are the performance benefits of upgrading consider it will be on the same hardware?  For example, speed of indexing and lock handling are two big problem areas for us.  Would we a marked improvement in these areas?  
Thanks,
Rob
0
biffbyun
Asked:
biffbyun
  • 6
  • 5
2 Solutions
 
BrandonGalderisiCommented:
Yes you can restore a SQL 2000 DB to SQL 2008.
Yes you can attach a SQL 2000 DB to SQL 2008.  But they should be detached from the previous.  You shouldn't just "copy" the files.

Online indexing and greater functionality are just two of the benefits.  But you will need a beefier system to get the similar performance.  That doesn't mean that if you have a beefed up server now, it will perform slower.  But if you don't have the space to make tempdb large enough for sql 2005/8 it will suffer.  SQL 2005+ uses more tempdb than 2000 did.
0
 
biffbyunAuthor Commented:
Hi, thanks for your response.  I tried restoring from a 2000 backup and I am getting an error.  Below is the text.

===================================

Restore failed for Server 'SQL2008DEMO\SQL2008'.  (Microsoft.SqlServer.SmoExtended)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
   at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseOptions.RunRestore()

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages)
   at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
   at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)

===================================

An error occurred during recovery, preventing the database 'hcs' (database ID 7) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=3414&LinkId=20476

------------------------------
Server Name: SQL2008DEMO\SQL2008
Error Number: 3414
Severity: 21
State: 1
Line Number: 1


------------------------------
Program Location:

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

0
 
BrandonGalderisiCommented:
I'm not familiar with executing restores with SMO, but are you specifying the equivalent "with move" option to specify a new valid path for the file?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
biffbyunAuthor Commented:
Yes, I am specifying the path when I do the restore.  It actually goes through and errors out at the very end whereas if it was a path issue it would error out in the begining.
0
 
BrandonGalderisiCommented:
Can you try doing the restore through SSMS (SQL Server Management Studio) to see if it gives you a more descriptive error.
0
 
biffbyunAuthor Commented:
That's what I used.  I can see the database, but it is classified in the management interface as 'suspect'
0
 
BrandonGalderisiCommented:
Are you sure the backup wasn't taken from a corrupt database?
0
 
biffbyunAuthor Commented:
no, the backup works if I restore to a sql 2000 server.
0
 
biffbyunAuthor Commented:
Anyone else care to take a shot?
0
 
BrandonGalderisiCommented:
I've asked chapmandew to join in the conversation!
0
 
chapmandewCommented:
give this a go:

restore database dbname from disk = 'c:\backupname.bak' with move 'datafile' to 'c:\datafile.mdf', move 'logfilename' to 'c:\logfile.ldf'
0
 
BrandonGalderisiCommented:
To help clarify, run that in a Query window.  Not in query builder.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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