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:\SQLBackup\sandbox.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:\SQLBackup\sandbox.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:\SQLBackup\sandbox.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:\sqlbackup\sandbox.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.
