Carry on It turns out that the only thing harder than checking for new databases restored to a SQL Server, is checking a folder for a backup of a database that doesn’t exist on another SQL Server. These are both part of what sp_AllNightLog has to do. The other components, which use workers and a…
Building stuff I sometimes really hate coming up with ideas, and much prefer someone to just say “I want this” so I can go out into the world and figure out how to do that. Occasionally though, I realize that I’m not going to get all that much direction. That’s what happened with sp_BlitzQueryStore. It…
Phantom of the Nopera Let’s say you have a server. Let’s go one step further and say SQL Server is running on it. Crazy, I know. But hey, we specialize in crazy, here. If you want to track CREATE, ALTER, or DROP DATABASE commands, that’s easy enough with DDL triggers. But guess what? None of…
In our Faux PaaS project, we need a backup plan – or rather, a restore plan. On each SQL Server instance, clients can create as many databases as they want, anytime they want, with no human intervention. We need those databases covered by disaster recovery as quickly as practical. SQL Server’s newer disaster recovery options – Always On Availability Groups and…
Brent had an idea that we should set aside a day per month where we could build things in the cloud and blog about our experiences. I was tasked with Amazon RDS for SQL Server: create an instance, configure backups and test point-in-time restores. After grabbing some coffee, I logged into the AWS console. We…
We’re trying something new at the company: Builder Day. We define a slightly-out-of-the-norm task, and then the team splits up and tackles the task on different cloud platforms, writing it up as we go. These posts aren’t going to make you an expert on the topic – they’re just meant to let you skim through…
For some of you, the headline is self-explanatory, and you just want the URL, so here it is: https://github.com/olahallengren/sql-server-maintenance-solution For the rest of you, here’s what it means. What are Ola Hallengren’s maintenance scripts? Ola Hallengren’s maintenance scripts are a widely used replacement for maintenance plans. His backup, integrity check, and index optimization stored procedures are…
We see a lot of SQL Servers. During our sales process, prospective customers run sp_Blitz on their SQL Server, and we talk through the results. I see a lot of horrifying stuff. Over time, I’ve built up a pretty good sense of signs that a SQL Server is running around loose in the data center,…
We got an email!!! It was weird. It was from an Australian, and it was right side up. I could hardly believe it. Normally we encourage people to ask us questions during Office Hours, or over on Stack Exchange. That way lots of people can benefit from the answer. But since this reader is on…
Welcome to the DatabaseRestore documentation page. We were looking for a script that restores a database including the transaction log chain using the backup files in a folder. We also wanted it to be able to continue applying transaction log backups. In other words, we were looking for a custom Log Shipping solution. We could…
In part 1, I showed you how to corrupt a database – not because you’d need to do it, or because SQL Server has some kind of vulnerability. I just wanted to show you how SQL Server reacts when it finds corruption in a data file, why you should check your databases for CHECKSUM settings, and…
In this multi-part series, we’ll start with easy corruption, work our way up to nonclustered indexes, then show how checksums and backups aren’t enough for protection, either. To get all the parts as they’re published, subscribe. Hold my beer. CREATE DATABASE [50Ways]; GO ALTER DATABASE [50Ways] SET PAGE_VERIFY NONE; /* Normally a bad idea */…
Companies call us for performance or high availability issues, but over and over, the very first two things we find are: They’re not taking backups to match the business’s RPO and RTO They’re not doing CHECKDB weekly, or at all, and don’t understand why that’s an issue So let’s walk through a simple scenario and…
Seriously DBCC CHECKDB (YourDatabaseHere) WITH NO_INFOMSGS, ALL_ERRORMSGS; Brent says: normally, we don’t post client screenshots on the blog because it’s hard to sanitize the sensitive data. This time, not so much. Storage took care of this for us.
#basicbackup So you’re taking backups! That’s great. Lots of people aren’t, and that’s not so great. Lots of people think they are, but haven’t bothered to see if the jobs are succeeding, or if their backups are even valid. That’s not great, either; it’s about the same as not taking them to begin with. If…
So you’re hosting your SQL Server in the cloud – say Amazon EC2, Azure VM, or Google Compute Engine – and you’ve noticed that when you’re running a backup or a DBCC CHECKDB, you suffer from extreme performance problems. Queries run slow, and even worse, applications report timeout errors even just trying to connect to…
At first glance, if you set up log shipping the way most folks do, it looks like you can’t run CHECKDB on a subscriber: On SQL Server Enterprise Edition, you can try working around it by creating a database snapshot – but that won’t work either: Here’s the trick: your database needs to be in…
One of the most popular things in our First Responder Kit is our HA/DR planning worksheet. Here’s page one: In the past, we had three columns on this worksheet – HA, DR, and Oops Deletes. In this new version, we changed “Oops” Deletes to “Oops” Queries to make it clear that sometimes folks just update…
I’ll get right to the point While you’re Direct Seeding, you have to be careful with any other full or differential backup jobs running on the server. This is an artifact of the Direct Seeding process, but it’s one you should be aware of up front. In the screencap below, courtesy of sp_whoisactive there’s a…
Contrary to popular belief, we spend a lot of time with clients when we’re not blogging, answering questions in Office Hours, or working on new features for the download pack. Something we hear a lot is, “How do we compare to other clients?” or “Is this the worst/best setup you’ve seen?”. This got me thinking,…
