October 18, 2023 at 9:26 am
I'm migrating some databases. I always find single_user a bit annoying. Also it means I have to maintain that single connection to ensure nobody else can. These database are accessed by webApps & only webApps (other than me as the only admin). Can I just rename them to ensure no user can access but that the database is still there in case I need to fail back? Is there any downside to this?
restricted_user won't work due to the webApps being db_owners. I can change this I know, but I want to be able to fail back easily if required.
Thank you.
October 18, 2023 at 9:53 am
Set the database to offline, then no one can access it at all, then if you need to failback you just set it back to online
ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE <dbname> SET ONLINE
The only issue here is that this releases the file locks to sqlservr.exe process puts on the MDF/NDF/LDF files so be careful not to move or delete them otherwise you need to restore from backup.
October 18, 2023 at 9:55 am
Oh yeah. Duh! Thank you. Totally forgot that as an option. Long day already at it's not even 11. 🙂
October 18, 2023 at 7:10 pm
prevent the accounts to connect to the database.
To differentiate these accounts for this action, create a database user role which you "deny connect"
for your maintenance, you add the accounts to be prevented database usage, add them to this "urole_deny_connect"
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 18, 2023 at 8:07 pm
If the database is in full recovery - and you are migrating by taking a full/diff backup and then applying all transaction logs to bring the new system up to date, then you can use NORECOVERY on the final log backup. This will perform a tail-log backup and set the database to RESTORING.
https://learn.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver16
This will ensure that there are no further transactions applied to that database that are not captured in the final log backup.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply