Restore One Backup From Many in a Device–#SQLNewBlogger

I wrote recently about finding multiple backups in a file. This post looks at how to restore one of those. The one you choose.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Setup

In the previous post, I did these things:

  • took a backup
  • added a table and data
  • took a second backup
  • truncated the table
  • took a third backup

If I restore the default last backup, I get my table without data. You can read that post to see how I got here.

Let’s restore things.

Restoring a Backup

I cheat with restores. I remember some syntax, but typing it in and trying to remember the order is a pain, even with SQL Prompt. So I click restore database in SSMS and fill out the dialog. I pick the device and when I change the name in the Destination database, the file names change. Once I have the dialog below, I click “Script” at the top.

2023-05-03 10_46_36-Restore Database - sandbox2

This gives me code in a new window. In my case, I get this code:

USE [master]
RESTORE DATABASE [sandbox2]
FROM  DISK = N'D:\SQLBackup\sandbox.bak'
WITH  FILE = 3, 
MOVE N'sandbox' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\sandbox2.mdf', 
MOVE N'sandbox_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\sandbox2_log.ldf', 
NOUNLOAD,  STATS = 5

By default, this gives me file=3, which is the third backup. If I run this and then query the new database, I see this:

2023-05-03 10_48_41-SQLQuery11.sql - ARISTOTLE.sandbox2 (ARISTOTLE_Steve (55))_ - Microsoft SQL Serv

That’s what I expect. The third backup had the table with no data. Let’s restore the second one. First delete the database and then change File=3 to File=2. Once I run the restore and the same query, now I see data:

2023-05-03 10_51_49-SQLQuery11.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (55))_ - Microsoft SQL Serve

If I restore file=1, then there is no table.

2023-05-03 11_04_47-SQLQuery11.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (55))_ - Microsoft SQL Serve

Alter the FILE parameter to pick the backup in the file.

SQL New Blogger

This post took less than the 10 minutes of the previous post. I basically restored my database a few times with a query. The code was a couple minutes to generate and modify in SSMS, and this writeup was short.

The key was doing this immediately after the previous post and reusing the setup and code. Plus, the concept was in my mind.

As with the previous post, this is a good way to show knowledge and learning, and in this case, 20 minutes got me two posts.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

1 Response to Restore One Backup From Many in a Device–#SQLNewBlogger

  1. Pingback: Restoring One Backup from a File – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.