Tag Archives: Backup

Use MSDB to Get Database Backup Size and Total Time For Each

— by Lori Brown

We recently started using a third party software to do our in-house SQL backups so that the backup files are stored in a redundant and safe place. To confirm that the software was indeed compressing backups as it stated it would, we wanted to see what each backup size actually was in SQL so that we could compare that to what the software was telling us.

SQL stores lots of handy backup information in msdb in the backupset and backupmediafamily tables.

https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/backupset-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/backupmediafamily-transact-sql

Here is my query. I am only wanting the information from the last 24 hours so have filtered the start date by subtracting 1 day from today. I have also provided some commented out options in case someone needs them.

— database backup size and how long it took to do backup

SELECT bs.database_name AS DatabaseName

, CAST(bs.backup_size/1024.0/1024/1024 AS DECIMAL(10, 2)) AS BackupSizeGB

, CAST(bs.backup_size/1024.0/1024 AS DECIMAL(10, 2)) AS BackupSizeMB

–, CAST(bs.compressed_backup_size/1024.0/1024/1024 AS DECIMAL(10, 2)) AS CompressedSizeGB   

       –, CAST(bs.compressed_backup_size/1024.0/1024 AS DECIMAL(10, 2)) AS CompressedSizeMB

, bs.backup_start_date AS BackupStartDate

, bs.backup_finish_date AS BackupEndDate

, CAST(bs.backup_finish_date – bs.backup_start_date AS TIME) AS AmtTimeToBkup

, bmf.physical_device_name AS BackupDeviceName

FROM msdb.dbo.backupset bs JOIN msdb.dbo.backupmediafamily bmf

ON bs.media_set_id = bmf.media_set_id

WHERE

–bs.database_name = ‘MyDatabase’ and   — uncomment to filter by database name

bs.backup_start_date > DATEADD(dd, -1, GETDATE()) and

bs.type = ‘D’ — change to L for transaction logs

ORDER BY bs.database_name, bs.backup_start_date

And, here is the output.

Blog_20170726_1

It turned out that the software was indeed compressing all backups so that was a good thing.

There is a lot more info that can be pulled from msdb regarding backups. Have some fun and experiment with getting information that you need from there. Here are some links to some other backup related topics that we have blogged about already.

https://blog.sqlrx.com/2017/03/02/sql-server-backup-and-restore-primer/

https://blog.sqlrx.com/2013/04/23/backup-database-commands/

https://blog.sqlrx.com/2013/05/28/be-prepared-for-a-crisis/

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

 

SQL Server Backup and Restore Primer

— by Lori Brown @SQLSupahStah

Back it up!!

blog_20170302_1  …. BEEP! BEEP! BEEP! … Oh wait! Not that kind of backup…

Since I spent a lot of time trying to creatively move a database from one server to another last night, I thought that a review of ways to backup and restore would be a good topic for today.

I had someone who needed me to move a database from one server to another in an effort to consolidate servers. The database backup file was a significant size and could not fit onto any of the local drives of the new server. And to make things more fun, the SQL Server service account did not have permission to view the old server at all. I think the inability to see the old server had something to do with the old server had been in a different domain at one point and there was some issue that was never fixed that could give the service account access. Nonetheless, the move had to be completed. So, this made it so that restoring would have to be performed using a location that the SQL Server service account could access. I could not use a UNC path for restoring the database.

The new server was really tight on space and the admins were very reluctant to add more space if that space was not needed permanently. I had several drives with differing amounts of free space so if I could create enough small backup files, I could put those files in several different places on the new server and could then restore since the SQL Server service account did have access to all local drives.

You can backup databases to one file or multiple files. Here are the basics of backing up to a single file. Since I am giving the T-SQL way to do things, I am also including a way to make the file name unique with a timestamp.

— Backup a database to a single backup file

DECLARE @BackupFile NVARCHAR(200)

DECLARE @Timestamp VARCHAR(50)

— create a timestamp for a unique name

SET @Timestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(), 20),‘-‘,),‘:’,),‘ ‘,)

— create backup file string

SET @BackupFile = ‘D:\SQLBackups\WideWorldImporters_Full_’+@Timestamp+‘.bak’

— backup the database

BACKUP DATABASE WideWorldImporters

TO DISK = @BackupFile

WITH NOFORMAT, INIT, NAME = N’WideWorldImporters-Full Database Backup’,

SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10;

GO

Backing up to multiple files is good to use for large databases and will break up a single large backup file to several smaller files. This is what I had to do last night when a large single database file would not fit on any local drive.

— Backup a database to multiple backup files

DECLARE @BackupFile1 NVARCHAR(200)

DECLARE @BackupFile2 NVARCHAR(200)

DECLARE @BackupFile3 NVARCHAR(200)

DECLARE @BackupFile4 NVARCHAR(200)

DECLARE @Timestamp VARCHAR(50)

— create a timestamp for a unique name

SET @Timestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(), 20),‘-‘,),‘:’,),‘ ‘,)

— create backup file strings

SET @BackupFile1 = ‘D:\SQLBackups\WideWorldImporters_Full1_’+@Timestamp+‘.bak’

SET @BackupFile2 = ‘D:\SQLBackups\WideWorldImporters_Full2_’+@Timestamp+‘.bak’

SET @BackupFile3 = ‘D:\SQLBackups\WideWorldImporters_Full3_’+@Timestamp+‘.bak’

SET @BackupFile4 = ‘D:\SQLBackups\WideWorldImporters_Full4_’+@Timestamp+‘.bak’

— backup the database

BACKUP DATABASE WideWorldImporters

TO DISK = @BackupFile1,

DISK = @BackupFile2,

DISK = @BackupFile3,

DISK = @BackupFile4

WITH NOFORMAT, INIT, NAME = N’WideWorldImporters-Full Database Backup’,

             SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10;

GO

Here is the MSDN link for all things backup…. https://msdn.microsoft.com/en-us/library/ms186865.aspx

Some backup options that are good to know about are BLOCKSIZE, BUFFERCOUNT and MAXTRANSFERSIZE. These can help speed up backups immensely but should be thoroughly tested. I recently had a 1.8 TB database that without setting those backup options, caused the database backup to take around 4 hours. With them set, the backup time was reduced to 50 minutes.

Restore it!!

blog_20170302_2  … Reunited and it feels so goooood… Yeah, no one wants to hear me sing.  Somehow I never win at karaoke.

Practicing restoring databases proves that the backup files are usable and will polish up your skills so that you are ready to restore in the event of a crisis. If you have to restore database files to different drives than are available on the production server or if you have to restore often as in the case of refreshing a database, create a script that will be easier to use than restoring using the GUI and navigating to the new file locations. Don’t forget that databases from newer version of SQL cannot be restored to an older version.

Here are some basic examples of restore statements.

— Restore a database from a single backup file

USE master

GO

RESTORE DATABASE WideWorldImporters

FROM DISK = N’D:\SQLBackups\WideWorldImporters_Full.bak’

WITH RECOVERY

GO

 

— Restore a database from a single database backup file

— and a transaction log backup file

USE master

GO

RESTORE DATABASE WideWorldImporters

FROM DISK = N’D:\SQLBackups\WideWorldImporters_Full.bak’

WITH NORECOVERY — NORECOVERY allows more backup files to be restored

GO

RESTORE LOG WideWorldImporters

FROM DISK = N’D:\SQLBackups\WideWorldImporters_tlog.trn’

WITH RECOVERY

GO

In my case, since I had backed up the database to multiple files, I moved those files to several different local drives and was able to restore from there. I don’t like it when space is that tight but some shops run a very tight ship and it is just something I have to live with.

— Restore a database from multiple backup files

USE master

GO

RESTORE DATABASE WideWorldImporters

FROM DISK = N’D:\SQLBackups\WideWorldImporters_Full_1.bak’,

DISK = N’D:\SQLBackups\WideWorldImporters_Full_2.bak’,

DISK = N’D:\SQLBackups\WideWorldImporters_Full_3.bak’,

DISK = N’D:\SQLBackups\WideWorldImporters_Full_4.bak’

WITH REPLACE, — REPLACE WILL OVERWRITE EXISTING FILES

RECOVERY

GO

 

— Restore a database from multiple backup files

— and move the database and log files to new drives

USE master

GO

RESTORE DATABASE WideWorldImporters

FROM DISK = N’D:\SQLBackups\WideWorldImporters_Full_1.bak’,

DISK = N’D:\SQLBackups\WideWorldImporters_Full_2.bak’,

DISK = N’D:\SQLBackups\WideWorldImporters_Full_3.bak’,

DISK = N’D:\SQLBackups\WideWorldImporters_Full_4.bak’

WITH MOVE N’WideWorldImporters’ TO N’H:\MSSQL\WideWorldImporters.mdf’,

MOVE N’WideWorldImporters_log’ TO N’L:\MSSQL\WideWorldImporters_log.ldf’,

RECOVERY

GO

Restore WITH RECOVERY to make the database ready to use. Restore WITH NORECOVERY if you want to restore more backups.   Use REPLACE only when you are certain that you want to overwrite the existing files so be careful with it.

More on RESTORE can be found at https://msdn.microsoft.com/en-us/library/ms186858.aspx

Some good things to know are available in the RESTORE are the ability to restore pages, verifying backups, NORECOVERY and STANDBY, and STOPAT. Some of those options are specific when restoring transaction logs but are good to know anyway.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!