The Performance Impact to Prefix Stored Procedures with sp_

Last week I ran across a blog post by Axel Achten (B|T) that outlined a few reasons why you should not use SELECT * in queries.   In the post, Axel used the SQLQueryStress tool by Adam Machanic (B|T) to stress-test a simple query using SELECT * and SELECT col1, col2,...  This gave me an idea to use the same SQLQueryStress tool to benchmark a stored procedure that's prefixed with sp_.

All DBAs know, or should know, you should not prefix stored procedures with sp_.  Even Microsoft mentions the sp_ prefix is reserved for system stored procedures in Books Online.

I'm not going to discuss the do's and don'ts of naming conventions.  What I want to know is there still a performance impact of using the sp_ prefix. 

For our test, we'll use the AdventureWorks2012 database.  First we need to create two new stored procedures that selects from the Person.Person table.

USE AdventureWorks2012;

GO

CREATE PROCEDURE dbo.sp_SelectPerson AS SELECT * FROM Person.Person;

GO

CREATE PROCEDURE dbo.SelectPerson AS SELECT * FROM Person.Person;

GO

Next, we'll clear the procedure cache, and then execute each procedure once to compile it and to ensure all the data pages are in the buffer.

DBCC FREEPROCCACHE;

GO

EXEC dbo.sp_SelectPerson;

GO

EXEC dbo.SelectPerson;

GO

Next, we'll run execute each stored proc 100 times using SQLQueryStress and compare the results.

Total time to execute sp_SelectPerson was 3 minutes 43 seconds, and only 3 minutes 35 seconds to execute SelectPerson.  Given this test run was only over 100 iterations, 8 seconds is huge amount of savings.

We can even query sys.dm_exec_procedure_stats to get the average worker time in seconds and average elapsed time in seconds for each procedure.

SELECT

     o.name AS 'object_name'

    ,p.execution_count

    ,p.total_worker_time AS 'total_worker_time(μs)'

    ,(p.total_worker_time/p.execution_count)*0.000001 AS 'avg_worker_time(s)'

    ,p.total_elapsed_time AS 'total_elapsed_time(μs)'

    ,(p.total_elapsed_time/p.execution_count)*0.000001 AS 'avg_elapsed_time(s)'

FROM sys.dm_exec_procedure_stats p

JOIN sys.objects o ON p.object_id = o.object_id;

GO

As you can see, the average time per execution is very minimal, but it does add up over time.  This could easy scale into a much larger difference if all stored procedures begin with sp_.

T-SQL Tuesday - Use Powershell to Restore a Database on a Different Server

T-SQL Tuesday - This month's party is hosted by Wayne Sheffield (blog|twitter), and the topic is about Powershell and how to use it for anything SQL Server.

With that challenge, I'd like to share a script I've written that takes a backup file from one server, copies to another server, and and then restores it.  That may sound pretty easy, but I've added in a few requirements to the restore.

Here's the scenario:

We have two SQL Servers, one production (TRON2\R2PROD) and one test (TRON3\R2TEST), and we have one user database (AdventureWorks2008R2) on each of the production and test servers.  The test server is used by a developer.  The developer send us a request to "refresh the development database with a copy of production".  This translates into: he needs the most recent backup of that production database copied from the production server over to the test server, then restored to it by overwriting the existing database, all while preserving his existing dbo level permissions.

The manual approach to completing this task.

1. Figure out which full database backup file is the most recent for AdventureWorks2008R2.
2. Copy the file from TRON2 to TRON3.
3. On TRON3\R2TEST, script out all existing user permissions for the AdventureWorks2008R2 database.
4. Restore the backup.
5. Run the script from step 3 to reapply the developers permissions.
6. Delete the backup file from TRON3.

Total time to execute this task manually: ~ 10 minutes.

That many not seem like much time out of your entire workday, but what if that same developer wants you to complete this task each morning at 8AM.  Now you're up to 10 minutes per day.  And what if he asked you to do it several times a day, every day of the week.  That 10 minutes can really add up.

The Powershell approach to completing this task.

1. Run the AutoDatabaseRefresh.ps1 script.

Total time to execute this task using Powershell: < 30 seconds.

How's that for performance improvement?

The great thing about Powershell is that it allows you to connect to different systems, such as Windows and SQL Server, all from a single programming language.  The entire script is written using the SQL Management Objects (SMO).  It does not use any of the SQL Server cmdlets, so there are no modules to import.  Let's take a closer look.

For this script you need to pass 6 parameters to this script.

1. $sourceInstance - Source SQL Server name
• Example: "TRON2\R2PROD"
2. $sourceDbName - Source database
• Example: "AdventureWorks2008R2"
3. $sourcePath - Source share where the file exists (UNC Path)
• Example: "\\TRON2\BACKUP\R2PROD\AdventureWorks2008R2"
4. $destinationInstance - Destination SQL Server name
• Example: "TRON3\R2TEST"
5. $destinationDbName - Database to be refreshed on destination server
• Example: "AdventureWorks2008R2"
6. $destinationPath - Destination share to copy backup file to (UNC Path)
• Example: "\\TRON3\BACKUP"

The script needs to know both the source and destination SQL Servers (#1 and #4), and the source and destination database names (#2 and #5).  The other two parameters are the source paths (#3 and #6) and they must be UNC file shares.  This is so the Powershell script can be executed from any server or from any DBA's workstation.

The basic workflow of the Powershell script is as follows:

Step 1:  Validate the input parameters.  All connectivity to the SQL Servers and to the file shares use Windows Authentication.  Tests for blank parameters.  Tests the connectivity to each SQL Server. Test that each file share exists.  If any of these validation tests fail, the script will halt.

if([String]::IsNullOrEmpty($sourceInstance))

{

    Write-Host "ERROR"

    $errorMessage = "Source server name is not valid."

    throw $errorMessage

}

Step 2:  Connect to $sourceInstance to get the name of the most recent backup file for $sourceDbName.  This is accomplished by running this TSQL script.

$server = GetServer($serverInstance)

$db = $server.Databases["msdb"]

$fileList = $db.ExecuteWithResults(

    @"

DECLARE

     @BackupId int

    ,@DatabaseName nvarchar(255);

SET @DatabaseName = '$sourceDbName';

-- Get the most recent full backup for this database

SELECT TOP 1

     @DatabaseName AS DatabaseName

    ,m.physical_device_name

    ,RIGHT(m.physical_device_name, CHARINDEX('\',REVERSE(physical_device_name),1) - 1) AS 'FileName'

    ,b.backup_finish_date

    ,b.type AS 'BackupType'

FROM msdb.dbo.backupset b JOIN msdb.dbo.backupmediafamily m

ON b.media_set_id = m.media_set_id

WHERE b.database_name = @DatabaseName

    AND b.type = 'D'

    AND b.is_snapshot = 0

    AND b.is_copy_only = 0

    AND b.backup_finish_date IS NOT NULL

ORDER BY b.database_backup_lsn DESC;

    "@

This give us the following output.

Step 3:  Copy the file from $sourcePath to $destinationPath.  From the output above, the physical file, AdventureWorks2008R2_db_201302060836.BAK, is located in D:\Backup\R2PROD\AdventureWorks2008R2, so the $sourcePath must match this location.  Our UNC path is \\TRON2\BACKUP\R2PROD\AdventureWorks2008R2.  This step uses the Copy-Item cmdlet.  In my testing I have seen this cmdlet outperform the regular Windows copy and even Robocopy.

$source = $sourcePath + "\" + $backupFile

Write-Host "Copying file..."

copy-item $source -destination $destinationpPath

Step 4:  Connect to $destinationInstance and script out all user-level permissions and database roles for the $destinationDbName.  The is accomplished by using the following script.

$server = GetServer($serverInstance)

$db = $server.Databases["$destinationDbName"]

if(-not $db)

{

    Write-Host "Database does not exist on: $serverInstance"

}

else

{

    Write-Host "Saving permissions on $destinationDbName..." -NoNewline

    $commandList = $db.ExecuteWithResults(

        @"

IF OBJECT_ID('tempdb..#Commands') IS NOT NULL

    DROP TABLE #Commands;

CREATE TABLE #Commands(

     RowId int identity(1,1)

    ,Cmd varchar(2000));

INSERT #Commands(Cmd)

SELECT 'USE [$destinationDbName];IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'+QUOTENAME(d.name,CHAR(39))+') ALTER USER ' + QUOTENAME(d.name) + ' WITH LOGIN = ' + QUOTENAME(s.name) + ';'

FROM [$destinationDbName].sys.database_principals d LEFT OUTER JOIN master.sys.server_principals s

    ON d.sid = s.sid

WHERE s.name IS NOT NULL

    AND d.type = 'S'

    AND d.name <> 'dbo';

INSERT #Commands(Cmd)

SELECT 'USE [$destinationDbName];IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'+QUOTENAME(d.name,CHAR(39))+') CREATE USER ' + QUOTENAME(d.name) + ' FOR LOGIN ' + QUOTENAME(s.name) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME(d.default_schema_name) + ';'

FROM [$destinationDbName].sys.database_principals d LEFT OUTER JOIN master.sys.server_principals s

    ON d.sid = s.sid

WHERE s.name IS NOT NULL

    AND d.type = 'S'

    AND d.name <> 'dbo';

INSERT #Commands(Cmd)

SELECT 'USE [$destinationDbName];IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'+QUOTENAME(d.name,CHAR(39))+') CREATE USER ' + QUOTENAME(d.name) + ' FOR LOGIN ' + QUOTENAME(s.name) + ';'

FROM [$destinationDbName].sys.database_principals d LEFT OUTER JOIN master.sys.server_principals s

    ON d.sid = s.sid

WHERE s.name IS NOT NULL

    AND d.type IN ('U','G');

INSERT #Commands(Cmd)

SELECT 'USE [$destinationDbName];IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'+QUOTENAME(p.name,CHAR(39))+') CREATE ROLE ' + QUOTENAME(p.name) + ' AUTHORIZATION '+QUOTENAME(o.name)+';'

FROM [$destinationDbName].sys.database_principals p JOIN [$destinationDbName].sys.database_principals o

    ON o.principal_id = p.owning_principal_id

WHERE p.type = 'R'

    AND p.is_fixed_role = 0

    AND p.principal_id <> 0;

INSERT #Commands(Cmd)

SELECT 'USE [$destinationDbName];EXEC sp_addrolemember N' + QUOTENAME(d.name,'''') + ', N' + QUOTENAME(m.name,CHAR(39)) + ';'

FROM [$destinationDbName].sys.database_role_members r JOIN [$destinationDbName].sys.database_principals d

    ON r.role_principal_id = d.principal_id JOIN [$destinationDbName].sys.database_principals m

    ON r.member_principal_id = m.principal_id

WHERE m.principal_id > 5;

INSERT #Commands(Cmd)

SELECT 'USE [$destinationDbName];' + dp.state_desc + ' ' + dp.permission_name + ' TO ' + QUOTENAME(d.name) COLLATE Latin1_General_CI_AS + ';'

FROM [$destinationDbName].sys.database_permissions dp JOIN [$destinationDbName].sys.database_principals d

    ON dp.grantee_principal_id = d.principal_id

WHERE dp.major_id = 0

    AND dp.state <> 'W'

    AND dp.permission_name <> 'CONNECT'

ORDER BY d.name, dp.permission_name ASC, dp.state_desc ASC;

INSERT #Commands(Cmd)

SELECT 'USE [$destinationDbName];GRANT ' + dp.permission_name + ' TO ' + QUOTENAME(d.name) COLLATE Latin1_General_CI_AS + ' WITH GRANT OPTION;'

FROM [$destinationDbName].sys.database_permissions dp JOIN [$destinationDbName].sys.database_principals d

    ON dp.grantee_principal_id = d.principal_id

WHERE dp.major_id = 0

    AND dp.state = 'W'

    AND dp.permission_name <> 'CONNECT'

ORDER BY d.name, dp.permission_name ASC, dp.state_desc ASC;

SELECT Cmd FROM #Commands

ORDER BY RowId;

        "@

}

This gives us the existing permissions that we'll re-apply later in step 6.  You can see we're creating code to resync logins (ALTER USER...WITH LOGIN), create the user if it doesn't exist, create database roles if they don't exist, and add users to those database roles.

Step 5:  Restore the backup file to $destinationInstance using the $destinationDbName name.  This is the real meat and potatoes of the script.  

$restore = new-object ('Microsoft.SqlServer.Management.Smo.Restore')

$restore.Database = $destinationDbName

$restore.NoRecovery = $false

$restore.PercentCompleteNotification = 10

$restore.Devices.AddDevice($backupDataFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)

First it checks $destinationInstance to see if $destinationDbName already exists.  If it does, then it just restores over it.  If $destinationDbName does not exist, then the script will create it using the RESTORE...WITH MOVE command.  Since the source and destination SQL Servers have different instance names, the file folders for the physical MDF & LDF files will be different.  The script uses the default folder locations to store the data and log files.  This folders were specified when you installed SQL Server.  If the $sourceDbName has several NDF files, all of them will be placed in the default data folder.

$defaultMdf = $server.Settings.DefaultFile

$defaultLdf = $server.Settings.DefaultLog

Before the restore, the script will set the recovery mode of $destinationDbName to SIMPLE.  This is avoid the "backup tail log" error message in case the database is in FULL recovery mode.  It sets the database to single-user mode to kill any existing connections before the restore.  And after the restore is complete, it sets the recovery mode back to SIMPLE.

$db.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple

$db.UserAccess = "Single"

$db.Alter(

    [Microsoft.SqlServer.Management.Smo.TerminationClause]

    "RollbackTransactionsImmediately")

Step 6:  Apply the saved permissions from step 4 to $destinationDbName.  These are the permissions that were scripted out from step 4.  They are applied to the $destinationDbName one line at a time.

foreach($Row in $commandList.Tables[0].Rows)

{

    $db.ExecuteNonQuery($Row["Cmd"])

}

Step 7:  Delete the backup from $destinationPath.  This is the cleanup step.

remove-item $backupFile

When running the script from a console, the output will look like this.

=============================================================

 1: Perform Initial Checks & Validate Input Parameters

=============================================================

Validating parameters...OK

Verifying source SQL Server connectivity...OK

Verifying source database exists...OK

Verifying destination SQL Server connectivity...OK

Verifying source file share exists...OK

Verifying destination file share exists...OK

=============================================================

 2: Get Source Backup for the Restore

=============================================================

Connecting to TRON2\R2PROD to find a restore file...

Selected file:  D:\Backup\R2PROD\AdventureWorks2008R2\AdventureWorks2008R2_db_201302060836.BAK

Verifying file: \\TRON2\BACKUP\R2PROD\AdventureWorks2008R2\AdventureWorks2008R2_db_201302060836.BAK exists...

Source file existence: OK

=============================================================

 3: Copy Backup File to the Destination

=============================================================

Copying file...

Copy file: OK

=============================================================

 4: Get Current Permissions on the Destination Database

=============================================================

Saving permissions on AdventureWorks2008R2...OK

=============================================================

 5: Restore Backup File to the Destination Server

=============================================================

Restoring database...

Database Restore: OK

=============================================================

 6: Restore Permissions to the Destination Database

=============================================================

Restoring existing permissions...

Existing permissions restored: OK

=============================================================

 7: Delete Backup File from the Destination Server

=============================================================

Deleting file...

Delete file: OK

=============================================================

    Database refresh completed successfully

=============================================================

The best part about using the Powershell script, is you can setup a SQL Agent job to call the script with the parameters already specified.  That way when the developer asks you refresh the same database then all you have to do is run the job, or you can work the developer to schedule the job to run automatically each day.

The SQL Agent job will need to setup as an "Operating system (CmdExec)" job type.  This is because it uses Powershell components that are outside the normal SQLPS group of commands.

The entire script is below.  Feel free to modify it as you see fit for your environment.

########################################################################################### # # File Name: AutoDatabaseRefresh.ps1 # # Applies to: SQL Server 2008 # SQL Server 2008 R2 # SQL Server 2012 # # Purpose: Used to automatically restore a database in another environment. # # Prerequisite: Powershell v2.0 must be installed. # SQL Server components must be installed. # # Parameters: [string]$sourceInstance - Source SQL Server name (Ex: SERVER\INSTANCE) # [string]$sourceDbName - Source database # [string]$sourcePath - Source share where the file exists # [string]$destinationInstance - Destination SQL Server name (Ex: SERVER\INSTANCE) # [string]$destinationDbName - Database to be refreshed/created on desitination server # [string]$destinationPath - Share to copy backup file to (UNC Path Ex: \\SERVER\backup$) # # Author: Patrick Keisler # # Version: 1.0.0 # # Date: 02/06/2013 # # Help: http://www.patrickkeisler.com/ # ########################################################################################### #Enable Debug Messages #$DebugPreference = "Continue" #Disable Debug Messages $DebugPreference = "SilentlyContinue" #Terminate Code on All Errors $ErrorActionPreference = "Stop" #Clear screen CLEAR #Load Assemblies [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | out-null [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoExtended') | out-null [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') | out-null function CheckForErrors { $errorsReported = $False if($Error.Count -ne 0) { Write-Host Write-Host "******************************" Write-Host "Errors:" $Error.Count Write-Host "******************************" foreach($err in $Error) { $errorsReported = $True if( $err.Exception.InnerException -ne $null) { Write-Host $err.Exception.InnerException.ToString() } else { Write-Host $err.Exception.ToString() } Write-Host } throw; } } function GetServer { Param([string]$serverInstance) $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server")($serverInstance) $server.ConnectionContext.ApplicationName = "AutoDatabaseRefresh" $server.ConnectionContext.ConnectTimeout = 5 $server; } function GetRestoreFileList { Param([string]$serverInstance, [string]$sourcePath) Write-Host "Connecting to $serverInstance to find a restore file..." $server = GetServer($serverInstance) $db = $server.Databases["msdb"] $fileList = $db.ExecuteWithResults( @" DECLARE @BackupId int ,@DatabaseName nvarchar(255); SET @DatabaseName = '$sourceDbName'; -- Get the most recent full backup for this database SELECT TOP 1 @DatabaseName AS DatabaseName ,m.physical_device_name ,RIGHT(m.physical_device_name, CHARINDEX('\',REVERSE(physical_device_name),1) - 1) AS 'FileName' ,b.backup_finish_date ,b.type AS 'BackupType' FROM msdb.dbo.backupset b JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id WHERE b.database_name = @DatabaseName AND b.type = 'D' AND b.is_snapshot = 0 AND b.is_copy_only = 0 AND b.backup_finish_date IS NOT NULL ORDER BY b.database_backup_lsn DESC; "@ ) CheckForErrors if ($fileList.Tables[0].Rows.Count -ge 1) { foreach($file in $fileList.Tables[0].Rows) { $source = $sourcePath + "\" + $file["FileName"] Write-Host "Selected file: " $file["physical_device_name"] Write-Host "Verifying file: $source exists..." if((Test-Path -Path $source) -ne $True) { $errorMessage = "File:" + $source + " does not exists" throw $errorMessage } } Write-Host "Source file existence: OK" $file["FileName"].ToString(); } else { $errorMessage = "Source database " + $sourceDbName + " does not have any current full backups." throw $errorMessage } } function GetExistingPermissions { Param([string]$serverInstance, [string]$destinationDbName) $server = GetServer($serverInstance) $db = $server.Databases["$destinationDbName"] if(-not $db) { Write-Host "Database does not exist on: $serverInstance" } else { Write-Host "Saving permissions on $destinationDbName..." -NoNewline $commandList = $db.ExecuteWithResults( @" IF OBJECT_ID('tempdb..#Commands') IS NOT NULL DROP TABLE #Commands; CREATE TABLE #Commands( RowId int identity(1,1) ,Cmd varchar(2000)); INSERT #Commands(Cmd) SELECT 'USE [$destinationDbName];IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'+QUOTENAME(d.name,CHAR(39))+') ALTER USER ' + QUOTENAME(d.name) + ' WITH LOGIN = ' + QUOTENAME(s.name) + ';' FROM [$destinationDbName].sys.database_principals d LEFT OUTER JOIN master.sys.server_principals s ON d.sid = s.sid WHERE s.name IS NOT NULL AND d.type = 'S' AND d.name <> 'dbo'; INSERT #Commands(Cmd) SELECT 'USE [$destinationDbName];IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'+QUOTENAME(d.name,CHAR(39))+') CREATE USER ' + QUOTENAME(d.name) + ' FOR LOGIN ' + QUOTENAME(s.name) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME(d.default_schema_name) + ';' FROM [$destinationDbName].sys.database_principals d LEFT OUTER JOIN master.sys.server_principals s ON d.sid = s.sid WHERE s.name IS NOT NULL AND d.type = 'S' AND d.name <> 'dbo'; INSERT #Commands(Cmd) SELECT 'USE [$destinationDbName];IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'+QUOTENAME(d.name,CHAR(39))+') CREATE USER ' + QUOTENAME(d.name) + ' FOR LOGIN ' + QUOTENAME(s.name) + ';' FROM [$destinationDbName].sys.database_principals d LEFT OUTER JOIN master.sys.server_principals s ON d.sid = s.sid WHERE s.name IS NOT NULL AND d.type IN ('U','G'); INSERT #Commands(Cmd) SELECT 'USE [$destinationDbName];IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'+QUOTENAME(p.name,CHAR(39))+') CREATE ROLE ' + QUOTENAME(p.name) + ' AUTHORIZATION '+QUOTENAME(o.name)+';' FROM [$destinationDbName].sys.database_principals p JOIN [$destinationDbName].sys.database_principals o ON o.principal_id = p.owning_principal_id WHERE p.type = 'R' AND p.is_fixed_role = 0 AND p.principal_id <> 0; INSERT #Commands(Cmd) SELECT 'USE [$destinationDbName];EXEC sp_addrolemember N' + QUOTENAME(d.name,'''') + ', N' + QUOTENAME(m.name,CHAR(39)) + ';' FROM [$destinationDbName].sys.database_role_members r JOIN [$destinationDbName].sys.database_principals d ON r.role_principal_id = d.principal_id JOIN [$destinationDbName].sys.database_principals m ON r.member_principal_id = m.principal_id WHERE m.principal_id > 5; INSERT #Commands(Cmd) SELECT 'USE [$destinationDbName];' + dp.state_desc + ' ' + dp.permission_name + ' TO ' + QUOTENAME(d.name) COLLATE Latin1_General_CI_AS + ';' FROM [$destinationDbName].sys.database_permissions dp JOIN [$destinationDbName].sys.database_principals d ON dp.grantee_principal_id = d.principal_id WHERE dp.major_id = 0 AND dp.state <> 'W' AND dp.permission_name <> 'CONNECT' ORDER BY d.name, dp.permission_name ASC, dp.state_desc ASC; INSERT #Commands(Cmd) SELECT 'USE [$destinationDbName];GRANT ' + dp.permission_name + ' TO ' + QUOTENAME(d.name) COLLATE Latin1_General_CI_AS + ' WITH GRANT OPTION;' FROM [$destinationDbName].sys.database_permissions dp JOIN [$destinationDbName].sys.database_principals d ON dp.grantee_principal_id = d.principal_id WHERE dp.major_id = 0 AND dp.state = 'W' AND dp.permission_name <> 'CONNECT' ORDER BY d.name, dp.permission_name ASC, dp.state_desc ASC; SELECT Cmd FROM #Commands ORDER BY RowId; "@ ) CheckForErrors Write-Host "OK" } $commandList; } function CopyFile { Param([string]$sourcePath, [string]$backupFile, [string]$destinationpPath) $source = $sourcePath + "\" + $backupFile try { Write-Host "Copying file..." Write-Debug "Copy $source to $destinationpPath" copy-item $source -destination $destinationpPath } catch { CheckForErrors } Write-Host "Copy file: OK" } function DeleteFile { Param([string]$backupFile) try { Write-Host "Deleting file..." Write-Debug "Deleting file: $backupFile" remove-item $backupFile } catch { CheckForErrors } Write-Host "Delete file: OK" } function RestoreDatabase { Param([string]$serverInstance, [string]$destinationDbName, [string]$backupDataFile, [string]$actionType) Write-Host "Restoring database..." $server = GetServer($serverInstance) $server.ConnectionContext.StatementTimeout = 0 $db = $server.Databases["$destinationDbName"] #Create the restore object and set properties $restore = new-object ('Microsoft.SqlServer.Management.Smo.Restore') $restore.Database = $destinationDbName $restore.NoRecovery = $false $restore.PercentCompleteNotification = 10 $restore.Devices.AddDevice($backupDataFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File) if(-not $db) { Write-Debug "$destinationDbName does not exist..." #Grab the default MDF & LDF file locations. $defaultMdf = $server.Settings.DefaultFile $defaultLdf = $server.Settings.DefaultLog #If the default locations are the same as the master database, #then those values do not get populated and must be pulled from the MasterPath. if($defaultMdf.Length -eq 0) { $defaultMdf = $server.Information.MasterDBPath } if($defaultLdf.Length -eq 0) { $defaultLdf = $server.Information.MasterDBLogPath } if(-not $defaultMdf.EndsWith("\")) { $defaultMdf = $defaultMdf + "\" } if(-not $defaultLdf.EndsWith("\")) { $defaultLdf = $defaultLdf + "\" } $restore.ReplaceDatabase = $True #Get the database logical file names try { $logicalNameDT = $restore.ReadFileList($server) } catch { CheckForErrors } $FileType = "" Write-Debug "Restoring $destinationDbName to the following physical locations:" foreach($Row in $logicalNameDT) { # Put the file type into a local variable. # This will be the variable that we use to find out which file # we are working with. $FileType = $Row["Type"].ToUpper() # If Type = "D", then we are handling the Database File name. If($FileType.Equals("D")) { $dbLogicalName = $Row["LogicalName"] $targetDbFilePath = $Row["PhysicalName"] $position = $targetDbFilePath.LastIndexOf("\") + 1 $targetDbFilePath = $targetDbFilePath.Substring($position,$targetDbFilePath.Length - $position) $targetDbFilePath = $defaultMdf + $targetDbFilePath if((Test-Path -Path $targetDbFilePath) -eq $true) { $targetDbFilePath = $targetDbFilePath -replace $dbLogicalName, $destinationDbName } #Specify new data files (mdf and ndf) $relocateDataFile = new-object ('Microsoft.SqlServer.Management.Smo.RelocateFile') $relocateDataFile.LogicalFileName = $dbLogicalName $relocateDataFile.PhysicalFileName = $targetDbFilePath $restore.RelocateFiles.Add($relocateDataFile) | out-null Write-Debug $relocateDataFile.PhysicalFileName } # If Type = "L", then we are handling the Log File name. elseif($FileType.Equals("L")) { $logLogicalName = $Row["LogicalName"] $targetLogFilePath = $Row["PhysicalName"] $position = $targetLogFilePath.LastIndexOf("\") + 1 $targetLogFilePath = $targetLogFilePath.Substring($position,$targetLogFilePath.Length - $position) $targetLogFilePath = $defaultLdf + $targetLogFilePath if((Test-Path -Path $targetLogFilePath) -eq $true) { $tempName = $destinationDbName + "_Log" $targetLogFilePath = $targetLogFilePath -replace $logLogicalName, $tempName } #Specify new log files (ldf) $relocateLogFile = new-object ('Microsoft.SqlServer.Management.Smo.RelocateFile') $relocateLogFile.LogicalFileName = $logLogicalName $relocateLogFile.PhysicalFileName = $targetLogFilePath $restore.RelocateFiles.Add($relocateLogFile) | out-null Write-Debug $relocateLogFile.PhysicalFileName } } } else { Write-Debug "Overwritting existing database..." #Set recovery model to simple on destination database before restore if($db.RecoveryModel -ne [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple) { Write-Debug "Changing recovery model to SIMPLE" $db.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple try { $db.Alter() } catch { CheckForErrors } } #Set destination database to single user mode to kill any active connections $db.UserAccess = "Single" try { $db.Alter([Microsoft.SqlServer.Management.Smo.TerminationClause]"RollbackTransactionsImmediately") } catch { CheckForErrors } } #Do the restore try { $restore.SqlRestore($server) } catch { CheckForErrors } #Reload the restored database object $db = $server.Databases["$destinationDbName"] #Set recovery model to simple on destination database after restore if($db.RecoveryModel -ne [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple) { Write-Debug "Changing recovery model to SIMPLE" $db.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple try { $db.Alter() } catch { CheckForErrors } } Write-Host $actionType.ToString() "Restore: OK" } function RestorePermissions { Param([string]$destinationInstance, [string]$destinationDbName, $commandList) Write-Host "Restoring existing permissions..." $server = GetServer($destinationInstance) $db = $server.Databases[$destinationDbName] foreach($Row in $commandList.Tables[0].Rows) { #Apply existing permissions back to destination database Write-Debug $Row["Cmd"] try { $db.ExecuteNonQuery($Row["Cmd"]) } catch { CheckForErrors } } Write-Host "Existing permissions restored: OK" } function PerformValidation { Param($sourceInstance, $sourceDbName, $sourcePath, $destinationInstance, $destinationDbName, $destinationPath) Write-Host "Validating parameters..." -NoNewline if([String]::IsNullOrEmpty($sourceInstance)) { Write-Host "ERROR" $errorMessage = "Source server name is not valid." throw $errorMessage } if([String]::IsNullOrEmpty($sourceDbName)) { Write-Host "ERROR" $errorMessage = "Source database name is not valid." throw $errorMessage } if([String]::IsNullOrEmpty($sourcePath)) { Write-Host "ERROR" $errorMessage = "Source path is not valid." throw $errorMessage } else { if(-not $sourcePath.StartsWith("\\")) { Write-Host "ERROR" $errorMessage = "Source path is not valid: " + $sourcePath throw $errorMessage } } if([String]::IsNullOrEmpty($destinationInstance)) { Write-Host "ERROR" $errorMessage = "Destination server name is not valid." throw $errorMessage } if([String]::IsNullOrEmpty($destinationDbName)) { Write-Host "ERROR" $errorMessage = "Destination database name is not valid." throw $errorMessage } if([String]::IsNullOrEmpty($destinationPath)) { Write-Host "ERROR" $errorMessage = "Destination path name is not valid." throw $errorMessage } else { if(-not $destinationPath.StartsWith("\\")) { Write-Host "ERROR" $errorMessage = "Destination path is not valid: " + $destinationPath throw $errorMessage } } Write-Host "OK" Write-Host "Verifying source SQL Server connectivity..." -NoNewline $conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($sourceInstance) $conn.ApplicationName = "AutoDatabaseRefresh" $conn.NonPooledConnection = $true $conn.ConnectTimeout = 5 try { $conn.Connect() $conn.Disconnect() } catch { CheckForErrors } Write-Host "OK" Write-Host "Verifying source database exists..." -NoNewline $sourceServer = GetServer($sourceInstance) $sourcedb = $sourceServer.Databases[$sourceDbName] if(-not $sourcedb) { Write-Host "ERROR" $errorMessage = "Source database does not exist on $sourceInstance" throw $errorMessage } Write-Host "OK" Write-Host "Verifying destination SQL Server connectivity..." -NoNewline $conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($destinationInstance) $conn.ApplicationName = "AutoDatabaseRefresh" $conn.NonPooledConnection = $true $conn.ConnectTimeout = 5 try { $conn.Connect() $conn.Disconnect() } catch { CheckForErrors } Write-Host "OK" Write-Host "Verifying source file share exists..." -NoNewline if((Test-Path -Path $sourcePath) -ne $True) { Write-Host "ERROR" $errorMessage = "File share:" + $sourcePath + " does not exists" throw $errorMessage } Write-Host "OK" Write-Host "Verifying destination file share exists..." -NoNewline if((Test-Path -Path $destinationPath) -ne $True) { Write-Host "ERROR" $errorMessage = "File share:" + $destinationPath + " does not exists" throw $errorMessage } Write-Host "OK" } function Main{ Param([string]$sourceInstance, [string]$sourceDbName, [string]$sourcePath, [string]$destinationInstance, [string]$destinationDbName, [string]$destinationPath) $Error.Clear() Write-Host Write-Host "=============================================================" Write-Host " 1: Perform Initial Checks & Validate Input Parameters" Write-Host "=============================================================" PerformValidation $sourceInstance $sourceDbName $sourcePath $destinationInstance $destinationDbName $destinationPath Write-Host Write-Host "=============================================================" Write-Host " 2: Get Source Backup for the Restore" Write-Host "=============================================================" $restoreFile = GetRestoreFileList $sourceInstance $sourcePath Write-Host Write-Host "=============================================================" Write-Host " 3: Copy Backup File to the Destination" Write-Host "=============================================================" CopyFile $sourcePath $restoreFile $destinationPath Write-Host Write-Host "=============================================================" Write-Host " 4: Get Current Permissions on the Destination Database" Write-Host "=============================================================" $existingPermissions = GetExistingPermissions $destinationInstance $destinationDbName Write-Host Write-Host "=============================================================" Write-Host " 5: Restore Backup File to the Destination Server" Write-Host "=============================================================" $restoreFile = $destinationPath + "\" + $restoreFile RestoreDatabase $destinationInstance $destinationDbName $restoreFile "Database" Write-Host Write-Host "=============================================================" Write-Host " 6: Restore Permissions to the Destination Database" Write-Host "=============================================================" if($existingPermissions) { RestorePermissions $destinationInstance $destinationDbName $existingPermissions } Write-Host Write-Host "=============================================================" Write-Host " 7: Delete Backup File from the Destination Server" Write-Host "=============================================================" DeleteFile $restoreFile Write-Host Write-Host "=============================================================" Write-Host " Database refresh completed successfully" Write-Host "=============================================================" } #Hard-coded values used only for development #$sourceInstance = "TRON2\R2PROD" #$sourceDbName = "AdventureWorks2008R2" #$sourcePath = "\\TRON2\BACKUP\R2PROD\AdventureWorks2008R2" #$destinationInstance = "TRON3\R2TEST" #$destinationDbName = "AdventureWorks2008R2" #$destinationPath = "\\TRON3\BACKUP" #Prompt for inputs for an interactive script #$sourceInstance = $(Read-Host "Source SQL Server name (Ex: SERVER\INSTANCE)") #$sourceDbName = $(Read-Host "Source database") #$sourcePath = $(Read-Host "Source share where the file exists (UNC Path Ex: \\SERVER\BACKUP)") #$destinationInstance = $(Read-Host "Destination SQL Server name (Ex: SERVER\INSTANCE)") #$destinationDbName = $(Read-Host "Database to be refreshed/created on desitination server") #$destinationPath = $(Read-Host "Destination share to copy backup file to (UNC Path Ex: \\SERVER\BACKUP)") #Capture inputs from the command line. $sourceInstance = $args[0] $sourceDbName = $args[1] $sourcePath = $args[2] $destinationInstance = $args[3] $destinationDbName = $args[4] $destinationPath = $args[5] $debug = "Source Instance Parameter: " + $sourceInstance Write-Debug $debug $debug = "Source Database Parameter: " + $sourceDbName Write-Debug $debug $debug = "Source Path Parameter: " + $sourcePath Write-Debug $debug $debug = "Destination Instance Parameter: " + $destinationInstance Write-Debug $debug $debug = "Destination Database Parameter: " + $destinationDbName Write-Debug $debug $debug = "Destination Path Parameter: " + $destinationPath Write-Debug $debug Main $sourceInstance $sourceDbName $sourcePath $destinationInstance $destinationDbName $destinationPath