Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a live database which i would like to create a backup and restore script that will take the "live" database every night, create a back, then drop the existing "testing" database (if any) and restore the same .bak file (on the same server) into a new database, this way, i always have the most up-to-date database to test on.

You might want to delete the file before you back it up. You could do this with powershell (my recommendation), xp_delete_file (undocumented SP), or xp_cmdshell (not sure if it is enabled on your system or not)

The advantage of this solution is that is very fast to create a snapshot and doesn't take the full space size of the complete db. The disadvantage is that you need to take care of it so that it doesn't stay on the server indefinitely, because it will keep growing, as the source db will be updated. This snapshot database is read-only, so you won't be able to write data on it, only run reports, selects..etc. But is a fast solution when need to test something with prod data.