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

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
0
mcrmg
Asked:
mcrmg
  • 9
  • 7
  • 5
2 Solutions
 
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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