Aside: Since I'm using Powershell to run all this, why do I use the TF.cmd application instead of the Cmdlets? Simplicity. The Cmdlets appear to need the FULL version of a TFS client installed, plus you have to grab and install the cmdlets... and there are bugs if you don't use the right (x32 vs x64) version of the cmdlets with the right version of powershell. So I built it around the TF CLI (Command-Line Interface). The original post was for 2008, the new one (plus script below) are for 2013.
IMPORTANT:
Upgrading from 2008 to 2013 blows, since the config and cache can be screwed up.
What I had to do was completely drop the workfold and the workspace (PROTIP: first, list your workfold and workspaces and SAVE THOSE DETAILS), delete the entirety of the cache folder, delete everything from my EN_Workspace folder on disk, then rebuild from scratch.
One other oddity: before, from the root of my installation (c:\tfs_cli) I could run the script, and it could properly list the folders underneath it. That appears to have changed for some reason; my Powershell script now CDs into the subfolder before it proceeds. Not a major change... but took several hours to diagnose, since I was also fighting with other issues on the setup. Caveat Emptor and all that.
Also for the TFS newbies: the default path you use for a LOT of things is:
http://myservername:8080/tfs/DefaultCollection .
Steps to get going:
- Install Java. Got it from Oracle, which doesn't appear to have the same cruftware that Sun's does. Version 7-something
- Grab Team Foundation Everywhere 2013. Reading the docs, it works for TFS 2010, 2012, 2013, and Online. http://www.microsoft.com/en-us/download/details.aspx?id=40785
- Unzip into one massive folder (I called mine "c:\TFS_CLI\APP_2013"), then try running (since I'm on Server 2012, in Powershell):
- ".\tf.cmd"
- it works - success! Sorta.
- Found this page which seems to make the below setup easier:
- http://msdn.microsoft.com/en-us/library/hh873092.aspx
- For the rest of this, I'm doing it as the Service account that SQL Server is running under on the box that will run the job. Oddly enough, it wasn't set that way when I built it on 2008, but it continued to work even though my password changed. Note that nowhere in here do you actually put in a password.
- Type ".\tf eula", then accept ("y").
- muddle through the web site (tfsservername:8080/tfs) and add the SQL Service account as an admin to the branch I'm using (YourENProject).
- Using the MS page above:
- .\tf workspace -new TF_Workspace -collection:http://mytfservername:8080/tfs/DefaultCollection
- the collection URL is pretty standard - it should work in 2010,2012,2013.
- .\tf workfold -map $/YourENProject -workspace:TF_Workspace c:\TFS_CLI\EN_Workspace
- This line has several names. Let's break them out.
- $/YourENProject is the "Branch" in TFS. Call it something like $/ProductionEN or something like that. I created the branch using SSDT before I did any of this, but you can create one using the TF.exe
- TF_Workspace is the workspace you created in the step above.
- c:\TFS_CLI\EN_Workspace - the physical folder on your EN server (NOT the TFS server). I named them differently to make it more obvious what each part of the command is doing.
- now try and check something in.
- Get to the workspace
- cd c:\TFS_CLI\EN_Workspace
- new-item test.txt -type file
- &cmd /c "c:\tfs_cli\app_2013\tf add test.txt"
- &cmd /c "c:\tfs_cli\app_2013\tf checkin /author:memyselfandi"
- I use that weird syntax to catch errors and return as text into Powershell, so it can properly eat and pass on errors as necessary, as opposed to having PS choke on it.
- Pull and update something: this is where I had massive problems. (use script)
- Finally verify it worked (I used my "real" TFS client on my desktop).
SP:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE eventnotificationrec | |
GO | |
CREATE PROCEDURE [dbo].[EN_TFS_Checkin] | |
AS | |
/* | |
Purpose - using Event Notifications, determines which objects have changed (sub-database-level, for now). | |
Once list has been generated, repeatedly call a custom powershell script that will | |
"get" and "checkout" the object from TFS if it exists, overwrite it with a current copy (scripted via SMO), | |
then "checkin" the new object with a comment based on the loginname | |
mdb 2013/10/04 1.00 Works well (albeit a little slowly)and handled multiple days' worth of checkins. | |
Error items were due because of short-term objects that are created then dropped. | |
mdb 2013/10/11 1.10 Using TFSsecurity to find valid users. If user is valid, use their name to checkin. | |
mdb 2013/10/22 1.11 Changing INDEX to include table. Makes it easier to read in history. | |
mdb 2013/11/08 1.12 Let's handle extended properties! | |
mdb 2013/12/03 1.13 the PS code can now script out database creation. Adding things like ALTER_DATABASE, | |
and dealing with full-database checkins. | |
mdb 2013/12/04 1.14 fixing author bug | |
To be done still: | |
* Script server-level | |
* Database-level: perms and ALTER/CREATE_SCHEMA | |
* Logic for Drop and Renames. How do we want to handle that? (TF.exe RENAME, either to new name or "DROPPED - oldname") | |
* Better logic to skip events? We would conceivably need more table(s) to hold the exception types. | |
sample of items not yet done | |
add_role_member --must be at the database level(?) | |
ADD_SERVER_ROLE_MEMBER | |
ALTER_ASSEMBLY | |
ALTER_CREDENTIAL | |
*/ | |
SET NOCOUNT ON | |
DECLARE @cmd VARCHAR(8000), @min INT, @max INT, @EventType VARCHAR(128), @Time_of_last_run datetime, @now DATETIME | |
, @loginname VARCHAR(128), @loginname_temp VARCHAR(128), @TargetObjectType VARCHAR(128) | |
--mdb 2013/11/08 we didn't add ObjectType as it would screw up our dupe-elimination, but I need TargetObjectType for ExtendedProperties | |
DECLARE @change_list TABLE (id INT IDENTITY, ServerName VARCHAR(128), EventType VARCHAR(128), LoginName VARCHAR(128) | |
, DatabaseName VARCHAR(128), SchemaName VARCHAR(128), ObjectName VARCHAR(128) | |
, TargetObjectName VARCHAR(128), TargetObjectType VARCHAR(128)) | |
DECLARE @valid_tfs_logins TABLE (id INT IDENTITY, loginname VARCHAR(128)) | |
IF OBJECT_ID('tempdb..#holding_tank_for_errors') IS NOT NULL | |
DROP TABLE #holding_tank_for_errors | |
CREATE TABLE #holding_tank_for_errors (id INT IDENTITY, resultant VARCHAR(800)) | |
if object_id('tempdb..#tfssecurity') is not null | |
DROP TABLE #tfssecurity | |
CREATE TABLE #tfssecurity (id INT IDENTITY, resultant nVARCHAR(800)) | |
--TFSSecurity. Ensures the user is valid by querying for "Team Foundation Valid Users" (though you may have other groups). | |
-- If valid, uses their name for checkin. If not, uses a default (set in PS) and adds it to the Comment | |
INSERT INTO #tfssecurity | |
(resultant) | |
EXEC master..xp_cmdshell 'C:\tfs_cli\app_2013\tfssecurity /imx "Team Foundation Valid Users" /collection:http://yourtfserver:8080/tfs/DefaultCollection' | |
--find only users, parsing into a comparable field. | |
INSERT INTO @valid_tfs_logins (loginname) | |
SELECT | |
SUBSTRING(resultant, CHARINDEX(']',resultant)+2, CASE CHARINDEX('(', resultant) WHEN 0 THEN 200 ELSE CHARINDEX('(', resultant) - CHARINDEX(']',resultant)-3 end) | |
FROM #tfssecurity | |
WHERE resultant LIKE '%[[]U]%' | |
--go back 1 day, the first time you run. | |
IF (SELECT COUNT(*) FROM eventnotificationrec.dbo.TFS_Last_Time_Run) = 0 | |
BEGIN | |
INSERT INTO eventnotificationrec.dbo.TFS_Last_Time_Run VALUES (GETDATE()-1) | |
END | |
--find the last time we ran, so we're only getting in that range | |
SET @time_of_last_run = (select MAX(last_time_run) | |
FROM eventnotificationrec.dbo.TFS_Last_Time_Run) | |
--located here, as soon as possible before the query to get the list of events. We could change it to use the ID field instead. | |
SELECT @now = GETDATE() | |
SELECT @time_of_last_run AS last_run_time, @now AS now_run_time | |
-- can simplify this further. | |
-- Multiple changes to the same object don't need multiple checkins. Indexes, for instance, only need 1 checkin of the table. | |
-- will probably need a case statement since (for indexes, in this case) each would be a different object w/same targetobject | |
-- as of 1.1, each index change requires a separate checkin. | |
INSERT INTO @change_list | |
SELECT ServerName, EventType, LoginName, DatabaseName, SchemaName, ObjectName, TargetObjectName, TargetObjectType | |
FROM eventnotificationrec.dbo.ENAudit_View | |
WHERE EventType IN | |
( | |
--these lines are blocked out by the groupings below, use them to make it easier to read this. | |
'ALTER_FUNCTION','ALTER_PROCEDURE','ALTER_TABLE','ALTER_VIEW','CREATE_FUNCTION', | |
'CREATE_PARTITION_FUNCTION', 'CREATE_PARTITION_SCHEME', 'CREATE_PROCEDURE', 'CREATE_TABLE', 'CREATE_VIEW' | |
,'ALTER_TABLE' | |
,'Create_Type', 'Alter_Type' | |
,'ALTER_TRIGGER', 'ALTER_INDEX', 'ALTER_SPATIAL_INDEX' | |
,'CREATE_TRIGGER', 'CREATE_INDEX', 'CREATE_SPATIAL_INDEX' | |
,'ALTER_EXTENDED_PROPERTY','CREATE_EXTENDED_PROPERTY','DROP_EXTENDED_PROPERTY' | |
,'ALTER_AUTHORIZATION_DATABASE','ALTER_DATABASE' | |
) | |
AND NOT (EventType = 'ALTER_INDEX' AND commandtext LIKE '%reorg%') --ignore reorgs | |
AND NOT (EventType = 'ALTER_INDEX' AND commandtext LIKE '%REBUILD%' AND commandtext NOT LIKE '%fillfactor%') | |
AND insert_datetime > @time_of_last_run | |
AND insert_datetime <= @now | |
GROUP BY ServerName, EventType, LoginName, DatabaseName, SchemaName, ObjectName, TargetObjectName, TargetObjectType | |
ORDER BY MAX(insert_datetime) | |
select count(*) from @change_list AS number_of_items_to_do | |
--now that we have a list to process, invoke the powershell script for each. | |
--The powershell script does the work; this just tells it what's changed and needs to be done. | |
SELECT @min = MIN(id), @max = MAX(id) FROM @change_list | |
WHILE @min <= @max | |
BEGIN | |
SET @EventType = NULL | |
SET @loginname_temp = NULL | |
SET @loginname = NULL | |
SET @TargetObjectType = NULL | |
--Using TFSSecurity block above, change the loginname to be the actual loginname if they're a valid user | |
SELECT @loginname_temp = loginname FROM @change_list WHERE id = @min | |
IF EXISTS (SELECT * FROM @valid_tfs_logins WHERE loginname = @loginname_temp) | |
BEGIN | |
SET @loginname = ' -author "' + @loginname_temp + '" -comment "' | |
END | |
ELSE | |
BEGIN | |
SET @loginname = ' -comment "' + ISNULL(@Loginname_Temp,'blank.username') + ' ----- ' | |
END | |
--clear the error trap - we have 2, one transient and one permanent | |
TRUNCATE TABLE #holding_tank_for_errors | |
--easier to select this once; makes below code more readable. | |
SELECT @EventType = EventType, @TargetObjectType = TargetObjectType FROM @change_list WHERE id = @min | |
--basic call, comment has loginname & eventtype | |
IF @EventType IN ('ALTER_FUNCTION','ALTER_PROCEDURE','ALTER_VIEW','CREATE_FUNCTION', | |
'CREATE_PARTITION_FUNCTION', 'CREATE_PARTITION_SCHEME', 'CREATE_PROCEDURE', 'CREATE_TABLE', 'CREATE_VIEW') | |
or (@EventType IN ('ALTER_EXTENDED_PROPERTY','CREATE_EXTENDED_PROPERTY','DROP_EXTENDED_PROPERTY') AND @TargetObjectType = '') | |
BEGIN | |
SELECT @cmd = 'powershell -file c:\tfs_cli\sql_script_to_tfs.ps1 -server ' + servername | |
+ ' -database "' + databasename + '" -schematoscript "' + SchemaName + '" -objecttoscript "' + ObjectName + '"' | |
+ @LoginName + EventType + ' ' + ObjectName | |
+ CASE | |
WHEN TargetObjectName = '' THEN '' | |
WHEN TargetObjectName IS NOT NULL THEN ' on ' + TargetObjectName | |
ELSE '' END + '"' | |
FROM @change_List WHERE id = @min | |
END | |
--when we want to include the actual change in the comment, to get specifics like ALTER TABLE ADD COLUMN | |
ELSE | |
IF @EventType IN ('ALTER_TABLE') | |
BEGIN | |
SELECT @cmd = 'powershell -file c:\tfs_cli\sql_script_to_tfs.ps1 -server ' + servername | |
+ ' -database "' + databasename + '" -schematoscript "' + SchemaName + '" -objecttoscript "' + ObjectName + '"' | |
+ @LoginName + EventType + ' ' + ObjectName + '"' | |
FROM @change_List WHERE id = @min | |
END | |
--objects without schemas, so searching the SMO to match SchemaName won't find anything | |
ELSE | |
IF @EventType IN ('CREATE_TYPE', 'ALTER_TYPE') | |
BEGIN | |
SELECT @cmd = 'powershell -file c:\tfs_cli\sql_script_to_tfs.ps1 -server ' + servername | |
+ ' -database ' + databasename + ' -objecttoscript ' + ObjectName | |
+ @LoginName + EventType + ' ' + ObjectName + '"' | |
FROM @change_List WHERE id = @min --when scripted, they have no schema | |
END | |
--objects that are actually "part" of another object, and thus use the TargetObject to determine what to script out. | |
ELSE | |
IF @EventType IN ('ALTER_TRIGGER', 'ALTER_INDEX', 'ALTER_SPATIAL_INDEX', 'CREATE_TRIGGER', 'CREATE_INDEX', 'CREATE_SPATIAL_INDEX') | |
or (@EventType IN ('ALTER_EXTENDED_PROPERTY','CREATE_EXTENDED_PROPERTY','DROP_EXTENDED_PROPERTY') AND @TargetObjectType <> '') | |
BEGIN | |
SELECT @cmd = 'powershell -file c:\tfs_cli\sql_script_to_tfs.ps1 -server ' + servername | |
+ ' -database "' + databasename + '" -schematoscript "' + SchemaName + '" -objecttoscript "' + TargetObjectName + '"' | |
+ @Loginname + EventType + ' ' + ISNULL(ObjectName,'') + '.' + TargetObjectName + '"' | |
FROM @change_List WHERE id = @min | |
END | |
--database-level objects, such as CREATE or changes to a database | |
ELSE | |
IF @EventType IN ('ALTER_AUTHORIZATION_DATABASE','ALTER_DATABASE','CREATE_DATABASE') | |
BEGIN | |
SELECT @cmd = 'powershell -file c:\tfs_cli\sql_script_to_tfs.ps1 -server ' + servername | |
+ ' -database "' + databasename + '"' | |
+ @LoginName + EventType + ' ' + databasename + '"' | |
FROM @change_List WHERE id = @min | |
END | |
--run the powershell command, saving errors to a temp table | |
RAISERROR (@cmd, 0, 1) WITH NOWAIT | |
INSERT INTO #holding_tank_for_errors | |
EXEC master..xp_cmdshell @cmd | |
--if any errors during execution, save to a permanent table to look at later. | |
IF EXISTS | |
( | |
SELECT * | |
FROM #holding_tank_for_errors | |
WHERE resultant LIKE '%sql_script_to_tfs.ps1%' | |
) | |
BEGIN | |
INSERT INTO eventnotificationrec.dbo.TFS_Checkin_Errors (time_job_run, original_id, resultant) | |
SELECT GETDATE(), id, resultant | |
FROM #holding_tank_for_errors ORDER BY id | |
END | |
SET @min = @min + 1 | |
END | |
--and now that we're successful, add a record so we know when we ran. | |
INSERT INTO eventnotificationrec.dbo.TFS_Last_Time_Run (last_time_run) VALUES (@now) | |
GO |
Powershell:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<# | |
.SYNOPSIS | |
Will script an object from SQL Server and CHECKIN/ADD to TFS. | |
.EXAMPLE | |
sql_script_to_TFS.ps1 -server yourservername -Database yourdatabasname -ScriptType "FULL" -Author yourTFSname -Comment "full checkin of database" | |
#> | |
# Purpose - given parameters, script out an object from a SQL Server, using SMO, then check it into TFS. | |
#Scripting code gleefully copied from Phil Factor - the great parts that work are his, the errors are mine | |
# https://www.simple-talk.com/sql/database-administration/automated-script-generation-with-powershell-and-smo/ | |
#Other code from others copied and attributed below. | |
#mdb 2013/12/03 adding ScriptType so that we can add "create database"-specific changes ONLY. | |
# To do FULLs you will have to have -ScriptType = 'Full' parameter. By default it will do objects. | |
#mdb 2013/12/05 trying to better bullet-proof the app against TF failures, which appear to occur randomly | |
#mdb 2013/12/07 works great, though occasionally it cant script out an item. Trying to bulletproof that | |
#mdb 2013/12/16 broke the individual-item portion, fixed. | |
#mdb 2013/12/17 more cleanup and dealing with no objects found. | |
#mdb 2014/09/08 removing .exe references, and changing folder name, so 2013 works. | |
#mdb 2018/03/06 the -notmatch doesn't include usp_M* under certain circumstances. Changing to notlike | |
#param has to be the first line of the script. | |
param( | |
[Parameter(Mandatory=$true,Position=1)] | |
[string]$Server, | |
[Parameter(Mandatory=$true,Position=2)] | |
[string]$Database, | |
[string]$ScriptType ='Object', | |
[string]$SchemaToScript, | |
[string]$ObjectToScript, | |
[string]$Author, | |
[string]$Comment | |
) | |
#make sure not ObjectToScript blank if scripting out the entire DB; makes checkin better | |
#these are the parameters for testing | |
#$Server='sql_repository'# the server it is on | |
#$Database='model' # the name of the database you want to script as objects | |
#$SchemaToScript = 'dbo' | |
#$ObjectToScript = 'spy_tempdef' | |
#$Author = 'michael.bourgon' | |
#$ScriptType ='Object' | |
#$Comment = 'bourgon_test' | |
#setting up an error code for later | |
$myerror = 0 | |
cd c:\tfs_cli\en_workspace | |
if ($comment -eq '') | |
{ $comment = "generic EN checkin"} | |
if ($author -eq '') | |
{ $author = "erxnetwork\sqlservice"} | |
#field is mandatory, if we dont know, use a known-valid. | |
$ServerNameClean = "$($Server -replace '[\\\/]','__')" | |
clear | |
#writing this out for logging and troubleshooting. These are all the parameters except ScriptType | |
write-host $Server, $Database, $SchemaToScript, $ObjectToScript, $Author, $Comment | |
#TFS workspace folder - whatever you set it up as on your server | |
$DirectoryToSaveTo='C:\TFS_CLI\EN_Workspace' # the directory where you want to store them | |
# Load SMO assembly, and if we are running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries | |
$v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO') | |
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') { | |
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null | |
} | |
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoEnum') | out-null | |
set-psdebug -strict # catch a few extra bugs | |
$ErrorActionPreference = "stop" | |
$My='Microsoft.SqlServer.Management.Smo' | |
$srv = new-object ("$My.Server") $Server # attach to the server | |
if ($srv.ServerType-eq $null) # if it managed to find a server | |
{ | |
Write-Error "Sorry, but I could not find Server '$Server' " | |
return | |
} | |
$scripter = new-object ("$My.Scripter") $srv # create the scripter | |
#Add the various options we care about | |
$scripter.Options.ToFileOnly = $true | |
$scripter.Options.ExtendedProperties= $true # yes, we want these | |
$scripter.Options.DRIAll= $true # and all the constraints | |
$scripter.Options.Indexes= $true # Yup, these would be nice | |
$scripter.Options.Triggers= $true # This should be includede | |
$scripter.Options.AppendToFile = $False | |
$scripter.Options.AllowSystemObjects = $False | |
$scripter.Options.ClusteredIndexes = $True | |
$scripter.Options.DriAll = $True | |
$scripter.Options.ScriptDrops = $False | |
$scripter.Options.IncludeHeaders = $False #so you do not get the one line "scripted at..." which would be NOW. | |
#$scripter.Options.ToFileOnly = $True | |
#$scripter.Options.Indexes = $True | |
$scripter.Options.Permissions = $True | |
$scripter.Options.WithDependencies = $False | |
$scripter.Options.Bindings = $true | |
$scripter.Options.IncludeDatabaseRoleMemberships = $true | |
################################################# | |
#First, script out the database "create" itself.# | |
################################################# | |
if (($Database) -and $ObjectToScript -eq '') #if database has a value but there is no object, script out the DB. | |
{ | |
$db_scripter = new-object ("$My.Scripter") $srv # script out the database creation | |
$db_scripter.options=$scripter.options # with the same options | |
$db_scripter.options.filename="$($DirectoryToSaveTo)\$($ServerNameClean)\$($Database)\$($Database)_database_create.sql" # with the same options | |
#explcitly creating the path for the DB script here. We still need to do it for all the sub-types. | |
# Could probably move the $d declaration up here, but leaving it here for readability. | |
$SavePath="$($DirectoryToSaveTo)\$($ServerNameClean)\$($Database)" | |
# create the directory if necessary (SMO does not). | |
if (!( Test-Path -path $SavePath )) # create it if not existing | |
{Try { New-Item $SavePath -type directory | out-null } | |
Catch [system.exception]{ | |
Write-Error "error while creating '$SavePath' $_" | |
return | |
} | |
} | |
#Use TF to see if the object exists on our TFS server. | |
# Optimization idea: DIR the entire subfolder on a FULL and compare all at once. | |
$tf = &cmd /c "c:\TFS_CLI\App_2013\TF dir $/Randolph/$ServerNameClean/$Database/$($Database)_database_create.sql 2>&1" | |
# Running the TF calls this way as per Simon Ejsing to ignore the error state and capture the actual error message | |
# http://stackoverflow.com/questions/2095088/error-when-calling-3rd-party-executable-from-powershell-when-using-an-ide | |
# However, that also means we need | |
#Note that if the database create has not changed, it will still attempt to CHECKIN, but TFS will ignore as it is the same. | |
if ($tf -like "No items match*" -or $tf -like "*is not found or not supported*") | |
{ | |
"database script does not exist; scripting out and ADDing to TFS" | |
if(Test-Path -Path $db_scripter.options.filename) | |
{ | |
#delete the file manually, since we have seen permission issues where the $script cannot overwrite. | |
$deleteme = "$SavePath\$($Database)_database_create.sql" | |
$deleteme | |
try | |
{ | |
remove-item "$SavePath\$($Database)_database_create.sql" -force | |
} | |
catch | |
{ | |
$error[0].Exception | |
} | |
} | |
#putting in a try/catch so we get error messages if it breaks, and it can continue. | |
try | |
{ | |
$db_scripter.Script($srv.Databases[$Database]) # do it | |
} | |
Catch | |
{ | |
"Error Message trying to script out $SavePath\$Filename" | |
$error[0].Exception | |
} | |
"database create script done" | |
$tf = &cmd /c "c:\TFS_CLI\App_2013\TF add $/Randolph/$ServerNameClean/$Database/$($Database)_database_create.sql 2>&1" | |
$tf | |
#use mass checkin at the end | |
} | |
else | |
{ | |
"database script exists; get, check out, script to override, check in" | |
$tf = &cmd /c "c:\TFS_CLI\App_2013\TF get ""$/Randolph/$ServerNameClean/$Database/$($Database)_database_create.sql"" /noprompt 2>&1" | |
"database script GET results" | |
$tf | |
$tf = &cmd /c "c:\TFS_CLI\App_2013\TF checkout ""$/Randolph/$ServerNameClean/$Database/$($Database)_database_create.sql"" 2>&1" | |
"database script CHECKOUT results" | |
$tf | |
"database checkout done" | |
#If the file exists, manually delete; we have seen permission issues where $script cannot overwrite. | |
if(Test-Path -Path $db_scripter.options.filename) | |
{ | |
$deleteme = "$SavePath\$($Database)_database_create.sql" | |
$deleteme | |
try | |
{ | |
#bug exists with standard remove - if there are read-only items in the same folder, -force is required | |
remove-item "$SavePath\$($Database)_database_create.sql" -force | |
} | |
catch | |
{ | |
$error[0].Exception | |
} | |
} | |
#putting in a try/catch so we get error messages if it breaks, and it can continue. | |
try | |
{ | |
$db_scripter.Script($srv.Databases[$Database]) # do it | |
} | |
Catch | |
{ | |
"Error Message trying to script out $SavePath\$Filename" | |
$error[0].Exception | |
} | |
"database script out done" | |
#use mass checkin at the end | |
} | |
} | |
########################### | |
## Scripting out Objects ## | |
########################### | |
# we now get all the object types except extended stored procedures | |
# first we get the bitmap of all the object types we want | |
$all =[long] [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::all ` | |
-bxor [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::ExtendedStoredProcedure | |
# and we store them in a datatable | |
$d = new-object System.Data.Datatable | |
# get almost everything; skipping most service broker, information_schema, system_views, certificates (cannot be scripted) | |
# there are other items that may need to be skipped, like SymmetricKeys in SSISDB | |
# Yes, I realize the irony in skipping SB given that it is powering this. | |
#putting in a try/catch so we get error messages if it breaks, and it can continue. | |
try | |
{ | |
$d=$srv.databases[$Database].EnumObjects([long]0x1FFFFFFF -band $all) | ` | |
Where-Object {$_.Schema -ne 'sys'-and $_.Schema -ne "information_schema" -and $_.DatabaseObjectTypes -ne 'ServiceBroker' ` | |
-and $_.DatabaseObjectTypes -ne 'Certificate' ` | |
-and $_.DatabaseObjectTypes -ne 'MessageType' ` | |
-and $_.DatabaseObjectTypes -ne 'ServiceContract' ` | |
-and $_.DatabaseObjectTypes -ne 'ServiceQueue' ` | |
-and $_.DatabaseObjectTypes -ne 'ServiceRoute' ` | |
-and ($SchemaToScript -eq '' -or $_.Schema -eq $SchemaToScript) ` | |
-and (($ObjectToScript -eq '' -and $ScriptType -eq 'Full') -or $_.Name -eq $ObjectToScript) ` | |
-and ($_.Name -notlike 'sp_MS*') } | |
# mdb 2013/11/07 previous line skips replication objects. This comment below code as comment lines break extended 1-liner. | |
} | |
Catch | |
{ | |
"Error Message trying to enumerate the database - may be logshipped or being restored" | |
$myerror = 1 | |
$error[0].Exception | |
} | |
# List every item that we are going to do | |
$d = $d | sort -Property DatabaseObjectTypes,Schema,Name | |
$d | select databaseobjecttypes, schema, name | |
if ($d.Count -gt 10000) | |
{ | |
"skipping the database objects - more than 10000" | |
} | |
# Now write out each scriptable object as a file in the directory you specify | |
#it appears that an empty array never actually enters the FOREACH, leaving variables unset | |
# -and -$d.Count -ne 0 | |
if ($myerror -eq 0 -and $d.Count -lt 10001) #20k of objects takes up 5gb of RAM in the PS script and causes problems | |
{ | |
$d| FOREACH-OBJECT { # for every object we have in the datatable. | |
"" #blank line so each block of error messages is separated out | |
$SavePath="$($DirectoryToSaveTo)\$ServerNameClean\$Database\$($_.DatabaseObjectTypes)" | |
# create the directory if necessary (SMO does not). | |
if (!( Test-Path -path $SavePath )) # create it if not existing | |
{Try { New-Item $SavePath -type directory | out-null } | |
Catch [system.exception]{ | |
Write-Error "error while creating '$SavePath' $_" | |
return | |
} | |
} | |
# tell the scripter object where to write it, and make sure it is actually writeable | |
if ($_.schema) | |
{ | |
$Filename = "$($_.schema -replace '[\\\/\:\.]','-').$($_.name -replace '[\\\/\:\.\ ]','-').sql"; | |
} | |
else | |
{ | |
$Filename = "$($_.name -replace '[\\\/\:\.]','-').sql"; | |
} | |
$scripter.Options.FileName = "$SavePath\$Filename" | |
$scripter.Options.FileName #print it out so we know what is being done | |
$UrnCollection = new-object ('Microsoft.SqlServer.Management.Smo.urnCollection') | |
$URNCollection.add($_.urn) | |
############################ | |
# TFS code for each object # | |
############################ | |
#Use TF to see if the object exists on our TFS server | |
"checking to see if object exists" | |
$tf = &cmd /c "c:\TFS_CLI\App_2013\TF dir $/Randolph/$ServerNameClean/$Database/$($_.DatabaseObjectTypes)/$Filename 2>&1" | |
# Running all the TF commands this way as per Simon Ejsing to ignore the error state and capture the actual error message. | |
# http://stackoverflow.com/questions/2095088/error-when-calling-3rd-party-executable-from-powershell-when-using-an-ide | |
if ($tf -like "No items match*" -or $tf -like "*is not found or not supported*") | |
{ | |
"no items match; scripting out and ADDing to TFS" | |
try | |
{ | |
$scripter.script($URNCollection) | |
} | |
Catch | |
{ | |
"Error Message trying to script out $SavePath\$Filename" | |
$error[0].Exception | |
} | |
"script done" | |
$tf = &cmd /c "c:\TFS_CLI\App_2013\TF add /noprompt ""$/Randolph/$ServerNameClean/$Database/$($_.DatabaseObjectTypes)/$Filename"" 2>&1" | |
$tf | |
#mdb 2013/11/07 only do ONE checkin at the end if we are doing an entire database; all will have the same comment | |
if ($ObjectToScript -ne '') | |
{ | |
$tf = &cmd /c "c:\TFS_CLI\App_2013\TF checkin /author:$author /comment:""$comment"" /noprompt 2>&1" | |
$tf | |
} | |
} | |
else | |
{ | |
"item exists; get, check out, script to override, check in" | |
#noprompt causes it to crash, virtually every time | |
$tf = &cmd /c "c:\TFS_CLI\App_2013\TF get ""$/Randolph/$ServerNameClean/$Database/$($_.DatabaseObjectTypes)/$Filename"" 2>&1" | |
$tf | |
$tf = &cmd /c "c:\TFS_CLI\App_2013\TF checkout ""$/Randolph/$ServerNameClean/$Database/$($_.DatabaseObjectTypes)/$Filename"" 2>&1" | |
$tf | |
#Delete file before scripting; we have seen permission issues. | |
if(Test-Path -Path $scripter.options.filename) | |
{ | |
try | |
{ | |
remove-item "$SavePath\$Filename" -force | |
} | |
catch | |
{ | |
$error[0].Exception | |
} | |
} | |
try | |
{ | |
$scripter.script($URNCollection) | |
} | |
Catch | |
{ | |
"Error Message trying to script out $SavePath\$Filename" | |
$error[0].Exception | |
} | |
#mdb 2013/12/03 making this part only run if it is a specific object; that way we can rerun an entire database | |
if ($ObjectToScript -ne '') | |
{ | |
$tf = &cmd /c "c:\TFS_CLI\App_2013\TF checkin /author:$author /noprompt /comment:""$comment"" ""$/Randolph/$ServerNameClean/$Database/$($_.DatabaseObjectTypes)/$Filename"" 2>&1" | |
$tf | |
} | |
} | |
} | |
} | |
#If it is a mass add or a database-specific, CHECKIN now. | |
if ($ObjectToScript -eq '') | |
{ | |
"final mass checkin" | |
$tf = &cmd /c "c:\TFS_CLI\App_2013\TF checkin /author:$author /comment:""$comment"" /noprompt 2>&1" | |
#$tf we do not need this one here because it will be shown below | |
#$tf | |
"mass checkin done" | |
} | |
"--------------------------------------------------------------------------" | |
#if the checkin failed, UNDO so the next one does not make it worse. | |
#The next checkin would probably fix it, but I have had to go back and manually undo. Not fun. | |
#mdb 20131107 If there were any items to check in, get the results. Throws an error otherwise. | |
#using Stej code to verify the variable exists; if no objects, $tf is never set, so it bombs here | |
# http://stackoverflow.com/questions/3159949/in-powershell-how-do-i-test-whether-or-not-a-specific-variable-exists-in-global | |
if (Test-Path variable:local:tf) | |
{ | |
if ($tf -like "Changeset * checked in." -or $tf -like "There are no remaining changes to check in.") | |
{ | |
#mdb 20131107 If there were any items to check in, get the results. Throws an error otherwise. | |
if ((Test-Path variable:local:d) -and $ObjectToScript -eq '') | |
{ | |
$tf | |
} | |
} | |
else | |
{ | |
"changes not made - errorlog follows" | |
$tf | |
"================================UNDO BEGINS==================================" | |
$tf = &cmd /c "c:\TFS_CLI\App_2013\TF undo ""$/Randolph/$ServerNameClean/$Database/*.*"" /recursive 2>&1" | |
$tf | |
"=================================UNDO ENDS===================================" | |
} | |
} | |
else | |
{ | |
"No objects found, nothing done" | |
} | |
#TFS rollback code, should look something like this | |
#c:\TFS_CLI\App_2013\TF undo $/Randolph/$ServerNameClean/VOAgent/DatabaseRole/*.* | |
#No files checked in due to conflicting changes. These conflicting changes have been automatically resolved. Please try the check-in again. | |
#rolling back code: c:\TFS_CLI\App_2013\TF undo $/Randolph/cm-01/VOAgent/DatabaseRole/*.* | |
#for some reason "override:" did not work. | |
#C:\TFS_CLI\EN_Workspace\server\file.sql | |
#item exists; get, check out, script to override, check in | |
#All files are up to date. | |
No comments:
Post a Comment