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.
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.
- Figure out which full database backup file is the most recent for AdventureWorks2008R2.
- Copy the file from TRON2 to TRON3.
- On TRON3\R2TEST, script out all existing user permissions for the AdventureWorks2008R2 database.
- Restore the backup.
- Run the script from step 3 to reapply the developers permissions.
- Delete the backup file from TRON3.
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.
- Run the AutoDatabaseRefresh.ps1 script.
Total time to execute this task using Powershell: < 30
seconds.
How's that for performance improvement?
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.
- $sourceInstance - Source SQL Server name
- Example: "TRON2\R2PROD"
- $sourceDbName - Source database
- Example: "AdventureWorks2008R2"
- $sourcePath - Source share where the file exists (UNC Path)
- Example: "\\TRON2\BACKUP\R2PROD\AdventureWorks2008R2"
- $destinationInstance - Destination SQL Server name
- Example: "TRON3\R2TEST"
- $destinationDbName - Database to be refreshed on destination server
- Example: "AdventureWorks2008R2"
- $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.
Great script! It's nice to see such well formed Powershell, with error handling and the code broken out in the functions and such. Was playing around with it and found one slight issue. Given the scenario of a source database that was itself restored from somewhere else, your script to determine the latest full backup may not work since it is sorting by LSN, but not filtering by $serverInstance on b.server_name. If the LSNs are higher on the 'ultimate' source, you may get an old non-existent backup file returned. An easy fix, but it tripped me up. Thanks again! My existing scripts will be incorporating some ideas from yours.
ReplyDeleteThis is a very nice script however we had come across 3 issues that we have to modify.
ReplyDelete1. This will combine permissions a user has on both source and destination. Example: UserA has R permissions on the source and UserA has W permissions on the destination. Running the script will then change the destination UserA to have RW permissions.
2. Users that are on the source but are not on the destination will carry over to the destination. If this is an issue, perhaps after copying the backup to the destination, drop all / most of the users then recreate them from the created temp destination permission / user table?
3. This one had us stumped for a while. We had one instance where everything worked as it was supposed to but we had one user that would never have permissions assigned. It was a unique user to the destination and it did recreate the user, but no permissions. The problem was with the following code:
--------------------
INSERT #Commands(Cmd)
SELECT 'USE [CaseManagementCriminal];EXEC sp_addrolemember N' + QUOTENAME(d.name,'''') + ', N' + QUOTENAME(m.name,CHAR(39)) + ';'
FROM [CaseManagementCriminal].sys.database_role_members r JOIN [CaseManagementCriminal].sys.database_principals d
ON r.role_principal_id = d.principal_id JOIN [CaseManagementCriminal].sys.database_principals m
ON r.member_principal_id = m.principal_id
WHERE m.principal_id > 5;
--------------------
The user that never had permissions assigned after refresh had a principal_id = 5. Therefore, it never got grabbed. Don't fully understand why it matters why the id was set to > 5 so we just changed ours to !=1 since 1 is usually dbo, and it started to work for us!
The principal_id > 5 was a result of the environment I work in. We have some special users that we don't want copied from server to server. I should have removed that line.
Delete