What is the best way to backup 25 dbs ?
Take into accnt I'm doing daily full (25 files), hourly diff (10 files - 10 hours per day), 15 tran log (4, because of 60 minutes).
planning to store all the files in one folder. (Let me know if there is a better way.)
April 8, 2020 at 2:27 am
If you are using SQL Server that have SQL Agent Job, try using SQL maintenance job.
refer to this link
https://www.sqlservercentral.com/articles/backup-and-housekeeping-with-maintenance-plans
April 8, 2020 at 2:34 am
What about differential and transactional log?
where do i store the backups?
Anyone else have experience with backups?
Backup strategy you describe at earlier post is already good.Backup Interval of those backup types can always be adjusted depending of the RPO/data loss that data owner can tolerate. I've seen someone configure log backup for every minutes.
If you cannot afford data loss, you need to use SQL Alwayson Availability Group or DB Mirroring with synchrnous options.
To store backup usually i put in a disk that different than Data file and Log files, or a network drive.
Example
\\Backup
\\Backup\SQLInstanceName\Full\DBname
\\Backup\SQLInstanceName\Diff\DBname
\\Backup\SQLInstanceName\Log\DBname
With this approach, i can makes sure i know which file is full backup or differential backup and where is the source SQL server backup from. Myshop can have same db name in different SQL instance.
April 8, 2020 at 9:41 am
Ola Hallengren's maintenance solution sets this up as the default.
https://ola.hallengren.com/sql-server-backup.html
You should also look at automating the restore of you Full backups, to another database name, and running DBCC CHECKDB against them as backups are no good unless they can be successfully restored.
April 8, 2020 at 3:27 pm
My preference is to avoid maintenance plans wherever possible. I have run into issues with them and when a problem comes up, it can be tricky to reproduce and debug. On top of that, maintenance plans lack the customization that you can get by writing your own scripts.
As for Ola Hallengren's scripts, my advice -if you want to use those, take the scripts and re-write them to work for your environment. The reason I recommend this is if the script fails and your boss comes to your desk to stand over your shoulder while you fix it and you have TONS of code you don't understand and can't debug and thus can't fix, you may end up having to look for another job. On top of that, if you use Ola's scripts as a base, you can remove the extra stuff that you are not using. For example, you likely aren't running native SQL backups AND RedGate SQL Backups. It will be one or the other.
You don't want to rely 100% on Ola's scripts and then find out they break due to a service pack update AND find out that nobody is supporting the scripts anymore.
What I did is write up my own scripts for doing backups and maintenance that are pretty simple to follow,but have a bunch of customizations for my environment such as using RedGate SQL Backup for the backup solution, and have no support for native SQL backup. But essentially, the script builds up a dynamic SQL query for doing backups through multiple stored procedures (makes testing MUCH easier as you can test in parts) and then has a cursor that loops through sys.databases to generate the backup script and then runs the backup once it generates it for each database.
The stored procedures allow for transaction log backups, differential backups and full backups. As for the schedule, that is just done by the SQL agent jobs. One customization that I put in that Maintenance Plans do not support is doing index reorganizing when page lock is off. Maintenance plans (in 2016 anyways... not sure about newer) will fail. My scripts explicitly filter out the indexes with page lock disabled from reorganizing. Offhand, I am not sure if Ola's scripts handle cases like that.
For your question about backups - I recommend storing them on a separate machine from the SQL box. In the event the SQL box gets compromised, you could lose the live data AND the backups to ransomware. If you can, I STRONGLY recommend offsite backups. Even if it is on-site first and moved from onsite to offsite at a later time. Otherwise if your server room goes up in smoke and everything is destroyed, your backups are toast too. The solution we do at work is to do backups to the SAN in the server room, those get moved to tape backup and the tape backups get moved to a second storage location nightly (manually process unfortunately). So worst case, we lose 1 day of data.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply