Voice of the DBA

Tag: Backup/Recovery

  • Attaching All Databases with PowerShell – The Overview

    TL;DR Script is here: Git Hub Powershell Scripts. It’s the attachdbs.ps1 and will attach all databases in a folder to a SQL Server instance, if they don’t exist.

    I wrote a PowerShell script recently to actually accomplish a task I that I needed. What’s more, this was the first time I thought that Powershell might prove more useful than other methods. This series looks at my script, and this part examines the first part that I wrote.

    After my problems with Windows 8.1 and my reinstallation of SQL Server, I had a problem. I had no databases.

    I had the files. I had backup files. However the instance didn’t have any databases registered. I started down this path.

    attach_a

    However that seemed inefficient. I actually had a pattern of things that I knew needed to be done, I had a bunch of repeatable work, this sounded like it should be a PowerShell type task. I could have done it in T-SQL, or grabbed a script from SQLServerCentral, but it made more sense to load databases with PowerShell.

    The Start

    Of course I started Googling, but didn’t see any posts that shower someone with mdf/ldf files and needing to attach them to an instance without knowing what you had. What I had was an instance, with no backup/restore/detach history.

    attach_b

    I also had a bunch of mdf/ldf files in a folder. As well as some folders for Filestream/Filetable information.

    attach_c

    What did I do? I’ve got the script on GitHub, and you can grab the latest version at: Powershell Scripts (choose the attachdbs.ps1 file)

    This post will give an overview of what I needed to do and I’ll post more details about how I built the script in pieces. The overview of the process is:

    • Get all MDF Files in a folder
    • Connect to a SQL Server instance and loop through all databases
    • If a file name (less the .mdf) does not exist as a database, track this.
    • Get the log file associated with an mdf
    • Attach the mdf and ldf files to the SQL Server.

    That’s what I needed to do and development went in those stages. Certainly there were issues, but I got it working as of this post. When I ran my script, I saw these results:

    attach_f

    In SSMS, I had my databases.

    attach_d

    I even had my Filestream stuff in place. SQL Server handled that for me.

    attach_e

    I’ll include other posts that talk about the details of how I build this, which took about 3 hours one day, and an hour the next.

    References

    Here are a few posts where I picked up bits and pieces of what I needed to do.

  • The Development Backup

    Have you ever had a development server crash? Have you lost work because of this? Had delays or had to recreate code? You shouldn’t, or at least you shouldn’t lose much work or time..

    There was a time when I offered to manage backups on all development servers. This was in a large environment with hundreds of instances.  I wasn’t worried. I had scripts to do the work of setting up, running, and reporting on backups for instances. I knew how to deploy these scripts to hundreds of servers.

    My reasoning was the our development servers were really our manufacturing environment for software. Wouldn’t you ensure your machinery was well maintained and kept in top condition if you had a factory? I know I would.

    The developers passed and once in awhile they’d call and ask of we could recover a server. 

    “Do you have backups?,” I’d ask. “No” was the usual reply. I’d appligize and reiterate my offer to manage the system. They were always resistent and that was fine. They were responsible, and these were their systems. However they had a backup system already. They just didn’t use it.

    Almost all of these people were using a version control system (VCS) for their code, but not for database code. Do me a favor; put your database object code in source control. Add all your DDL for tables, views, functions, stored procedures, and anything else you use.

    As long as it’s on a different physical machine than the development server, you’ll thank me one day.

    Just as long as you also run backups of that VCS database.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 1.7MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

    The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

     

  • Backup Responsibility

    One of the most important things that you can do as a DBA, or really as any sort of system administrator, is back up your system.Ensuring that you have have backups, and of course, that you can restore them, is the number one priority for sysadmins. Everything else that you need to do is second to backups. After all, backups ensure you still have a system after a disaster. If you can’t do that, then security, performance, features, none of that matters.

    I have worked in large and small environments, and in all cases, I’ve assumed that as the DBA, I need to be checking that backups are occurring, and that I can restore them in case of any issues. Often this has meant I need to work more closely with others that have the actual responsibility for performing backups. This week, I’m wondering how many of you work in similar situations.

    Who is responsible for backups in your company?

    Is it the sysadmin of each particular application? Does the DBA ensure database backups while the Exchange administrator handles mail backups? Do you have a centralized system for backups? If backups fail, who’s going to get yelled at? Or perhaps more importantly, who will notice that backups have failed?

    There are any number of ways to handle backups, and honestly, the best way I’ve seen had a centralized person responsible for running backups every day and checking on automated tasks, but the individual system owners (DBAs, Exchange admins, application managers) checking that backups had been made. These individuals also test restores periodically. In this way there was always someone to double check the person responsible.

    Let us know this week how things work in your environment.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.2MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

    The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

  • How Often Do I Backup my Log?

    Do you know how often to back up your transaction log in SQL Server? Most of us have stock answers, hopefully answers that have time intervals like every hour. I worry that most people have a time interval of “never” because their answer is “I make full backups and don’t need to backup logs” or “What’s a transaction log?” That’s a different discussion and if those are your answers, I have an article for you to read.

    Managing a transaction log is a bit tricky and not straightforward. I think far too many people manage their logs based on the space they are trying to maintain on disk. However that’s not the way you decide when to back up logs. The way you decide how to back up a log has nothing to do with space. It’s best stated in this quote from Gail Shaw: “the log backup interval should be less than the allowable data loss for the system.”

    Simply put, decide how much data you can afford to lose. Or how much loss will exceed your tolerance for being yelled at. That’s your log backup interval. Make a log backup after that much time passes. Schedule that interval into your maintenance operations and observe how big the log backups are. That will determine your log size, not the other way around.

    As with most guidance and advice, this is based on a time and place in the platform’s lifecycle. This is the advice I’ve used from SQL Server 7 to 2012, but it’s subject to change, so make sure it’s still valid if you’re reading this in 2020.

    And, of course, make sure that you also understand how to restore these backups in case you do have an issue. That’s probably the most important thing to know.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and Mevio . feed

    The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.