none
How to restore SQL 2005 DB to SQL 2000

    Question

  • Hello,

    I'm tring to restor a DB from sql 2005 (which is located on my local computer) into sql 2000 (which is located  on the server) and I am getting this type of message:

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------
    ADDITIONAL INFORMATION:

    Too many backup devices specified for backup or restore; only 64 are allowed.
    RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0818&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Why? and how to restore SQL 2005 DB to SQL 2000?

     

    Monday, January 02, 2006 1:21 PM

Answers

  • 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.

     

    www.hellasinternet.com

    Panos Tzirakis & George Dounavis

    Wednesday, September 13, 2006 12:48 PM

All replies

  • Backups created with Microsoft SQL Server 2005 cannot be restored to an earlier version of SQL Server.  For more information about restoring databases, see Books Online topic RESTORE (Transact-SQL).

    Tuesday, January 03, 2006 10:46 AM
  • Hi,

    If its any clue or consolation, I get an error 3205 in the case where I backup {example} db from SQL 2000 SP3,

    1. restore example db to same SQL version on another computer.

    2. restore example db to SQL 2000 upgraded to SQL express 2005 

    3. backup from SQL express 2005, restore to same version on another computer. 

    "Too many backup devices specified for backup or restore; only 64 are allowed.
    (Microsoft SQL Server, Error: 3205)"

    Also,  when alternatatively trying to attach the .mdf and .ldf files for this SQL 2000 database to a SQL express 2005 version, I get a 

    "Error 602 Severity Level 21 Message Text

    Could not find row in sysindexes for database ID %d, object ID %ld, index ID %d. Run DBCC CHECKTABLE on sysindexes."

    Although when I ran DBCC CHECK, it returned 0 errors.

    ANY RECOMMENDATIONS PLEASE !

    -

    PS. with respect to your problem: Did you try uninstalling SQL express and then backup from SQL 2000 and restoring from that .BAK (then re-install SQL express)?

     

    Tuesday, January 24, 2006 1:03 PM
  • The only way to get data from sql2005 to sql2000 is to export/import (SSIS packages can be setup to do this).

    As for the 3205 problem, there was a problem with one of the SQL2000.SP3 QFE's where the backup header was written in a way that earlier versions of the server could not read.  That was fixed in later versions.  We recommend that you move to SQL2000.SP4.

    When attempting to restore a sql2005-formatted backup, versions of SQL2000 earlier than SP4 may receive the 3205 due to changes in the format of the backup header.

    As for the 602 during upgrade, that's new to me.  You might want to contact our product support:

    http://www.microsoft.com/services/microsoftservices/srv_support.mspx

     Hope that helps.

     

    Tuesday, January 24, 2006 8:52 PM
  • Dear ,

    I have created  a SQL DB 2005 ,and now my  laptop have Win XP OS,that's why i can't install SQL 2005,and i want to restore the DB atleast to SQL Express installed with VS 2005..can i do that?

    if you have any other suggestion please Advice...

    i get excited after i knew that any SQL 2005 can't be restored to any earlier DB....what's my solution on my current state.

    Saturday, February 25, 2006 12:16 PM
  • The structure of the backup header was changed in sql2005, so older versions of sql2000 (prior to sp4) may have trouble recognizing the backup.

    But that won't help you restore to sql2000.  We can't attach or restore sql2005 databases on earlier versions.  There are internal structural differences that make it impossible.

    The only way to get the data into sql2000 is to export all the tables from sql2005 and them import them back to sql2000.

     

    Thursday, March 02, 2006 7:41 PM
  • Moustafa, any backup created on SQL 2005 can be restored to any other edition of SQL 2005.
    Thursday, March 02, 2006 9:51 PM
  • i find this amazing that this is how MS decides to force an upgrade.  I want to goto sql 2005 but I need to feel comfortable and if they don't allow a simple way to transform headers for backward compatibility I am seriously disappointed.

    Who develops like this.......

    Wednesday, March 15, 2006 2:28 AM
  • Sorry to disagree PailnessProd, but on DBServers there's no safe backwards compatibility, never, as when a DB server goes to a next version, it implies structural changes, thus, making backwards compatibility pretty hard, as older versions DON'T have the structural changes. If they had, they would be the latest version.

    so it's the correct way to go for Microsoft. :)

    Friday, March 17, 2006 5:00 AM
  • Steve,

    Is it possible to "export all the tables from sql2005" if I'm using SQL Server 2005 Express?

    Thanks,
    Jason

     

     Steve Schmidt wrote:

    The structure of the backup header was changed in sql2005, so older versions of sql2000 (prior to sp4) may have trouble recognizing the backup.

    But that won't help you restore to sql2000.  We can't attach or restore sql2005 databases on earlier versions.  There are internal structural differences that make it impossible.

    The only way to get the data into sql2000 is to export all the tables from sql2005 and them import them back to sql2000.

    Monday, April 03, 2006 4:09 PM
  • You can use the bcp utility to copy table data between any version of sql server.

    I suggest you might start a new thread with a more specific question with respect to export/importing data, if that is your goal.  You might get a wider audience of potential responders.

    To reiterate the answer to the question "how to restore sql2005 to sql2000": it is simply not possible.

     

    Wednesday, April 05, 2006 6:56 PM
  • 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.

     

    www.hellasinternet.com

    Panos Tzirakis & George Dounavis

    Wednesday, September 13, 2006 12:48 PM
  • I may have to differ with you on that point.  I am trying to do just that with a small database created  on my laptop running SQL Server 2005.  I have created a backup and tried to restore it on the class server also running SQL Server 2005.  The following is the error message: 

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------
    ADDITIONAL INFORMATION:

    Too many backup devices specified for backup or restore; only 64 are allowed.
    RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476

    The above link is of no value as the 2005 to 2005  error occurance seems to be very rare and I have as yet found no one with a solution.  I just spent an hour with the server admin for the class, and I seem to be the only one with this issue.  Until a solution is found I am forced to create my assignments and work from the class server.

    Since then, I have created a couple of databases, one empty, the other populated, with the exact same results.  Do you have a solution for this particular error message? 

    • Proposed as answer by Crakdkorn Friday, October 05, 2012 3:03 PM
    Thursday, October 12, 2006 3:58 PM
  • Hello,

    PanosTZ - thanks for your detailed instructions.

    I've attempted to follow your instructions (panosTz).  It's worked successfully for one of my databases but I'm getting the following error for the other.

    "Generate Script Progress

    - Determining objects in database 'RetailStore' that will be scripted. (Error)

    Messages

    Either the object or one of its properties is not supported on the target server version. (Microsoft.SqlServer.Smo)"

    I can't get any information about the error received on any other forums or on the Microsoft KB.

    Anyone who can explain what this means of point out area of fault would be much appreciated.



    Friday, October 20, 2006 7:40 AM
  • Well, SQL Server 2005 Developer Edition is supported in Windows XP.

     

    Thursday, February 15, 2007 10:50 AM
  • Thanks Panos!

    I cant believe personally that Microsoft couldnt have spent that little extra time creating the feature for generating a backup from 2005 which can be restored on SQL 2000. They still dont seem to really have their feet on the ground, although SQL Server 2005 is a great product.

    Jean

     

    Tuesday, February 27, 2007 10:36 AM
  • This is not possible for most, if not all, database products.  Even MS competitors won't do or support this.
    Tuesday, February 27, 2007 3:39 PM
  • Hello everyone,

    Great Tutorial only the last step to restore the Foreign Keys and Triggers is not working for me:

    ----- Action -----
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Account]') AND type in (N'U'))

    ----- Result -----
    Server: Msg 208, Level 16, State 1, Line 1
    Invalid object name 'sys.objects'.

    I hope someone can tell me what I am doing wrong. I did the tutorial twice I thought maby I did something wrong... but I think i did to it exactly as he told me so.

    Maby the problem is that the two servers (2000/2005) are running on the same machine?

    Thanks
    Bjorn

    If you need more information just let me know!
    Wednesday, March 28, 2007 2:25 PM
  • i dont get it. so i had a windows 2000 server and our software provider switched from pervasive to sql.  they recommended th sql express since we fell into the requirements for it and it was free.  Its been running fine.  It is now time to get a new server.  Ordered sbs2003 premium because it came with sql server.  Now it looks like sbs 2003r2 pre is sql 2005 workgroup edition and i get an error when i try to restore the back up to it.

     

    TITLE: Microsoft SQL Server Management Studio Express
    ------------------------------

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

    ------------------------------
    ADDITIONAL INFORMATION:

    The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.
    RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3169)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=3169&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------
    again sql express to sql 2005 workgroup. any help would be appreciated. 

    mike

    Wednesday, April 04, 2007 1:19 AM
  •  

    If you have a copy of SQL2005:

    Create a new database and in the Options Tab choose compatibility Level for SQL 2000.

    Once DB is created, import the tables from the old DB.

    Adjust keys and indexes after import.

     

    Job done

     

     

    I know this works for simple DBs of a few tables without complex relationships as I've done it myself.

     

    Hope this helps.

    AR

    • Proposed as answer by pytte Thursday, March 12, 2009 2:14 PM
    Wednesday, June 27, 2007 11:32 PM
  • Hello everyone,

    Great Tutorial only the last step to restore the Foreign Keys and Triggers is not working for me:

    I hope someone can tell me what I am doing wrong. I did the tutorial twice I thought maby I did something wrong... but I think i did to it exactly as he told me so.

    I have simply created a database in sql server 2005 instance with comaptibility level of sql2000 8.0.0..

    Now I have done all my working then created a backup by thinking that this will be easily restore.But it isn't..

     

    I don't know why is this fucntionality is provided if we can't allow to restore the database ?

     

    Tuesday, August 07, 2007 2:02 PM
  •  

    Hi..

     

    Client :

    I am having MSDE 2000 and SQL Express Edition Client system. But the Backup is in Express 2005 Edition.

    If u configured database using MSDE DATABASE u will get version some 8.0x (to findout version Select @@Version). The above error will come when u restored using SQL Server management Studio Express 2005.

    Un Installed the MSDE. Installed Sql Express and configured. Findout the Version. If it is 9.0x then it will restore using

    SQL Server management Studio Express 2005.

     

    Regards,

    Arun

    Tuesday, August 28, 2007 7:41 AM
  • Yes, SQL Server 2005 Developer Edition can be installed on Windows XP machine.

     

    RK Reddy

     

    Thursday, November 01, 2007 3:50 PM
  •  

    Hello,

    I'm tring to restor a DB from sql 2005 (which is located on my local computer) into sql 2000 (which is located  on the server) and I am getting this type of message:

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------
    ADDITIONAL INFORMATION:

    Too many backup devices specified for backup or restore; only 64 are allowed.
    RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0818&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Why? and how to restore SQL 2005 DB to SQL 2000?

    Thursday, November 15, 2007 10:09 AM
  •  

    using System;

    using System.Data;

    using System.Configuration;

    using System.Web;

    using System.Web.Security;

    using System.Web.UI;

    using System.Web.UI.WebControls;

    using System.Web.UI.WebControls.WebParts;

    using System.Web.UI.HtmlControls;

    public partial class _Default : System.Web.UI.Page

    {

    protected void Page_Load(object sender, EventArgs e)

    {

    }

    protected void Btn_add_Click(object sender, EventArgs e)

    {

    lstData.Items.Add(new ListItem(txtNewText.Text,txtNewValue.Text));

    }

    protected void btn_clear_Click(object sender, EventArgs e)

    {

    txtNewText.Text = "";

    txtNewValue.Text = "";

    lstData.Items.Clear();

    }

    protected void Button1_Click(object sender, EventArgs e)

    {

    lstData.Items.RemoveAt(lstData.SelectedIndex);

    }

    }

     

     

    Invoice

    using System;

    using System.Data;

    using System.Configuration;

    using System.Web;

    using System.Web.Security;

    using System.Web.UI;

    using System.Web.UI.WebControls;

    using System.Web.UI.WebControls.WebParts;

    using System.Web.UI.HtmlControls;

     

    public partial class _Default : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

     

        }

        protected void Button1_Click(object sender, EventArgs e)

        {

            try

            {

     

     

                double x = double.Parse(txtQtySold.Text);

                double y = double.Parse(txtUnitPrice.Text);

                double total = x * y;

     

                txtTotalGoods.Text = total.ToString();

            }

            catch (Exception ex)

            {

                lblData.Text = ex.Message;

            }

     

     

        }

        protected void Button2_Click(object sender, EventArgs e)

        {

            double enterdiscount = double.Parse(txtdiscount.Text);

            double totalgoods = double.Parse(txtTotalGoods.Text);

            double discountTotal = ((totalgoods / 100) * enterdiscount);

     

            txtTotalDiscount.Text = discountTotal.ToString();

     

        }

        protected void Button3_Click(object sender, EventArgs e)

        {

            double totalgoods = double.Parse(txtTotalGoods.Text);

            double charges = (totalgoods / 100) * 5;

            txtTransport.Text = charges.ToString();

     

     

        }

        protected void Button4_Click(object sender, EventArgs e)

        {

            double totalgoods = double.Parse(txtTotalGoods.Text);

            double trans = double.Parse(txtTransport.Text);

            double vat = (totalgoods + trans) * 0.20;

            txtVat.Text = vat.ToString();

     

        }

        protected void Button5_Click(object sender, EventArgs e)

        {

            double totalgoods = double.Parse(txtTotalGoods.Text);

            double trans = double.Parse(txtTransport.Text);

            double vat = double.Parse(txtVat.Text);

            double discount = double.Parse(txtTotalDiscount.Text);

     

            double total = (totalgoods + trans + vat) - discount;

     

            txtInvoiceTotal.Text = total.ToString();

     

     

        }

        protected void Button6_Click(object sender, EventArgs e)

        {

            double totalgoods = double.Parse(txtTotalGoods.Text);

            double discount = double.Parse(lstData.SelectedValue);

            double totaldiscount = (totalgoods / 100) * discount;

     

            txtTotalDiscount.Text = totaldiscount.ToString();

     

     

        }

    }

     

    Inser and updating a database

     

    try

    {

    SqlConnection con = new SqlConnection("Data Source=PC247762153672\SQLEXPRESS;Initial Catalog=;Integrated Security=True");

    con.Open();

    SqlCommand cmd = new SqlCommand();

    cmd.Connection = con;

    cmd.CommandType = CommandType.Text;

    cmd.CommandText = "Update exam set price = @price where product_code = @code";

    cmd.Parameters.Add(new SqlParameter("@code", SqlDbType.Int)).Value = (txtcode.Text);

    cmd.Parameters.Add(new SqlParameter("@price", SqlDbType.Decimal)).Value = (decimal)double.Parse(txtprice.Text);

    cmd.ExecuteNonQuery();

    con.Close();

    }

    catch(Exception ex)

    {

    lblmsg.Text = ex.Message;

    }

    SqlConnection con = new SqlConnection("Data Source=PC247762153672\SQLEXPRESS;Initial Catalog=;Integrated Security=True");

    con.Open();

    SqlCommand cmd = new SqlCommand();

    cmd.Connection = con;

    cmd.CommandType = CommandType.Text;

    cmd.CommandText = "Insert into division_tbl(code, price, desc, qty) Values (@code, @price, @qty, @desc)";

    cmd.Parameters.Add (new SqlParameter ("@code", SqlDbType.Char)).Value = txtcode.Text;

    cmd.Parameters.Add(new SqlParameter("@desc", SqlDbType.Char)).Value = txtcode.Text;

    cmd.Parameters.Add(new SqlParameter("@price", SqlDbType.Decimal)).Value = (decimal) double.Parse (txtcode.Text);

    cmd.Parameters.Add(new SqlParameter("@price", SqlDbType.Int)).Value = (int)int.Parse(txtcode.Text);

    cmd.ExecuteNonQuery();

    con.close;

    }

    }

    Sunday, May 18, 2008 8:31 PM
  •  

    Excellent post, PanosTz!    Much more helpful than Jeremy Hurst's irrelevant chides. 

    Wednesday, June 18, 2008 6:03 PM
  •  

    Monday, September 01, 2008 8:44 AM
  • Hi,

    I read you issue above & the same is faced by me,

    Did you find the solution for restoring the database from SQL 2005 to SQL 2000.

    If yes Pl reply

     

    thks

     

    Monday, September 01, 2008 8:46 AM
  • Try

    "Database Publishing Wizard" from CodePlex

    http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard&referringTitle=Home

     

    "The Database Publishing Wizard enables the deployment of SQL Server 2005 databases (both schema and data) into a shared hosting environment on either a SQL Server 2000 or 2005 server."

     

    I havent try it.

    • Proposed as answer by ValentinS Friday, May 08, 2009 7:30 AM
    Wednesday, September 24, 2008 3:14 PM
  • Correction, the software is from Microsoft:

     

    Microsoft SQL Server Database Publishing Wizard 1.1

    http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

     

    And it works .... (my database has 200 tables and views -> no errors reported)

     

    It´s possible to script a SQL 2005 database and recreate the database in SQL 2000.

    Wednesday, September 24, 2008 4:44 PM
  • THANK YOU!

    T His link and the wizard saved my night. I had an small, yet complex SQL 2005 detabase. Generated a 150 or so MB SQL script which is currently importing into 2000 with no issues. I really, really appreciate this post :)

    I am using MSDE and SQL Express 2005 - just fyi... the DTS wizard was not an option :(
    Friday, May 08, 2009 7:27 AM
  • Has anyone tried this from SQL 2008 to SQL 2005?

    I have made some databases in SQL 2008 and now the client wants to install elsewhere where it is only running 2005. Short of scripts (and we all know how shakey they can be) I cannot think of a way of downgrading to SQL 2005.

    Tuesday, July 14, 2009 4:10 PM
  • Ah. Was hoping there was a more robust answer than scripts!
    Wednesday, July 15, 2009 11:11 PM
  • You can use Snapshot replication to move your database from 2005 to 2000.
    • Proposed as answer by JCEH Saturday, July 18, 2009 8:05 PM
    Friday, July 17, 2009 8:14 PM
  • Hmmm. Interesting. So presumably should work from 2008 to 2005 too.

    Just had a look at http://msdn.microsoft.com/en-us/library/ms151198.aspx but need to digest this before I try it.

    Thanks for the pointer!
    Saturday, July 18, 2009 7:57 PM
  • I think this will work.

    Thank you very much!
    Saturday, July 18, 2009 8:05 PM
  • i have this problem on converting 2008 db to 2005 db.
    Thursday, January 07, 2010 10:08 AM
  • I think if you have your 2008 instance setup as 9 and 10 compatible (when you install) then as long as you don't use any specific 2008 functionality it'll just detach and attach to a 2005 instance.
    Thursday, January 07, 2010 6:09 PM
  • No, that will not work.  Compatibility mode really only controls T-SQL syntax, not the structure of the database.

    When a database is attached to a higher version SQL Server it will (even if run in compatibility mode) have it structure updated for the new version of SQL Server.  Once that is done, it cannot be directly restored to an earlier version. 

    You will still need to use scripts, export and import data, et cetera, as described in several earlier posts in this thread.

    RLF
    • Proposed as answer by Kalman Toth Thursday, January 20, 2011 12:23 PM
    Thursday, January 07, 2010 8:09 PM
  • I can't find Export option in "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)."

    Pls help


    Thanks

    ElanGovan
    GoFrugal support team
    Friday, March 05, 2010 10:53 AM
  • To Move a database from one location to another we basically need to do three things

    • Detach the Detabase from SQL Server
    • Move the datafile and log file to the new location
    • Attach the database back to SQL Server

     


    Cheers, Eliza
    Tuesday, June 15, 2010 9:13 AM
  • To Move a database from one location to another we basically need to do three things

    • Detach the Detabase from SQL Server
    • Move the datafile and log file to the new location
    • Attach the database back to SQL Server

     


    Cheers, Eliza

     

    Where going between same version or newer version this is pretty much true. The thread here is concerned with moving a database to an older version of SQL server. This can have compatability issues, hence the thread.

    ;-)

    J

    Monday, June 28, 2010 8:32 AM
  • Wow thanks a lot. It works for me and honestly speaking I as finding this.

    Thanks again.

    Wednesday, August 04, 2010 11:12 AM
  • @ PanosTz - Might be an old answer but it works... Thank you.

    Tuesday, April 10, 2012 3:24 PM

  • Alternatively, use the Restore headeronly command to note the physical file names, use the MOVE command with your Restore Database command (noting the correct file names) but be SURE to add the "REPLACE" command at the end of the sql query. http://msdn.microsoft.com/en-us/library/ms186858.aspx Please note concerns in this link regarding the use of REPLACE.

    RESTORE DATABASE TestDB
       FROM AdventureWorksBackups
       WITH MOVE 'AdventureWorks2012_Data' TO 'C:\MySQLServer\testdb.mdf',
       MOVE 'AdventureWorks2012_Log' TO 'C:\MySQLServer\testdb.ldf',REPLACE;
    GO

     


    R, J

    Friday, October 05, 2012 7:23 PM
  • The above is not going to change versions downwards, is it?  Thanks.

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    • Edited by Kalman Toth Friday, October 05, 2012 8:07 PM
    Friday, October 05, 2012 8:07 PM
  • We had a domain name change and for a couple of hours, we found the changes we made to our 2008 production database were accidentally applied to the 2012 development readonly copy.  

    Kalman,

    We backed up that data and tried to restore it to 2008 and use our Red Gate compare tools and it would not restore.   As we don't have our compare tools registered for 2012 yet, restoring to 2012 for the compare was not an option.  Instead, I tried to restore the 2012 backup to SQL 2008.  

    Guess what... it didn't work (nor did I expect it to). 

    So what you see above is the "What do you do at that point so you can restore your 2012 version down to 2008?"  in print.   I read in far too many places that it can't be done and since I have done it on every version since 2000 ie, restore 2005 to 2000, restore 2008 to 2005, restore 2012 to 2008, it seems appropriate to contradict what some of the other members have said about how it "doesn't" work.

    Obviously those statements are incorrect.   My concern is that I see this in print everywhere on the internet and these statements are not true (BUT WATCH THAT REPLACE FEATURE - you can't go backwards from there on a Production Server if you screw it up).  

    Give it a try.  Works fine.  I'm agreeing with Russell, which is the answer you support.  But instead of saying it needs to be done with scripts, etc, I simply published the solution.  There is nothing difficult about it.


    R, J



    • Edited by Crakdkorn Friday, October 05, 2012 8:57 PM
    • Proposed as answer by Kalman Toth Friday, October 05, 2012 10:02 PM
    Friday, October 05, 2012 8:50 PM
  • Hmm, I tried to restore a SQL 2012 backup to SQL 2008 R2 using a similar command:

    RESTORE DATABASE restoredownlevel 
    FROM DISK = 'D:\Program Files\Microsoft SQL Server\restoredownlevel.bak'
    WITH MOVE 'restoredownlevel' TO 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\restoredownlevel.mdf',
    MOVE 'restoredownlevel_log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\restoredownlevel_log.ldf',REPLACE;
    GO

    and it failed with:

    Msg 3241, Level 16, State 13, Line 1
    The media family on device 'D:\Program Files\Microsoft SQL Server\restoredownlevel.bak' is incorrectly formed. SQL Server cannot process this media family.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

    Do you see anything wrong with that command or does this simply not work as has been suggested? I've never heard of this in the 20+ years I've been working with SQL Server so I'm not real confidant that it should work, but it'd be handy.

    Thanks,
    Steve

    Wednesday, October 17, 2012 4:49 PM
  •  Ah, my first question might be, Did you try to restore using SQL 2012 or with SQL 2008?.  

    I did the restore FROM SQL 2008 on a 2008 machine.  I don't think I tried to restore the 2008 database from a 2012 SSMS.   I'm trying to remember the steps exactly.  

    1) backed up the database on a 2012 server - call it 2012.bak

    2) moved the backup file to a central repository - say \\myserver\backupfiles\2012.bak

    3) RESTORE DATABASE TestDB
       FROM  \\myserver\backupfiles\2012.bak
       WITH MOVE '2012_Data' TO 'C:\MySQLServer\testdb.mdf',
        MOVE '2012_Log' TO 'C:\MySQLServer\testdb.ldf',REPLACE;
     GO

    Pretty sure that was all there was to it.   I'll give it another shot in my spare time at the office tomorrow to be sure.


    R, J

    • Edited by Crakdkorn Saturday, October 20, 2012 12:47 AM
    Wednesday, October 17, 2012 9:38 PM