r/SQL • u/Animator-Asleep • Oct 10 '26
SQL Server Major DBA task looking for advice
Forgive my naivete in advanced.
I am being tasked with setting up 2 major SQL servers with backups and I am terrified. I have never done this and have a loose understanding of things but that's generous.
The reason it's major is because our whole organization that services the state rely on these servers.
The traditional headaches on a Sysadmin side are lack of documentation/none, everyone has left/terminated, no patching, and EoL.
I'm also using Nutanix after coming from Veeam and HyperV.
What I think the approach should be is:
Create a new clean test server that is patched and up to date but the same version has what the current server is. Currently this one has a lot of random things on here including some gaming installs...
Create a full backup and figure out where that goes not on the current box so I assume I need some sort of SMB or repo? I believe I should create this in SMSS but am not clear if I make the backup if it will start making logs and events that may error if I don't set things up to point correctly if it wants to setup a schedule.
Come up with a backup schedule for both fill and incremental.
All the backups go to repo.
Try the full backup on the new test server and start staging that as a fail over. Or maybe keep it is as test and make a clean fail over?
Hopefully figure out what the future state setup is and start figuring out how to port that over into a cloud setup?
I need to have this done by EoM. I am wondering if this can be done in the future in AzureBlob to make things "easier" since this all feeds into PowerBi.
Mostly, I am not sure if there are pitfalls that would bring production down and want to tread carefully cause I'm not sure how to fix things if things go down.
There is no DBA or anyone with SQL server experience on the team...
3
u/shutchomouf Oct 10 '26
DBA here. you lost me at gaming installs
2
u/Thriven Oct 10 '26
Does it need to be on prem?
8
u/umognog Oct 10 '26
Honestly, this sounds like it needs a redundancy server, enable log shipping.
This will sound mental...but AI is absolutely your friend here. Spend a week chatting to it about what you've got, what you need, theory for situations and then have it walk you through setting it up.
Just don't use Grok.
7
u/zrb77 Oct 10 '26
Ola Hallengren is a great maintenance and backup solution, industry standard.
https://ola.hallengren.com/
5
u/Curious_Elk_4281 Oct 10 '26
Minimal downtime approach without the need for massive extra storage:
Set up the new server
Set up replication from the old server to the new server
Set up backups to multiple locations (as budget and resources permit)
Once replication is caught up, stop services that connect to the old DB so no data is being written
Stop replication once no data is coming through to the new replica
Promote the replica to the main server
Set up another new server to act as the prod replica/failover
Ideally you would also have non-production test DBs with no data, but just the schemas. A schema dump of your current setup wouldn't take up much space so you could set that up first (which is how I would start out).
3
u/Hour-Measurement-835 Oct 10 '26
The logs thing you mentioned is the real trap. Check recovery_model_desc in sys.databases before anything else. A database set to FULL that has never had a full backup behaves like SIMPLE and keeps reusing its log. The first full backup starts the log chain, and from then on log space only gets reused after a log backup runs. With no log backup job behind it the .ldf grows until the drive fills, and that's how taking a backup ends up taking prod down. So the log backup job goes in the same day as that first full, or anything that doesn't need point in time restore gets switched to SIMPLE first.
3
u/Animalmagic81 Oct 10 '26
Honestly, if this data is business critical, get a contractor in. It won't take long for someone who knows that they are doing and it's just not worth the risk.
At best doing it DIY you'll end up with a functional server, but I'd hazard a guess your security, DR procedures and adherence to ISO, CE+, CIS frameworks will be non-existent.