I do need to restore a database, in a weekly or daily basis from server A to server B. But! if the database is already there, I need to rename it, add existing date, and restore the other one there too.
I thought about this:
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE MyDatabase Modify Name = MyDatabase_TodaysDate ;
ALTER DATABASE MyDatabase_TodaysDate SET MULTI_USER;
where "TodaysDate" should be a variable, and of course, appended to the ALTER command.
So at any point in time, if the database was already there, I should have:
Where 09_20_2016 on this case is the oldest version
How can I accomplish this? I imagine I will need dynamic sql.