如何通过恢复从sql 2000迁移到sql 2008？

发布于 2024-07-25 01:11:59 字数 842 浏览 15 评论 0原文

我正在运行此代码：

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

我收到此错误：

Msg 3176，Level 16，State 1，Line 1 文件“C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\MyDB_2009May11.mdf”由“Archive_Data”(3) 和“Data”(1) 声明。 WITH MOVE 子句可用于重新定位一个或多个文件。消息 3013，16 级，状态 1，第 1 行 RESTORE DATABASE 异常终止。

当我只有备份文件时，我如何从 sql server 2000 迁移到 2008？

:) 如有任何帮助，我们将不胜感激。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浪漫人生路 2024-08-01 01:11:59

您正在尝试将 Archive_Data 和 Data 移动到同一个文件 - 更改文件名，应该没问题:)

回复收藏 0 原文

眼泪也成诗 2024-08-01 01:11:59

从 sysaltfiles 中执行 select * 并查看正确的文件名，然后相应地更改移动到部分中的名称。

这将为您提供当前所有文件组的名称，

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

如果您还没有编写数据库脚本，那么然后先执行此操作。确保文件夹存在，您可能还想更改文件大小

    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

现在恢复应该是这样，注意存档文件组的 ndf 扩展名

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

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

回复收藏 0 原文

终陌 2024-08-01 01:11:59

我也遇到了这个问题，只需添加一点，我就必须使用 REPLACE 子句，因为我要恢复的数据库与数据库备份中的数据库不同：

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

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

回复收藏 0 原文

~没有更多了~