sql2000还原2005

1. 跪求如何将sql2000里的数据库还原到2005里！急！急！急！

这个很简单，你可以在cmd里,mysql的命令窗口中 :
mysql -u登录名 -p密码 回车登录，//登录名一般为root
create database 数据库名;//创建数据库,该数据库名为你要导入的数据库
exit;//然后退出
mysql -u登录名 -p密码 刚才创建的数据库名<d:\数据.sql

你也可以用Navicat 或SQL Manager 2010 Life for MySql 窗口化软件

2. 怎么让sql 2000的数据还原到2005上

may all know, SQL Server 2005 request a minimum of 8GB RAM to work… let’s say satisfactorily. I first didn’t know that and after a while from the upgrade I did from SQL Server 2000 to 2005 my SQL Services were starting to crash three or four times per DAY!!!

At first I thought I was being attacked, but soon I realized it was nothing like that. I then decided to downgrade to a SQL Server 2000 edition. Though I looked around the internet to find some information on how to do that, I got very disappointed when I realized that no actual documentation of any kind could be found for that. So I am posting this thread to inform you on the proceres I had to follow for this action.

Before beginning I must assume, firstly that the user, who will attempt such thing, has a basic knowledge of SQL Environment, secondly that he has the two versions already installed (both 2000 and 2005), that a basic backup of the databases has been created and finally that all the 2005 SQL Server Users have been created at the SQL Server 2000 environment as well.

Step 1: Generating Scripts for the Database Elements and Structures

1. Right-click over the desired Database at 2005, Choose Tasks and the Generate Scripts (Option).

2. At the pop-up Dialog Box click at the Script All Objects in the selected Databases check box, to activate it and then Click the Next Button.

3. Set the following Elements to the following Values

1. Script Collation , set to TRUE

2. Script Database Create, set to TRUE

3. Script of SQL Version, set to SQL SERVER 2000

4. Script foreign keys, set to FALSE

5. Script Triggers, set to FALSE Then Hit the Next button

4. Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.

5. Click Finish

After completing this procere, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Structure of the Database will be created.

Be careful, the SQL Server 2005 Edition inserts the Views in a random place through the script. Therefore, all the scripts that are referred to the Views MUST be moved to the end of the script. If the Query Analyzer shows some errors do not be bothered. Delete all the elements created from the script and after you fix the code run it again.

Step 2: Moving the data from 2005 to 2000

1. After completing the previous step successfully, moving the data follows. Right-click at the 2005 database you used to run the previous step and select Tasks and then choose the Export Data (option).

2. From the pop-up Dialog Box, select the Source Db and Click at the Next Button.

3. At the next step you will have to choose the destination server and the destination Database for the Data to be exported. Then Click Next.

4. A List of all the Source Database’s Elements will appear in the screen. Select one by one all the Elements you wish to move and for each one click at the button Edit Mappings (Located at the bottom right corner of the Dialog Box just under the Elements list). A new Dialog box will pop-up. Select the Delete rows in Destination Tables option and activate the Enable Identity Insert Option. (Remember to repeat this action for each of the selected Element from the list that will be moved.

CAUTION!!! A malfunction of the SQL Server 2005 has been found. Not sure why, after multiple tries I have observed that when I tried to move more than twelve Elements at once, the Export Data Wizard of SQL Server 2005 seemed to disable the Enable Identity Insert Option that was activated over the Edit Mappings Dialog Box. But if the number of the selected Elements is smaller than 12 no problem seemed to appear.

Step 3: Generating Scripts for the Database Foreign Keys and Triggers

Finally, to successfully finish the downgrade of the Database, the Triggers and the Foreign Keys of the DB must be proced. The procere that should be followed is the one stated next:

1. Right-Click at the SQL 2005 Database and Select from Tasks Menu the Generate Scripts Option.

2. Using the pop-up Dialog Box make sure that the check box Script All Objects in the selected Databases is not enabled and hit the Next Button.

3. Set all the Elements on the List to a False Value except the ones that follow:

1. Include IF NOT EXISTS , set to TRUE

2. Script Owner, set to TRUE

3. Script of SQL Version, set to SQL SERVER 2000

4. Script foreign keys, set to TRUE

5. Script Triggers, set to TRUE

6. Then Hit the Next button

4. After finishing reading the Elements of the Database, a new list will appear at the Dialog Box. Make sure that you select ONLY THE TABLES of the Database and hit the Next Button.

5. At the screen that follows hit the Select All button and the Next.

6. Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.

7. Click Finish Button.

After completing this procere, we have to move to the SQL SERVER 2000 environment. Here, by using the Query Analyzer, we will have to run the scripts that were generated using the master database. Copy and Paste the script at the Query Analyzer and run it. After that the Foreign Keys and the Triggers of the Database will be created.

After these steps the database should be fully functional under the SQL Server 2000 edition.

3. 目前有一个SQL2005备份出来的数据库，可是我电脑上装的是SQL2000，怎么可以还原数据库呀求解

先将SQL2005的数据还原到SQL2005，再通过SQL,导出数据库结构、数据库作业、数据库关系，用BCP导出数据（BCP OUT）;在SQL2000中创建数据库，并创建表结构，BCP IN导入数据，导入数据库作业，导入数据库关系；这样就行了，别的方法我试过没戏

4. 怎么才能将MSSQL2000的数据库备份文件还原为MSSQL2005呢

先把2000的数据库分离出来，然后复制一份到2005.在使用2005里面的附加数据库。这样以后就可以直接把2000的备份文件还原到2005数据库当中了。

5. 将sql2000数据库还原到SQL2005上

1。先在还原数据库任务中选择源（你要还原的数据库备份文件）
2。添加完成后再备份集 选框中打钩
3。点击选择页里“选项” 进入选项页面
4。还原选项选择覆盖原有数据库 并把“将数据库文件还原为” 其中的“还原为”项目中 数据文件和log日志该为目标文件的数据文件和日志地址

6. 如何将SQL server 2000的数据库转化为2005的数据库。

你看看能不能再mysql里面使用导入命令，首先安装mysql，登陆到mysql数据库，然后创建数据库，命令create
database
数据库名称，如果mysql的语句结束符是分号，然后use
数据库名称，然后输入source
sql文件所在的路径。
如果不行只能通过命令来转了。

7. 用sql2000备份的数据库用sql2005怎么还原

进SQL Server Management Studio
在数据库下右键 新建一个数据库 名字和2000下那个一样
点击完成
然后在新建的数据库上右键中 找到 任务 ---备份---数据库
选择源设备 找到你的2000数据库MDF文件 ，完了之后，在下面选 择你添加进来的数据库
在左方 有两个选项，一个‘常规’，‘选项’
选项里选择覆盖数据库 对于路径 你自己修改 到你的DATA下面去就行了

8. 如何sql2000的数据库还原到sql2005上

用微软提供的数据发布向导Database Publishing Wizard就可以在2005和2000之间转移数据库结构和数据，操作比较简单，具体搜索一下就知道了。

9. 怎么把sql2000的数据库恢复到sql2005中

sql2000的备份文件可以直接在sql2005恢复，恢复失败的话你可以找找自己的原因
可能的情况：备份文件坏；备份保存了全路径，这要求恢复的话要恢复到制定路径，或者自己写恢复语句，“move to" 指定新路径。
还有种方法，可以分离SQL2000数据库，将分离的SQL2000转移到安装SQL2005的服务器上附加上去，附加过程中SQL2000的数据库会升级到SQL2005数据库。附加升级不可逆，只有通过其他方法降级才能在SQL2000使用。

10. SQL2000数据库可以还原SQL2005的备份文件吗

其他地方找到的：

sql2005中的备份要在sql2000中还原，首先在sql2005中还原，然后选择兼容sql2000(80),备份后再在sql2000种还原。

用mdf和ldf来备份数据库，首先要分离数据库，右键备份数据库-任务-分离，对数据库分离，数据库的readonly要为false。分离后，在sql的data目录下找到要备份的mdf和ldf数据库。

还原时选择附加数据库，选择对应的mdf和ldf文件，附加即可。要判断要附加的数据库是否为只读，如果是，要去掉只读属性。要附加的数据库文件即mdf和ldf文件的安全设置中设置user用户完全控制权限，即为数据库文件的属性，设置成功后才可以附加。否则会抱错，要附加的数据库为只读。

要导出数据库脚本时,右键导出的数据库-任务-生成脚本

以下是sql语句备份还原数据库：

/*--备份数据库

--邹建 2003.10--*/

/*--调用示例

--备份当前数据库
exec p_backupdb @bkpath='c:\',@bkfname='db_\DATE\_db.bak'

--差异备份当前数据库
exec p_backupdb @bkpath='c:\',@bkfname='db_\DATE\_df.bak',@bktype='DF'

--备份当前数据库日志
exec p_backupdb @bkpath='c:\',@bkfname='db_\DATE\_log.bak',@bktype='LOG'

--*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_backupdb]') and OBJECTPROPERTY(id, N'IsProcere') = 1)
drop procere [dbo].[p_backupdb]
GO

create proc p_backupdb
@dbname sysname='', --要备份的数据库名称,不指定则备份当前数据库
@bkpath nvarchar(260)='', --备份文件的存放目录,不指定则使用SQL默认的备份目录
@bkfname nvarchar(260)='', --备份文件名,文件名中可以用\DBNAME\代表数据库名,\DATE\代表日期,\TIME\代表时间
@bktype nvarchar(10)='DB', --备份类型:'DB'备份数据库,'DF' 差异备份,'LOG' 日志备份
@appendfile bit=1 --追加/覆盖备份文件
as
declare @sql varchar(8000)
if isnull(@dbname,'')='' set @dbname=db_name()
if isnull(@bkpath,'')='' set @bkpath=dbo.f_getdbpath(null)
if isnull(@bkfname,'')='' set @bkfname='\DBNAME\_\DATE\_\TIME\.BAK'
set @bkfname=replace(replace(replace(@bkfname,'\DBNAME\',@dbname)
,'\DATE\',convert(varchar,getdate(),112))
,'\TIME\',replace(convert(varchar,getdate(),108),':',''))
set @sql='backup '+case @bktype when 'LOG' then 'log ' else 'database ' end +@dbname
+' to disk='''+@bkpath+@bkfname
+''' with '+case @bktype when 'DF' then 'DIFFERENTIAL,' else '' end
+case @appendfile when 1 then 'NOINIT' else 'INIT' end
print @sql
exec(@sql)
go

----------------------------------------------------------------------
/*--恢复数据库

--邹建 2003.10--*/

/*--调用示例
--完整恢复数据库
exec p_RestoreDb @bkfile='c:\db_20031015_db.bak',@dbname='db'

--差异备份恢复
exec p_RestoreDb @bkfile='c:\db_20031015_db.bak',@dbname='db',@retype='DBNOR'
exec p_backupdb @bkfile='c:\db_20031015_df.bak',@dbname='db',@retype='DF'

--日志备份恢复
exec p_RestoreDb @bkfile='c:\db_20031015_db.bak',@dbname='db',@retype='DBNOR'
exec p_backupdb @bkfile='c:\db_20031015_log.bak',@dbname='db',@retype='LOG'

--*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_RestoreDb]') and OBJECTPROPERTY(id, N'IsProcere') = 1)
drop procere [dbo].[p_RestoreDb]
GO

create proc p_RestoreDb
@bkfile nvarchar(1000), --定义要恢复的备份文件名
@dbname sysname='', --定义恢复后的数据库名,默认为备份的文件名
@dbpath nvarchar(260)='', --恢复后的数据库存放目录,不指定则为SQL的默认数据目录
@retype nvarchar(10)='DB', --恢复类型:'DB'完事恢复数据库,'DBNOR' 为差异恢复,日志恢复进行完整恢复,'DF' 差异备份的恢复,'LOG' 日志恢复
@filenumber int=1, --恢复的文件号
@overexist bit=1, --是否覆盖已经存在的数据库,仅@retype为
@killuser bit=1 --是否关闭用户使用进程,仅@overexist=1时有效
as
declare @sql varchar(8000)

--得到恢复后的数据库名
if isnull(@dbname,'')=''
select @sql=reverse(@bkfile)
,@sql=case when charindex('.',@sql)=0 then @sql
else substring(@sql,charindex('.',@sql)+1,1000) end
,@sql=case when charindex('\',@sql)=0 then @sql
else left(@sql,charindex('\',@sql)-1) end
,@dbname=reverse(@sql)

--得到恢复后的数据库存放目录
if isnull(@dbpath,'')='' set @dbpath=dbo.f_getdbpath('')

--生成数据库恢复语句
set @sql='restore '+case @retype when 'LOG' then 'log ' else 'database ' end+@dbname
+' from disk='''+@bkfile+''''
+' with file='+cast(@filenumber as varchar)
+case when @overexist=1 and @retype in('DB','DBNOR') then ',replace' else '' end
+case @retype when 'DBNOR' then ',NORECOVERY' else ',RECOVERY' end
print @sql
--添加移动逻辑文件的处理
if @retype='DB' or @retype='DBNOR'
begin
--从备份文件中获取逻辑文件名
declare @lfn nvarchar(128),@tp char(1),@i int

--创建临时表,保存获取的信息
create table #tb(ln nvarchar(128),pn nvarchar(260),tp char(1),fgn nvarchar(128),sz numeric(20,0),Msz numeric(20,0))
--从备份文件中获取信息
insert into #tb exec('restore filelistonly from disk='''+@bkfile+'''')
declare #f cursor for select ln,tp from #tb
open #f
fetch next from #f into @lfn,@tp
set @i=0
while @@fetch_status=0
begin
select @sql=@sql+',move '''+@lfn+''' to '''+@dbpath+@dbname+cast(@i as varchar)
+case @tp when 'D' then '.mdf''' else '.ldf''' end
,@i=@i+1
fetch next from #f into @lfn,@tp
end
close #f
deallocate #f
end

--关闭用户进程处理
if @overexist=1 and @killuser=1
begin
declare @spid varchar(20)
declare #spid cursor for
select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
open #spid
fetch next from #spid into @spid
while @@fetch_status=0
begin
exec('kill '+@spid)
fetch next from #spid into @spid
end
close #spid
deallocate #spid
end

--恢复数据库
exec(@sql)

go

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fenghao_5555/archive/2007/08/07/1729284.aspx