Post reply

BAckup a SQL Server 2000 datbase on our server to a database on a hosted SQL Server 2005

  • May 13, 2009 at 1:07 pm

    #132270

    Hi

    We use a hosted service to backup our databases on a daily basis. With SQL Server 2000 copying Database objects in a dts package and arranging for it to run automatically was a fairly simple procedure.

    How do you do this with SQL Server 2005. I have started to look at SSIS but it seems that this will take many many days to understand. I need to backup our database off site within the next day or so.

    I tried to use the new 'Copy Database' manually but was told that SQL Server Agent was not run on the destination (Hosted) server so I dont think automating the 'Copy Database' task is an option.

    Your feedback and assistance would be much appreciated.

  • May 27, 2009 at 9:28 am

    #1000734

    "With SQL Server 2000 copying Database objects in a dts package and arranging for it to run automatically was a fairly simple procedure."

    What kind of objects are you reffering to above?

    What you don't know won't hurt you but what you know will make you plan to know better

  • May 27, 2009 at 9:57 am

    #1000771

    Thanks for the reply

    I want to copy Tables, Views, Table triggers and possible stored procedures as well.

  • May 28, 2009 at 1:02 am

    #1001242

    You can do this by creating a SSIS package through the Data Import and Export Wizard.

    Right click the db that you want to copy its objects and follow the path: Taks>>Export Data. This will show you the export wizard.

    When done create a job that will excute the ssis package.

    Hope this helps.

    What you don't know won't hurt you but what you know will make you plan to know better

  • May 28, 2009 at 7:23 am

    #1001389

    Thanks for the reply, the only problem with tyhis is we have to go into each of the 200 tables and click 'drop & create destination table.

    Is there a way we can set this for all tables in one action?

  • May 29, 2009 at 8:39 am

    #1002087

    Are you upgrading your 2000 database to 2005? If so, you can still run your 2000 DTS packages in 2005. Or you can convert them using the Package Upgrade Wizard. See the following article:

    http://qa.sqlservercentral.com/articles/SQL+Server+2005+-+SSIS/upgradingsqlserver2000dtspackagestossis/2201/

  • May 29, 2009 at 8:53 am

    #1002094

    Take a look at the "Transfer SQL Server Objects Task" in SSIS. It think it should do what I understand you're trying to do.

    You could also consider doing a backup from one server and restore on the other. SSIS can do that for you too.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • May 29, 2009 at 8:55 am

    #1002097

    Are you not backing up data? Or are you just trying to get schema data?

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply