Is there a simple script to "duplicate/clone" a SQL Server database on the same server?

Question

I'm looking for a way to automatize a copy of a database each day on the same SQL Server.

For example, I have a database MyDB. I would like, each day, do a copy of MyDB in a MyDB_TEST on the same server.

Is there any simple script to do this "simple" task ?

I found this script:

backup database OriginalDB
to disk = 'D:\backup\OriginalDB_full.bak'
with init, stats =10;

restore database new_db_name
from disk = 'D:\backup\OriginalDB_full.bak'
with stats =10, recovery,
move 'logical_Data_file' to 'D:\data\new_db_name.mdf',
move 'logical_log_file' to 'L:\log\new_db_name_log.ldf'

But I don't understand for what to replace in 'logical_Data_file' and 'logical_log_file'.

It's a move and I want a copy of my database...Why these two latest lines are "move" ?

I think I misunderstand this script...anyone could help me please?

EDIT :

I just edited my code like this :

backup database MY_DB
to disk = 'D:\BACKUP\MY_DB.bak'
with init, stats =10;

restore database MY_DB_NEW
from disk = 'D:\BACKUP\MY_DB.bak'
with stats =10, recovery,
move 'D:\Microsoft SQL Server\MSSQL12.SQLDIVALTO\MSSQL\DATA\MY_DB.mdf' to 'D:\Microsoft SQL Server\MSSQL12.SQLDIVALTO\MSSQL\DATA\MY_DB_new.mdf',
move 'D:\Microsoft SQL Server\MSSQL12.SQLDIVALTO\MSSQL\DATA\MY_DB_log.mdf' to 'D:\Microsoft SQL Server\MSSQL12.SQLDIVALTO\MSSQL\DATA\MY_DB_new_log.ldf'

And I sadly get an error telling the logical file "MY_DB.mdf" is not a part of the My_DB_New database...use RESTORE FILELISTONLY to get logicals file name.

I don't understand where is my mistake in this script, any inputs?

Answer 1

CORRECTION:

I wasn't putting the logical file name but the file name instead. Just put the logical file name without the path!

Answer 2

When you RESTORE a database, unless you specify otherwise it would create the same files that it had in the previously; same name, same path. As you want a copy, that would be overwriting the existing ones. That would, obviously fail, as those files are in use by your original database.

Therefore you need tell the instance to put the database files ("move" them) to a different path, hence the MOVE clause. This means that you then don't have the 2 databases conflicting over trying to use or write other each others files.

---

Side note, this type of thing does normally, however, tend to suggest an xy problem, though that is a different question.