Sql-server – Restore script while setting up database mirroring on secondary server

backupmirroringrestoresql serversql server 2014

I am trying to set up database mirroring for 10-15 databases . All these are around 1-2 TB in size. They have multiple groups and hence are scattered on multiple mount points on their primary server.

Now to begin with database mirroring , as best practise i want to make sure tat when database are restored on mirror they are on exact drives and paths as they were on primary. We have all the mount points and storage similar to primary.

Now this can be done manually but its way too tedious finding the path and drive and restoring database one by one. I see many PS scripts for restore but they just copy and restore db on default path. I know we can change using MOVE option but still finding current location of each files within those database is tedious.

Is there a better way of doing this that i am unable to figure out or a way i can prepare restore script for above case?

Best Answer

We have all the mount points and storage similar to primary.

If both primary and secondary have same file layout / structure, you will not even require to use RESTORE ... WITH MOVE. Just use restore database from disk .. with norecovery.

Alternatively, you can use

  • backup-dbadatabase with FileCount parameter to stripe the backups, CompressBackup. you can even use buffercount and maxtransfersize to make the backups faster. Then use

  • restore-dbadatabse with -UseDestinationDefaultDirectories parameter. This way it uses source folder structure when it does restore at destination. Just make sure it use NoRecovery parameter since you will set up dbs for mirroring.

Is there a better way of doing this that i am unable to figure out or a way i can prepare restore script for above case?

Use dbatools as I mentioned above and no need to prep script. dbatools takes care of automation :-)

Related Question