BACKUP AND RESTORE :- I know most of you already know this but as I go thru my older notes, I thought I’d start listing a few ideas that can be used as a reference when with working with multi-part backup files.
MULTI-PART backup files are actually pretty simple to work with and this is just a quick refresher of sta–tement construction. Once your statement is constructed it will be easy for you to automate the process.
FIND BACKUP LOCATIONS
- Find location of your backup files
- For example your backups are in E:\BACKUP
- remove the comment “–” to run for a single database
TSQL TO DISPLAY BACKUPS
SELECT physical_device_name
,backup_start_date
,backup_finish_date
,backup_size/1024.0 AS BackupSizeKB
,backup_size/1024.0/1024.0 AS BackupSizeMB
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
--WHERE database_name = 'YOUR_DATABASE_NAME'
ORDER BY backup_finish_date DESC
IF Required Can Copy To Destination
- On occasion you will notice files are broken down into multiple PARTS for a backup and that will contain files
- DATABASENAME_PART01.bak, DATABASENAME_PART02.bak ETC. You will need to grab all parts of a backup in order to restore to various environments.
- Use ROBOCOPY to get copy the files to the deployment folder on destination server for restoring files.
- ROBOCOPY “\SOURCE_SERVER\DRIVE$\FOLDER” “\DEST_SERVER\DRIVE$\FOLDER” PART.BAK
- If not copying or moving databases you can still verify multi-part files and restore. Hopefully you’ve automated this process.
IMPLEMENTATION
- Open SQL SMSS on the SQL server where you want to do the restore
- Use the following commands to check the validity of the copied files
- Or all this can be automated with PowerShell and a few quick commands.
VERIFY Quality Of Backup Files
The RESTORE VERIFYONLY TSQL command has been enhanced
Restore Verifyonly from
Disk='\SERVERNAME\DATABASENAME_PART011.bak' ,
Disk='\SERVERNAME\DATABASENAME_PART010.bak' ,
Disk='\SERVERNAME\DATABASENAME_PART09.bak' ,
Disk='\SERVERNAME\DATABASENAME_PART08.bak' ,
Disk='\SERVERNAME\DATABASENAME_PART07.bak' ,
Disk='\SERVERNAME\DATABASENAME_PART05.bak' ,
Disk='\SERVERNAME\DATABASENAME_PART04.bak' ,
Disk='\SERVERNAME\DATABASENAME_PART03.bak' ,
Disk='\SERVERNAME\DATABASENAME_PART02.bak' ,
Disk='\SERVERNAME\DATABASENAME_PART01.bak'
With Move 'YOURDATABASE'
to 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\YOURDATABASE.mdf' ,
Move 'YOURDATABASE_log' to 'F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\YOURDATABASE_log.ldf' ,
RECOVERY,Stats=5
BACKUP AND RESTORE DATABASE MULTI-PART
A simple method for restoring a multi-part database read the syntax below.
Restore Database YOURDATABASE
from
Disk='\SERVERNAME\PATH\PART011.bak' ,
Disk='\SERVERNAME\PATH\PART010.bak' ,
Disk='\SERVERNAME\PATH\PART09.bak' ,
Disk='\SERVERNAME\PATH\PART08.bak' ,
Disk='\SERVERNAME\PATH\PART07.bak' ,
Disk='\SERVERNAME\PATH\PART05.bak' ,
Disk='\SERVERNAME\PATH\PART04.bak' ,
Disk='\SERVERNAME\PATH\PART03.bak' ,
Disk='\SERVERNAME\PATH\PART02.bak' ,
Disk='\SERVERNAME\PATH\PART01.bak'
With Move 'YOURDATABASE' to 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\YOURDATABASE.mdf' ,
Move 'YOURDATABASE_log' to 'F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\YOURDATABASE_log.ldf' ,
RECOVERY,Stats=5:
As I mentioned, I’m in the process of getting many of my notes for my refresher posted for me and others to use. Many notes are on onedrive and google drive. I should figure out a way to automate post here from that content!
If these types of methods have changed or are not used since I put these together please let me know. And, if there are some newer methods for working with multi-part SQLSERVER files please let me know.
Thank you!
Thank you for visiting this post! Your time and interest are truly appreciated. If you found the content engaging or thought-provoking, please feel free to share your thoughts or insights in the comments.
Thoughts & Ideas, Joseph Kravis 🙂
Categories: TSQL
Leave a Reply