• Skip to main content
  • Skip to primary sidebar

DallasDBAs.com

SQL Server Database Consulting

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

Restore

Why Your SQL Server Backups Aren’t Enough

April 9, 2025 by Kevin3NF Leave a Comment

SQL Server backups are database insurance – you don’t really appreciate them until disaster strikes. But here’s the kicker: having backups doesn’t mean your data is safe. If you’re not regularly doing test restores, your backup strategy is a ticking time bomb.

 

The False Sense of Security

Many IT leaders and system admins think, “We have full backups every night. We’re covered.” But when the time comes to restore, they discover:

· The backup file is corrupt.
· The storage location is inaccessible.
· The restore process takes way longer than expected.
· The recovery model wasn’t configured properly.
· The point-in-time restore doesn’t actually bring back the data they need.

At that point, it’s not a “backup strategy.” It’s a data loss incident.

Why Restores Must Be Tested

SQL Server backups are only as good as your ability to restore them. If you’re not proactively testing restores, you’re just hoping they work when disaster strikes. Hope is not a strategy.

• Test different restore scenarios: Full, differential, and transaction log restores.
• Validate integrity with DBCC CHECKDB after restores.
• Ensure RTO and RPO align with business needs. (Translation: Can you restore fast enough to avoid a business meltdown?)
• Automate restore tests so you’re not guessing when the pressure is on.

Automating Restore Tests with dbatools

If you’re not testing restores because it’s “too time-consuming,” dbatools.io has your back. The Test-DbaLastBackup command automates the process by:

1. Restoring the last backup of a database to a test environment.
2. Running DBCC CHECKDB to ensure data integrity.
3. Reporting on success or failure, so you know where issues exist before you actually need the backup.

Example Command:
Test-DbaLastBackup -SqlInstance YourSQLServer -Databases YourDatabase

This takes a lot of manual effort out of the equation—no more excuses for not testing!

Action Plan for SQL Server Resilience

1. Schedule regular test restores (weekly or monthly) and document the process.
2. Use DBCC CHECKDB to verify database integrity post-restore.
3. Leverage Test-DbaLastBackup to automate restore verification.
4. Confirm backup files are accessible and stored securely offsite.
5. Train your team on recovery processes—because backups are useless if no one knows how to restore them.
6. Monitor backup success, but more importantly, track restore success.

The Bottom Line:

A backup plan without restore testing is a disaster waiting to happen. Don’t wait until you’re knee-deep in downtime to realize your backups weren’t as solid as you thought. Test your restores. Validate your data. Sleep better at night.

Thanks for reading!

–Kevin

New Pocket DBA® clients get the first month FREE!

Get a Free SQL Server Health Check!

 

 

Filed Under: backup, HADR, Restore, SQL Tagged With: syndicated

Test Restore Your SQL Server Databases

July 25, 2023 by Kevin3NF Leave a Comment

Pain Point: Something bad happened and you need to restore a SQL Server database.

Pain Point you didn’t know you had: The backup files are all corrupt due a problem with the storage subsystem.

Solution: Do test restores with one line of code from DBATools.io

Set-DbatoolsInsecureConnection -SessionOnly 

Test-DbaLastBackup -SqlInstance Precision7770-1

You will need a modern version of PowerShell and the DBATools module. Also a destination for the test restores to land.

This video is not sponsored by Sean or Jen from MinionWare, they are just really nice people selling a SQL Server maintenance solution for large enterprises 🙂

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs

Filed Under: backup, Dallas DBAs, Restore, SQL Tagged With: DBATools.io, syndicated

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

TIL: Restore-DbaDatabase

April 29, 2020 by SQLDork 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!

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

IRL #7: Test Restores and CheckDB

April 23, 2020 by Kevin3NF 2 Comments

My Pluralsight course for new SQL Server DBAs

Problem:

My client asked me for a way to automate test restores and CheckDB either ad hoc or on a schedule, but not for certain databases that are close to or over 1TB in size.

Background:

My client has a number of SQL Server instances across multiple versions from 2008R2 up to 2016, Standard and Enterprise edition. We recently standardized all of their maintenance (backups, indexes, stats and checkdb) across the board using the Maintenance Solution from Ola Hallengren.

Investigation:

Digging around a bit revealed several choices:

  • Roll my own T-SQL ($$$)
  • Use a 3rd party product ($$)
  • Use DBATools.io (Free)

I was given the OK to spend up to 4 hours for a Proof of Concept on the DBATools option. DBATools is a set of commands that are all built using PowerShell. You don’t need to know PowerShell to use them.

Process:

I have a lab with SQL Server 2016, 2017 and 2019 installed (all Developer edition). Each instance has a few test databases, including WideWorldImporters, StackOverflow and some other local ones used just to test specific ideas.

I am using PSVersion 5.1.x, with the DBATools module installed.

The core command I am using (out of well over 550 available) is Test-DbaLastBackup. I need to start with getting one instance to restore its databases into another, run CheckDB and then drop the DB.

Test-DbaLastBackup -Destination kbh-precision\SQL2019 -SqlInstance  KBH-Precision\SQL2017 `
                   -IgnoreLogBackup -ExcludeDatabase master,model,msdb, StackOverflow

This allowed me to do the minimum, skipping System databases and StackOverflow (due to its size) and work from just the most recent Full backup plus any Differentials. I only have one database that met the criteria, and I have intentionally corrupted it using DBCC WRITEPAGE. DONT DO THIS UNLESS YOU KNOW WHAT YOU ARE DOING!!!

Results:

SourceServer   : KBH-Precision\SQL2017
TestServer     : kbh-precision\SQL2019
Database       : XE_demo
FileExists     : True
Size           : 3.20 MB
RestoreResult  : Success
DbccResult     : Table error: Object ID 565577053, index ID 0, partition ID 72057594041270272, alloc unit ID 72057594046906368 (type In-row data), page (1:560). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. 
                 Values are 133129 and -4.
                 Object ID 565577053, index ID 0, partition ID 72057594041270272, alloc unit ID 72057594046906368 (type In-row data): Page (1:560) could not be processed.  See other errors for details.
                 DBCC results for 'RndNums'.
                 There are 0 rows in 0 pages for object "RndNums".
                 CHECKDB found 0 allocation errors and 2 consistency errors in table 'RndNums' (object ID 565577053).
                 DBCC results for 'sys.plan_persist_query_hints'.
                 There are 0 rows in 0 pages for object "sys.plan_persist_query_hints".
                 DBCC results for 'sys.plan_persist_query_template_parameterization'.
                 There are 0 rows in 0 pages for object "sys.plan_persist_query_template_parameterization".
                 DBCC results for 'sys.plan_persist_wait_stats'.
                 There are 0 rows in 0 pages for object "sys.plan_persist_wait_stats".
                 DBCC results for 'sys.persistent_version_store'.
                 There are 0 rows in 0 pages for object "sys.persistent_version_store".
                 DBCC results for 'sys.persistent_version_store_long_term'.
                 There are 0 rows in 0 pages for object "sys.persistent_version_store_long_term".
                 DBCC results for 'sys.wpr_bucket_table'.
                 There are 0 rows in 0 pages for object "sys.wpr_bucket_table".
                 DBCC results for 'sys.queue_messages_1977058079'.
                 There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
                 DBCC results for 'sys.queue_messages_2009058193'.
                 There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
                 DBCC results for 'sys.queue_messages_2041058307'.
                 There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
                 DBCC results for 'sys.filestream_tombstone_2073058421'.
                 There are 0 rows in 0 pages for object "sys.filestream_tombstone_2073058421".
                 DBCC results for 'sys.syscommittab'.
                 There are 0 rows in 0 pages for object "sys.syscommittab".
                 DBCC results for 'sys.filetable_updates_2105058535'.
                 There are 0 rows in 0 pages for object "sys.filetable_updates_2105058535".
                 CHECKDB found 0 allocation errors and 2 consistency errors in database 'dbatools-testrestore-XE_demo'.
                 repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (dbatools-testrestore-XE_demo).
                 DBCC execution completed. If DBCC printed error messages, contact your system administrator.
RestoreStart   : 2020-04-22 07:36:09.391
RestoreEnd     : 2020-04-22 07:36:10.706
RestoreElapsed : 00:00:01
DbccStart      : 2020-04-22 07:36:10.772
DbccEnd        : 2020-04-22 07:36:12.323
DbccElapsed    : 00:00:01
BackupDates    : {04/16/2020 10:19:19}
BackupFiles    : D:\Backups\KBH-Precision_SQL2017\XE_Demo_20200416.bak

PS C:\WINDOWS\system32>

From there I tested various methods to get the output into something I can look at or, alert on, etc.  Not knowing how deep my client wanted to go at the time, I settled on this:

#Import-Module dbatools

Test-DbaLastBackup -Destination kbh-precision\SQL2019 -SqlInstance  KBH-Precision\SQL2017, KBH-Precision\SQL2016 `
                   -IgnoreLogBackup -ExcludeDatabase master,model,msdb, StackOverflow | `
                    Select-Object -Property SourceServer,Database,RestoreResult,DBCCResult | `
                    Write-SQLTableData -ServerInstance "kbh-precision\SQL2019" -DatabaseName "DBA" -SchemaName "dbo" -Tablename "Test_DBALastBackup" -Force

The last step was to get this into a SQL Agent job (or jobs, depending on the flexibility needed).

I assumed a SQL Agent PowerShell job step would be the best, but my friend Andy Levy(b|t) suggested using this approach.

Results:

With these 4 lines of code and less than 3 hours (ignoring the permissions issues I had), I was able to create a flexible process that can be changed by a simple edit of the .ps1 file.

No doubt there are better ways to implement all of this, error handling, etc. but the ideas got my client what they needed.

“That’s exactly what we wanted”

“How well does it work on Availability Groups?” (Don’t know yet, will test that soon).

Conclusion:

  • DBATools.io has a ton of useful, free, opensource commands…look there before you roll your own T-SQL. Someone else may have already solved your problem.
  • You don’t have to know PowerShell to use these tools, any more than you have to know how fuel injectors work to drive a car.

Other posts in the IRL – In Real Life series:
https://dallasdbas.com/category/sql/irl/

 

The video version:

Thanks for reading!

Kevin3NF

Follow @Dallas_DBAs

Filed Under: IRL, PowerShell, Restore Tagged With: DBATools.io, syndicated

SQL Server Point In Time Restore

October 26, 2017 by Kevin3NF Leave a Comment

20 minute video of me setting up and demonstrating a point-in-time database restore in SQL Server.

If you’ve ever been on a SQL DBA interview, more than likely you’ve been asked about this.   I was recently, and I’ve been a DBA for 18 years…it never goes away.  But, the reason for that is that many DBAs know the concept, but have never actually needed to do one.

Its a basics video, so please don’t be looking for Paul Randal level internals here 🙂

Thanks for watching!

Kevin3NF

 

Filed Under: Accidental DBA, backup, Beginner, Restore

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

Primary Sidebar

Search

Sign up for blogs, DBA availability and more!

Home Blog About Privacy Policy
  • Home-draft
  • Blog
  • About Us

Copyright © 2025 · WordPress · Log in

 

Loading Comments...