Tag Archives: SQL

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

Changing SQL collation for deployed instance without reinstall (almost)

IMPORTANT UPDATE: If you use K2 4.6.11 or newer you have to have SQL_Latin1_General_CP1_CI_AS collation on a SQL Server instance which hosts K2 DB (more details)

Collation matters. Period. If you not sure just wait till you get an incident or issue which will teach you that it is. Some apps require you to have specific collation for their back end DB. For example for K2 you need to have  Latin1_General_CI_AS collation (see my earlier post on that _ ).

Nonetheless it can often be the case that your RTFM/Prerequisites_Check moment (I wonder if I can copyright/trademark this term? I heard that Michael Buffer trademarked his “let’s get ready to rumble” phrase _¬†)¬†happens after SQL server instance has been already provisioned. For newly created (clean install) K2 database you just need to delete database which was created on instance with wrong collation and allow K2 Setup Manager to create it again after you deal with your instance collation. There is an MSDN article detailing that _ but I will outline the procedure below.

Aforementioned MSDN article says that you have to have installation media to perform this operation, but in fact you can get away with Setup Bootstrap folder which should present on your server in location similar to one below (may vary from version to version, see details _ )

C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014

So steps are the following:

0. In case you have any non system databases attached to this instance take a note of database(s) file location, frequently it is just default SQL instance data folder common to all databases. Save this path or paths to notepad. 

1. Locate setup bootstrap folder for your SQL server installation, open CMD or PS window (don’t forget “dot net sourcing” thing for the latter):

# SQL 2012
cd "C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012"
# SQL 2014
cd "C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014"
# SQL 2016:
cd "C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\SQLServer2016"
# SQL 2017:
cd "C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\SQL2017"

2. Execute the following command:

setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=Domain\User /SAPWD=P@ssw0rd /SQLCOLLATION=Latin1_General_CI_AS

Once this operation completes  master, model, msdb, and tempdb system databases are rebuilt (dropped and re-created) with collation you specified in the command. Obviously all your modifications to these databases are lost (if you made any) and essentially it is sort of reinstall/repair type of operation just much quicker and without need to wade through SQL Setup GUI.

3. In case there were non system databases attached to this SQL instance (see step 0) they all are detached now. See my other blog post explaining how to attach all the databases from specific folder. 

Please follow and like us:
error0

SQL Server: Giving user role membership on specific DB

Just a really quick note on how to manage RBA in SQL Server not relying on SQL Server Management GUI, as sometimes it is far quicker to execute a few lines, for example to create DB and then grant dbowner role membership, for let’s say you application service account so that your app can use newly created DB. So SQL code is the following:

--Create database

CREATE DATABASE YourDatabaseName

--Grant dbowner role to specified user

USE YourDatabaseName

EXEC sp_addrolemember N'db_owner', N'YourDomain\YourUser'

NOTE: In real world/production environments you should not be so generous with granting dbowner role to each and every user, event to app service account. See some expanations for example here: 5 Reasons Against Allowing db_owner Role Permissions.

Please follow and like us:
error0

SQL Script: List all tables in DB with their row counts

Often I need to test something and use SQL database table of particular size for my test, and sometimes it is easier to pick up some table of appropriate size (at least in terms of rows number) from what you already have. This is the case when this SQL script may come in handy:

DROP TABLE #counts

CREATE TABLE #counts

(

table_name varchar(255),

row_count int

)

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'

SELECT table_name, row_count FROM #counts ORDER BY row_count DESC

Please follow and like us:
error0