?
Solved

Restore a SQL 2000 DB to SQL2008

Posted on 2008-11-06
21
Medium Priority
?
4,210 Views
Last Modified: 2012-08-14
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
Comment
Question by:mcrmg
  • 9
  • 7
  • 5
21 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22894633
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
 

Author Comment

by:mcrmg
ID: 22894762
i have the backup file already, but in 2008, I did not see where I can do the restore...thx
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 22894809
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 22913017
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
 

Author Comment

by:mcrmg
ID: 22921777
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 22921799
do this:

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

then try the restore process again.
0
 

Author Comment

by:mcrmg
ID: 22921896
?
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 22921900
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
 

Author Comment

by:mcrmg
ID: 22922145
please bare with me, where should I type in this?
backup database yourdbname to disk = 'c:\dbname.bak' with init

thx
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 320 total points
ID: 22922159
in a new query window in Query Analyzer...swap out yourdbname w/ the name of your actual database.
0
 

Author Comment

by:mcrmg
ID: 22922200
ok, will try it and post back..thx
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22922362
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
 

Author Comment

by:mcrmg
ID: 22959211
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22959850
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 22959860
...or use my original sql statement.  :)
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22959946
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
 

Author Comment

by:mcrmg
ID: 22961742
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 22961871
I am pretty sure the new name isn't necessary so long as you use INIT w/ the backup statement.
0
 

Author Comment

by:mcrmg
ID: 22974902
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
 

Author Comment

by:mcrmg
ID: 23100796
hi, EEs,

it still doesnt allow me to restore 200 db to 2008....any other work around?  thx
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 480 total points
ID: 23101197
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question