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:

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:

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.

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.