March 21, 2008 at 3:22 pm
For taking db/tran log backups which one to be used as a best practice:
1. Using database maintenance plans
2. Using sql/stored proc scripts.
Thanks!
March 21, 2008 at 4:49 pm
Either one can be used. They both can accomplish full and log backups, so it's a matter of preference.
March 22, 2008 at 6:31 pm
Actually our old dba has taken daily full db backup's and tran log backup's are evry 5 hrs, I am new to backup's and we are planning to redo backup's as the following:
1. Take full db backup every week sunday
2. Diff database backup's monday-saturday
3. Tran log backup's every 30mins or 1 hr.
We shall be redoing our backup's so please let me know if it's better to use stored proc scripts for the backup's. Thanks!!
March 22, 2008 at 8:32 pm
There is no better. It's whichever works better for you. I don't believe that maintenance plans work for differentials, so you might need to use scripting. You could use the maintenance plans for the full/logs and script the diffs, though that can get confusing.
There are lots of scripts here on the site for doing backups, so you can see how others do it.
February 25, 2016 at 12:03 pm
I'm a newbie to SQL server and I'm not sure if I'm posting this correctly so please let me know if I have it wrong.
What is the best practice order for the order of the below items. Is it DBCC just before a full backup?
full backup
reorg
rebuild
runstat
dbcc
full backup
tran backup(s...)
differential backup
tran backup(s...)
cycle. Are there any big pitfalls to avoid?
February 25, 2016 at 2:25 pm
Lwestcott 17148 (2/25/2016)
I'm a newbie to SQL server and I'm not sure if I'm posting this correctly so please let me know if I have it wrong.What is the best practice order for the order of the below items. Is it DBCC just before a full backup?
full backup
reorg
rebuild
runstat
dbcc
full backup
tran backup(s...)
differential backup
tran backup(s...)
cycle. Are there any big pitfalls to avoid?
Do NOT use maintenance plans for Reorg, Rebuild, or RunStat. They basically do all indexes and stats, even if they aren't needed.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 25, 2016 at 5:02 pm
I agree wholeheartedly with Jeff. Maintenance plans ARE the pitfall to avoid when it comes to anything related to index or statistics maintenance. Personally, I don't use them for anything. There are several products out there, both free and paid. If you're new to the topic and need a free tool, Ola's scripts are an option.
I would never recommend maintenance plans to anyone. They're better then nothing, but not much better.
February 26, 2016 at 6:40 am
Thank you for the help!
Ok, dumb question, but why can't the "tool" take a parameter or two like fragmentation percent and/or number of pages in use? When above certain thresholds the produce the script to rebuild etc. (I probably have having a logic attack and need to relax...)
I have found a script that I have used for rebuilding indexes with parameters and have started to use that.
I'll look into the items you mention.
thanks again,
Lisa
February 26, 2016 at 5:03 pm
Lwestcott 17148 (2/26/2016)
Thank you for the help!Ok, dumb question, but why can't the "tool" take a parameter or two like fragmentation percent and/or number of pages in use? When above certain thresholds the produce the script to rebuild etc. (I probably have having a logic attack and need to relax...)
I have found a script that I have used for rebuilding indexes with parameters and have started to use that.
I'll look into the items you mention.
thanks again,
Lisa
The answer to the question above is simply because they didn't write it that way. Heh... yeah. I know. It was a rhetorical question based on frustration. These types of shrink-wrapped oversights and omissions drive me nuts as well but the drive was short and I've grown to expect such lunacy from any product. 😉
Shifting gears to the script you found... if it doesn't have a section for rebuilding stats, your job of finding a script isn't done yet. Most folks recommend Ola Hallegren's scripts, which are pretty good, but there's nothing like building your own for a bit of self training and understanding.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 27, 2016 at 7:29 am
Jeff Moden (2/26/2016)
Shifting gears to the script you found... if it doesn't have a section for rebuilding stats, your job of finding a script isn't done yet. Most folks recommend Ola Hallegren's scripts, which are pretty good, but there's nothing like building your own for a bit of self training and understanding.
Echoing Jeff's point here, writing your own is an awesome way to learn. You'll really gain an understanding of how things work when you write it - I know I did. Don't expect to write a fully-finished product with every option you'll ever need. To start, write it with the options you need. If the need arises one day to have different options, add them it. Just don't forget to test your solution well with each update.
There are scripts out there you can learn from, but don't skip the step of reading about what statistics are and how they're used. Adding this to your collective sum of knowledge will only expand your understanding of how SQL Server works, which is never a bad thing.
March 1, 2016 at 6:42 am
I have debated heavily on replying at all but, have decided to. I need to pick up on the nuances between SQL server and IBM DB2. I've been a mainframe DB2 administrator for decades, so table, row, column cardinalities and how they participate in a cost based optimizer are ingrained in me. Like the fill factor on SQL server is akin to the percent free and free pages on DB2. On more than one occasion I have been the person who "knows windows or Unix" and have helped out on SQL server but now need to do it full time. Also, tools to (reorg, runstat, DB2 terms) (reorg, rebuild, update statistics) that determine what needs to be made more contiguous by using parameters are fairly second nature to me.
So selfishly; I don't want to build or make scripts for this myself. I want to find a vendor who stays current and I can install, learn, use their tools to keep DBs in line and update their product periodically. I don't know if that exists.
Obviously I need SQL server knowledge but I don't need the elementary admin. courses... I think... or maybe others would judge and say I need that.
I can performance tune predicates and logical units of work. And I love the SQL server full backup, incremental backup, and application of the transaction log(s) for DR. I have already used this for point in time recovery.
With all that said. Are there good next steps for me? I really appreciate the help as I have not been able to find what I really would like thus far. A good book? A good conference? A good maintenance toolset? I really want "SQL server administration for an IBM DB2 database administrator" but I don't think anyone has written that one. LOL.
Thank you very much for you time and help! 🙂
March 2, 2016 at 9:32 am
We use a term called the accidental DBA, where someone is thrust in to managing a server who has limited prior experience. Your background in DB2 will help. Myself, I came from a 370 Cobol JCL background and got to do some work on an AS/400 at my previous gig, along with some experience on Windows DB2. A lot of your experience will transfer.
Take a look at the Stairways series to the left of the page, there's lots of articles there that focus on specific subjects. Another excellent source of info is through Redgate's site, they offer a number of free ebooks on a variety of subjects. You're coming in at a somewhat advanced level because of your DB2 experience, which is good. You'll run in to some vocabulary differences, but I can't say where you'll hit those. I would suggest getting Tony Davis and Gail Shaw's book on transaction log management (Gail is on this site pretty much 24/7) and Shawn McGehee's on backups and restores.
I would also recommend Brent Ozar's[/url] newsletter and his excellent sp_blitz script, it reveals excellent insights to your databases. They also have a weekly 'office hours' chat where you can ask questions, it's a great way to pick up info and learn about the horrors that others have to endure.
Idera is also a good source for free tools, depending on how deep you want to get as an admin.
The most important things for a DBA is to ensure a database is recoverable through backups and restores. Since you've been able to do a point in time restore, you've got a decent handle on that. You need to be careful of backups and LSNs (logical sequence numbers) to know how the sequence can be broken and thwart point in time restores.
Welcome to the zoo!
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
March 10, 2016 at 6:47 am
Thank you for this information. It will help me a lot.
March 11, 2016 at 6:45 am
I don't see that anyone here has suggested it yet so I would like to very strongly suggest using Ola Hallengren's maintenance Scripts
All you need to do is to download the script, run it on your server and it will create everything you need including the jobs. All you need to do is enable the jobs and change any schedules you want. The defaults should be just fine for what you need, but you can easily change ANYTHING you want very easily. This is for the absolute beginner who knows nothing about SQL and just wants a run and forget it solution all the way up to large enterprises who want a scalable and configurable solution that can handle anything.
https://ola.hallengren.com/[/url]
This is very simple to use and to configure. It is also well documented on his site. This is very highly recommended and used in the DBA communities.
March 29, 2016 at 8:18 am
Hello again. I have come across a situation I looking for some insight. There are parent child tables defined with foreign references across the database with on delete no action (by default) on the identity column and on delete cascade on other "business key" columns. So the parent child identity column in the 1st FK has no action on delete. But the 2nd FK on columns with business value has a cascade.
For the 1st FK does this mean that on insert, update, and delete there is No Action? If so, can you help me with why you would define it? If it's not going to help you, why put it in place is where my head is right now. I am a bit of an SQL newbie so maybe I'm missing something.
1st
ALTER TABLE [dbo].[t_order_detail_comment] WITH CHECK ADD CONSTRAINT [fk_order_detail_comment] FOREIGN KEY([order_detail_id])
REFERENCES [dbo].[t_order_detail] ([order_detail_id])
GO
ALTER TABLE [dbo].[t_order_detail_comment] CHECK CONSTRAINT [fk_order_detail_comment]
GO
2nd
ALTER TABLE [dbo].[t_order_detail_comment] WITH CHECK ADD CONSTRAINT [fk_order_detail_comment_2] FOREIGN KEY([wh_id], [order_number], [line_number])
REFERENCES [dbo].[t_order_detail] ([wh_id], [order_number], [line_number])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[t_order_detail_comment] CHECK CONSTRAINT [fk_order_detail_comment_2]
GO
Thanks again for any help you can provide!
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply