how to migrate from sql 2000 to sql 2008 via restore?

Question

I'm running this code:

RESTORE DATABASE [MyDB_2009May11] 
FROM  DISK = N'C:\DB Copy\MyDB_2009May11.bak' 
WITH  
MOVE N'Archive_Data' 
    TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\MyDB_2009May11.mdf',  
MOVE N'Data' 
    TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\MyDB_2009May11.mdf',  
MOVE N'Log' 
    TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\MyDB_2009May11.ldf',  
NOUNLOAD,  
STATS = 10
GO

I'm getting this error:

Msg 3176, Level 16, State 1, Line 1 File 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\MyDB_2009May11.mdf' is claimed by 'Archive_Data'(3) and 'Data'(1). The WITH MOVE clause can be used to relocate one or more files. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

How the crap do I migrate from sql server 2000 to 2008 when all I have is the backup file?

:) Any help is appreciated.

Answer 1

You're trying to move Archive_Data and Data to the same file - change the file name and you should be fine :)

Answer 2

do a select * from sysaltfiles and look at the correct filenames and then change the names in the move to part accordingly

This will give you the names of all the filegroups currently

select filename from master..sysaltfiles
where name = 'MyDB_2009May11'

if you don't have the databse scripted yet then do this first. make sure the folders exists, you might also want to change the filesizes

    CREATE DATABASE [MyDB_2009May11] ON (NAME = N'Data',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\MyDB_2009May11.mdf'
     , SIZE = 12, FILEGROWTH = 10%),
     (NAME = N'Archive_Data',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\MyDB_2009May11.ndf'
     , SIZE = 12, FILEGROWTH = 10%)
    LOG ON (NAME = N'Log', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\MyDB_2009May11.ldf'
     , SIZE = 21, FILEGROWTH = 10%)
     COLLATE SQL_Latin1_General_CP1_CI_AS

Now the restore should be this, notice the ndf extension for the archive filegroup

RESTORE DATABASE [MyDB_2009May11] 
FROM  DISK = N'C:\DB Copy\MyDB_2009May11.bak' 
WITH  
MOVE N'Archive_Data' 
    TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\MyDB_2009May11.mdf',  
MOVE N'Data' 
    TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\MyDB_2009May11.ndf',  
MOVE N'Log' 
    TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\MyDB_2009May11.ldf',  
NOUNLOAD,  
STATS = 10
GO