Archive for the ‘SQL Server’ 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 […]

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

TSQL: When were databases last restored

Here’s a couple of queries I’ve modified from When was the last time your SQL Server database was restored? This first query shows restore times by database and restore type. ?View Code TSQLDECLARE @dbname sysname, @days int SET @dbname = NULL –substitute for whatever database name you want   — Max data / restore type […]

TSQL: Database Permission Excercise for 70-462

Here’s the TSQL for an exercise, involving database permissions, from the 70-462. Explanatory comments are included. ?View Code TSQLCREATE DATABASE Saturn; GO   USE Saturn; GO CREATE ROLE Moon_Table_Editors; GO   — Create tables CREATE TABLE Mimas ( id INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY, column1 VARCHAR(100) NOT NULL ); GO CREATE TABLE Thethys ( […]