Script

  • We have about 30 databases in server A.  All of them need to migrate to server B. Decided to use the backup & restore method.

    The dat file & log file paths are will be different on source server & destination server. I am looking for the script to backup all the databases on the server A & restore script to restore the user databases , which will move the file location.

    Found some links but looking for some simple ones. Thanks in advance

     

    • This topic was modified 4 weeks, 1 day ago by  ramana3327.
  • Not clear what you require, if you backup dbs to shared drive and want to restore from there why do you want to change the path.

    Do you mean you want to change the file paths at restore time as the new server has different drive letters/paths?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Take a look at the tools from here: https://dbatools.io/

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Thank you.

    We’re not allowed to download the DBA tools

  • I have a script depending on exactly what you need to do

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • You can build out a script and use the "RESTORE FILELISTONLY" into a temp table to create your restore script, doing a replace on the file location, when procesisng through the full backups (no need for the method for any follow up Diff's / Transaction Log's restores), something like this:

    https://www.sqlshack.com/use-of-the-restore-filelistonly-command-in-sql-server/

  • Script the RESTORE on Server A, then use FIND/REPLACE to change the destination path to be run on Server B

  • Yes, Thank you. The requirement is below

    I have TestDB1.bak, TestDB2.bak, TestDB3.bak , TestDB4.bak …etc in the below

    G:/Backups

    Need to restore all 30 databases with replace option using those backup available in that location (G:/Backups) and setup sa as owner.

     

  • heres some code you may find useful

    http://blog.perrywhittle.co.uk/post/2024/06/25/Restore-database-code.aspx

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

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

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