How to automate database backup and restore using T-SQL


We may have scenario to restore a development or test database or just create another copy of the database. Restoring the latest backups of Production databases on a various test servers is one of the frequent activity we perform on a regular basis, various application development teams often request a restore the latest backup on new or existing test or quality servers, many times we receive this kind of requests in the eleventh hour when packing up to go back home.