justin
thêm cái này nữa nhưng cố gắng dịch
How to Downgrade a Database from SQL Server 2005 to SQL Server 2000
As
you may all know, SQL Server 2005 request a minimum of 8GB RAM to work…
let say satisfactorily. I first didn’t knew 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 an 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 procedures 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
a. Script Collation , set to TRUE
b. Script Database Create, set to TRUE
c. Script of SQL Version, set to SQL SERVER 2000
d. Script foreign keys, set to FALSE
e. 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 procedure, 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.
Step2 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 produced. The procedure 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:
a. Include IF NOT EXISTS , set to TRUE
b. Script Owner, set to TRUE
c. Script of SQL Version, set to SQL SERVER 2000
d. Script foreign keys, set to TRUE
e. Script Triggers, set to TRUE
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 procedure, 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.
----------------------------------------------
codePrject
Introduction
Moving databases from SQL Server 2005 to SQL Server 2000 is not
impossible and can really be quite easy once you come to grips with the
basics. Microsoft has made it very easy to move databases from 2000 to
2005 by using backup and restore built into the admin tools, but this
is not backwards compatible and has caused some issues, to say the
least. The content of this article owes a lot to the very talented Craig Murphy, who suggested using a publishing wizard for reverting 2005 databases to 2000 in a blog entry.
I am trying to write this for all levels, so please excuse the simplistic nature, as not everyone is as special as you.
SQL Server 2005 Database Publishing Wizard 1.1
The Database public wizard is intended for Developers to create
databases with script or management GUIs, develop and change databases
and then be able to script the finished database for inclusion to
installation routines. This is an installation and separate program
from your 2005 Server Management Studio, which can script and publish
databases from 2005 to 2005 and well as 2000 databases.
The current download can be found here
(this may well change as Microsoft does move and change pages). You
need the .NET 2.0 Framework and Microsoft Feature Pack installed, as
well. I didn't know if I had the Feature Pack, as I have the SQL 2005
Developer Edition and it installed fine without any Feature Pack.
Download and Install
You should be able to do this without my help.
Running Publishing Wizard for 2005 to 2000ll
Start The Wizard (once you install it, it's also a separate program from SQL Management Studio).
Select a Server and authentication details.
Select a Database.
Select an Export File.
Set Requirements. Note that this extract will do Schema and Data for
SQL Server 2000. If you have a large database, this could take a long
time and produce a massive file.
Review Summary:
Progress and Results:
With luck, you will see a success. If not, go back and review the
selections. I've tried this with various live and not live databases
with no issues.
Getting It All Back Into SQL Server 2000
So you now have a SQL file with your schema and data as describe
above. If it is fairly small, then you can simply open it in a Query
Analyzer (SQL 2000) or a Query Window in 2005 Management Studio and
connect to your 2000 database. However, if your database is even
slightly complex or you have a fair bit of data, the SQL script file
will be too big and it just won't load. So you have to use a command
line option. You should be able to do this without my help.
Warning: Running very large SQL scripts could take a while.
SQL 2005 sqlcmd
Below is using my SQL Server 2005 Developer Edition with sqlcmd to execute the file. This works fine with targeted SQL Server 2000 SP4.
 Collapsec:> sqlcmd -E -S my2000server -d mydatabase -i c:\sandbox.sql
or
c:> sqlcmd -U sa -P password -S my2000server -d mydatabase -i c:\sandbox.sql
SQL 2000 osql
If you really want to use osql on your SQL 2000 box,
then this should do it. No prizes for figuring out the command
parameters are the same. There might be a performance advantage running
it directly on the SQL 2000 box, but I've not looked into this.
Collapse c:> osql -E -S my2000server -d mydatabase -i c:\sandbox.sql
or
c:> osql -U sa -P password -S my2000server -d mydatabase -i c:\sandbox.sql
Where -U is sqllogin, -P is password, -E is integrated authentication, -S is server name, -d is database name and -i
is the SQL script file you created. If you get stuck with these command
items, read the SQL server books online. Oh, please don't post saying I
should never use SA for this sort of script; I may already know that.
History
1.0 Initial Release 06/09/2005
License
This
article has no explicit license attached to it but may contain usage
terms in the article text or the download files themselves. If in doubt
please contact the author via the discussion board below. A list of licenses authors might use can be found here About the Author
Frank Kerrigan
|
Frank Kerrigan
Currently developing Insurance systems
with SQL Server, ASP.NET, C#, ADO for a company in Glasgow Scotland.
Very keen on OOP and NUNIT testing. Been in IT forever (12+ years) in
mix of development and supporting applications / servers. Worked for
companies big and small and enjoyed both.
Developed in (newest
first) : C#, Progress 4GL, ASP.NET, SQL TSQL, HTML, VB.NET, ASP, VB,
VBscript, JavaScript, Oracle PSQL, perl, Access v1-2000,
sybase/informi, Pic Controllers, 6502 (ask your dad).
MCAD ongoing
MCP C# ASP.NET Web Applications
MCP SQL Server 2000
HND Computing
OND / HNC Electrical Engineering,
|
|
工具 
RE: Convert SQL2005 sang SQL Server 2000 - 
