TSQL: Restore a multiple file backup

Just a follow up post using the backup files created in TSQL: Backup a database to multiple files. Here’s the script I used…

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
 
-- Single file restore
RESTORE DATABASE dbname
FROM DISK = '\\path\to\backup\location\tmp\single_file_backup.bak'
WITH RECOVERY,
MOVE 'dbname' TO 'd:\SQLData\dbname.mdf',
MOVE 'dbname_log' TO	'E:\SQLLogs\dbname_2.LDF',
MOVE 'ftrow_ForumTextSearch' TO	'E:\ForumTextSearch\dbname_2\tmp_ftrow_ForumTextSearch.ndf';
GO
 
DROP DATABASE dbname;
GO
 
-- Two file restore
RESTORE DATABASE dbname
FROM DISK = '\\path\to\backup\location\tmp\two_files_file1.bak',
DISK = '\\path\to\backup\location\tmp\two_files_file2.bak'
WITH RECOVERY,
MOVE 'dbname' TO 'd:\SQLData\dbname.mdf',
MOVE 'dbname_log' TO	'E:\SQLLogs\dbname_2.LDF',
MOVE 'ftrow_ForumTextSearch' TO	'E:\ForumTextSearch\dbname_2\tmp_ftrow_ForumTextSearch.ndf';
GO
 
DROP DATABASE dbname;
GO
 
-- Five file restore
RESTORE DATABASE dbname
FROM DISK = '\\path\to\backup\location\tmp\five_files_file1.bak',
DISK = '\\path\to\backup\location\tmp\five_files_file2.bak',
DISK = '\\path\to\backup\location\tmp\five_files_file3.bak',
DISK = '\\path\to\backup\location\tmp\five_files_file4.bak',
DISK = '\\path\to\backup\location\tmp\five_files_file5.bak'
WITH RECOVERY,
MOVE 'dbname' TO 'd:\SQLData\dbname.mdf',
MOVE 'dbname_log' TO	'E:\SQLLogs\dbname_2.LDF',
MOVE 'ftrow_ForumTextSearch' TO	'E:\ForumTextSearch\dbname_2\tmp_ftrow_ForumTextSearch.ndf';
GO
 
DROP DATABASE dbname;
GO
 
-- Ten file restore
RESTORE DATABASE dbname
FROM DISK = '\\path\to\backup\location\tmp\ten_files_file1.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file2.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file3.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file4.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file5.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file6.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file7.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file8.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file9.bak',
DISK = '\\path\to\backup\location\tmp\ten_files_file10.bak'
WITH RECOVERY,
MOVE 'dbname' TO 'd:\SQLData\dbname.mdf',
MOVE 'dbname_log' TO	'E:\SQLLogs\dbname_2.LDF',
MOVE 'ftrow_ForumTextSearch' TO	'E:\ForumTextSearch\dbname_2\tmp_ftrow_ForumTextSearch.ndf';
GO
 
DROP DATABASE dbname;
GO
 
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

The results are less than exciting…

Files 1 2 5 10
CPU time (ms) 39577 37595 35162 32790
Elapsed time (ms) 1478788 1443965 1450444 1454009

I’ve sexed up my results a bit on these graphs by leaving the restore times in ms…

tsql multiple backup files sql server1 TSQL: Restore a multiple file backup

 

Have a more sensible scale and the truth is less sexy (minutes instead of ms)…

tsql restore multiple files minutes1 TSQL: Restore a multiple file backup

Now I’m aware this isn’t the best of tests. I had a quick glance through the documentation and I couldn’t see any indication that the RESTORE command was multi-threaded. I keep an eye on sys.dm_exec_requests and this didn’t indicate this either. I guess a better test might be to see if having multiple data files has any significant effect. Fill your boots if you fancy having a go!

 

 

 

 


Copy date stamped backups with a regex & scp

Lets assume you have a directory of date stamped backups you want to scp to another location…

backup_20141003.tar.gz
backup_20141004.tar.gz
backup_20141005.tar.gz
backup_20141006.tar.gz
backup_20141007.tar.gz
backup_20141008.tar.gz
backup_20141009.tar.gz

scp can accept a regex like below to do this as a one-liner. Note the quotes are required for the expression to work.

scp "user@hostname:/data/backup/backup_2014100[3-9].tar.gz" /path/to/location/

You should see something like this output…

backup_20141003.tar.gz                                                                                                                            100%   22GB  55.5MB/s   06:43    
backup_20141004.tar.gz                                                                                                                            100%   22GB  12.0MB/s   31:13    
backup_20141005.tar.gz                                                                                                                            100%   22GB  12.3MB/s   30:41    
backup_20141006.tar.gz                                                                                                                            100%   22GB   7.5MB/s   49:52    
backup_20141007.tar.gz                                                                                                                             77%   17GB  38.7MB/s   02:12 ETA

TSQL: Backup a database to multiple files

I wanted to see how much I could reduce backup times by specifying multiple files in the BACKUP TSQL command. Here’s a script I wrote to do this and I present a summary of the results below. The times are based on a database that produced a backup file(s) of approximately 51GB. You mileages will vary here based on a whole bunch of factors. Therefore consider these results illustrative and do your own testing.

SET STATISTICS TIME ON;
SET STATISTICS IO ON;
 
-- Single file
BACKUP DATABASE dbname
TO DISK = '\\path\to\backup\location\single_file_backup.bak';
GO
 
-- Two files
BACKUP DATABASE dbname
TO DISK = '\\path\to\backup\location\two_files_file1.bak',
DISK = '\\path\to\backup\location\two_files_file2.bak'
GO
 
-- Five files
BACKUP DATABASE dbname
TO DISK = '\\path\to\backup\location\five_files_file1.bak',
DISK = '\\path\to\backup\location\five_files_file2.bak',
DISK = '\\path\to\backup\location\five_files_file3.bak',
DISK = '\\path\to\backup\location\five_files_file4.bak',
DISK = '\\path\to\backup\location\five_files_file5.bak'
GO
 
-- Ten files
BACKUP DATABASE dbname
TO DISK = '\\path\to\backup\location\ten_files_file1.bak',
DISK = '\\path\to\backup\location\ten_files_file2.bak',
DISK = '\\path\to\backup\location\ten_files_file3.bak',
DISK = '\\path\to\backup\location\ten_files_file4.bak',
DISK = '\\path\to\backup\location\ten_files_file5.bak',
DISK = '\\path\to\backup\location\ten_files_file6.bak',
DISK = '\\path\to\backup\location\ten_files_file7.bak',
DISK = '\\path\to\backup\location\ten_files_file8.bak',
DISK = '\\path\to\backup\location\ten_files_file9.bak',
DISK = '\\path\to\backup\location\ten_files_file10.bak'
GO
 
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

Results are as follows…

Files 1 2 5 10
CPU time (ms) 16878 13527 16159 21745
Elapsed time (ms) 1983023 1545460 1372302 1237149

tsql multiple backup files sql server TSQL: Backup a database to multiple files


Check a html page with check_http

With the check_http Nagios plugin we can check that a url returns an OK status code as well as verifying the page contains a certain string of text. The usage format is a s follows…

/usr/local/nagios/libexec/check_http -H hostname  -r search_string

For example…

/usr/local/nagios/libexec/check_http -H www.youdidwhatwithtsql.com -r "wordpress"

If you want to make the check case-insensitive then change to…

/usr/local/nagios/libexec/check_http -H www.youdidwhatwithtsql.com -R "wordpress"

Happy monitoring!


Modifying elasticsearch index settings

To view the settings of an index run the following at the command-line…

curl -XGET http://hostname:9200/indexname/_settings

From here you can indeify the setting you need and modify it as you wish. This example sets the number of replicas to zero.

curl -XPUT http://hostname:9200/indexname/_settings -d '{ "index": {"number_of_replicas":"0"}}'

For further details see the manual.