June 22, 2024 at 1:06 pm
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
June 22, 2024 at 4:54 pm
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" 😉
June 22, 2024 at 5:04 pm
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
June 24, 2024 at 9:25 am
Thank you.
We’re not allowed to download the DBA tools
June 24, 2024 at 12:53 pm
I have a script depending on exactly what you need to do
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 24, 2024 at 1:08 pm
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/
June 24, 2024 at 4:21 pm
Script the RESTORE on Server A, then use FIND/REPLACE to change the destination path to be run on Server B
June 24, 2024 at 7:25 pm
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.
June 25, 2024 at 1:28 pm
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