WokingTrafficSafety I agree, that's very relevant information... the problem is that it goes down the rabbit hole really quickly into a discussion about logging and recovery, which I think would be better to keep in a separate video. Everything is very interconnected, and it's tough to figure out where and how to limit what's presented. Thanks for the feedback, though, I really appreciate it!
The business this is being set up for is the only one that can determine what the schedule should be for each type of backup. I hope you're able to figure that part out on your own, though perhaps omitting an example from the video was an oversight.
Can you please tell the difference between Full, Differential and Transaction Log? and if I'm taking a backup how can I save old and new backup ( will it overwrite each time)?
Every backup will be saved in its own timestamped file, so no, it won't overwrite. In fact, you need to have a process to clean them up once they're older than the retention period you want. Have a look at this series of blog posts: www.sqlskills.com/blogs/paul/the-accidental-dba-day-6-of-30-backups-understanding-rto-and-rpo/
Thanks for your reply! Now I have another issue. Message The job failed. Unable to determine if the owner (abc) of job Test_varshaBackup.Subplan_1 has server access (reason: Could not obtain information about Windows NT group/user 'abc', error code 0x6e. [SQLSTATE 42000] (Error 15404)).
In terms of purely backing up databases, how would you say this method performs in relation to setting it up through SSIS, My business has SSIS but they use these style of maintenance plans instead and I am wondering what the pros and cons are of each
A maintenance plan is simplified SSIS. I've used it in production, and recommended and set it up for customers. The main thing this (and SSIS) has going for it is simplicity as almost everything is GUI-driven. The biggest drawback is that it doesn't scale easily or transparently to many servers. If you know you'll have to scale, or if you're an advanced user, I would look into using a script-based solution.
Well, there are other benefits in the tooling, because as long as you have access to SSMS (which will be true in 99.9% of cases), that's all you need to create/edit a maintenance plan. That might not apply to your situation, but it's critical if you're dealing with clients that don't know what they're doing. IMO, going from maintenance plans to SSIS for the sake of change isn't worth it unless you have a really good deployment system. That's the key part, as it's a big drawback with maintenance plans. Still, if I was going to touch a critical working system like that, I would be replacing with a script-based solution, because that's so much better than either SSIS or maintenance plans in the long-term.
Thanks! Yes, you can set the backups to go to a UNC path (I don't recommend using a mapped network drive); you may have to paste the path into the dialog box manually instead of using the browser. Remember to grant appropriate write permissions on the target location.
Bobby Wilson Good question. There are a lot of reasons; here are a few: - A network drive mapping has to be maintained in order for the backups to work. If it doesn't get recreated automatically by Windows when the server restarts, or if it fails for some reason, there will be no backups. - If the SQL Server instance is clustered, the network drive would have to be maintained on every node in the cluster. - It would take up what may be a very valuable drive letter (using mount points isn't always feasible). Abstraction of the location can be an advantage if flexibility is needed; that particular method just has too many negatives for my liking. Setting up something like a DNS alias would meet the need of abstraction while being much simpler and more reliable, IMO.
Hi Olivier, I didn't cover scheduling here because there are so many different ways to do it depending on the requirements, i.e., database recovery model(s), data loss SLAs, etc. For the same reason I can't cover it in the comments here, but I will consider talking about it in a future video. In the meantime, you should be able to find some articles that discuss this by searching around, or you can ask a question on dba.stackexchange.com that includes all the requirements for your environment. Thanks for watching!
Please you can help me, why when I install SQL server 2012 not appear in SQL Managment Studio. While I installing all this files 1-SQL Managment Studio - Complete 2- SSDTBI_VS2012_x64_ENU 3- SQLEXPR_x64_ENU 4- SQLEXPRADV_x64_ENU 5- SQLEXPRWT_x64_ENU 6- SqlLocalDB Thanx for all you cooperation
+Mohammed Ahmed SQL Express does not include SQL Agent functionality, so Management Studio does not show it in the interface. If your installation is for development purposes, I would recommend using the Developer Edition, which includes all functionality in Enterprise Edition, but is not licenced for a production environment.
Hi Mario, this is a complicated question. I would suggest reading Paul Randal's series of blog posts on backup and restore, starting here: www.sqlskills.com/blogs/paul/the-accidental-dba-day-6-of-30-backups-understanding-rto-and-rpo/
This was wonderful. Thank you for taking the time to create this!!!!
Thanks mate.. I owe you a beer...
Respect from Turkey.
I like the demo, thanks for that!
You're welcome! I'm glad you like it.
Great video. Would be worth mentioning when or how truncation of logs happens.
WokingTrafficSafety I agree, that's very relevant information... the problem is that it goes down the rabbit hole really quickly into a discussion about logging and recovery, which I think would be better to keep in a separate video. Everything is very interconnected, and it's tough to figure out where and how to limit what's presented. Thanks for the feedback, though, I really appreciate it!
Thanks a lot. It helped me in learning things.
You're welcome!
Great explanation on the subjet.. congrats... however without schedule details... the demo is half complete.
The business this is being set up for is the only one that can determine what the schedule should be for each type of backup. I hope you're able to figure that part out on your own, though perhaps omitting an example from the video was an oversight.
Can you please tell the difference between Full, Differential and Transaction Log? and if I'm taking a backup how can I save old and new backup ( will it overwrite each time)?
Every backup will be saved in its own timestamped file, so no, it won't overwrite. In fact, you need to have a process to clean them up once they're older than the retention period you want. Have a look at this series of blog posts: www.sqlskills.com/blogs/paul/the-accidental-dba-day-6-of-30-backups-understanding-rto-and-rpo/
Thanks for your reply! Now I have another issue.
Message
The job failed. Unable to determine if the owner (abc) of job Test_varshaBackup.Subplan_1 has server access (reason: Could not obtain information about Windows NT group/user 'abc', error code 0x6e. [SQLSTATE 42000] (Error 15404)).
Thanks it worked, changing owner to sa.
In terms of purely backing up databases, how would you say this method performs in relation to setting it up through SSIS, My business has SSIS but they use these style of maintenance plans instead and I am wondering what the pros and cons are of each
A maintenance plan is simplified SSIS. I've used it in production, and recommended and set it up for customers. The main thing this (and SSIS) has going for it is simplicity as almost everything is GUI-driven. The biggest drawback is that it doesn't scale easily or transparently to many servers. If you know you'll have to scale, or if you're an advanced user, I would look into using a script-based solution.
So are there any benefits to using this method over SSIS? other that it being simple?
Well, there are other benefits in the tooling, because as long as you have access to SSMS (which will be true in 99.9% of cases), that's all you need to create/edit a maintenance plan. That might not apply to your situation, but it's critical if you're dealing with clients that don't know what they're doing.
IMO, going from maintenance plans to SSIS for the sake of change isn't worth it unless you have a really good deployment system. That's the key part, as it's a big drawback with maintenance plans. Still, if I was going to touch a critical working system like that, I would be replacing with a script-based solution, because that's so much better than either SSIS or maintenance plans in the long-term.
Good stuff. Can you set a backup to a networked drive?
Thanks! Yes, you can set the backups to go to a UNC path (I don't recommend using a mapped network drive); you may have to paste the path into the dialog box manually instead of using the browser. Remember to grant appropriate write permissions on the target location.
Voluntary DBA Thanks for the quick response. Why don't you recommend using a mapped network drive if you don't mind me asking.
Bobby Wilson Good question. There are a lot of reasons; here are a few:
- A network drive mapping has to be maintained in order for the backups to work. If it doesn't get recreated automatically by Windows when the server restarts, or if it fails for some reason, there will be no backups.
- If the SQL Server instance is clustered, the network drive would have to be maintained on every node in the cluster.
- It would take up what may be a very valuable drive letter (using mount points isn't always feasible).
Abstraction of the location can be an advantage if flexibility is needed; that particular method just has too many negatives for my liking. Setting up something like a DNS alias would meet the need of abstraction while being much simpler and more reliable, IMO.
Very nicely done.
Thanks! I'm glad you liked it.
Fantastic video!
Could you please brush up on how to schedule a backup maintenance plan?
Kind Regards
Hi Olivier,
I didn't cover scheduling here because there are so many different ways to do it depending on the requirements, i.e., database recovery model(s), data loss SLAs, etc. For the same reason I can't cover it in the comments here, but I will consider talking about it in a future video. In the meantime, you should be able to find some articles that discuss this by searching around, or you can ask a question on dba.stackexchange.com that includes all the requirements for your environment. Thanks for watching!
Please let me know if the Hindi closed captions were helpful by voting on this comment or by leaving a reply. Thank you!
+Voluntary DBA yes it was!! but we were pretty much comfortable with the audio as well, thanks!
Thank you!!!!!! Thanks a lot man!!!!!...
Please you can help me, why when I install SQL server 2012 not appear in SQL Managment Studio.
While I installing all this files
1-SQL Managment Studio - Complete
2- SSDTBI_VS2012_x64_ENU
3- SQLEXPR_x64_ENU
4- SQLEXPRADV_x64_ENU
5- SQLEXPRWT_x64_ENU
6- SqlLocalDB
Thanx for all you cooperation
+Mohammed Ahmed SQL Express does not include SQL Agent functionality, so Management Studio does not show it in the interface. If your installation is for development purposes, I would recommend using the Developer Edition, which includes all functionality in Enterprise Edition, but is not licenced for a production environment.
how can recover a DB from a backup file?
Hi Mario, this is a complicated question. I would suggest reading Paul Randal's series of blog posts on backup and restore, starting here: www.sqlskills.com/blogs/paul/the-accidental-dba-day-6-of-30-backups-understanding-rto-and-rpo/
ohhh I like it