Bjorn Peters needs to migrate a few databases over to a test server nightly:
My first thoughts about that were creating a SQL Server Agent Job with following steps:
- check the availability of Shared-Destination-Folder
- delete/clear Destination-Folder-Content
- Shrink all Transaction-Logfiles
- Backup all Databases from given list
- Restore each Backup-File from folder
- Check all orphaned user
- delete/clear Destination-Folder-Content
A year or two ago, I had built this with a lot of normal T-SQL-Agent-Jobs, now I’m doing this with dbatools which make it very easy and fast (and in one step)
It’s only a few lines of Powershell code, which you can see upon clicking through.