SQL Server 2019 RTM Cumulative Update (CU) 4 KB4548597 resulted in SQL Service unable to start

Early this morning Windows Updates updated one of our SQL 2019 servers with “SQL Server 2019 RTM Cumulative Update (CU) 4 KB4548597”.

That update resulted in the server being placed into a state whereby the SQL Server Service could not start. 

Event Viewer for System Showed every attempt at Starting the SQL Server service with the following error:

Event ID: 7024 “The SQL Server (MSSQLSERVER) service terminated with the following service-specific error:
WARNING: You have until SQL Server (MSSQLSERVER) to logoff. If you have not logged off at this time, your session will be disconnected, and any open files or devices you have open may lose data.”

Opening Windows Update, you could see that the update had been installed and that a server restart was required.

Restarting the server solved the issue and services came back up on their own.

Additional Information:
Windows Server 2016 Datacenter
The following Windows Updates were all installed at the same time:

Screen Capture of Windows Update History

2020-05 Cumulative Update for Windows Server 2016 for x64-based Systems (KB4556813)
SQL Server 2019 RTM Cumulative Update (CU) 4 KB4548597
Windows Malicious Software Removal Tool x64 – v5.82 (KB890830)
2020-04 Servicing Stack Update for Windows Server 2016 for x64-based Systems (KB4550994)

At this point I do not know if this issue is specific to this particular combination of KBs and Server 2016 or if other operating systems combinations will be affected. Will post updates if more systems are affected.

Improving Database Performance–Index Creation Recommendation

Because different customers use software in different ways, not every customer will benefit from exactly the same database-level indexes.

As a best practice, when users complain about database performance in a specific area of the application, or when performance tuning in general, I like to let the customer run with the application for a few days, and then use a query such as the one included below (adapted from Jeff Garbus at Soaring Eagle: https://mssqlperformance.blogspot.com):

–Source: Jeff Garbus from Soaring Eagle – Blog: https://mssqlperformance.blogspot.com/
–DRM – 2020-05-04 – Adapted by Darren to deal with mixed SQL Environments, include table name in index name, deal with table names containing spaces, etc.
–Use this SQL Script to identify indexes that if added could help improve performance.
–IMPORTANT: Do not BLINDLY implement the script recommendations.  For example, if the SAME TABLE is mentioned multiple times, then
–           it may be better to COMBINE the recommendations of multiple statements into a single one.
–           If you need help, Soaring Eagle. 
DECLARE @Edition varchar(50)
SET @Edition = CONVERT(varchar(50), SERVERPROPERTY(‘Edition’))
DECLARE @bEnterpriseEdition bit
IF (LEFT(@Edition, 9) = ‘Enterpris’ OR LEFT(@Edition, 9) = ‘Developer’)
SET @bEnterpriseEdition = 1

            + REPLACE(obj.Name, ‘ ‘, ”) +’_’
            + replace(replace(replace (equality_columns, ‘[‘, ”),’]’,”),’, ‘,’_’)
            + ‘ ON ‘
            + sch.name COLLATE SQL_Latin1_General_CP1_CI_AS + ‘.’ + QuoteName(obj.name)
            + ‘ (‘
            + equality_columns
             + CASE WHEN inequality_columns IS NULL
                   THEN ”
                   ELSE ‘,’ +  inequality_columns
            + ‘)’
            + CASE WHEN included_columns IS NOT NULL
                   THEN ‘ INCLUDE (‘ + ISNULL(included_columns,”) + ‘) ‘
                   ELSE ”
            + CASE WHEN @bEnterpriseEdition = 1
                   THEN ‘ WITH (online = ON)’
                    ELSE ”
            + ‘ — ‘ + CONVERT (varchar, avg_user_impact) + ‘% anticipated impact’
      FROM sys.dm_db_missing_index_details mid
        JOIN sys.dm_db_missing_index_groups mig
            ON mid.index_handle = mig.index_handle
        JOIN sys.dm_db_missing_index_group_stats migs
             ON migs.group_handle = mig.index_group_handle
        JOIN sys.objects obj
            ON obj.object_id = mid.object_id
        JOIN sys.schemas sch
            ON obj.schema_id = sch.schema_id
        database_id = db_id()
        AND avg_user_impact > 60
        AND equality_columns IS NOT NULL
      ORDER BY
        , equality_columns
         , inequality_columns
        , included_columns

The output of this script should be reviewed carefully to see if the tables / recommendations make sense for the specific scenario.  Remember that there is a cost for implementing index changes that may outweigh the benefits.  The most common change that I make to the results from the above is to combine multiple recommendations for the same index into a single index with more elements in the INCLUDE() section.

For Blue Link employees reading this Blog, a version of the above is now available as IndexCreationRecommendationPROC().

How to determine SQL Server Version with SQL Server Year Name

One of the frustrations with SQL Server is that they do not expose the SQL Server Marketing Name that contains the year in the name as a Server Property.  Instead, to get that you either need your own lookup table, or you have to parse it from the @@Version string.

The following query returns every attribute that most people would want in discrete columns:

, SUBSTRING(@@Version, 11, CHARINDEX(‘ (‘, @@VERSION, 1) – 11) as SQLVersionLabel
, SUBSTRING(@@Version, 22, CHARINDEX(‘ (‘, @@VERSION, 1) – 22) as SQLYearReleaseLabel
, SERVERPROPERTY(‘ProductLevel’) AS ProductLevel
, SERVERPROPERTY(‘ProductUpdateLevel’) AS ProductUpdateLevel
, SERVERPROPERTY(‘ProductBuildType’) AS ProductBuildType
, SERVERPROPERTY(‘ProductUpdateReference’) AS ProductUpdateReference
, SERVERPROPERTY(‘ProductVersion’) AS ProductVersion
, SERVERPROPERTY(‘ProductMajorVersion’) AS ProductMajorVersion
, SERVERPROPERTY(‘ProductMinorVersion’) AS ProductMinorVersion
, SERVERPROPERTY(‘ProductBuild’) AS ProductBuild

Combining a query like that with a product like Toad for SQL Server’s group execute feature, it becomes possible to pull the SQL Version info from an entire group of servers all at once.

Office 365 Version 1912 Build 12325.20288 Released 2020-01-08 64-bit Issue Linking SQL Tables with Identity True

Microsoft Access users may start experiencing #Deleted in all columns when adding new records to forms bound to SQL Server tables containing identity columns as of 64-bit Office 365 Version 1911 Build 12228.20364.

The issue occurs when linking SQL Server tables to a Microsoft Access database using the 64-bit version of Office 365 Version 1912 build 12325.20288 released on January 8th, 2020.

UPDATE: Office 365 Version 1912 build 12325.20298 released on January 14th, 2020 is ALSO affected by this issue.

UPDATE: 2020-01-17: Microsoft confirms the issue publicly: https://support.office.com/en-us/article/Fixes-or-workarounds-for-recent-issues-in-Access-54962069-14f4-4474-823a-ff7e5974a570

Direct Link: https://support.office.com/en-us/article/access-does-not-recognize-the-identity-column-in-a-linked-sql-server-table-ae418bbf-2658-453a-82f1-7e043812d60d

UPDATE: 2020-01-23: Microsoft Released Version 1912.12325.20344 that fixes the issue covered in this blog post. https://docs.microsoft.com/en-us/officeupdates/monthly-channel-2020#version-1912-january-22  From that URL “This update fixes an issue that can cause Microsoft Access to fail to identify an Identity Column in a linked SQL Server table, which can cause rows to be reported as deleted incorrectly”

The issue is as follows:

In SQL Server, assume you have a SQL Table with a column where the Identity property is true like this:

SQL Server Management Studio Table Designer – Showing (Is Identity) = Yes

When linked to Microsoft Access via an ODBC Link, that ID column normally appears in Microsoft Access as Data Type “AutoNumber”:

Microsoft Access Linked SQL Table Shows Identity Column Data Type as "AutoNumber"

When linked from Build 12325.20288, you can see that the same field now links as “Number” (Field Size: Long Integer), but it no longer recognizes the identity property from SQL Server:

Microsoft Access Linked SQL Table Shows Identity Column Data Type as "Number"
on Office 365 Build 12325.20288 64-bit

The effect of the Data Type being set incorrectly on the linked table is that an end-user after entering a new record in the Access Form bound to that table will see #Deleted in every field/column of the new record when they leave the record like this:


If the user forces a refresh of the data by using the SHIFT+F9 keyboard shortcut, the refreshed screen will display the new record since the record actually was committed to the database. It is worth noting that end users who do not realize this may panic since the #Deleted in every cell may appear to them as data loss.

Work Around:

Two different workarounds appear to work.

Work Around #1 – Change the bitness level back to 32-BIt by using the MigrateArch setting in the ODT:

If you have a business reason why you must run Version 1912 Build 12325.20288, switching from architecture from 64-bit back to 32-bit does seem to work as a workaround. However if you do not need to run that build a much better solution is simply to revert to the stable Dec 10, 2019 Version 1911 Build 12228.20364. To switch the architecture, download a current version of the ODT Setup, then use the following XML:

< Add SourcePath="C:\odt" OfficeClientEdition="32" MigrateArch="TRUE" Channel="Monthly" >
< Product ID="O365ProPlusRetail">
< Language ID="en-us" />
< /Product>
< /Add>
< Updates Enabled="FALSE" />
< Display Level="Full" AcceptEULA="TRUE" />
< Logging Path="%temp%" />
< Property Name="SharedComputerLicensing" Value="1" />
< Property Name="AUTOACTIVATE" Value="1" />
< /Configuration>
Office ODT XML File Sample with "MigrateArch" Setting

Work Around #2 – Force Installation of Previous Build or the Fixed Version Build by specifying Version Explicitly in the ODT:

A better solution is simply to change to a version that does not contain the issue.

To force the installation of a particular build, first, use Control Panel Programs and Features to uninstall the existing Office 365 ProPlus build, then using the most recent ODT Installer download and then install the specific build using a config.xml file with the following contents:

OPTION 1: Revert to the last known good release of Office 365, which in my testing is Version 1911 Build 12228.20364.

  <Add SourcePath="C:\odt" Version="16.0.12228.20364" OfficeClientEdition="64" Channel="Monthly" >
    <Product ID="O365ProPlusRetail">
      <Language ID="en-us" />
  <Updates Enabled="FALSE" TargetVersion="16.0.12228.20364" />
  <Display Level="Full" AcceptEULA="TRUE" />
  <Logging Path="%temp%" />
  <Property Name="SharedComputerLicensing" Value="1" />
  <Property Name="AUTOACTIVATE" Value="1" />
< /Configuration>
Office ODT XML File Sample to Install Specific Build 12228.20364

OPTION 2: Skip to the fix Version 1912 12325.20344

The complete list of versions /builds can be found here: https://docs.microsoft.com/en-us/officeupdates/update-history-office365-proplus-by-date?redirectSourcePath=%252fen-us%252farticle%252fae942449-1fca-4484-898b-a933ea23def7).

The sample below jumps you to Build 12325.20344 – the first build where this issue is fixed:  

  <Add SourcePath="C:\odt" Version="16.0.12325.20344" OfficeClientEdition="64" Channel="Monthly" >
    <Product ID="O365ProPlusRetail">
      <Language ID="en-us" />
  <Updates Enabled="FALSE" TargetVersion="16.0.12325.20344" />
  <Display Level="Full" AcceptEULA="TRUE" />
  <Logging Path="%temp%" />
  <Property Name="SharedComputerLicensing" Value="1" />
  <Property Name="AUTOACTIVATE" Value="1" />
< /Configuration>
Office ODT XML File Sample to Install Specific Build 12325.20344

IMPORTANT: After you have changed the version of the ODT, you must RE-ATTACH the Linked Tables.  Those tables previously re-attached by the corrupted application are still linked as Number, not “AutoNumber” so you must RE-ATTACH the tables for the identity columns to be re-linked as AutoNumber once again by the database engine.

The following instructions are intended for customers of Blue Link who encounter this issue with Blue Link Elite:

  <Add SourcePath="C:\odt" Version="16.0.12325.20344" OfficeClientEdition="64" Channel="Monthly" >
    <Product ID="O365ProPlusRetail">
      <Language ID="en-us" />
  <Updates Enabled="FALSE" TargetVersion="16.0.12325.20344" />
  <Display Level="Full" AcceptEULA="TRUE" />
  <Logging Path="%temp%" />
  <Property Name="SharedComputerLicensing" Value="1" />
  <Property Name="AUTOACTIVATE" Value="1" />
< /Configuration>

  • Run setup.exe /download Install20191210Build.xml from Administrative Command Prompt
  • Un-Install Microsoft Office 365 ProPlus using CONTROL PANEL “Uninstall a Program” option:

Control Panel > Programs > Programs and Features

When uninstall is complete, install with the new ODT XML:

  • From Administrative Command Prompt, run:
  • C:\ODT\Setup.exe /configure Install20191210Build.xml

When install is complete:

  • Open Access as Administrator  (this is so that the Blue Link Add-In can be registered correctly with the updated build of Access)
  • Do not open a database, instead, hit the escape key on your keyboard and then click File > Account and verify that the build installed is the correct build:

Microsoft Access File > Account Screen Showing Build 12228.20364

  • Finally, re-link the app to the SQL Database by re-selecting the data file from the Choose Company List:
    • Open "D:\BlueLink\Master\BLElite64.accde"
    • Click Choose Company
    • Select the company in question and click Use

To test: Open a sales order, add a new line, leave the row.  If no #Deleted, then the issue is solved.

Please comment and/or share this article with anyone else performing patch maintenance or managing a WSUS server / Office Updates.

Microsoft Access Error: Query ” is corrupt caused by KB4484127, KB4484119, KB4484113

As confirmed in this Microsoft Article: https://support.office.com/en-us/article/access-error-query-is-corrupt-fad205a5-9fd4-49f1-be83-f21636caedec

Microsoft Access application users may suddenly start to receive “Error Query ‘’ is corrupt”.

Microsoft released a security update as of November 12th, which can cause the following error in Microsoft Office and Blue Link applications:


The following KB’s released by Microsoft, are known to cause the issue:


Admins who manage WSUS servers should block the three KBs listed above to prevent additional end user machines or servers from receiving these updates.

If you already have these updates installed, and have admin rights to your computer, follow these steps to remove the updates:

(1) Open Programs & Features


(2) Click on view installed updates.


(3) Search for KB KB4484119 & KB4484113
(4) Once you find the update, click on Uninstall update. Please note, this may require a server reboot.


Tip: you can search the list:


  • Tip: To save time, since these KBs all start with “KB4484” searching for that will narrow the list, saving you time.

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:



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.  (NOTE: This step is not required if you use a WSUS Server and have already blocked the update there).



Click Start, Type “Check for Update”


Look at the list of pending updates – if the server has already downloaded and partially installed the update it may show as shown in this screen capture.  If it does, the update is already partially installed – it is too late to block it.  You must force the server restart and then after the server comes back up, then un-install the update.

Update: 2019-11-17

For servers where the KB update was PENDING (showed in the Updates screen as about to be installed) if actual installation happened on Nov 17th or later it appears not to actually install that KB, but rather installs the updated KB:


In such instance, there is no “un-install” step required.

Access 365 (Office 365)

UPDATE: This issue is also affecting Access 365 (installed via Office 365 with Monthly Channel (Confirmed issue in Build 12130.20344 Click-to-Run).

Apparently, you can force the version of Office to a previous update by following these instructions:


Here’s how to determine the “previous version” build numbers:


The previous build (October 30, 2019) is build: 12130.20272

2019-11-25 – UPDATE: Microsoft has released a patched version 12130.20410 – instructions below are designed to force you to the fixed version, instead of forcing you backwards to the prior version.

To update the app, the Office Deployment Tool (ODT) has to be used.  Assuming you extracted it to C:\ODT, then you would update the ODT XML as follows:

     <Add SourcePath="C:\odt" OfficeClientEdition="64" Channel="Monthly" >
          <Product ID="O365ProPlusRetail">
               <Language ID="en-us" />
    <Updates Enabled="TRUE" TargetVersion="16.0.12130.20410" />
     <Display Level="Full" AcceptEULA="TRUE" />
     <Logging Path="%temp%" />
     <Property Name="SharedComputerLicensing" Value="1" />
     <Property Name="AUTOACTIVATE" Value="1" />

If you have a C:\ODT\Office folder, delete it.

setup /download MyConfigFile.xml

setup /configure MyConfigFile.xml

For me this worked.  All I had to do was kick everyone off the server to allow the update to complete.  Minutes later the error was gone.

IMPORTANT: After the install completes click File, Account, Update Options then select Disable Updates to prevent the update from just being installed again:

For Access 2019 (Office 2019)

Same as Office 365, except the build numbers are different.  In my case, the affected build was Build 10352.20042 Click-to-Run. 

Here’s how to determine the “previous version” build numbers:


Need to the October 8, 2019 build: 10351.20054

  <Add OfficeClientEdition="64" Channel="PerpetualVL2019">
      <Product ID="ProPlus2019Volume"  PIDKEY="XXXXX-XXXXX-XXXXX-XXXXX-XXXXX" >
         <Language ID="en-us" />
  < RemoveMSI />
  <Updates Enabled="TRUE" TargetVersion="16.0.10351.20054" />
  <Display Level="None" AcceptEULA="TRUE" /> 
  <Property Name="AUTOACTIVATE" Value="1" />

SQL Server 2016 SP2 (KB) Causes SQL Backups to Fail with error 3601

After applying SQL Server 2016 SP2 Backups of Databases that had SQL Change Tracking enabled at the time SP2 was applied to the server no longer work.  Backups will fail with this message:


TITLE: Microsoft SQL Server Management Studio

Backup failed for Server ‘ServerName’.  (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=13.0.15700.28+((SSMS_Rel).160810-2135)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


Failed to flush the commit table to disk in dbid 10 due to error 3601. Check the errorlog for more information. (Microsoft SQL Server, Error: 3999)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.5026&EvtSrc=MSSQLServer&EvtID=3999&LinkId=20476


My Scenario: Server OS: Windows Server 2016 Datacenter

SQL Server 2016 Standard, SP2 Applied

Databases affected are those that had SQL Change Tracking enabled at the time of SQL Server 2016 SP2 being applied to the server, like this:

Ola Hallengren Backup Scripts Also Fail

If you happen to be using the Ola Hallengren DatabaseBackup SPROC, you may also notice that SPROC start to fail with error


Msg 207, Level 16, State 1, Line 1 Invalid Column name ‘modified_extent_page_count’.

That failure is happening because there is no column ‘modified_extent_page_count’ in the DatabaseName.sys.dm_db_file_space_usage views after the application of SQL 2016 SP2.

This is the specific line from the DatabaseBackup SPROC that fails:

SET @CurrentCommand07 = ‘SELECT @ParamAllocatedExtentPageCount = SUM(allocated_extent_page_count), @ParamModifiedExtentPageCount = SUM(modified_extent_page_count) FROM ‘ + QUOTENAME(@CurrentDatabaseName) + ‘.sys.dm_db_file_space_usage’

Solution: Remove SP2:Click Start, type “Update” and then choose “View Installed Updates”:


Scroll down to “Microsoft SQL Server 2016 (64-Bit)


Select “Service Pack 2 for SQL Server 2016 (KB4052908) (64-bit) 

Click Uninstall


If as in my case a restart is required, the setup will tell you that.  Restart the server then repeat this process and you’ll be able to click through the wizard to the end.

When the service pack removal is complete, backups will work once again.

Disabling change tracking does not solve this problem only uninstall of SP2 worked for me

I’m adding the steps to disable change tracking here for your easy reference, but in my experience this DOES NOT actually solve the problem.  Using a script like this you can disable change tracking:

FROM sys.change_tracking_tables ctt
JOIN sys.tables t
    ON t.object_id = ctt.object_id
JOIN sys.schemas s
    ON s.schema_id = t.schema_id
ORDER BY s.name, t.name

–Copy the results of the script above to a new query window and execute to turn SQL Change Tracking off on the tables for which it was enabled, and then run a command like this to disable change tracking on the database.


MSAccess Date Picker Not Available for Unbound Text Fields with Input Mask

Starting with Microsoft Access 2007, 2010, 2013, 2016, if you create a database of type .accdb, text controls bound to date datatype fields will automatically display a date picker with or without an input mask:


Unbound text fields will also display the date picker if you set the Format property to a valid date format and set the Show Date Picker property to “For dates”:


… but only if the Input Mask property is left blank.

As soon as you specify an input mask on the unbound text field, the date picker immediately stops working.  Here’s an example:

Input Mask Property is Blank:


Input Mask Property is set to any value:


Result: Date Picker is available:


Result: Date Picker is Not Available:


This is true even if you happen to use Microsoft’s own Input Mask Wizard to set the input mask.

If you happen to hit this bug and would also like to have it fixed, please vote for the idea on the Access user voice site: https://access.uservoice.com/forums/319956-access-desktop-application/suggestions/31526878-fix-bug-date-picker-not-available-if-unbound-text

Simply removing the input mask solves the problem – if you can convince your end users that they no longer need the input mask!

Windows Server 2016 RDP–You must change your password before logging on the first time

As I start to roll out Windows Server 2016 to replace Windows Server 2008 R2 RDP Servers, I’ve been encountering issues that just didn’t exist with Server 2008 R2.  Today’s issue is that by default, if a new user account is created, or if the user’s password expires, or if an admin simply checks the Active Directory “User must change password at next login” property, when the user attempts to connect via RDP instead of getting a password change prompt they instead see this error:


Title: Remote Desktop Connection

You must change your password before logging on the first time.  Please update your password or contact your system administrator or technical support.

This has the unusual effect of preventing the user from changing their password themselves, leaving the administrator with one of two undesirable options:

  • Turn off the “User must change password at next login” property.
  • Change the password to some other password yourself, and also make sure that property is not set.

In either case, the implication is that some person other than that user now knows what that user’s password.

How to solve the issue – Change the RDP Security Layer

To solve the issue, you have to edit the Session Collection, Security, Configure Security settings and then change the Security Layer setting from Auto-Negotiate to RDP Security Layer.


Once that change has been applied, remote RDP users return to being able to set a new password.

As an added bonus, this RDP Security Layer is actually more secure, and is also PCI Compliant (at least as of this writing anyway).

Facebook Copy-Paste Threads

Why are so many people falling for copy/paste bait threads lately? Copy/paste Facebook threads are the modern day equivalent of chain letters. The people who start them are doing so to see how many people that they can trick into doing it.

It is a form of social engineering, a psychological test to see what works and what doesn’t – what words and phrases can we use to evoke sufficient emotion in the reader to make them do what they were told to do? (i.e. copy/paste).

It is not hard to imagine this being done on purpose so that the data of who follows the instructions can be data-mined and correlated with which technique worked. You could be telling a data-miner which techniques will work ON YOU.

If you haven’t figured it out yet, the copy/paste threads all follow similar patterns. There are many different patterns, but the worst in my opinion are these two:

Pattern #1 – Prey on fear:

These are the messages that start with a warning or something scary that sounds true. The people who share these are concerned about their friends and probably want to protect them. These are the "add this legal notice to your profile to stop your pictures from being used in ads" ones, the "someone is spoofing profiles" ones, the "make sure you change these privacy settings or else" ones. They all say something scary and they all share in common that you must "copy/paste" the message and "Not just share it".

If you’re being told to copy/paste it then it should immediately seem suspicious to you. Take some time, research the issue, find an article in a reputable news media outlet, search the Internet to see if that phrase or keyword shows up as a known fraud or as something that has been disproven. Really want to project your friends? Stop spreading fear.

Pattern #2 – Prey on insecurities:

These are the messages that try to make you "test" who your friends are. The "did you read this whole post" ones, the "I’m trying to figure out who only likes my photos and doesn’t read my text so read this and copy/paste it to prove your friendship" ones, etc.

All you figure out when you do this are which of your friends are gullible, and depending on the content of the message, which of them you can manipulate using guilt.

This is not data that you should be sharing about yourself. This is not data that you should be asking your friends and family to share about themselves.

If you really are feeling insecure and want to know who your real friends are? Ask them to private message you or to share a story about a great moment they shared with you or of a great memory that they have of you. Better yet, reach out to them and share one of your memories.


So how do you know if something is worth copying or not? The answer is simple: if the thread ends with the phrase "copy/paste" just DON’T DO IT. If if the thread ends with instructions that "you must follow exactly" then it should be considered highly suspicious. Just like chain mail, these threads will only stop when a critical mass of people STOP DOING IT.

Stop and think about WHY the original message is asking you to copy/paste instead of sharing or adding your own original thoughts. The message is likely crafted to make it seem to your friends and family reading the message as though the thoughts expressed were yours. But by definition since you just copy/pasted it they are NOT your own original thoughts! There is a word for passing someone else’s thoughts off as if they were your own: Plagiarism.

If you agree with what someone is saying, like it, or share the thought and add your own original thought. Do the research, credit the original author, but if a friend asks you to copy/paste something, be a real friend: Just Don’t.

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:


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 DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model')

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:

DECLARE     @cmd        VARCHAR(MAX),
            @dbname     VARCHAR(200),
            @prevdbname VARCHAR(200)

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

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

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

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


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

      ‘USE [‘ + d.name + N’]’ + CHAR(13) + CHAR(10)
    + ‘DBCC SHRINKFILE (N”’ + mf.name + N”’ , 0, TRUNCATEONLY)’
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
         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]

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.