A Fine Slice Of SQL Server
Randolph West wants you to keep some things in mind after you restore that database:
Whenever I restore a database — especially one I obtained outside of my regular environment (for example a customer database, a development database, or even a sample database like WideWorldImporters) — there are a few things I like to check to make sure it’s configured for peak performance.
Note that some of this advice may apply only to non-production databases.
Click through for the list.
Comments closedStuart Moore talks us through a (rare) gap in dbatools:
In the comments here I was asked about using Restore-DbaDatabase when all you have is blobs in an Azure Storage account. This can be done, involves a couple of non dbatools steps.
Restore-DbaDatabase doesn’t natively ‘talk’ Azure, nor do any of the other dbatools commands. The reason for this is that we didn’t want to force dbatools users to have to install the AzureRM/Az powershell modules just to use our module. We’ve gone to a lot of effort to make sure that dbatools is acceptable to Security Admins and that it has a small(ish) footprint, and adding those large modules as prerequisites would have broken that.
Read on for how you can get around that.
Comments closedChad Callihan takes us through our sordid pasts:
What do you do when you want to check the history of your database backups? Do you look at a drive containing your backups? Do you have software like Netbackup for you to open and review? My preference is to run a query in SSMS to get a quick overview.
With a couple tweaks this versatile script can be helpful in a variety of scenarios. You can check a single database, an entire SQL server, or multiple SQL servers. Here is how I use this type of script that queries the backupset table and backupmediafamily table in msdb. I recommend checking out these two tables because there are some good column options that you may want to include when reviewing backups. If you’re feeling dangerous, just modify the scripts below to be SELECT * and see what you get.
Click through for the scripts.
Comments closedGarry Bargsley has a solution to an annoyance:
Whether you are new to SQL Server or a seasoned veteran, you will notice odd behavior in the SQL Server Error Log. When a database backup is performed, an entry is put into the SQL Error Log. The SQL Server team decided to log successful backup messages to the Error Log. If you ask most technology professionals, you will find that logging successful events are not really a common occurrence. This behavior causes a bloated Error Log that can make it hard to find what you need quickly.
Luckily, that same SQL Server team built in a solution to this situation.
Read on to see what the solution is, as well as how to use it.
Comments closedTaryn Pratt has a process for offloading maintenance operations onto another server:
Early on when I started working on the SQL Servers at Stack Overflow, we were taking daily backups. We had a handful of databases that were being restored for other processes, but the majority weren’t actively tested to ensure the backups were good. Since you never want to be in a situation where you need to restore a database and find it doesn’t work, my goal was to create a process to automatically restore our backups to a separate server, and then run
DBCC CHECKDBon it.
This is a T-SQL-driven process and I appreciate that. If you want a Powershell-driven process, Kevin Hill has you covered.
Comments closedChad Callihan reminds us of the other half of backups:
You setup TDE for your database. Great! Now you need to restore that database to a different server. Maybe you’re migrating off of an older server or maybe there is testing to be completed for an upcoming release. If we try to backup/restore or attach the database on a different server we’ll get some variation of an error stating “cannot find server certificate with thumbprint…” and the process will fail. There are a few steps to get the TDE database restored and we’re going to walk through them today.
Read on for those instructions.
Comments closedJack Vamvas has a process for us:
Steps to restore a database from a backup device that was part of an Always On Availability Group, and now needs to be restored
Recovery Scenario : Requesting an older database copy previously backed up
Name of Always on Availability Group = MyAG1
Name of Always On Availability Group db = MyAGDB1
Note: this is a workflow – and there may be some slight variations depending your Availability Group set up
Read on for rest of the workflow.
Comments closedHasan Savran reviews a new bit of functionality in Cosmos DB:
Azure Cosmos DB announced Continuous Backup in Cosmos DB on March 2021. This feature is currently in public preview mode and It is not recommended to use in production. This option gives you more options for your backup requirements. You might be using Azure Data Factory to handle your custom backup needs. Azure Data Factory is the SSIS in cloud. ETL jobs can be problematic. Backing up a database is half of the problem; other half is restoring a database. Until now, we had to call Microsoft to restore Cosmos DB databases/accounts.
By using Continuous backup, you can easily backup and restore your database. For now, this option is available only for SQL API and Mongo API. There are many limitations in this public preview version. I am sure many of these limitations will go away when it becomes generally available to everybody.
Click through for more details about the offering, as well as how to enable it. We’ll have to wait until it’s out of public preview to see how much it will cost, but it does look interesting.
Comments closedJonathan Kehayias explains why SSDs aren’t good long-term backup devices:
Back in 2012, I wrote a post titled Looking at External Disk Performance using USB 3.0 and eSATA with SSD, where I tested a number of external drive caddy’s with SSD’s that I had replaced and just had sitting around. Ultimately I started to use those SSD’s for storing information that I didn’t really need to have on my laptops and when they were full, they ended up in my desk drawer, where they have sat, unplugged and “safe” for the last 7-8 years. Or so I thought. With cheer competitions season in full swing, one of the things I love to do is shoot photos of my kids and their teammates competing, and storing RAW files that are 25-35MB per photo when you shoot 1000+ photos in a weekend across four different teams starts to take up a lot of space, so I figured I would pull out the old SSD’s and see what was on them that was worth keeping, delete what wasn’t and I could then move last years RAW files over to them and archive them for safe keeping. WRONG!!! Of the four SSD’s I had stored data on, 100% of them had data loss due to a phenomenon known as bit rot. One of them wouldn’t even show up in Disk Manager in Windows and had to be low level formatted and reset using diskpart’s clean command due to partition table corruption.
This is a nasty scenario. Read on to learn more about how you can detect the issue and keep in mind the 3-2-1 rule: 3 copies of your data in at least 2 storage media, at least 1 of which is offsite.
Comments closedArun Sirpal takes us through a fairly new feature in Azure SQL Database:
There is a new (ish) interface to looking and configuring backups for your Azure SQL Database. This can be found within the settings section of the SQL Server.
As you can see, by default we have 7 days retention to allow for PITR – Point In Time Recovery, anything longer you will need to setup long term retention.
Click through to see how to set this up.
Comments closed