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