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.
This gives me code in a new window. In my case, I get this code:
USE [master]
RESTORE DATABASE [sandbox2]
FROM DISK = N'D:SQLBackupsandbox.bak'
WITH FILE = 3,
MOVE N'sandbox' TO N'C:Program FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLDATAsandbox2.mdf',
MOVE N'sandbox_log' TO N'C:Program FilesMicrosoft SQL ServerMSSQL15.MSSQLSERVERMSSQLDATAsandbox2_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:
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:
If I restore file=1, then there is no table.
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.