Restore a SQL 2000 DB to SQL2008

hi,

I just installed SQL2008, and I have a SQL2000 db backup, how can I restore it back to SQL 2008?
in the meantime, any good books for 2008?  thx
mcrmgAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

chapmandewCommented:
you can do a simple backup and restore to 2008.  You can use the same commands provided in this link.

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx
0
mcrmgAuthor Commented:
i have the backup file already, but in 2008, I did not see where I can do the restore...thx
0
chapmandewCommented:
in a new query window, you can do this:

restore filelistonly from disk = 'c:\backupfile.bak'

--get the names from the list above


restore database DBname
from disk = 'c:\backupfile.bak'
with move 'datafile' to 'c:\datafile.mdf',
move 'logfile', to 'c:\logfile.ldf'

you get the datafile and logfile values from the restore filelistonly statement.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Mark WillsTopic AdvisorCommented:
Or, you can also create a new database (using ssms) right click on the databases folder goup and create a new databse, then right click on that database and restore your backup over the top of (need to select the database - bit different from 2000 days, and then go into options on the left and overwrite database, and enter in the correct filenames).

as for a good book, found the MSDN libraries and books online are pretty good. there are also a couple floating in the e-learning education pages (microsoft) but not quite released just yet.
0
mcrmgAuthor Commented:
hi,

I am getting this error

An exception occured while executing a Transact-SQL statments or batch

The backup set holds a backup of database other than the existing "my_db" database RESTORE DATABASE is terminating abnornally
(MS SQL Server, Error: 3154)

help..thx
0
chapmandewCommented:
do this:

backup database yourdbname
to disk = 'c:\dbname.bak' with init

then try the restore process again.
0
mcrmgAuthor Commented:
?
This is how I bk the db, in em, all tasks->bk db

and then in sql 2008, created a new db, and do the restore...thx
0
chapmandewCommented:
back the database how how I indicated above....when you're backing it up in EM, you're appending to the current media set, which is causing your error above.  
0
mcrmgAuthor Commented:
please bare with me, where should I type in this?
backup database yourdbname to disk = 'c:\dbname.bak' with init

thx
0
chapmandewCommented:
in a new query window in Query Analyzer...swap out yourdbname w/ the name of your actual database.
0
mcrmgAuthor Commented:
ok, will try it and post back..thx
0
Mark WillsTopic AdvisorCommented:
chapmandew is correct, but if you are still struggling, use Enterprise Manager, right click on the database, "all Tasks", "Backup Database" then the pop-up window will display the last backup options - you can change those without affecting any maintenance plans - simply remove the current backup destination (remove button on right towards bottom) and add a new file c:\dbname.bak (press add button, then a new pop-up, select "file" radio button and type in the name). If it is a brand new name, then you do not have to worry about override or append, but could choose override, and then press OK.
0
mcrmgAuthor Commented:
this is the new error I have:

Restore failed for server 'myserver\myserver' (microsoft.sqlserver.smoextended)

additional info

system.data.sqlclient.sqlerror: the backup set holds a backup of a database ither than the existing 'mydb' datanase (microsoft.sqlserver.smo)
0
Mark WillsTopic AdvisorCommented:
Yes, you are still appending backups to the backup media - do the Enterprise Manager thingy and OVERWRITE the backup data set (use a new name though).
0
chapmandewCommented:
...or use my original sql statement.  :)
0
Mark WillsTopic AdvisorCommented:
Or you can always do that... yes...

just make sure it is NEW NAME, somewhere different, so there is no risk of picking up any other backup....
0
mcrmgAuthor Commented:
ok, I will try a new name, but it is a brand new server, not a single db is on that except the empty db I created...
0
chapmandewCommented:
I am pretty sure the new name isn't necessary so long as you use INIT w/ the backup statement.
0
mcrmgAuthor Commented:
I put INIT with bk stmt, I am still getting the same error, if I need to keep it as the same name, any ideas?  thx
0
mcrmgAuthor Commented:
hi, EEs,

it still doesnt allow me to restore 200 db to 2008....any other work around?  thx
0
Mark WillsTopic AdvisorCommented:
Please examine below, check the steps, check naming and let us know where it goes wrong...

/****************************************************************************************************************
 
In this example there is a new backup folder - the sample uses C:\backup  
if you want something different, must change every instance of just that name.
 
Also
 
In this example there is a database - the sample uses  mrwtemp  
if you want something different, must change every instance of just that name, we do use other bits.
 
You will be creating a new database in SQL2008. It can have the same name, but disk files should be located
seperately to the old SQL2000 - otherwise you could render the old database inoperable.
 
You will be changing servers after the backup, and need to run each command individually on the right server 
as detailed below. To run an individual command, simply highlight and press the F5 button on your keyboard.
 
There are 3 SQL commands:
 
First  = In SQL2000 run the backup command 
Second = In SQL2008 run the restore filelist only command
Third  = In SQL2008 tun the restore database command
 
***************************************************************************************************************/
 
-- 1) in windows explorer on your server create a brand new folder that you have write access to - say c:\backup 
-- 2) in Enterprise Manager, click on the database name, open a query window, 
--    or, in query analyser, open a new query and select / connect to your SQL2000 database
-- 3) run the backup command
 
 
BACKUP DATABASE mrwtemp TO DISK = 'c:\backup\mrwtemp_20081205.bak'              -- dont forget change mrwtemp to your dbname and c:\backup to your foldername
 
--    you will get some messages at the end - copy them to notepad
 
-- 4) in windows explorer - check it is there - if not there STOP now and advise
-- 5) get out of Enterprise Manager and / or query analyser
-- 6) if neccessary, copy that folder to the SQL2008 server
-- 7) log into SQL 2008 SQL Server Management Studio
-- 8) open a new query window and run the following command
 
RESTORE FILELISTONLY FROM DISK = 'c:\backup\mrwtemp\mrwtemp_20081205.bak'
GO
--    it will display a results grid and/or messages
-- 9) if you get an error message - STOP now and advise - copy that error message to notepad.
--10) the grid has a column LogicalName. Write them down. Normally only two rows, but can be more
--    the grid also has a Type column - D is for data and extension MDF, L is for log and extenson LDF
 
RESTORE DATABASE New_Database                                          -- name of new database - can be same
   FROM DISK = 'c:\backup\mrwtemp\mrwtemp_20081205.bak'                        
WITH 
   MOVE 'mrwtemp'     TO 'c:\New_Database_Folder\New_Database.mdf',    -- first name after MOVE is the logical name for backup DATA. Last name is the NEW location, file name should be as per database name
   MOVE 'mrwtemp_log' TO 'c:\New_Database_Folder\New_Database.ldf'     -- first name after MOVE is the logical name for backup LOG.  Last name is the NEW location, file name should be as per database name
GO

Open in new window

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
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 2008

From novice to tech pro — start learning today.