SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    Git-R-Done
    Join Date
    Nov 2001
    Posts
    1,194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to download a backup copy of a MS SQL database?

    I would like to download a copy of an MS SQL database that is at a hosting provider and upload it to another provider. I can log in using Enterprise Manager but I cannot figure out how to get a copy of what I need so I can move it over to a new site. If somebody can give me a list of steps of what I need to do to download everything off the old server and what I need to do to upload it to the new one, I would be really appreciative.
    John Saunders

  2. #2
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    there's 2 ways. use DTS (you'll have to read up, i'm not very good at it). the way that i do it mostly is to right click on the database and go to import or export data - depending on which db you right clicked on. it's pretty easy, just follow the instructions.

    edit -- check out this thread: http://www.sitepoint.com/forums/show...56#post1041556

  3. #3
    Git-R-Done
    Join Date
    Nov 2001
    Posts
    1,194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Bill,

    Thanks for your response. I read your other post and am not sure if I did this right. Does this sound correct?

    Right click on database
    All Tasks
    Export Data
    Next
    Enter the old web host's login info
    Enter the old web host's login info a second time?
    Copy tables and view from the source database
    Select All Tables
    Here's where I get confused.

    My host gives me 3 databases and I'm already maxed out. Is this the correct way to do this?

    I also noticed that instead of selecting the SQL server for the second time, I can pick Text file. When doing this it then asks for a file name then asks for Delimited or Fixed Field, file type, row delimiter, etc. The only thing is I couldn't get it to let me select more than one source and it only exports one of the tables. Is there anyway to get it to work this way? If so, what options should I select and would it import correctly onto a new server/database so it's exactly the same as it was before?

    Thanks for your help!
    John Saunders

  4. #4
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i think first you need to set up the db(s) on the new server. then import the tables in.

    but yeah, that's pretty much the steps.

  5. #5
    Git-R-Done
    Join Date
    Nov 2001
    Posts
    1,194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Bill,

    I'm afraid I'm not following you. How should I go about retrieving the table info? Should I just export the info to a text file for each table in the databases then do import them one by one?
    John Saunders

  6. #6
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Assuming that you have access to both SQL Servers via Enterprise Manager, and that they are both SQL2000, you can use the "copy objects & data" option of Import/Export to copy the database between the two servers directly without an intermediate stage (well, actually the data travels via your PC, but the end effect is a direct copy).
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  7. #7
    SitePoint Enthusiast geebee2's Avatar
    Join Date
    Mar 2004
    Location
    Gloucester UK
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Copy objects and data is quite tricky (and dangerous - be careful) to use, and there are bugs, especially when working across different servers.

    If the number of tables/fields is moderate, it may well be easier to export the data to text files and import them one by one. You can use scripting to transport the database structure / stored procedures etc. if necessary.

    Another approach is to back up the database and restore it on the target machine, but that may not be possible depending on the hosting arrangements. There can be problems if the SQL server version is not the same.

    HTH
    Last edited by geebee2; Mar 16, 2004 at 05:20.

  8. #8
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by M@rco
    Assuming that you have access to both SQL Servers via Enterprise Manager, and that they are both SQL2000, you can use the "copy objects & data" option of Import/Export to copy the database between the two servers directly without an intermediate stage (well, actually the data travels via your PC, but the end effect is a direct copy).
    this is what i was referring to. sorry for not explaining fully.

  9. #9
    SitePoint Addict flyingpylon's Avatar
    Join Date
    Mar 2002
    Location
    Fishers, IN USA
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I could be wrong about this, but I think one of the problems I ran into with DTS was that foreign keys were not recreated in the destination database. Perhaps it was just that I did something wrong, but it's something to watch out for.

    What I do is always keep a script updated with any changes to the database structure so that I could just run that in a new database, then I could use DTS or whatever to import just the data.

  10. #10
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'd be *astonished* if that were generally true, since the copy/move wizard is supposed to make an *identical* copy... but perhaps you found a bug?

    http://msdn.microsoft.com/library/de...igwiz_0z50.asp
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  11. #11
    SitePoint Enthusiast geebee2's Avatar
    Join Date
    Mar 2004
    Location
    Gloucester UK
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by M@rco
    I'd be *astonished* if that were generally true, since the copy/move wizard is supposed to make an *identical* copy... but perhaps you found a bug?

    http://msdn.microsoft.com/library/de...igwiz_0z50.asp
    My impression is there are some fundamental design problems with it, and it is probably best avoided where possible. Of course this could be out of date, it may have improved. Once bitten twice shy is my approach though.

    George
    George

    http://qaaz.com

    For low cost database-driven web-sites

  12. #12
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by flyingpylon
    I could be wrong about this, but I think one of the problems I ran into with DTS was that foreign keys were not recreated in the destination database. Perhaps it was just that I did something wrong, but it's something to watch out for.
    it's happened to me before, but i figured i did something wrong. once my tables were setup properly, importing the data didn't change anything though.

  13. #13
    SitePoint Addict flyingpylon's Avatar
    Join Date
    Mar 2002
    Location
    Fishers, IN USA
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think what might have happened to me is that I did a "copy data" with "create objects" selected. In that case, I think DTS just creates the fields so it has a place to dump the data.

    If you do "copy objects" instead, it may also copy the relationships, etc. Of course, you won't be able to copy all tables at once, because inevitably some will be created with foreign keys where the primary keys don't yet exist, and the operation will fail.

    I'd have a hard time believing that I ran into a bug. Much more likely to be user error.

  14. #14
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that's basically what i've come to believe as well. i'm still trying to understand the whole user thing. i keep bumping my head on that issue.

  15. #15
    SitePoint Enthusiast geebee2's Avatar
    Join Date
    Mar 2004
    Location
    Gloucester UK
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    flyingpylon>> Of course, you won't be able to copy all tables at once, because inevitably some will be created with foreign keys where the primary keys don't yet exist, and the operation will fail.

    It does try to cope with this, but I don't think it entirely succeeds. One strategy it could use would be to remove the RI constraints and restore them afterwards, however this is not the approach they actually use.

    Another problem seems to be in the comms library - I have had strange comms errors when running it remotely.

    My strategy was to run it locally ( if essential to say merge databases ), and then use (before or after) Backup/Restore to move the resulting database to another server.

    Besides unreliability, the performance is pretty abysmal when you run it remotely.
    George

    http://qaaz.com

    For low cost database-driven web-sites

  16. #16
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by geebee2
    Besides unreliability, the performance is pretty abysmal when you run it remotely.
    Surely that's because all the data travels via your own PC, so data is effectively transferred at half the speed of your internet connection?

    (Therefore, this approach is *not* recommended for modem users... lol )
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  17. #17
    SitePoint Addict flyingpylon's Avatar
    Join Date
    Mar 2002
    Location
    Fishers, IN USA
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something else I'll mention is that if you have access to both database servers (which you probably wouldn't have in a shared environment) you can simply detach a database from one server, copy the data and log files to the second server, and then attach the database to that second server.

    I think you could also just stop the first server instead of doing a detach, and then copy the two files to the second server and attach them there.

    I don't know if this is an "approved" way of doing it, but it seems to have worked for me in the past.

  18. #18
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I wasn't going to mention it, because it's clear that he does NOT have the required access, but since you have mentioned it, here's a good guide:
    http://www.mssqlcity.com/Articles/Ad...h_database.htm
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  19. #19
    SitePoint Enthusiast geebee2's Avatar
    Join Date
    Mar 2004
    Location
    Gloucester UK
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by M@rco
    Surely that's because all the data travels via your own PC, so data is effectively transferred at half the speed of your internet connection?

    (Therefore, this approach is *not* recommended for modem users... lol )
    I think it's much worse than that. Transferring a modest table of say 100,000 records seems to take forever (even on fairly decent 2mbit connection), whereas using CSV it might take just a few seconds.

    It's not just bandwidth, there seem to be some major efficiency problems. Maybe each row needs a round trip, or something like that.
    George

    http://qaaz.com

    For low cost database-driven web-sites

  20. #20
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by geebee2
    It's not just bandwidth, there seem to be some major efficiency problems. Maybe each row needs a round trip, or something like that.
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  21. #21
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that's a great web site. hadn't come accross this one before. looks like some good reading there. thanks.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •