July 11, 2022 at 10:21 pm
Hi All,
How the SQL server backup will work with scheduled job? What is the sequence it follows? What is the criteria it choose that it need to take this DB backup first, second .. like that? Is it possible before it complete the backup of the one database it will start another database backup?
July 12, 2022 at 6:34 am
If you chose to use "managed backups" it will figure it out itself.
If you chose not to use managed backups ( as it's always been ), you decide what kind of backup is created and when.
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
July 12, 2022 at 1:13 pm
Is it possible before it complete the backup of the one database it will start another database backup?
SQL Server Agent will not start another instance of a job if job is still running from prior scheduled time. So no, it won't start another backup from that job. But if you have one job for full backups, one for differential backups, & one for log backups, one of the other backup types could certainly start before the other completed.
You need to know how long backups tend to take (and how they work/relate), and adjust your schedule accordingly.
For example, if you run a weekly full backup at midnight Sunday, a nightly diff backup at 1 am, and log backups every 15 minutes, there is little value in running the differential backup on Sunday morning, and no value if the full backup takes an hour or longer.
July 12, 2022 at 1:18 pm
Backups should be run serially. I tried running them in parallel. It's a great way to bring down a server. So yes, most tools, software and suggested solutions, show backing up one at a time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 12, 2022 at 8:20 pm
We have full backup job scheduled. We have about 10 databases on that instance. We are using powershell script to backup. It will written the status of the back up to a text file(.bch) as out put like database name that is backing up, instance name…
Sometimes the backup job is failing because the output file is being used by other processes. If it’s doing the back up the databases one after other the file shouldn’t be used by other processes (Diff backup file & log back out put files are different). So the SQL server try to start the database backup before it complete the other database backup
July 12, 2022 at 9:01 pm
First things first... ALWAYS read the Microsoft Documentation on such things. Here's the link so there's no excuses on the prime duty of a DBA...
https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql
Second, look at the options... the explanations are accurate but they certainly don't tell the full story. Take the following two "options" they speak of
--Data Transfer Options
BUFFERCOUNT = { buffercount | @buffercount_variable }
| MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
They give all sorts of warnings about how you can run your server out of memory by using these but they never explain the critical very high performance advantages of using them properly. I ended up taking a crack at using BUFFERCOUNT = 17 and MAXTRANSFERSIZE = 1048576 along with page compression being turned on at the server level. I'm also using "single file" backups because I've never seen anyone actually get much in the line of performance improvements there UNLESS they actually backing up to multiple drives of spinning rust and then I've seen that also slow things down because their "pipe" couldn't handle it.
I read the article below a long time ago to give me an idea of what to set things to. Understand that the author does backup to NUL for a lot of his tests, which is not like backing up to the real thing. I've been using the numbers I provided above for as long as I can remember (about the same time I started my current job more than a decade ago). I'm backing up to NAS SSDs at the rate of nearly 3TB per hour to a single file one database at a time.
https://www.mssqltips.com/sqlservertip/2539/options-to-improve-sql-server-backup-performance/
Once you're well versed on those setting, look up Ola Hallengren's solution for backups and then build yourself a RESTORE plan.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply