My manager wants me to design a metadata table/tables and a job for backup and restore.

The job takes the backup locations as the input and copies the backup files of all the databases to another server and restores them on another server. However, the restore is based on the value of Isrestore column (the explanation is provided below)

Metadata table /tables should contain the following columns:

1)servername
2)database name
3)full backups(these are splitted)
3)differential backups(these are splitted)
5)destination server name (server where the above full and differential backups need to be copied)
6)Destination full backup folder
7)destination differential backup folder
8)isrestore (this value should be 1 or 0. 1 means the full and differential backups should be restored as a database after copying on a destination server, 0 means they should just be copied to a destination server but should not be restored)
9)restored database name (this is the name to be kept to the restored database if 'isrestore' column is set to 1)
10) destination mdf path ( restored database mdf location)
11)destination ldf path (restored database ldf location)

These columns may be kept in one or two tables.


The job which needs to be created should take the full and differential backups of each database and just copy to the destination full backup folder and destination differential backup folder if the isrestore column value is 0. If the value is 1, It should copy to the backup folders and restore on the destination server as a database having mdf and ldf files specified in destination mdf path and destination ldf path. If possible, this restore should be done in the ascending order of the size of the backup file ( u can add a column if you want for this purpose ). The table purpose is some developers can enter the destination full backup folder and destination differential backup folder and if they run the job, the job should copy the backup files succesfully to the different server and restore if specified