Archive for the ‘DBA’ Category

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… ?View Code TSQLSET 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 […]

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. ?View Code BASHscp "user@hostname:/data/backup/backup_2014100[3-9].tar.gz" /path/to/location/ You should see […]

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 […]

TSQL: Estimated database restore completion

Here’s a query proving you approximate percentage compeled, and estimated finish time, of any database restores happening on a SQL Server instance… ?View Code TSQLSELECT st.[text], r.percent_complete, DATEADD(SECOND, r.estimated_completion_time/1000, GETDATE()) AS estimated_completion_time, r.total_elapsed_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) st WHERE [command] = ‘RESTORE DATABASE'; The resultset will look something like below… text percent_complete estimated_completion_time […]

TSQL: Database Mirroring with Certificates

Here’s some more TSQL for the 70-462 exam. The script shows the actions needed to configure database mirroring using certificates for authentication. Explanatory notes are included but you’re likely to need the training materials for this to make sense. TSQL is not included for the backup/restore parts needed for database mirroring. ?View Code TSQLSELECT * […]