Moving databases to a new server

This weekend I was retiring an old server, replacing it with a new server.  The old server had 3 different SQL instances with hundreds of mounted databases.  To move them all to the new server (and get them re-mounted again) I figured there had to be some SQL Scripts already written for the task and after a brief search I found an excellent article with exactly the scripts that I needed here:

https://www.mssqltips.com/sqlservertip/1786/auto-generate-sql-server-database-attach-and-detach-scripts/ 

The important parts from that article were the script generator to detach the databases:

-- Build the sp_detach_db command (ONLINE, non-system databases only):
SELECT DISTINCT 'exec sp_detach_db ''' + DB_NAME(dbid) + ''';'
FROM master.dbo.sysaltfiles
WHERE SUBSTRING(filename,1,1) IN ('E','F')
AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE'
AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model')
GO

And the script generator to subsequently mount the databases (again, 100% of the credit for this script goes to Michelle Gutzait):

— Build the sp_attach_db:

SET NOCOUNT ON
DECLARE     @cmd        VARCHAR(MAX),
            @dbname     VARCHAR(200),
            @prevdbname VARCHAR(200)

SELECT @cmd = ”, @dbname = ‘;’, @prevdbname = ”

CREATE TABLE #Attach
    (Seq        INT IDENTITY(1,1) PRIMARY KEY,
     dbname     SYSNAME NULL,
     fileid     INT NULL,
     filename   VARCHAR(1000) NULL,
     TxtAttach  VARCHAR(MAX) NULL
)

INSERT INTO #Attach
SELECT DISTINCT DB_NAME(dbid) AS dbname, fileid, filename, CONVERT(VARCHAR(MAX),”) AS TxtAttach
FROM master.dbo.sysaltfiles
WHERE dbid IN (SELECT dbid FROM master.dbo.sysaltfiles
            WHERE SUBSTRING(filename,1,1) IN (‘E’,’F’))
            AND DATABASEPROPERTYEX( DB_NAME(dbid) , ‘Status’ ) = ‘ONLINE’
            AND DB_NAME(dbid) NOT IN (‘master’,’tempdb’,’msdb’,’model’)
ORDER BY dbname, fileid, filename

UPDATE #Attach
SET @cmd = TxtAttach = 
            CASE WHEN dbname <> @prevdbname
            THEN CONVERT(VARCHAR(200),’exec sp_attach_db @dbname = N”’ + dbname + ””)
            ELSE @cmd
            END +’,@filename’ + CONVERT(VARCHAR(10),fileid) + ‘=N”’ + filename +””,
    @prevdbname = CASE WHEN dbname <> @prevdbname THEN dbname ELSE @prevdbname END,
    @dbname = dbname
FROM #Attach  WITH (INDEX(0),TABLOCKX)
OPTION (MAXDOP 1)

SELECT TxtAttach
FROM
(SELECT dbname, MAX(TxtAttach) AS TxtAttach FROM #Attach
GROUP BY dbname) AS x

DROP TABLE #Attach
GO

I noticed however, that many of the files I had attached had massive sql .ldf (log) files.  Since none of my databases needed to run with full logging, I could go ahead and shrink the log files without any issue.  Shrinking the log files had the effect of reducing the data to move from server A to B by nearly 1TB.  Here’s the script, courtesy of: http://dba.stackexchange.com/questions/358/how-do-i-shrink-all-files-quickly-for-all-databases

SELECT
      ‘USE [‘ + d.name + N’]’ + CHAR(13) + CHAR(10)
    + ‘DBCC SHRINKFILE (N”’ + mf.name + N”’ , 0, TRUNCATEONLY)’
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM
         sys.master_files mf
    JOIN sys.databases d
        ON mf.database_id = d.database_id
WHERE d.database_id > 4;

This generates the shrink commands using the syntax:

USE [MountedDatabaseName]
DBCC SHRINKFILE (N’Demo Data SQL Master_dat’ , 0, TRUNCATEONLY)

The thing that’s a bit strange to me at least about the SHRKINFILE command is that it uses the internal database name instead of the mounted name.  So having a script generator like this one to create the script is invaluable!

The last thing that most of these articles do not mention is that if other users have detached databases in the folder structure that you’re trying to move, then their user accounts (or the SQL Service account if they were using SQL Auth) will then have exclusive security permissions to the detached .mdf/.ndf/.ldf files.

That means you will hit file system permission issues, so the way that you solve that is using an admin account, run the following commands from a command prompt:

First, change to the root folder where your databases are mounted on the old server, then:

1. Use the TakeOwn command to take ownershp of all of the files from the current folder (dot) and sub-folders /R:

TakeOwn /F . /R

2. Use the cacls command to grant permission to the physical files:

cacls * /G Everyone:F /T

Note: in my case  just granted “everyone” full permission to the SQL Database files, because when I move these files to the destination server the destination server’s folder permissions are going to impose new restrictions.

MSComCTL.ocx broken in Microsoft Security Update KB3039794 or KB2920727 or KB2881029 January 12, 2016

This week’s Microsoft Security update includes a breaking change to MSComCtl.ocx located on most computers in C:\Windows\SysWow6432\mscomctl.ocx.  The update has a different title / KB # depending on the version of Microsoft Office you have installed.  The ones that I have encountered so far include:

Security Update for Microsoft Office 2010 (KB2881029) 32-Bit Edition: Link:
https://support.microsoft.com/en-us/kb/2881029

Security Update for Microsoft Office 2013 (KB3039794) 32-Bit Edition: Link: https://support.microsoft.com/en-us/kb/3039794 

Security Update for Microsoft Office 2016 (KB2920727) 32-Bit Edition: Link: https://support.microsoft.com/en-us/kb/2920727

In all cases, the update replaces mscomctl.ocx with a new version 6.01.9846.

Microsoft had previously “broken” this control before, however last time it happened, the issue was with the registry.  A simple work around of registering an old version and re-registering the current version typically worked to fix the issue.

This time, that work around does not appear to work.

Users of Blue Link Elite that are affected will see an “Object doesn’t support this property or method” error message when they launch the software: “The OpenForm action was canceled” followed by:

clip_image001

As of this writing, the temporary work around is to un-install this update.  Blue  Link is working on a permanent fix that will allow the application to work with the new version of the control in place.  Until then, the approach to remove the update is different depending on the version of the operating system that you have installed.

How to Remove and Block the KB on different Windows Versions

Windows 10 / Office 2016 32-bit example

To remove the already installed update type “View installed updates” into your search bar and open the View installed updates applet:

image

Select the Security Update for Microsoft Office _____ (KB_______) that matches your version of Office and the KB# that updates mscomctl.ocx, then click the Uninstall button at the top of the screen.

Note: Depending on your windows update settings, it may almost immediately attempt to RE-INSTALL the update, so it is important that when this step is complete that you do not unnecessarily delay before performing the next step of blocking the update.

To Block the update on Windows 10 you now have to download a special tool since Microsoft removed the built-into-the-os version of the feature.

If running Windows 10, you’ll have to download the wushowhide.diagcab file from here:

https://support.microsoft.com/en-us/kb/3073930 

image

Click Next, To Block the update if already un-installed, choose the hide option and then you’ll be presented with a list of updates that have NOT YET BEEN APPLIED to your machine.  If the KB is in this list, you may selected it and then click through the wizard.  This will prevent it from being auto-reapplied:

image

image

image

The procedure on Windows 8.1, and Windows 7 is different.  This blog will be updated on a regular basis as new information is made available.

Note: If you can figure out a way to restore functionality without removing the update please post a comment below.

 

UPDATE: 2016-02-09: Orphaned Registry Keys are the issue

What we have been able to determine is that as Microsoft patches the mscomctl.ocx file new GUIDs get created.  The old GUIDs then become pointers to the new GUIDs, but on some cases, those old GUIDs are still pointing to other GUIDs which now no longer exist.  To fix that problem, those old invalid pointer GUIDs need to be deleted, but Microsoft’s installer for the KB does NOT detect and delete the bogus pointers successfully resulting in the controls not working.

One of our developers has created a utility that will crawl through the mscomctl related registry keys, deleting most.  When that utility is done running he has instructed us to then copy/paste some additional registry deletion commands to an admin command prompt.

Since I don’t expect readers of this blog to simply trust running a .net executable I’ve asked the developer to give me the files and the source code so that we can post it here. 

UPDATE: 2016-04-20: I wasn’t able to get the source code, but I am making a version available for download here.

It should go without saying that you should of course BACKUP your registry before you run any kind of registry script as a best practice.  I cannot assume any responsibility for your actions running deletion scripts against the Windows registry.  That said, our techs are now able to follow these steps and have this issue fixed 99% of the time using the latest security fix version of mscomctl.ocx:

Step 1: unregister the current file: From an Administrator command prompt:

  • change to the folder containing the mscomctl.ocx file (C:\WIndows\System32\ if 32-bit or C:\Windows\SysWow64 on a 64-bit machine).
  • regsvr32 /u mscomctl.ocx

Step 2: Run the BLRegClean_Net.exe utility (Source code coming soon).

Step 3: BACKUP YOUR REGISTRY (if this is the first time you’re trying this for example)

From an administrator command prompt run the following additional deletion statements.

reg delete "HKLM\SOFTWARE\WOW6432Node\Microsoft\Internet Explorer\ActiveX Compatibility\{DD9DA666-8594-11D1-B16A-00C0F0283628}" /f
reg delete "HKLM\SOFTWARE\WOW6432Node\Microsoft\Internet Explorer\ActiveX Compatibility\{2C247F23-8591-11D1-B16A-00C0F0283628}" /f
reg delete "HKLM\SOFTWARE\WOW6432Node\Microsoft\Internet Explorer\ActiveX Compatibility\{F91CAF91-225B-43A7-BB9E-472F991FC402}" /f
reg delete "HKLM\SOFTWARE\WOW6432Node\Microsoft\Internet Explorer\ActiveX Compatibility\{BDD1F04B-858B-11D1-B16A-00C0F0283628}" /f
reg delete "HKLM\SOFTWARE\WOW6432Node\Microsoft\Internet Explorer\ActiveX Compatibility\{996BF5E0-8044-4650-ADEB-0B013914E99C}" /f
reg delete "HKLM\SOFTWARE\WOW6432Node\Microsoft\Internet Explorer\ActiveX Compatibility\{979127D3-7D01-4FDE-AF65-A698091468AF}" /f
reg delete "HKLM\SOFTWARE\WOW6432Node\Microsoft\Internet Explorer\ActiveX Compatibility\{35053A22-8589-11D1-B16A-00C0F0283628}" /f
reg delete "HKLM\SOFTWARE\WOW6432Node\Microsoft\Internet Explorer\ActiveX Compatibility\{F08DF954-8592-11D1-B16A-00C0F0283628}" /f
reg delete "HKLM\SOFTWARE\WOW6432Node\Microsoft\Internet Explorer\ActiveX Compatibility\{8E3867A3-8586-11D1-B16A-00C0F0283628}" /f
reg delete "HKLM\SOFTWARE\WOW6432Node\Microsoft\Internet Explorer\ActiveX Compatibility\{627C8B79-918A-4C5C-9E19-20F66BF30B86}" /f
reg delete "HKLM\SOFTWARE\WOW6432Node\Microsoft\Internet Explorer\ActiveX Compatibility\{1EFB6596-857C-11D1-B16A-00C0F0283628}" /f
reg delete "HKLM\SOFTWARE\WOW6432Node\Microsoft\Internet Explorer\ActiveX Compatibility\{24B224E0-9545-4A2F-ABD5-86AA8A849385}" /f
reg delete "HKLM\SOFTWARE\WOW6432Node\Microsoft\Internet Explorer\ActiveX Compatibility\{66833FE6-8583-11D1-B16A-00C0F0283628}" /f
reg delete "HKLM\SOFTWARE\WOW6432Node\Microsoft\Internet Explorer\ActiveX Compatibility\{7DC6F291-BF55-4E50-B619-EF672D9DCC58}" /f
reg delete "HKLM\SOFTWARE\WOW6432Node\Microsoft\Internet Explorer\ActiveX Compatibility\{C74190B6-8589-11D1-B16A-00C0F0283628}" /f
reg delete "HKLM\SOFTWARE\WOW6432Node\Microsoft\Internet Explorer\ActiveX Compatibility\{9181DC5F-E07D-418A-ACA6-8EEA1ECB8E9E}" /f
reg delete "HKLM\SOFTWARE\WOW6432Node\Microsoft\Internet Explorer\ActiveX Compatibility\{95F0B3BE-E8AC-4995-9DCA-419849E06410}" /f

Step 4: Re-Register the mscomctl.ocx file

  • change to the folder containing the mscomctl.ocx file (C:\WIndows\System32\ if 32-bit or C:\Windows\SysWow64 on a 64-bit machine).
  • regsvr32 /u mscomctl.ocx

In most cases these steps alone are sufficient to solve the problem.

In the cases where this has not worked, the technique of register an older version of mscomctl.ocx and then re-register the current version has worked after performing the steps above.

 

Skype for Business 2016 from Office 2016 media endless authentication loop

For those of you who have upgraded to Microsoft Office 2016 and are using Microsoft Skype for Business 2016 with Microsoft Office 365, you may have noticed a seemingly endless authentication loop where after having successfully signed in with your Office 365 credentials, you keep getting prompted to indicate if this is a work or Microsoft Account.

This basically  makes Skype for Business 2016 (Lync’s new name) unusable so we opened a case with Microsoft to try to get a fix.

The result of the case was as follows:

1. Exit out of Skype for Business, and delete following files:

C:\Users\profile\AppData\Local\Microsoft\Office\15.0\Lync\Tracing   

2. Make sure you empty Lync folders under:        

C:\Users\profile\AppData\Local\Microsoft\Office\15.0

C:\Users\profile\AppData\Roaming\Microsoft\Office\15.0

3. Create these two DWORD32 entries

HKCU\SOFTWARE\Microsoft\Office\16.0\Common\Identity\EnableADAL

REG_DWORD

0

HKCU\SOFTWARE\Microsoft\Office\16.0\Common\Identity\Version

REG_DWORD

1

4. You may have to reboot the computer.

Start Skype. You will be prompted once to enter User/Pass to access corporate address book.

image

 

The other issue that we’ve noticed with Skype for Business 2016 is that after a few minutes of use the backspace key stops working in message replies and you’re stuck having to highlight words that you want to delete and use the DEL key instead.  We haven’t got a fix for that one yet, so my personal recommendation is to just avoid Skype for Business 2016 completely and revert to Skype for Business 2015 which is part of the Microsoft Office 2013 media.

To do this, in Programs and Features, select Microsoft Office 2016 and choose change.

Remove the checkbox beside Skype for Business 2016.

When that’s done, go back into Programs and Features and this time select Microsoft Office 2013 (assuming that it is still listed) and choose Change.

Check the checkbox beside Skype for Business 2015.

 

When done, you’re back up and running on the stable release of Lync (Skype for Business 2015) and aren’t stuck in endless authentication loops and you can actually use the backspace key.

I’ll update the article later when we get a solution to the backspace key.  For the moment we’re going to block the upgrade of Skype for Business from our volume media setup files.

Deleting User Profiles to which you do not have permission

Every once in a while I encounter user profiles for employees who have long since left the company that need to be deleted.

In the example below, I have a user account: "dmacdonald" that I want to delete.  The profile consumes 2 GB of storage, their user data folder another 6GB.

Step 1: Delete the user Profile from User Profiles tool:

The fastest way to get to the System Utility for user profiles:

clip_image001

Is to just key “User Profile” into the start menu and choose the  “Configure advanced user profile properties” link.

image

You can then select a profile, and then click the “Delete” button.

image

Normally this is all that’s required to delete the files and folders.

But this time, although it DID delete the profile from the User Profile utility and did delete the \Users\dmacdonald folder it did not actually delete the UserData files.  Those files if you attempt to access them still show Permission Denied, but using a tool like SpaceSniffer.exe I could see that the folder was still consuming nearly 6GB of storage so I didn’t just want to leave it around.

clip_image004

You could click through the continue boxes, and manually try to seize ownership of EACH sub-folder one at a time, and then after doing so grant yourself permission to the folder, but you don’t have time for that.  So instead, use these commands:

To fix this run these commands from an administrator command prompt:

takeown /F dmacdonald /R /D "Y"

Followed by:

icacls dmacdonald /grant Everyone:F /T

After that you can access the files and/or delete them.

Cisco AnyConnect Failed to initialize connection subsystem after applying MS15-011

After applying recent security fixes for Windows 8.1 (https://technet.microsoft.com/library/security/MS15-011) my Cisco ANYConnect client failed to load.  Error:

Cisco AnyConnect: Failed to Initialize connection subsystem.

I’m not sure what it is about the most recent security updates that made it stop working, but I found that by setting it into Windows 7 Compatibility mode the issue was resolved.

The registry hack for doing this was:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows
NT\CurrentVersion\AppCompatFlags\Layers

Add a string value with the string name set as the full path to this file:

C:\Program Files (x86)\Cisco\Cisco AnyConnect Secure Mobility Client\vpnui.exe

Then set the string value to:

$ Win7RTM

 

image

After making the registry change, right-click the AnyConnect icon, and choose Quit:

image 

Then re-launch the AnyConnect client. and all seems to work.

Dell M3800 missing pause/break key

I love my Dell M3800 but shortly after unboxing I realized that there was no PAUSE/BREAK key on the keyboard so breaking into code was not looking good.

I eventually found a registry hack to re-map another key on the keyboard, but then I loss the functionality of that key. 

Finally a co-worker of mine discovered that pressing CTRL+Fn+F12 = CTRL+BREAK!

Windows 10 Beta–how to sign into Domain instead of Microsoft Account

After installing the Windows 10 Enterprise beta the first thing you’ll notice is that Microsoft tries to make you sign into a Microsoft account immediately instead of giving you the option to first join your domain.

If you sign in immediately and then join the domain later, you’ll end up with two different profiles, both loaded with Microsoft Profile stuff, which is NOT A GOOD THING.

image

There’s no obvious way to “not” sign into the Microsoft account and instead join the domain.

So here’s the work around I’ve come up with.

At this point in the installation, disconnect from the Internet by either unplugging a physical netwok cable, entering airplane mode if you have the switch on your computer to do so, or otherwise just break your connection to the Internet.  If testing in a virtual machine, enter the settings of the VM< and either change the network adapter to “Host Only” such as in VMWare workstation
image

image

or if using a Windows Hyper V server, open Settings…

image

image

Then change the Networking Adapter Virtual Switch to “Not connected”.

When that step is complete, click the Back arrow at the top left of the screen to return to the previous screen, then click the “Next” button again to be presented with the old “Local Account” creation tool:

image

 

On this screen create an account name such as “Admin” – something OTHER THAN the name that you intend to log into this computer with later when joined to the domain.  This step is important to avoid ending up with your C:\User\ folder being called YourName.DomainName instead of just YourName.

When done, re-connect the machine to the network, then join the machine to the domain:

image

Type “Join a domain” in the box at the bottom of the screen.

image

Click “Change”

image

Enter the computer name that you want, the domain name that you want, then have a domain admin or user with add computer rights on the domain to authenticate to the domain and you’re set.

image

After restarting your computer, click the “Back” arrow to switch user:

image

Click the “Other User” option:

image

Then finally sign in with your domain credentials.

image

Note: if you aren’t normally a domain administrator, you may need your domain admin to first log into the machine to make your domain account be an admin of the machine if that is appropriate for your usage scenario.

 

NOTE: If you’re reading this from home and are trying to figure out how to join the domain over a VPN connection, this IS POSSIBLE by creating a shared network connection while logged in as that “Admin” user.  But that’s a topic for a different post.  If doing this with VMWare workstation, and using NAT networking as long as the host computer is already connected to the VPN this VM should also be able to use that VPN connection for the domain authentication.

Another note: Windows 10 Beta build 9926 seems to install just fine on VMWare Workstation 10 and does not appear to require an upgrade to version 11 even though the VMWare web site seems to encourage that.  I was able to successfully install VMware v10 tools to the Windows 10 machine.

One limitation of VMWare Workstation 10 however seems to be that it limits the maximum screen resolution to 2560×1920:

image

Supposedly version 11 fixes that and adds support for full QHD+ (3200×1800) displays like the one in my Dell M3800.  That alone may be worth the upgrade just to not be annoyed by the screen resolution not matching the actual display.  Otherwise the VM seems to run fine on Version 10.

Follow

Get every new post delivered to your Inbox.