BACKUP AND RESTORE SQLSERVER MULTI-PART DB

Backup And Restore Multi-Part Databases
Backup And Restore

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

  1. Find location of your backup files
    1. For example your backups are in E:\BACKUP
    2. 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

  1. Open SQL SMSS on the SQL server where you want to do the restore
  2. Use the following commands to check the validity of the copied files
  3. 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

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.

Thoughts & Ideas, Joseph Kravis πŸ™‚



Categories: TSQL

Tags: , , , , ,

Leave a Reply

%d bloggers like this: