SQL Server SET DATEFIRST scope

Just an additional point, if you want to avoid setting DATEFIRST you can just incorporate the value of DATEFIRST in your query to find your required day as :

    SELECT (datepart(dw,ADateTimeColumn) + @@DATEFIRST) % 7)  as 'MondayBasedDate'
    , ...
    FROM famousShipwrecks --

Then you dont need to worry about restoring it at all!

@@DATEFIRST is local to your session. You can verify it by opening to tabs in Sql Server Management Studio (SSMS). Execute this code in the first tab:

 SET DATEFIRST 5

And verify that it doesn't affect the other tab with:

select @@datefirst

See this MSDN article.