Renaming the name of the physical database and log file

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

VSDBCMD troubleshooting

Sometimes, just sometimes, I plunge right into using a tool without reading too much documentation. This has some advantages and some drawbacks. Trying to decipher error messages without the right background knowledge can be a hard nut to crack. Just now I found myself in one of these rare occasions, where I had to make the best of seemingly cryptic error messages.

The task at hand was to deploy a new database on a newly installed server using the vsdbcmd tool. The server came with just the rudimentary OS and software installed, hence I was bound for trouble.

My first bump on the head was when I first tried to run this command:

VSDBCMD

I received this error message (rather a critical dialog indicating application crash):

Dialog

This issue was solved by installing the .Net Framework 4.0
I then ran the same command line, and received another error:

VSDBCMD Error

BadImageFormatException is in my book a rather misleading error message, when you look at the actual problem at hand. There needs to be Microsoft SQL Server Compact 3.5 w SP 2 installed, both in 32 bit and 64 bit. Download details: Microsoft SQL Server Compact 3.5 Service Pack 2 for Windows Desktop

All this in a week, where Paul Randal (twitter | blog) has written a nice little piece on where to get your information. A classic on R.T.F.M and I’m glad I didn’t bother anyone but Google, as Google once again served the answer: http://msdn.microsoft.com/en-us/library/dd193258.aspx

Loading

Installing MS SQL Denali CTP3 failed

While I was on vacation, the news of the Denali CTP3 release (ref) reached me. When I got back to work I immediately downloaded the package. From there on it would be a smooth ride through the wizard, or at least so I imagined it. Only once before have I met problems installing a version of SQL Server on a machine, that was when I tried to install SQL Server 2005 Dev Ed. on an ASRock HT330. That didn’t work out too well. That aside, I wasn’t expecting any problems, since I was installing the CTP3 on a brand new stationary machine (Windows 7 Ent.) with lots of power.

Power
Current desktop

This isn’t a power issue though, going through the steps of the wizard, I’ll explain my actions and the outcome.

First the welcome screen, we you can view all kinds of requirements and documentation.

Welcomescreen

Selecting ‘Install‘ to proceed with the installation, and choosing the ‘New SQL Server stand alone…‘ option

Install

Click ‘OK’ on the Setup Support Rules dialog

Setup Support Rules

Click ‘Next’ on the Product Update dialog

Product Updates

The Install Setup Files dialog will show up (not clicking anything here)

Installing Files

Going through the Setup Support Rules, inspecting the Firewall warning. Since this is for local testing only this issue is not solved and should cause any problems.

Setup Support Rules

The details of the Windows Firewall warning

Firewall warning

Clicking ‘Next’ brings us to the Product Key dialog, where we will select ‘Evaluation’ as free edition

Product Key

Onwards to the License Terms dialog, where we will accept and choose to send any information to Microsoft

License Terms

On to features selection on the Setup Role dialog, where we will select ‘All Features With Defaults’

Setup Role

A detailed view awaits on the Feature Selection dialog, where ‘All Features With Defaults’ appears to not include ‘Reporting Services Add-in for Sharepoint products’ and ‘Master Data Services’, I wonder why…

Feature Selection

Now, the Installation Rules dialog is where I can’t get any further…

Install Issue

Errormessage

I’m stuck and can’t get any further. There was SQL Server 2005 client tools installed, but I uninstalled those the first time I was hit by the issue. I’ve tried this: http://bit.ly/pyt7mm and suggestions like it, w/out any effect.  I’ve tried to tweet about the issue and was by guided to: http://cot.ag/9OUV5A and http://cot.ag/qnzZid by @MicrosoftBI. None of those two provided any solution to the issue at hand.

How to get past this rather frustrating installation rule? And why does SQL Server 2005 Express tools block Denali CTP3?

Just as documentation, no SQL Server Express installed, allthough ‘Microsoft SQL Server “Denali” CTP3 Setup (English)‘ is listed twice…:

Programs and Features

There is a forum post on the issue: http://bit.ly/qjLTsJ

UPDATE:

I’ve tried to run the installer with the parameter /skpirules=Sql2005SsmsExpressFacet as show in picture, but the error persists:Command prompt

Error page #2

Loading