DBA

So you think you need Enterprise Edition, huh?

Posted by Jens Vestergaard on in Programming 2 Comments

Lately I have been struggling on a specific server in an environment I have been working on. Due to the need for partitioning in the cube, the project demanded an Enterprise Edition of SQL Server 2008R2. The only installation available, with Ent. Ed., had some 80+ databases, and several cubes, installed and running.

As I browsed through the databases, I noticed that quite a few of them could be run on a Standard Edition. I then turned to create a T-SQL statement, that would show which Ent. Ed. features was in use, in each database.

EXEC sp_MSforeachdb N'SELECT * FROM sys.dm_db_persisted_sku_features' 
GO

Turns out, none, zero, zip, nada of the databases uses any Ent. Ed. features. Imagine that.

Loading

Renaming the name of the physical database and log file

Posted by Jens Vestergaard on in Programming 0 Comment

During one of my current tasks, which was to run the same test on multiple copies of the same database, I needed to setup a flow where I could

  1. deploy the database from VS2010 or setup database w/ vsdbcmd
  2. run the test (import 5.5 mio facts + 250K dimension elements into a dw model)
  3. rename the database including the physical files
  4. goto step 1 w/out overwriting the databases already created

Since both a deploy from VS2010 and a setup run through vsdbcmd will produce the same name on the physical files over and over again, I needed to alter these names. Here’s how the script looks:

USE [master]

DECLARE @Database NVARCHAR(255) = '<name of database>'
DECLARE @BackupDevice NVARCHAR(255) = N'<full path to backupmedia>'
DECLARE @CurrentDataFileName NVARCHAR(255) = 
              N'<name of logical database file>'
DECLARE @TargetDataFileFullPath NVARCHAR(255) = 
              N'<operating system file name for new database file>'
DECLARE @CurrentLogFileName NVARCHAR(255) = 
              N'<name of logical log file>'
DECLARE @TargetLogFileFullPath NVARCHAR(255) = 
              N'<operating system file name for new log file>'

DECLARE @SQL VARCHAR(MAX) = N'
RESTORE DATABASE ' + @Database + N'
FROM  DISK = N''' + @BackupDevice + '''
WITH  FILE = 1,  
MOVE N''' + @CurrentDataFileName + ''' 
TO N''' + @TargetDataFileFullPath + ''',  
MOVE N''' + @CurrentLogFileName + ''' 
TO N''' + @TargetLogFileFullPath + ''',  STATS = 10
'
--PRINT @SQL
EXEC (@SQL)

rename_database_physical_files.sql

Loading