• Skip to main content
  • Skip to primary sidebar

DallasDBAs.com

Explaining SQL Server in plain english

  • Services
    • SQL Server Health Check
  • Pocket DBA®
  • Blog
    • Speaking
  • Testimonials
  • Contact
  • About

backup

Restore Database or Restore Log?

May 11, 2022 by Kevin3NF Leave a Comment

How long has this worked, and why didn’t anyone tell me?

A typical restore statement from a FULL backup and a LOG backup:

USE [master]

RESTORE DATABASE [XE_demo] 
FROM  DISK = N'D:\Backups\KBH-Precision_SQL2016\KBH-PRECISION$SQL2016\XE_demo\FULL\KBH-PRECISION$SQL2016_XE_demo_FULL_20220511_092333.bak' 
    WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 25, REPLACE

RESTORE LOG [XE_demo] 
FROM  DISK = N'D:\Backups\KBH-Precision_SQL2016\KBH-PRECISION$SQL2016\XE_demo\LOG\KBH-PRECISION$SQL2016_XE_demo_LOG_20220511_092414.trn' 
    WITH  FILE = 1,  NOUNLOAD,  STATS = 25

GO

Results:

100 percent processed.
Processed 352 pages for database 'XE_demo', file 'XE_demo' on file 1.
Processed 2 pages for database 'XE_demo', file 'XE_demo_log' on file 1.
RESTORE DATABASE successfully processed 354 pages in 0.016 seconds (172.393 MB/sec).
30 percent processed.
60 percent processed.
90 percent processed.
100 percent processed.
Processed 0 pages for database 'XE_demo', file 'XE_demo' on file 1.
Processed 27 pages for database 'XE_demo', file 'XE_demo_log' on file 1.
RESTORE LOG successfully processed 27 pages in 0.011 seconds (18.909 MB/sec).

Completion time: 2022-05-11T09:26:44.3023396-05:00

Change the Restore Log to Restore Database:

USE [master]

RESTORE DATABASE [XE_demo] 
FROM  DISK = N'D:\Backups\KBH-Precision_SQL2016\KBH-PRECISION$SQL2016\XE_demo\FULL\KBH-PRECISION$SQL2016_XE_demo_FULL_20220511_092333.bak' 
    WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 25, REPLACE

RESTORE DATABASE [XE_demo] 
FROM  DISK = N'D:\Backups\KBH-Precision_SQL2016\KBH-PRECISION$SQL2016\XE_demo\LOG\KBH-PRECISION$SQL2016_XE_demo_LOG_20220511_092414.trn' 
    WITH  FILE = 1,  NOUNLOAD,  STATS = 25

GO

Results:

100 percent processed.
Processed 352 pages for database 'XE_demo', file 'XE_demo' on file 1.
Processed 2 pages for database 'XE_demo', file 'XE_demo_log' on file 1.
RESTORE DATABASE successfully processed 354 pages in 0.015 seconds (183.886 MB/sec).
30 percent processed.
60 percent processed.
90 percent processed.
100 percent processed.
Processed 0 pages for database 'XE_demo', file 'XE_demo' on file 1.
Processed 27 pages for database 'XE_demo', file 'XE_demo_log' on file 1.
RESTORE LOG successfully processed 27 pages in 0.006 seconds (34.667 MB/sec).

Completion time: 2022-05-11T09:30:42.0225244-05:00

I had no idea the engine would determine the type of backup file and apply it appropriately. I knew it worked for Differential backups, but not Log.

20+ years and I still find new (to me) things every day.  Technically Jon (t) found it but hey…we’re a team here 🙂

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs

Filed Under: backup, Restore, TSQL Tagged With: Restore, syndicated

Ola log backup job failure

September 28, 2021 by Kevin3NF Leave a Comment

I had a log backup job that was not failing, and was also not backing up logs:

EXECUTE [DBA].[dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES. model',
@Directory = 'D:\Backups\KBH-Precision_SQL2016',
@BackupType = 'LOG',
@Verify = 'Y',
@CleanupTime = NULL,
@CheckSum = 'Y',
@LogToTable = 'Y'

Older version of Ola. Newer versions give this:

Date and time: 2021-09-28 14:56:12
Server: KBH-PRECISION\SQL2016
Version: 13.0.5026.0
Edition: Developer Edition (64-bit)
Platform: Windows
Procedure: [DBA].[dbo].[DatabaseBackup]
Parameters: Removed for cleanliness
Version: 2020-12-31 18:58:56
Source: https://ola.hallengren.com
The following databases in the @Databases parameter do not exist: [USER_DATABASES. model].
Date and time: 2021-09-28 14:56:12
Completion time: 2021-09-28T14:56:12.6474426-05:00

 

Watch those typos, kids!

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs

Filed Under: backup Tagged With: ola

TIL: Backup-DbaDatabase

April 27, 2020 by SQLDork Leave a Comment

(This post comes from one of our Junior DBAs – @SQLDork)

I’m learning how to use dbatools.

Today’s command: Backup-DbaDatabase

I’m using the 64-bit PowerShell ISE (running as administrator), running PowerShell version 5.1 on Windows 10, with a local SQL 2016 install.

Here’s the code, each snippet adding additional parameters and building off the one before it, mostly, with a bunch of 100% hilarious comments.*

*Comments may or may not be 100% hilarious, on account of hilarity not being a measurable substance.**
**Probably.

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test

This one works, yaaaay! All it does is take a full backup of one database. We can do better, probably.

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type Diff

This takes a differential backup, but calling your diffs .bak is bad™

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type Differential -FilePath test2.dff

-FilePath lets us set the extension, but in doing so, we lose timestamps. I wonder if there’s a way to fix that… (Spoiler alert: there totally is)

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type Differential -FilePath test2.dff -CompressBackup

Also figured out how to compress backups, but it doesn’t work if you try to take a compressed diff after an uncompressed full.

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type full -FilePath test3.bak -CompressBackup
Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type diff -FilePath test3.dff -CompressBackup

This one worked, because science!™ Also backup chains, there’s probably a blog post around here somewhere explaining those, you should go find it and send it to all your friends!

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type full -FilePath test3.bak -CompressBackup -initialize

Using -initialize makes it overwrite the existing copy, nothing too exciting.

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016

Bare minimum required to run without errors, takes full backups of every system and user database on the box.
Except for tempdb for some reason, idk why*
* I totally know why

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type diff `
-FilePath test3_timestamp.dff -CompressBackup -ReplaceInName

Using -ReplaceInName lets us use timestamps with reasonable file extensions
Welcome to people-not-yelling-at-you-because-they-can’t-tell-if-the-important-backup-file-is-a-full-or-a-log town!
Population: Us

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test, model

Use commas to take backups of multiple databases at once.

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -confirm

Anything that’s a checkbox in the Command Add-On doesn’t need any fancy parameters, it just works™.
If you’re using the PS ISE. If you’re using notepad, stop it.

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test -Type full `
-FilePath test3_timestamp.bak -ReplaceInName -buildpath -createfolder `
-path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup'

-Path tells the files where to go
-FilePath sets the name/extension of the file
-BuildPath tells PowerShell to make the path if it doesn’t exist
-CreateFolder sends all the files into nice little subfolders for the humans to look at and go “Hey look, subfolders!”.

And then they go drink coffee and complain about the database being “slow”.

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Type full `
-FilePath dbname_timestamp.bak -ReplaceInName -ExcludeDatabase test -buildpath `
-path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\donuts'

-ExcludeDatabase has a very non-intuitive and confusing function: it excludes databases from being backed up.
Useful for the really big ones you only want to backup once a week or something.

 

Backup-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Database test `
-FilePath dbname_timestamp.bak -ReplaceInName -FileCount 2

This makes multiple striped files, appending numbers automatically, as so:
[filename]-1-of-2.bak
[filename]-2-of-2.bak
[filename]-donuts-of-2.bak
Actually that last one was a joke, but that’s all I got for today, and probably the next month or so.

 

Follow me (b|t) on twitter, where I also probably won’t say anything for the next month or so.

 

Filed Under: backup, PowerShell, TIL Tagged With: DBATools.io, syndicated

Your DR Plan isn’t a Plan

August 8, 2018 by Kevin3NF 9 Comments

 

My apologies, but someone had to tell you.

You don’t have a Disaster Recovery Plan™. You have a Disaster Recovery Hope. (paraphrased from a source I’ve forgotten…)

If I’m wrong (and I hope I am), its because you are in the 10% (optimistically) of companies that actually test their DR plans and document the results.

In the 18 years I’ve been asking the following question, I’ve gotten one excellent and correct response, and one that was good, but I still poked holes in it.

“If your most important database server melted right now, how long until you are back online?”

I get a lot of responses on this question:

  • 2 days
  • 30 minutes
  • I don’t know
  • Blank Stares (its a response…just a scary one)

If you are not testing your DR plan, you may be betting your company’s existence on a flawed premise: “Our people know what to do. We have backups.”

If your DR plan is a digital “living document” that lives on your Sharepoint server, you are already in trouble.  Everyone involved needs a current, non-digital copy on their desk and at their home.

Disaster recovery is about far more than restoring databases.  But, for my mostly-SQL Server audience, let me ask you this:

Can you do an emergency test restore of your most mission critical database onto identical hardware, to a point 27.5 minutes ago and have everything work as expected?

If you don’t do test restores, you are HOPING your backups are valid. If you don’t have the ability to replace critical hardware or spin up an identical VM, you are HOPING that your server or SAN never fails.

If you are running through your DR Plan regularly, with everyone ready to go and knowing their place in the process…you are HOPING the next disaster happens when everyone is available, online and up to speed.   What if that disaster happens when your Sr. DBA is on vacation?  Who is her backup? A Jr. DBA?

If you don’t know the answer to the “How long…” question above, you really should go find out who in your organization does.  If nobody does, contact me.

If you think you have it 100% nailed…I challenge you to let me into your server room unattended.

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs

Filed Under: backup, HADR

Installing the ‘Ola Scripts’…quick and easy database maintenance

December 12, 2017 by Kevin3NF 1 Comment

I was recently in a conversation about the best way to go about setting up maintenance (Backups, Integrity Checks, Indexes and stats) for a group of SQL Servers, with minimal hassle, and easy to deploy to new servers.

The factors that came into play on this were:

  • Supportability
  • Cost
  • Ease of use

We discussed the following different options (I was not talking to a DBA, but a SQL Developer):

  • SQL Server Maintenance Plans
  • Ola Hallengren’s scripts
  • 3rd party products from Red Gate, Minion, etc.
  • Custom scripts

For this customer, in this environment, I decided to recommend Ola’s scripts.  The primary drivers were ease of installation and the amazing free support from the hundreds (thousands?) of DBAs that know and love them.  Myself included.

But I still have to prove my point to this client no matter what I recommend…so I made this video.

Enjoy:

Skip to 5:00 if you already have SQL Server installed…that first bit is just to show this on a clean instance 🙂

If you have any questions, feel free to comment on the video, or if you need specific help hit up #sqlhelp on Twitter.

Thanks for reading and watching!

Kevin3NF

Filed Under: backup, backup, Configuration, Deployment

Database Delete vs. Detach vs. Offline

May 31, 2017 by Kevin3NF Leave a Comment

If you haven’t been asked to Delete a database in your DBA career…its just a matter of time:

“We don’t need database XYZ anymore…delete it.” — Some well meaning manager

Stop right now, before blindly following an order like that.

First…there are some questions to ask:

  • What environment is this? (Prod, Dev, etc.)
  • Are you sure we don’t need it?
  • Has anyone verified that?
  • When was it last used?
  • Did something replace it?
  • Is it backed up?

As a DBA, any time anyone asks you to delete anything, from data to an entire database, you need to question it.  Not doing so can be a CLM/RGE combo!  (Career Limiting Move/Resumé Generating Event)

The request may be perfectly valid, but in my experience most people that ask for a database to be deleted use the term to mean “I don’t want to see that database in SSMS anymore” or “I don’t want it on Server A, put it on Server B”, with very little regard for impact to other teams or applications.

There are three main ways to take a database “out of service”:

  • Take it Offline
  • Detach it
  • Delete it

Each has its own considerations and each is the proper choice in different circumstances.  I am going to walk you through each one, from least risky to most, in that order.

Offline:

Takes the database offline, but still visible in SSMS.  Cannot be accessed by applications, backups, etc.   Can be brought back online relatively quickly.

My list of databases before starting this post…we’ll be messing with the Rename2222 database (nothing in it…came from a rename script I was writing/testing):

Right-click the database in question, Tasks, Offline:

 

Dialog Box you get after clicking “Take Offline”:

Note 1: You do not get the below dialog in SSMS 2014 (and possibly earlier)…this was done using SSMS 2016.
Note 2:  If you click the Script button, it will take the database offline in SSMS2016, instead of generating a script.  I’m told this is resolved in SSMS 2017.

 

Results in SSMS after Offline Operation:

Upside to Offline method: Database is still in place,  visible in SSMS, Record in master database is still there, relatively simple.

Downside to Offline: May cause SQL Monitoring tools to throw alerts and create tickets, files are not in use by SQL Server, can be deleted.

Detach:

Removes the database from SSMS, deletes its meta information in the master database, but leaves the physical files intact on the drive.

The detach menu:

The detach dialog:

Choose “drop connections” here…if not, the detach may fail.  Or, leave it unchecked as a test to see if anyone is using it

After Detaching:

Note that the MDF/LDF files are still right where they were.  SQL Server no longer has a handle on them:

You can re-attach fairly quickly should the need arise (i.e. user comes screaming at you 5 minutes after the detach):

Right-click “Databases”, select Attach

 

Click ‘Add’ and browse for the .MDF file….should find and include the .LDF file on its own

 

Click ‘OK’ to attach

Upside to Detach method: Files are still in place, re-attach is fairly straightforward, Monitoring tools will generally not error. Can save your job by not having deleted a database someone else needed.

Downside to Detach: Files not manually deleted take up disk space…set reminders to delete after some time period.

Delete:

Drops the database, its meta data in Master, AND its associated physical files…you cannot reverse a Delete (DROP Database).

This is one of those “Stop right here” moments.  Before you Delete a database, make very sure you are following protocols.   Make sure you have a recent FULL backup:

 

If not, run one.  The simplest possible command to backup a database:

 -- backs up to default location

Backup Database Rename2222 
To Disk = 'Rename2222.bak'

Better:

-- backs up to specified location 
-- with date included in the file name:

Backup Database Rename2222 
To Disk = 'c:\MyBackups\Rename2222_20170531.bak' 

Once you have a backup, you also should get a ticket, change control, or at the very minimum an email from the requester with approval for the delete.  Depends on your process.   Especially important if this is a production environment.   This can save your job.  If you Delete a database that a system depends on and cause an outage…whoever told you to do may claim they were not involved.

To delete is way too simple:

Deleting backup/restore history is up to you.  Drop all connections to force everyone out.  
If anyone is connected, should you be deleting?

Remember…once you Delete a database…its gone.   The ONLY ‘undo’ is to restore from Backup.   You can call Microsoft, but they will tell you the same thing.  Worst case, you can spend tens of thousands of dollars on a data recovery service, assuming you can take the server down and nothing overwrote the space the the files were in on the drives.

Wrap up:

Almost everywhere I’ve worked, Detach with a 2-4 week follow up to delete the files was the best choice.   But beware that some systems may only be useful for Quarterly or Annual reporting.  In an ideal situation, you have a complete inventory of every database on every server from Production down to Dev and know exactly why they are there, as well as who the stakeholders are for each one.  If you don’t have this for the O/S, SQL and DB level info, start now.

Thanks for reading!

Kevin3NF

My Pluralsight course for new DBAs

Follow @Dallas_DBAs

Filed Under: Accidental DBA, backup, Beginner, EntryLevel

  • Go to page 1
  • Go to page 2
  • Go to Next Page »

Primary Sidebar

Search

Sign up for blogs, DBA availability and more!


Copyright © 2023 · Genesis Sample on Genesis Framework · WordPress · Log in

Privacy Policy
 

Loading Comments...