Automating an SQL task on a monthly basis At the start of each month I have to give a team a copy of the live database which is held in a backup from 1am each morning (D:\Backup) - We then copy this database elsewhere onto a dev server and restore it from the share.

When January is completed at the end of the month we repeat the process for the Feb - this time we take a copy of what has been worked on during the month of Jan by the team and back that up into an archive. The February 1st database then goes live onto the dev server.

Is it possible to automate this process? Our techie left us recently and didn't document this process so I've managed to do it manually thus far but imagine it can be automated.
What you are telling is possible.

A pseudo code would look like this :

[ Make sure to use database compression and have instant file initialization enabled.]


Check if it is first day of the month using select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)


if it is first day of month, backup the previous month database to archive as db_name_datetime_Month_archive.bak. 
backup the live PROD database and copy the file to DEV server and restore / overwrite the current database - use WITH REPLACE option when restoring.
Sync up users
Optionally since you are restoring database from PROD, you may have to sanitize it - remove PII or any sensitive information.
send out email to the concerned teams (DEV in your case) saying the restore or refresh job has been completed.



For backup and restore you can use PowerShell (make sure you use backup and restore method) or tsql.

You can completely automate the above pseudo code using sql agent job.