fbpx

DallasDBAs.com

Explaining SQL Server in plain english

SQLDork

TIL: Restore-DbaDatabase

by Leave a Comment

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

If you haven’t read it already, i recently wrote a blogpost where i go over Backup-DbaDatabase, you can read that post here.

Or here, it’s your choice which link you want to click to go there.

Today’s command: Restore-DbaDatabase

Same setup as last time, backups, ISE, SQL Server instances, all that good stuff.

Restore-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016

This kinda works, but asks to specify a bunch of paths if you run it. Best to specify those in the parameters ahead of time, i’d imagine.

 

Restore-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -Path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup'`
-DestinationDataDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationLogDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Log'

This seems to work, but won’t do anything because the databases already exist.
Warning message suggests using -WithReplace to overwrite the existing databases, but i think there’s another option…

 

Restore-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -DatabaseName 'test_restored'`
-Path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\test_202004241133.bak'`
-DestinationDataDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationLogDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Log'

Moved some parameters around for readabilities’ sake.
Warning message implies we need to specify the names for the .mdf and .ldf, which makes sense.

 

Restore-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 -DatabaseName 'test_restored'`
-Path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\test_202004241133.bak'`
-DestinationDataDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationLogDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Log'`
-DestinationFilePrefix 'restored_'

Success! Database name is “test_restored”, and file names are “restored_test.mdf” and “restored_test.ldf”
Let’s dork around with the parameters a bit to get the names to be consistent:

 

Restore-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 `
-Path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\test_202004241133.bak'`
-DestinationDataDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationLogDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationFilePrefix 'restored_' -RestoredDatabaseNamePrefix 'restored_'

Moved the log files to go in the DATA folder, because apparently the Log folder is for different stuff. (Ed: yep…that’s for errorlogs, crash dumps, default trace files, etc.)
No longer specifies the database name via -DatabaseName.
Instead uses -RestoredDatabaseNamePrefix to put ‘restored_’ at the beginning of the name.
This would be useful if we were restoring multiple databases at once.

NOTE: There is a -DestinationFileSuffix, but there is no -RestoredDatabaseNameSuffix parameter. I’ve asked twitter for clarification on this.

There doesn’t seem to be a parameter for renaming the logical names of the
files in Restore-DbaDatabase either, but there is one in Rename-DbaDatabase.

 

Restore-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 `
-Path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\test_202004241133.bak', 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\square[]brackets_202004241133.bak'`
-DestinationDataDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationLogDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationFilePrefix 'restored_' -RestoredDatabaseNamePrefix 'restored_'

Restore multiple databases at once, there’s probably a better way to do this without having to specify the obnoxiously long path both times.

Yes, i have a database named square[]brackets.
Yes, i have gone crazy.
No, you shouldn’t make a database with square brackets in the name.

 

Restore-DbaDatabase -SqlInstance PRECISION-M7520\SQL2016 `
-Path 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Backup\test_202004241133.bak'`
-DestinationDataDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationLogDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA'`
-DestinationFilePrefix 'restored_' -RestoredDatabaseNamePrefix 'restored_' -OutputScriptOnly

Using -OutputScriptOnly dumps a T-SQL script into the command line, which you can edit or run to your heart’s content.

 

Summary

Parameters i found to be useful, some of which i didn’t test
because i’d have to insert lines into the database and i’m lazy™:

-SqlInstance: Required, tells powershell what instance to restore to.
Can use commas to point to multiple instances, like if you wanted a fresh copy of prod data on your 7 dev boxi.

-Path: Tells powershell where the backups to restore are, enclose in single-quotes if there’s a space. (Looking at you, Program Files)
Can point to a folder to restore all the backups in that folder, or to a .bak file to restore just that file.
Use commas to specify multiple paths.

-DestinationDataDirectory: Where to put the physical data files, enclose in single-quotes if there’s a space.
-DestinationLogDirectory: Where to put the physical log files, enclose in single-quotes if there’s a space.

NOTE: All paths need to be specified relative to the instance specified with -SqlInstance.

-DestinationFilePrefix: Adds a string to the beginning of the names of the restored files.
-RestoredDatabaseNamePrefix: Adds a string to the beginning of the restored database name.

-DestinationFileSuffix: Adds a string to the end of the names of the restored files.
-RestoredDatabaseNameSuffix: Does not exist, i wish it did, but it doesn’t.

-OutputScriptOnly: Paranoid? This will give you the script for the thing you just did, just in case.

-NoRecovery: Restores the files with NoRecovery, for if you have more files to restore.
-Recover: Does a With Recovery, nothing fancy.

-RestoreTime: Point in time recovery, stop restoring before the bad thing™ happened.

-WithReplace: Overwrites the existing database if there is one with the same name.

-MaintenanceSolutionBackup: Tell dbatools that you’re using OlaHallengren’s (b) maintenance solution.
Makes things faster since it now knows what to look for, rather than checking every file in the folder.

That’s all for today, if you want to bombard me (b|t) with questions i don’t know how to answer, you can do so on twitter!

TIL: Backup-DbaDatabase

by 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.

 

T-SQL Tuesday 121: Gifts received for this year

by Leave a Comment

T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t) and this month’s edition is hosted by Mala (b|t) who has asked us to write about “Gifts received this year”

Over the last year or so I’ve attended several NTSSUG meetings on various topics, with various forms of food provided (usually pizza). Most of the time there’s more than enough leftovers to justify taking home an entire spare pizza, which I’d call a win.

Ed.: Yes, of course things were learned, people were met, networking happened and friends were made…but hey….free pizza is free pizza!

/*Please work*/,

SQLDork

T-SQL Tuesday: Your Fantasy SQL Feature

by Leave a Comment

T-SQL Tuesday is a monthly blog party for the SQL Server community. It is the brainchild of Adam Machanic (b|t) and this month’s edition is hosted by Kevin Chant (b|t) who has asked us to describe “Your Fantasy SQL Feature“. (This is also the first time I’ve made a blog post for T-SQL Tuesday.)

The Problem: As a junior DBA, part of my job involves checking job failures for 2-3 clients each day, which we have a script in-house for. Every once in a while, a job actually fails! So like any DBA who knows that you can go to the Job Activity Monitor under the SQL Agent to view details on job history, i do exactly that only to find it’s a job that ran an SSIS package and gave me this very informative error message:

That’s super helpful, thanks!

So now i have to:

  • Check the step properties
  • Look at the path for the SSIS package
  • Drill down into the integration services catalogue
  • clickity, clickity
  • Wait which folder was it in?
  • Go check the path again
  • clickity clickity
  • There’s 12 different packages with very similar names:

  • Resize the window or scroll over to see which one i care about
  • Finally, open the All Executions Report to get a very helpful error message:

Gee darnit, i’m out of coffee.

The Solution: Just put a button in the failure message/job history that does all that for you.  Or a hyperlink. Whatever.

Kevin Hill (b|t) has made a script that reads the SSISDB tables, but often takes a while to run with only the existing Microsoft indexes. Especially if you are keeping crazy amounts of history…like the MS default 365 days, on a busy system.

/*Please work*/,

SQLDork

TIL: Security Stuff™

by 3 Comments

Testing Grant and Deny permissions in SQL Server.

Here’s a rough breakdown of the steps we followed:

  • Create a windows group, and create 2 users in the group (We’ll call them test1 and test2).
  • Make a new database called SecurityTest.
  • Add the windows group from earlier as a SQL LOGIN.
  • Add a USER for the login in the SecurityTest database, with no permissions (yet).
  • Create a table and put some data™ in it (We’ll call it SuperSecretTable™).
  • Grant select permission to the USER (Group).
  • Verify that users test1 and test2 can see the data™ (You’ll have to logon to the machine as these users).
  • Test2 takes a screenshot of the SuperSecretTable™ and posts it on Twitter, gets 3 likes.
  • I login as myself and specifically DENY test2 rights to view the table because he cannot be trusted with the data in the SuperSecretTable™ (Or crayons).
  • Test2 logs in again, and finds himself unable to even see the SuperSecretTable™, much less the data™ in it, even though he’s in a group that has been GRANTed SELECT permissions.

In doing this, we’ve proved that DENY takes precedence over GRANT, because we’re cool like that

/*Please work*/,

The Apprentice