CODEWITHSUNDEEP
sqlsql-server-2008migrate
bitcycle
bitcycle

Reputation: 7792

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

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.

Upvotes: 2

Views: 9288

Answers (3)

Keith
Keith

Reputation: 1

I ran into this problem as well, and just a little add on I had to use the REPLACE clause because the database I was restoring to was different than in the database backup:

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',   
**REPLACE**,
NOUNLOAD,   
STATS = 10 
GO

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 134923

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

Upvotes: 1

Aaron Alton
Aaron Alton

Reputation: 23236

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

Upvotes: 3

Related Questions