April 15, 2011 at 10:38 am
Hi all,
I'm having strange problems with the transfer.
In SQL 2K, there are two views dbo.sysconstraints and dbo.syssegments. They are labelled as system objects.
I have a PS script to transfer my DBs between SQL 2K sp4 and SQL 2K8-R2 which works fine on 51 out of 53 DBs. On two DBs, I get no end of pain from these two views.
I've come to the conclusion that the best workaround would be to NOT transfer them to SQL 2K8 (since they're deprecated anyway).
How can I exclude them explicitly in the PS code?
Note that I want to transfer all views except those two, without knowing in advance what views exist in a given DB.
Here's the relevant transfer object properties:
#Transfer object properties
$xfr.PreserveDbo = $true
$xfr.DropDestinationObjectsFirst = $true
$xfr.CopyAllObjects = $false
$xfr.CopyAllDatabaseTriggers = $true
$xfr.CopyAllDefaults = $true
$xfr.CopyAllStoredProcedures = $true
$xfr.CopyAllTables = $true
$xfr.CopyAllUserDefinedFunctions = $true
$xfr.CopyAllUserDefinedTableTypes = $true
$xfr.CopyAllViews = $true
$xfr.CopyData = $true
$xfr.CopyAllRoles = $true
$xfr.CopyAllSchemas = $false
$xfr.CopyAllLogins = $false
$xfr.CopyAllUsers = $false
$xfr.CopySchema = $true
# Transfer object options
$xfr.Options.DriAll = $true
$xfr.Options.Triggers = $true
$xfr.Options.Permissions = $true
$xfr.Options.ClusteredIndexes = $true
$xfr.Options.NonClusteredIndexes = $true
$xfr.Options.AllowSystemObjects = $false
$xfr.Options.IncludeDatabaseRoleMemberships = $false
$xfr.Options.ContinueScriptingOnError = $true
April 15, 2011 at 10:41 am
Don't transfer system objects. System objects are for SQL to create only
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2011 at 10:44 am
Hi Gail,
I was hoping that this would prevent those objects being scripted, but it appears not.
$xfr.Options.AllowSystemObjects = $false
I certainly don't want any system objects transferred.
April 15, 2011 at 10:47 am
On 2000 there were a couple system objects that were system objects but were not marked system. Is there a way in powershell to check the ISMSShipped property? If so, exclude any objects where that is true.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2011 at 11:11 am
Thanks Gail, that moved me forward.
So now I have the correct collection of views in $Views
$Views = $sdb.Views | Where-Object {$_.IsSystemObject -eq $false}
So I've set TransferAllViews = $False, how do I add that collection to the transfer ObjectList?
P
April 15, 2011 at 12:20 pm
schleep (4/15/2011)
So I've set TransferAllViews = $False, how do I add that collection to the transfer ObjectList?
Err, you're asking me? I can barely spell powershel.
Let me get some assistance here...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2011 at 12:34 pm
schleep (4/15/2011)
So I've set TransferAllViews = $False, how do I add that collection to the transfer ObjectList?
How are the other objects you are transfering added?
Can you provide the full code you are using?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 15, 2011 at 12:45 pm
See above, in original post: rather than adding items, it's setting properties of the transfer object.
This creates the transfer object:
$xfr = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Transfer -argumentlist $sdb
Then the code above sets the properties, some of which flag the inclusion or exclusion of classes of DB objects.
I just tried adding (literally) the views array to the transferobject.objectlist array:
$xfr.ObjectList = $xfr.ObjectList + $Views...
Hasn't puked yet...txtPost_CommentEmoticon(':-)');
I'll post the outcome in a few minutes.
------------------------
On another note: In another DB, the transfer is failing because a CREATE FUNCTION is scripted prior to a table it references.
How to work around this issue?
Do I have to script functions and sprocs after other objects, and do the transfers in two steps?
April 15, 2011 at 12:57 pm
Boooo. It didn't work, sysconstraints is definitely not in the views array, and it was still scripted.
April 17, 2011 at 3:04 pm
err, backup and restore?
---------------------------------------------------------------------
April 18, 2011 at 6:57 am
George,
Thanks, but we're not really interested in that approach.
April 18, 2011 at 7:20 am
May I ask why not? It's certainly going to be far less problematic, probably faster too.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2011 at 7:23 am
Gail just asked my question for me.
Is it because this is a repeating process or just because you dont want to bring the users over?
---------------------------------------------------------------------
April 18, 2011 at 7:25 am
Mixed-mode logins (not my choice) w/ different sets of users on source and destination.
And George, yes to both: this is a nightly thing.
April 18, 2011 at 7:45 am
backup\restore still a possibility or SSIS. If this is nightly I can see your point about backup\restore as there are number of things you should run post the upgrade.
If you do use backup\restore to get rid of the users derive the SQL you need thus:
change default schema to dbo
set nocount on
select 'alter user '+ name+ ' with default_schema = dbo' from sys.sysusers
where uid > 4 and isntgroup = 0 and issqlrole = 0 and isapprole = 0
order by name
drop the schema
select 'drop schema ['+ name+ '] ' from sys.sysusers
where uid > 4 and issqlrole = 0 and isapprole = 0
order by name
and then drop your users
---------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply