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
biffbyunAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrandonGalderisiCommented:
To help clarify, run that in a Query window.  Not in query builder.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.