Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    May 2004
    Posts
    6

    Unanswered: Automatically copy database from one server to another

    Hello,

    I need to be able to automatically backup an entire database on one server and restore it on another.

    Currently I am doing this manually in Enterprise Manager by right clicking on the database going to All Tasks and Backup. I back it up to a file. Copy it over to the other server using a network drive. And then Use the Enterprise Manager on the other server to Restore the database.

    In all actuality, the Enterprise Manager can see both servers on the destination server, but I have just been following process that was set by someone else.

    I need a simple way to automatically copy the entire database from one server to another every night/early morning.

    I either just need a simple batch file or I was looking at Replication might be designed for this but seemed overly complicated for what I need.

    Any suggestions?

    Thanks,

    Neofree

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You should be able to set this up as a DTS or SSIS job very easily.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2009
    Location
    United Kingdom
    Posts
    77
    You could use the Copy SQL Server Objects in DTS \SSIS

  4. #4
    Join Date
    May 2004
    Posts
    6
    OK both of these are looking to be overly complicated for what I need. Unless you have some examples or links that makes this seem easier than it looks. I tried googling..

    Thanks,

    Neofree

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You need a simpler method of copying databases than DTS or SSIS?
    Perhaps a database gnome that will transcribe each day's log files onto tiny four-leaf clovers and hand them to a pixie who will carry them to the remote server and slip them through the cooling vent.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    May 2004
    Posts
    6
    In searching I found this:

    To backup a single database from the command line, use osql.

    "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\osql.exe" -E -Q "BACKUP DATABASE mydatabase TO DISK='C:\tmp\mydatabase.bak' WITH FORMAT"
    So this seems like a *simple* solution, but I couldn't find a way to Restore just as easily. Once I figure that out a simple batch file and scheduled task are no brainers..

    Thanks,

    Neofree
    Last edited by Neofree; 01-22-09 at 17:23.

  7. #7
    Join Date
    May 2004
    Posts
    6
    Also just to comment. Someone who never used DTS/SSIS, or even is a DBA or regular SQL user (I mainly do general IT stuff, but have written the most basic of SQL statements - still the most "experienced" on our team lol), it'd probably take me a few hours to read up on DTS/SSIS, based on the search results I found from Google.. It looks like it might be an easy visual system or something, but if you don't already "know" a lot before you look at it, it's suddenly not simple. Perhaps a Youtube video of someone doing this would make it all look simple.

    Anyhow.. Any help would be highly appreciated.

    Thanks,

    Neofree

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Really, its not that hard for simple tasks such as data loading or database copying. Though database copying in SSIS is a bit more quirky than in DTS, and certainly more quirky than it needs to be.
    We're trying to help you out with best-practices here.
    Give it a try. Set up a sample database and try using an ETL tool to transfer it. If you run into a specific error, we can help you trouble-shoot it.
    Are you running 2000, or 2005?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by Neofree
    In searching I found this:



    So this seems like a *simple* solution, but I couldn't find a way to Restore just as easily. Once I figure that out a simple batch file and scheduled task are no brainers..

    Thanks,

    Neofree

    Pretty much the same command, but RESTORE Database with move...

    i.e.:

    RESTORE DATABASE SecurityMaster
    FROM DISK = N'\\servername\sharename\Backup\SecurityMaster.bak '
    WITH MOVE 'securitymaster_Data' to 'C:\MSSQL\DATA\SecurityMaster_data.mdf',
    MOVE 'securitymaster_Log' to 'C:\MSSQL\DBLogs\SecurityMaster_log.ldf',
    STATS=10

  10. #10
    Join Date
    May 2004
    Posts
    6
    Thanks, I got by with a simpler RESTORE command. I don't really have time to study SQL much more right now.. SQL 2000 btw.

    Thanks,

    Neofree

  11. #11
    Join Date
    Mar 2010
    Posts
    2

    Unhappy

    Quote Originally Posted by blindman View Post
    You should be able to set this up as a DTS or SSIS job very easily.
    If it were only that easy. (-; Either I'm going about it the wrong way (which I think is the case) or something else is out of whack.

    I have two SQL 2005 SP3 servers. I want to have an SSIS job that copies 4 DBs from Server A to Server B then do a bit of Data Scrubbing.

    I started with the Transfer Database Task. I get Errors about it either cannot create files, or users do or dont exist...

    If I write a script to import all the users, then the users reference a DB that is not there yet. If the Transfer Task does it, then there are users that already do exist and it does not like that either.

    Suggestions?

    Thanks

  12. #12
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Idea here only
    what about write a script that stop the SQL Xcopy the database files
    start the SQL again

    then on the remote computer stop that SQL Xcopy the File to the SQL DATABASE folder
    and start the SQL again
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  13. #13
    Join Date
    Mar 2010
    Posts
    2
    Quote Originally Posted by myle View Post
    Idea here only
    what about write a script that stop the SQL Xcopy the database files
    start the SQL again

    then on the remote computer stop that SQL Xcopy the File to the SQL DATABASE folder
    and start the SQL again
    The source needs to be online.

    I think I've gotten past the Copy Issue. Though it was a mis-mash of manually creating some of the users on the destination server.

    Now if there was a way to do something like:

    delete * from pr*

    where pr* is any table that starts with PR...

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can write dynamic SQL to do that, referencing the sys.tables schema object.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by myle View Post
    Idea here only
    what about write a script that stop the SQL Xcopy the database files
    start the SQL again

    then on the remote computer stop that SQL Xcopy the File to the SQL DATABASE folder
    and start the SQL again
    No. This is bad bad bad. It must be a teeny tiny prod environment that lets you kill the service. Then the databases have to go into recovery and start applying the t-log stuff that has not made it to the db and with this carries the risk of torn pages and going into suspect mode. Have fun with this loaded gun. I am glad I am not a DBA anymore.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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