Category Archives: SQL

SQL Server: new instance installation frozen on “SqlEngineConfigAction_install_startup_Cpu64” step

I was installing additional new instance of SQL Server 2012 on a machine where other instance was installed earlier. In the process I run into the problem where Setup wuzard just frozen on about 99% on SqlEngineConfigAction_install_startup_Cpu64 step without flagging any errors and making no progress for hours, so that Setup window looks like that all the time:

I realized that I’ve seen it before but not made any efforts to fix it beyond starting process from scratch. This time I did some googling and found that there may be quite a few reasons for this problem, for example see this blog post: “Your SQL Server Setup may hang forever when it’s almost at the 99 %!” on MSDN Blogs. I tried a few suggestion with no luck but in the process I’ve noticed that Windows service for my new named instance has been already created but frozen in “Starting” state, so killing and restarting it manually revived SQL Setup Manager and it completed installation process with flying colors in a minute after this. So it seems SQL Setup Manager just endlessly waits for service started confirmation or something like this without any time out in place… Anyhow I’m just taking a note of my fix in case somebody else or myself run into this again.

Please follow and like us:
error0

Configuring SQL instance firewall rules via PowerShell

Doing distributed environments setups rather frequently these days I realized that I really don’t want wasting my time setting up SQL Server firewall rules via GUI (I described the process here) and luckily enough Ryan Mangan already created such script. All I had to do is try it (confirm that it works), save it on GitHub and share on my blog for the benefit of wider community:

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned 
#Enabling SQL Server Ports
New-NetFirewallRule -DisplayName “SQL Server” -Direction Inbound –Protocol TCP –LocalPort 1433 -Action allow
New-NetFirewallRule -DisplayName “SQL Admin Connection” -Direction Inbound –Protocol TCP –LocalPort 1434 -Action allow
New-NetFirewallRule -DisplayName “SQL Database Management” -Direction Inbound –Protocol UDP –LocalPort 1434 -Action allow
New-NetFirewallRule -DisplayName “SQL Service Broker” -Direction Inbound –Protocol TCP –LocalPort 4022 -Action allow
New-NetFirewallRule -DisplayName “SQL Debugger/RPC” -Direction Inbound –Protocol TCP –LocalPort 135 -Action allow
#Enabling SQL Analysis Ports
New-NetFirewallRule -DisplayName “SQL Analysis Services” -Direction Inbound –Protocol TCP –LocalPort 2383 -Action allow
New-NetFirewallRule -DisplayName “SQL Browser” -Direction Inbound –Protocol TCP –LocalPort 2382 -Action allow
#Enabling Misc. Applications
New-NetFirewallRule -DisplayName “HTTP” -Direction Inbound –Protocol TCP –LocalPort 80 -Action allow
New-NetFirewallRule -DisplayName “SSL” -Direction Inbound –Protocol TCP –LocalPort 443 -Action allow
New-NetFirewallRule -DisplayName “SQL Server Browse Button Service” -Direction Inbound –Protocol UDP –LocalPort 1433 -Action allow
#Enable Windows Firewall
Set-NetFirewallProfile -DefaultInboundAction Block -DefaultOutboundAction Allow -NotifyOnListen True -AllowUnicastResponseToMulticast True

Original blog post by Ryan/source of this script: PowerShell Script for SQL Firewall rules

Please follow and like us:
error0

PS script to get SQL version from BAK file

Quite unpleasant thing about MS SQL Server database backup files it that you can’t restore them on an older version of SQL Server (it seems that even if you really want to create such BAK file it is not possible), moreover this is valid not only for major versions but also for things like R2, meaning you can’t restore BAK file created in SQL Server 2008 R2 on SQL Server 2008 Server (non-R2).

Just to save my time trying to restore BAK files against wrong versions of SQL Server I created this script:

It will allow you to retrieve SQL version from BAK file headers and compare it with your server SQL version. Specify path to your BAK file and check the output – if BAK file SQL version is newer that your server version then BAK file can’t be restored on this server. Sample script output can be found below:

It tells you that backup was taken on SQL Server 2012 SP3, while you run SQL Server 2012 SP2. Once you install SP3 for SQL Server script output will change to this:

Once two numbers match (or your SQL Server version number is higher than BAK file SQL version number) you are ready for backup restore 🙂 I’ve also added to this script comparison and output of its result, along with prompt for BAK file name and location:

Please follow and like us:
error0

How to: Drop multiple databases via SQL Script (no worries backup/restore is covered too :) )

Recently I did rather a lot of test requiring me to work with non-consolidated K2 DBs. Test included multiple DB restore/delete operations and I realized that I need some script to quickly drop all my K2 DBs and start from scratch. Here is this script:

USE master;

GO

SELECT 'ALTER DATABASE ' + name + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE ' + 'DROP DATABASE '+ name

FROM sys.databases WHERE name like 'K2%';

GO

Script selects every database prefixes with “K2” and you just need to copy its output into new query window and execute.

And in case you tend to backup things before you delete them, similar script for backup:

USE master;

GO

SELECT 'BACKUP DATABASE ' + name + ' TO DISK=''C:\DBs\' + name + '.bak' + Char(39)

FROM sys.databases WHERE name like 'K2%';

GO

And for restore you can use the script below. Unfortunately it uses hard coded file paths but assuming your back up files have default DB names (and for example were created by the script above) you can get away with minimum find and replace adjustments (path to backup files and your SQL instance data directories may need to be adjusted). Here is the script for restore:

USE MASTER

RESTORE DATABASE [K2Categories]

FROM DISK = 'c:\DBs\K2Categories.bak'

WITH MOVE 'K2Categories' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2Categories.mdf',

MOVE 'K2Categories_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2Categories.log',

REPLACE;

GO

RESTORE DATABASE [K2Dependencies]

FROM DISK = 'c:\DBs\K2Dependencies.bak'

WITH MOVE 'K2Dependencies' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2Dependencies.mdf',

MOVE 'K2Dependencies_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2Dependencies.log',

REPLACE;

GO

RESTORE DATABASE [K2EnvironmentSettings]

FROM DISK = 'c:\DBs\K2EnvironmentSettings.bak'

WITH MOVE 'K2EnvironmentSettings' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2EnvironmentSettings.mdf',

MOVE 'K2EnvironmentSettings_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2EnvironmentSettings.log',

REPLACE;

GO

RESTORE DATABASE [K2EventBus]

FROM DISK = 'c:\DBs\K2EventBus.bak'

WITH MOVE 'K2EventBus' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2EventBus.mdf',

MOVE 'K2EventBus_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2EventBus.log',

REPLACE;

GO

RESTORE DATABASE [K2EventBusScheduler]

FROM DISK = 'c:\DBs\K2EventBusScheduler.bak'

WITH MOVE 'K2EventBusScheduler' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2EventBusScheduler.mdf',

MOVE 'K2EventBusScheduler_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2EventBusScheduler.log',

REPLACE;

GO

RESTORE DATABASE [K2HostServer]

FROM DISK = 'c:\DBs\K2HostServer.bak'

WITH MOVE 'K2HostServer' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2HostServer.mdf',

MOVE 'K2HostServer_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2HostServer.log',

REPLACE;

GO

RESTORE DATABASE [K2Server]

FROM DISK = 'c:\DBs\K2Server.bak'

WITH MOVE 'K2Server' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2Server.mdf',

MOVE 'K2Server_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2Server.log',

REPLACE;

GO

RESTORE DATABASE [K2ServerLog]

FROM DISK = 'c:\DBs\K2ServerLog.bak'

WITH MOVE 'K2ServerLog' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2ServerLog.mdf',

MOVE 'K2ServerLog_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2ServerLog.log',

REPLACE;

GO

RESTORE DATABASE [K2SQLUM]

FROM DISK = 'c:\DBs\K2SQLUM.bak'

WITH MOVE 'K2SQLUM' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2SQLUM.mdf',

MOVE 'K2SQLUM_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2SQLUM.log',

REPLACE;

GO

RESTORE DATABASE [K2WebDesigner]

FROM DISK = 'c:\DBs\K2WebDesigner.bak'

WITH MOVE 'K2WebDesigner' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2WebDesigner.mdf',

MOVE 'K2WebDesigner_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2WebDesigner.log',

REPLACE;

GO

RESTORE DATABASE [K2SmartBox]

FROM DISK = 'c:\DBs\K2SmartBox.bak'

WITH MOVE 'K2SmartBox' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2SmartBox.mdf',

MOVE 'K2SmartBox_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2SmartBox.log',

REPLACE;

GO

RESTORE DATABASE [K2SmartBroker]

FROM DISK = 'c:\DBs\K2SmartBroker.bak'

WITH MOVE 'K2SmartBroker' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2SmartBroker.mdf',

MOVE 'K2SmartBroker_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2SmartBroker.log',

REPLACE;

GO

RESTORE DATABASE [K2WebWorkflow]

FROM DISK = 'c:\DBs\K2WebWorkflow.bak'

WITH MOVE 'K2WebWorkflow' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2WebWorkflow.mdf',

MOVE 'K2WebWorkflow_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2WebWorkflow.log',

REPLACE;

GO

RESTORE DATABASE [K2Workspace]

FROM DISK = 'c:\DBs\K2Workspace.bak'

WITH MOVE 'K2Workspace' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2Workspace.mdf',

MOVE 'K2Workspace_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLK2\MSSQL\DATA\K2Workspace.log',

REPLACE;

GO
Please follow and like us:
error0

SQL Server: How to attach all databases from specific folder

I recently had to change SQL Server instance collation using the steps I described in my earlier post. After this operation dozen of databases which were hosted on that instance become detached and I was able to see only set of instance’s system DBs. So I had to look for an option to attach all DB from folder via script as doing this manually for 20 or so databases a bit too much of an effort to me 🙂

There is “Attach all the databases in one folder” script available in TechNet Script Center which was able to solve this problem for me. All you need it pass DatabaseDir parameter to the function specifying folder with your DBs:

Attach-AllDatabasesInDir -DatabaseDir "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA"

Just in case I provide PS code of this function below in case you don’t want to grab the same from TechNet Script Center. All credit goes to Ivan Josipovic.

#------------------------------------------------------------------------------------------------------

# Name: Attach-AllDatabasesInDir

# Description: This script will attach all the databases in a directory to the local SQL Instance

# Usage: Run the function with the -DatabaseDir parameter

# By: Ivan Josipovic, softlanding.ca

#------------------------------------------------------------------------------------------------------

#Do not modify below here

function Attach-AllDatabasesInDir{

param(

[string]$DatabaseDir = $(throw "-DatabaseDir `"C:\ExampleDir`" is required.")

)

#Load the SQL Assembly

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | out-null

#Connect to the local SQL Server Instance, change the (local) parameter if needed

$server = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")

if ($server -eq $null){

Write-host -foreground red "Unable to connect to the SQL Server"

return -1

}

$items = get-childitem $DatabaseDir *.mdf

foreach ($item in $items){

[bool]$ErrorExists = $false

$Item.name

try {

$DBName = $server.DetachedDatabaseInfo($($item.fullname)).rows[0].value

}

catch {

Write-host -foregroundcolor red "File was not able to be read. It is most likely already mounted or in use by another application"

$ErrorExists = $true

}

if ($ErrorExists -eq $false){

foreach ($db in $server.databases){

if ($db.name.Equals($DBName)){

write-host -foreground red "This database already exists on the server"

$ErrorExists = $true

}

}

if ($ErrorExists -eq $false){

$DbLocation = new-object System.Collections.Specialized.StringCollection

$DbLocation.Add($item.fullname)

$attach = $server.AttachDatabase($DBName, $DbLocation)

}

}

}

return

}

#------------------------------------------------------------------------------------------------------

Attach-AllDatabasesInDir -DatabaseDir "D:\Data\SQLDat\MOSSData"
Please follow and like us:
error0