Blog Post

What Backups Are In This File?–#SQLNewBlogger

,

I had a question on multiple backups in a file and had to check my syntax. This post shows how to see which backups are in a file.

Note: Don’t do this. Put backups in separate files.

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

Setup

I have a sandbox database. I made a backup of this.

BACKUP DATABASE [sandbox] TO  DISK = N'D:SQLBackupsandbox.bak' 
   WITH NOFORMAT, INIT,  
   NAME = N'sandbox-Full Database Backup', 
   SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Note I used INIT, which will ensure this is the only backup in this file.

I then changed something, in this case, I made a new table (I was testing things for Rich).

CREATE TABLE testforrich (myid INT)
GO
INSERT dbo.testforrich (myid) 
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
  FROM sys.columns AS c
GO

I then ran another backup. However, this time I wanted to append to the existing file.

BACKUP DATABASE [sandbox] TO  DISK = N'D:SQLBackupsandbox.bak'
  WITH NOFORMAT, NOINIT,  
  NAME = N'sandbox-Full Database Backup', 
  SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

The NOINIT keyword is in here, which appends the backup to the same file. In essence, sandbox.bak will then contain two different backups in one file. For this test, I then made another change and another backup.

TRUNCATE TABLE testforrich
GO
BACKUP DATABASE [sandbox] TO  DISK = N'D:SQLBackupsandbox.bak'
  WITH NOFORMAT, NOINIT,  
  NAME = N'sandbox-Full Database Backup', 
  SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Now I have three backups in the file.

Checking Contents

If I were to click the restore item in SSMS and pick the file, I see this:

2023-05-03 10_15_53-Restore Database - sandbox

Note that the position is listed as “3”, which means this is restoring the newest (most recent) backup by default. I don’t seem to be able to edit this, though if I click timeline and change the time, I can get a different backup. I see different backups in there:

2023-05-03 10_30_16-Backup Timeline_ sandbox

However, when are those backups? This timeline isn’t great.

I can use RESTORE HEADERONLY. The command I ran is:

RESTORE HEADERONLY FROM DISK = 'd:sqlbackupsandbox.bak'
GO

This gives me all three backups, which are shown as different positions in the file.

2023-05-03 10_31_38-SQLQuery7.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (72))_ - Microsoft SQL Server

From here, I could perform a restore with a different backup if I needed to.

SQL New Blogger

This was a quick post that I wrote after I spent 5 minutes creating a test for something. I grabbed my code, took a few screen shots, and it took about 10 minutes to assemble this.

Easy for you, and this shows a potential interviewer or manager that you can dig into a small issue, learn, and solve it. Try it for yourself and write a blog post.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating