I wonder if anyone would be able to help by giving me a few scenarios/tasks to complete on SQL Server 2012/16 please?
I am learning SQL Server to move from a desktop role to a junior DBA. I have my own lab at home, but only has adventureworks running on a single SQL instance. I can write basic/intermediate queries with inner joins, aggregates etc. What I'm looking for is some basic DBA tasks to practice with. Unfortunately, the interwebs is full of tutorials on how to write SQL, but I can't find much on then putting it into real life use.
You can then look at automated test restores with checkdb etc.
(A backup is no good if it is corrupted.)
Be very careful with Differential backups. You have to make sure you have access to the full backup it is based on. (ie Check for no COPY_ONLY VSS backups etc)
HAVING (learn how to properly use HAVING, sadly, most DBA's dont, and almost no developers do)
ROW_NUMBER(), RANK/DENSE_RANK windowing functions; other windowing functions later.
LEFT OUTER JOINs (I don't know anyone that uses RIGHT joins, it's just conceptually harder to grasp).
CROSS APPLY
CTEs
Inline Table-Valued functions (inline is vastly more efficient than multi-line. Find a multi-line function or two and convert them to inline (if possible; it most often is, esp. for simpler functions).
Indexes: clustered, nonclustered, filtered at minimum. Columnstore and full text if you really want to expand your knowledge.