Monthly Archives: February 2016

T-SQL Scripting Series

Schedule Ola Hallengren’s Maintenance Solution (T-SQL Scripting Series)

6 Comments

Introduction

If you have you ever struggled with setting up a maintenance plan for SQL Server, you’ll be glad that Ola Hallengren came around. This very clever SQL Server DBA thought, well the SQL Server Maintenance Plans aren’t that great. It does the job, but out of the box some jobs are scheduled at exactly the same time, amongst other inefficiencies. We all can be thankful that he wrote his own MaintenanceSolution.sql, which has won several awards and can be downloaded for free here.

F5 and then?

If you have downloaded Ola Hallengren’s MaintenanceSolution.sql, you still have to schedule the SQL Agent Jobs. But it is logical that this is not included, as every company, every server could have different requirements for backup, integrity checks, index optimization and statistics maintenance.

.. Configure!

In the script below you will find a CONFIGURATION section where you can configure how the SQL Agent Jobs created by Ola Hallengren should be scheduled. Just for your convenience, hope you like it.

Ola_Scheduling.sql

--\
---) Author: Hans Michiels
---) Creates jobschedules in SQL Server Agent jobs that
---) were created by Ola Hallengren's maintenance solution.
--/
/*
(c) Copyright 2016 - hansmichiels.com
 
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
 
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
 
You should have received a copy of the GNU General Public License
along with this program. If not, see http://www.gnu.org/licenses/.
*/

USE [msdb]
GO

--\-------------------------------------------------------------------------------------
---) DECLARATIONS
--/-------------------------------------------------------------------------------------
DECLARE @cur_days VARCHAR(50)
     -- @cur_days contains a space separated list of weekdays, e.g. 'mon tue wed thu fri sat sun',
     -- or use 'all' for all days.
     -- Please note that using 'mon tue wed thu fri sat sun' will create a weekly job with
     -- all days checked while using 'all' will create a daily job.

DECLARE @cur_start_time INT
     -- @cur_start_time: the starttime of the job in integer format (hhmmss)

DECLARE @cur_subday_interval INT
     -- @cur_subday_interval: N in 'every N seconds/minutes/hours (for seconds max N = 100)
     -- Use 0 when N/A

DECLARE @cur_intraday_unit VARCHAR(7)
     -- @cur_intraday_unit: seconds (s), minutes (m) or hours (h).
     -- For configuration you can use both the full name or the first letter.

DECLARE @cur_delete_any_schedule CHAR(1)
     -- @cur_delete_any_schedule: indicates if any schedule for the given jobname should be deleted.
     -- Use NULL if you want to use the default value (recommended).
     -- It is recommended only to use 'N' if you use multiple schedules for the same job.

DECLARE @default_delete_any_schedule CHAR(1)
     -- @default_delete_any_schedule: valued used if in the configuration below
     -- [delete_any_schedule] is NULL for a specific job.

DECLARE @delete_schedules_only CHAR(1)
     -- @delete_schedules_only: if set to 'Y', only the existing schedules are deleted.
     -- No new schedules are made.
     -- If you use this setting, make sure to set @default_delete_any_schedule also to 'Y'
     -- without overruling it to 'N' for specific jobs.

DECLARE @cur_schedule_id int
DECLARE @cur_job_name SYSNAME
DECLARE @cur_schedule_name SYSNAME
DECLARE @cur_freq_type INT
DECLARE @cur_freq_interval INT
DECLARE @cur_freq_subday_type INT

DECLARE @active_start_date INT
DECLARE @message VARCHAR(500)

SET @active_start_date = CONVERT(INT, CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112));

--\-------------------------------------------------------------------------------------
---) CONFIGURATION
--/-------------------------------------------------------------------------------------
SET @default_delete_any_schedule = 'Y';
-- If you DO NOT want to delete all existing schedules for all jobs in your configuration,
-- change @default_delete_any_schedule to 'N'.
-- Keeping this value on 'Y' is usually safe to do and this prevents that old schedules
-- might in some cases not be deleted which can give trouble due to multiple (conflicting)
-- schedules.
-- !! ONLY IF you have more than one schedule for a job, you should set [delete_any_schedule]
-- in the configuration above to 'N' for that specific job.

SET @delete_schedules_only = 'N';

DECLARE daily_weekly_cursor CURSOR LOCAL STATIC FOR
SELECT CONVERT(SYSNAME, NULL) AS [job_name], CONVERT(SYSNAME, NULL) AS [schedule_name],
        CONVERT(INT, NULL) AS [start_time], CONVERT(VARCHAR(50), NULL) AS [days],
        CONVERT(INT, NULL) AS [subday_interval],
        CONVERT(VARCHAR(7), NULL) AS [intraday_unit],
        CONVERT(CHAR(1), NULL) AS [delete_any_schedule]

-------------[job_name]------------------------------------[schedule]--[start]-[days]-----------------------[subday ]-[intraday]-[delete_any]
-----------------------------------------------------------[name ]--[time ]------------------------------[interval]-[unit ]-[schedule ]
UNION SELECT 'CommandLog Cleanup' , 'Schedule', 180000, 'wed sun' , 0 , NULL , NULL
UNION SELECT 'Output File Cleanup' , 'Schedule', 180500, 'wed sun' , 0 , NULL , NULL
UNION SELECT 'sp_delete_backuphistory' , 'Schedule', 181000, 'wed sun' , 0 , NULL , NULL
UNION SELECT 'sp_purge_jobhistory' , 'Schedule', 181500, 'wed sun' , 0 , NULL , NULL

UNION SELECT 'DatabaseIntegrityCheck - SYSTEM_DATABASES' , 'Schedule', 182000, 'wed sun' , 0 , NULL , NULL
UNION SELECT 'DatabaseIntegrityCheck - USER_DATABASES' , 'Schedule', 191500, 'wed sun' , 0 , NULL , NULL
UNION SELECT 'DatabaseBackup - SYSTEM_DATABASES - FULL' , 'Schedule', 001500, 'all' , 0 , NULL , NULL
UNION SELECT 'DatabaseBackup - USER_DATABASES - FULL' , 'Schedule', 003000, 'all' , 0 , NULL , NULL

UNION SELECT 'IndexOptimize - USER_DATABASES' , 'Schedule', 081000, 'mon tue wed thu fri sat sun', 0 , NULL , NULL

/* -- Weekly full, other days diff example:
UNION SELECT 'DatabaseBackup - USER_DATABASES - FULL' , 'Schedule', 201500, 'fri' , 0 , NULL , NULL
UNION SELECT 'DatabaseBackup - USER_DATABASES - DIFF' , 'Schedule', 201500, 'mon tue wed thu sat sun' , 0 , NULL , NULL
*/
/* -- Transaction log example / Example of use of [delete_any_schedule] with more than one schedule
UNION SELECT 'DatabaseBackup - USER_DATABASES - LOG' , 'Weekdays', 000001, 'mon tue wed thu fri' , 10 , 'minutes', 'Y'
UNION SELECT 'DatabaseBackup - USER_DATABASES - LOG' , 'Weekend' , 000001, 'sat sun' , 2 , 'hours', 'N'
*/
  

--\-------------------------------------------------------------------------------------
---) CREATION OF THE SCHEDULES
--/-------------------------------------------------------------------------------------
OPEN daily_weekly_cursor
FETCH NEXT FROM daily_weekly_cursor
INTO @cur_job_name, @cur_schedule_name, @cur_start_time, @cur_days,
     @cur_subday_interval, @cur_intraday_unit, @cur_delete_any_schedule

WHILE @@FETCH_STATUS = 0
BEGIN

    --\
    ---) Check if it is the dummy row to specify datatypes:
    --/
    IF @cur_job_name IS NULL
    BEGIN
      -- RAISERROR('NULL', 0, 1) WITH NOWAIT;
      FETCH NEXT FROM daily_weekly_cursor
      INTO @cur_job_name, @cur_schedule_name, @cur_start_time, @cur_days,
           @cur_subday_interval, @cur_intraday_unit, @cur_delete_any_schedule
    END

    SET @message = 'Job: ' + @cur_job_name
    RAISERROR(@message , 0, 1) WITH NOWAIT;

    --\
    ---) Get the current schedule_id, if any.
    --/
    SELECT @cur_schedule_id = -1;
    WHILE @cur_schedule_id IS NOT NULL
    BEGIN
        SELECT @cur_schedule_id = (
            SELECT TOP 1 sch.schedule_id
            FROM msdb.dbo.sysschedules sch
              JOIN msdb.dbo.sysjobschedules js
                ON js.schedule_id = sch.schedule_id
              JOIN msdb.dbo.sysjobs jobs
                ON jobs.job_id = js.job_id
             WHERE jobs.name = @cur_job_name
               AND ( ISNULL(@cur_delete_any_schedule, @default_delete_any_schedule) = 'Y' OR sch.name = @cur_schedule_name )
            );

        --\
        ---) If schedule exists, delete it first.
        --/
        IF @cur_schedule_id IS NOT NULL
        BEGIN
            EXEC msdb.dbo.sp_delete_schedule @schedule_id = @cur_schedule_id, @force_delete = 1;
            SET @message = ' ' + 'Schedule deleted with @schedule_id = ' + CONVERT(VARCHAR, @cur_schedule_id)
            RAISERROR(@message , 0, 1) WITH NOWAIT;
        END
    END

    --\
    ---) Set @cur_freq_type depending on days specification.
    --/
    SELECT @cur_freq_type = CASE @cur_days WHEN 'all' THEN 4 ELSE 8 END;

    --\
    ---) Set @cur_freq_interval depending on days specification.
    --/
    SELECT @cur_freq_interval = 0, @cur_days = ' ' + @cur_days + ' ';
    IF CHARINDEX(' all ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 1;
    IF CHARINDEX(' sun ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 1;
    IF CHARINDEX(' mon ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 2;
    IF CHARINDEX(' tue ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 4;
    IF CHARINDEX(' wed ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 8;
    IF CHARINDEX(' thu ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 16;
    IF CHARINDEX(' fri ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 32;
    IF CHARINDEX(' sat ', @cur_days) > 0 SET @cur_freq_interval = @cur_freq_interval + 64;
        
    IF @cur_freq_interval = 0
    BEGIN
      RAISERROR('Days string does not contain any valid day abbreviations', 16, 1);
    END

    IF @delete_schedules_only != 'Y'
    BEGIN
        IF @cur_subday_interval = 0
        BEGIN
            EXEC msdb.dbo.sp_add_jobschedule @job_name=@cur_job_name, @name=@cur_schedule_name,
          @enabled=1,
          @freq_type=@cur_freq_type,
          @freq_interval=@cur_freq_interval,
          @freq_subday_type=1,
          @freq_subday_interval=0,
          @freq_relative_interval=0,
          @freq_recurrence_factor=1,
          @active_start_date=@active_start_date,
          @active_end_date=99991231,
          @active_start_time=@cur_start_time,
          @active_end_time=235959, @schedule_id = @cur_schedule_id OUTPUT;

            SET @message = ' ' + 'Schedule created for days' + @cur_days + 'at ' + CONVERT(VARCHAR, @cur_start_time)
            RAISERROR(@message , 0, 1) WITH NOWAIT;

        END ELSE BEGIN

            SELECT @cur_freq_subday_type =
            CASE LOWER(LEFT(@cur_intraday_unit, 1))
            WHEN 'h' THEN 8
            WHEN 'm' THEN 4
            WHEN 's' THEN 2
            ELSE 0
            END

            IF @cur_freq_subday_type = 0
            BEGIN
              RAISERROR('''intraday_unit'' must contain a valid value when ''subday_interval'' contains a number greater than 0. Please check your configuration.', 16, 1);
            END

            EXEC msdb.dbo.sp_add_jobschedule @job_name=@cur_job_name, @name=@cur_schedule_name,
          @enabled=1,
          @freq_type=@cur_freq_type,
          @freq_interval=@cur_freq_interval,
          @freq_subday_type=@cur_freq_subday_type,
          @freq_subday_interval=@cur_subday_interval,
          @freq_relative_interval=0,
          @freq_recurrence_factor=1,
          @active_start_date=@active_start_date,
          @active_end_date=99991231,
          @active_start_time=@cur_start_time,
          @active_end_time=235959, @schedule_id = @cur_schedule_id OUTPUT;

            SET @message = ' ' + 'Schedule created for days' + @cur_days + 'every ' + CONVERT(VARCHAR, @cur_subday_interval) + ' ' + @cur_intraday_unit
            RAISERROR(@message , 0, 1) WITH NOWAIT;

        END
    END
    RAISERROR('-----' , 0, 1) WITH NOWAIT;
    
    SET @cur_schedule_id = NULL;
    FETCH NEXT FROM daily_weekly_cursor
    INTO @cur_job_name, @cur_schedule_name, @cur_start_time, @cur_days,
         @cur_subday_interval, @cur_intraday_unit, @cur_delete_any_schedule
END

CLOSE daily_weekly_cursor
DEALLOCATE daily_weekly_cursor

Download the script here.

(Note: the downloadable script has slightly better indenting than the html version above).

TSQL-S01E02-Schedule

Conclusion / Wrap up

With this script you can easily schedule the SQL Agent Jobs that Ola Hallengren’s MaintenanceSolution-script creates.

(c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.
T-SQL Scripting Series

How to copy a database table structure (T-SQL Scripting Series)

9 Comments

Problem

Do you recognize the need to copy a table structure in MS SQL Server, for temporary or permanent usage?

Most of you will know the famous

Insert Into Example.sql
SELECT TOP 0 *
INTO [dbo].[some unexisting table]
FROM [dbo].[some existing table]

But this approach has some limitations: Computed columns change into regular columns, and primary and foreign keys are not copied. Sometimes you just need more than this.

Solution

So I have made a script to copy a SQL server table structure with foreign keys but without indexes.
This script handles user defined types and computed columns gracefully.

My credits go to Aaron Bertand (see his post on mssqltips). I used his script for copying the foreign keys and added my own parts to it (download it here).

TSQL-S01E01-copy_table

TSQL-S01E01-CopyTableStructure.sql

--\
---) Author: Hans Michiels
---) Script to copy a sql server table structure with foreign keys but without indexes.
---) This script handles user defined types and computed columns gracefully.
--/
/*
(c) Copyright 2016 - hansmichiels.com
 
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
 
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
 
You should have received a copy of the GNU General Public License
along with this program. If not, see .
*/

--\
---) VARIABLES DECLARATIONS
--/
DECLARE @CrLf NVARCHAR(2)
DECLARE @Indent NVARCHAR(2)
DECLARE @nsql NVARCHAR(MAX)
DECLARE @SimulationMode CHAR(1)
DECLARE @SourceSchemaAndTable NVARCHAR(260)
DECLARE @TargetSchemaAndTable NVARCHAR(260)
DECLARE @FkNameSuffix NVARCHAR(128)
DECLARE @TableOptions NVARCHAR(500)

--\
---) CONFIGURATION: set the source and target schema/tablename here, and some other settings.
--/
SELECT
    @SimulationMode = 'Y' -- Use Y if you only want the SQL statement in the output window without it being executed.
  , @SourceSchemaAndTable = '[dbo].[Order]'
  , @TargetSchemaAndTable = '[dbo].[OrderCopy]'
  , @TableOptions = ' WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]'
  , @FkNameSuffix = '_' + REPLACE(CAST(NEWID() AS VARCHAR(40)), '-', '') -- A Guid is added to the foreign key name to make it unique.
  , @CrLf = CHAR(13) + CHAR(10)
  , @Indent = SPACE(2)
  -- For 'min' script use this (in case sql is near 4000 characters):
  -- , @CrLf = ' '
  -- , @Indent = ''

--\
---) BUILD SQL FOR CLONING TABLE
--/
SELECT @nsql
    = ISNULL(@nsql, '')
    + CASE col_sequence WHEN 1 THEN
      @CrLf + 'IF OBJECT_ID(N''' + @TargetSchemaAndTable + ''', ''U'') IS NOT NULL DROP TABLE ' + @TargetSchemaAndTable + ';'
    + @CrLf + 'CREATE TABLE ' + @TargetSchemaAndTable + @CrLf + @Indent + '( ' ELSE @CrLf + @Indent + ', ' END
    + [definition]
FROM (
      SELECT ROW_NUMBER() OVER (PARTITION BY tb.object_id ORDER BY tb.object_id, col.column_id) AS col_sequence
        , QUOTENAME(col.name) + ' '
        + COALESCE(
            'AS ' + cmp.definition + CASE ISNULL(cmp.is_persisted, 0) WHEN 1 THEN ' PERSISTED ' ELSE '' END,
            CASE
              WHEN col.system_type_id != col.user_type_id THEN QUOTENAME(usr_tp.schema_name) + '.' + QUOTENAME(usr_tp.name)
              ELSE
                QUOTENAME(sys_tp.name) +
                CASE
                  WHEN sys_tp.name IN ('char', 'varchar', 'binary', 'varbinary') THEN '(' + CONVERT(VARCHAR, CASE col.max_length WHEN -1 THEN 'max' ELSE CAST(col.max_length AS varchar(10)) END) + ')'
                  WHEN sys_tp.name IN ('nchar', 'nvarchar') THEN '(' + CONVERT(VARCHAR, CASE col.max_length WHEN -1 THEN 'max' ELSE CAST(col.max_length/2 AS varchar(10)) END) + ')'
                  WHEN sys_tp.name IN ('decimal', 'numeric') THEN '(' + CAST(col.precision AS VARCHAR) + ',' + CAST(col.scale AS VARCHAR) + ')'
                  WHEN sys_tp.name IN ('datetime2') THEN '(' + CAST(col.scale AS VARCHAR) + ')'
                  ELSE ''
                END
            END
            )
        + CASE col.is_nullable
            WHEN 0 THEN ' NOT NULL'
            ELSE CASE WHEN cmp.definition IS NULL THEN ' NULL' ELSE ' ' END
          END AS [definition]
       FROM sys.tables tb
       JOIN sys.schemas sch
         ON sch.schema_id = tb.schema_id
       JOIN sys.columns col
         ON col.object_id = tb.object_id
       JOIN sys.types sys_tp
         ON col.system_type_id = sys_tp.system_type_id
        AND col.system_type_id = sys_tp.user_type_id
       LEFT JOIN
            (
            SELECT tp.*, sch.name AS [schema_name]
            FROM sys.types tp
            JOIN sys.schemas sch
            ON tp.schema_id = sch.schema_id
            ) usr_tp
         ON col.system_type_id = usr_tp.system_type_id
        AND col.user_type_id = usr_tp.user_type_id
       LEFT JOIN sys.computed_columns cmp
         ON cmp.object_id = tb.object_id
        AND cmp.column_id = col.column_id
      WHERE tb.object_id = OBJECT_ID(@SourceSchemaAndTable, 'U')
      ) subqry
;
SELECT @nsql
    = ISNULL(@nsql, '')
    + CASE col_sequence
        WHEN 1 THEN @CrLf + ', PRIMARY KEY ' + CASE is_clustered_index WHEN 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END
           + @CrLf + @Indent + '( '
        ELSE @CrLf + @Indent + ', '
      END
    + QUOTENAME(pk_cols.column_name)
    + CASE is_descending_key
        WHEN 1 THEN ' DESC'
        ELSE ' ASC'
      END
FROM (
      SELECT TOP 2147483647 sch.name as schema_name, tb.name as table_name, col.name as column_name
               , ROW_NUMBER() OVER (PARTITION BY tb.object_id ORDER BY tb.object_id, ic.key_ordinal) AS col_sequence
               , ic.is_descending_key
               , CASE WHEN idx.index_id = 1 THEN 1 ELSE 0 END AS [is_clustered_index]
       FROM sys.tables tb
       JOIN sys.schemas sch
         ON sch.schema_id = tb.schema_id
       JOIN sys.indexes idx
         ON idx.is_primary_key = 1
        AND idx.object_id = tb.object_id
       JOIN sys.index_columns ic
         ON is_included_column = 0
        AND ic.object_id = tb.object_id
        AND ic.index_id = idx.index_id
       JOIN sys.columns col
         ON col.column_id = ic.column_id
        AND col.object_id = tb.object_id
      WHERE tb.object_id = OBJECT_ID(@SourceSchemaAndTable, 'U')
      ORDER BY tb.object_id, ic.key_ordinal
      ) pk_cols
 
SELECT @nsql = @nsql + @CrLf + @indent + ') ' + @TableOptions

IF @SimulationMode = 'Y'
BEGIN
  PRINT '-- Simulation mode: script is not executed.'
END
PRINT @nsql;
IF @SimulationMode != 'Y'
BEGIN
  EXEC(@nsql);
END
 
--\
---) Copy foreign key constraints
---) A guid is added to the foreign key name to make it unique within the database.
--/
SET @nsql = N'';
SELECT @nsql += N'
ALTER TABLE '
      + @TargetSchemaAndTable
      + ' ADD CONSTRAINT [' + LEFT(fk.name + @FkNameSuffix, 128) + '] '
      + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(col.name)
      -- get all the columns in the constraint table
      FROM sys.columns AS col
      JOIN sys.foreign_key_columns AS fkc
        ON fkc.parent_column_id = col.column_id
        AND fkc.parent_object_id = col.[object_id]
      WHERE fkc.constraint_object_id = fk.[object_id]
      ORDER BY fkc.constraint_column_id
      FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
      + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rtb.name)
      + '('
      + STUFF((SELECT ',' + QUOTENAME(col.name)
      -- get all the referenced columns
      FROM sys.columns AS col
      JOIN sys.foreign_key_columns AS fkc
        ON fkc.referenced_column_id = col.column_id
       AND fkc.referenced_object_id = col.[object_id]
      WHERE fkc.constraint_object_id = fk.object_id
      ORDER BY fkc.constraint_column_id
      FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
JOIN sys.tables AS rtb -- referenced table
   ON fk.referenced_object_id = rtb.[object_id]
JOIN sys.schemas AS rs
   ON rtb.[schema_id] = rs.[schema_id]
JOIN sys.tables AS ctb -- constraint table
   ON fk.parent_object_id = ctb.[object_id]
WHERE rtb.is_ms_shipped = 0 AND ctb.is_ms_shipped = 0
  AND ctb.object_id = OBJECT_ID(@SourceSchemaAndTable, 'U');

IF @SimulationMode = 'Y'
BEGIN
  PRINT '-- Simulation mode: script is not executed.'
END
PRINT @nsql;
IF @SimulationMode != 'Y'
BEGIN
  EXEC(@nsql);
END

Download the script here.

Conclusion / Wrap up

With this script you can copy the structure of a table including primary and foreign keys, computed columns and columns using a user defined datatype.
Last updated: 14 July, 2016.

(c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.

Windows User Series

Connect to a removable drive in a Hyper V Virtual Machine (Windows User Series)

Leave a comment

Introduction

This post is based on using Windows 10 on the host and Windows Server 2012 R2 on the guest (Virtual Machine), but on Windows 7, 8 and 8.1 things work similarly.

People coming from a VMWare environment might wonder what this is all about. What is the point, right?

I must admit, sharing or mapping a drive in a Hyper V virtual machine (a.k.a. “guest”) is not such a nobrainer as in VMWare. But no worries, with a little bit of effort it can be done easily.

So, what is the problem?

Well, I mount a VeraCrypt container on my host machine, and want to see the drive in my guest machine.  Or plug in a USB pen drive or removable harddisk, and use it on the guest machine.

Common ways to connect to a (removable) drive

I did it my way

I was looking for a more convenient way, that is, one I have to set up only once, and does not depend on the RDP protocol.

In my opinion a networkshare was the way to go.

But there were a few challenges:

  1. Windows tends to “forget” a shared drive or folder once this drive has been gone (usb media removed or VeraCrypt container unmounted). This is a problem, because then you have to configure the removable drive for sharing every time it is mounted.
  2. I did not want to do a round trip to my router, I want it to be a host/guest only party.

But I managed to get it working, and in the next paragraph I will tell you how.

myway3

Steps to do it “my way”

Again (see my previous post), the magic word to get this working is .. a junction!

Alright, having said that, where do we start?

We need set up a few things;

  1. An internal network connection between guest and host OS.
  2. A junction pointing to a removable drive letter on the host.
  3. Share that junction to one or more Windows users on the host.
  4. Create a mapped network drive on the guest.

Important note : if you want to map more than one drive on the host, you have to edit the hosts file on the virtual machine. After Step 4 below, I will explain how to do this.

Are you ready? Let’s roll up our sleeves and get going!

Step 1 – Set up an internal network connection between guest and host OS

1a – Create a Virtual Network Switch

Start Hyper V Manager, and select Virtual Switch Manager from the Actions pane.

WinS01E02_Step1a1

Create a new virtual network switch of type Internal.

WinS01E02_Step1a2

Give the Virtual Switch a name (I couldn’t think of a better name than “Internal”), leave other options as they are by default and press OK.

WinS01E02_Step1a3

1b – Add a network adapter to the virtual machine and configure it to use the Internal Switch

Now, open the Settings for the virtual machine.

WinS01E02_Step1b1

Choose Add Hardware – Network AdapterWinS01E02_Step1b2

Configure the network adapter to use the Virtual switch Internal (or any other name you gave it). Press OK.

WinS01E02_Step1b3

1c – Find the Internal IP Address of the host

Still on the host, start a command prompt and type ipconfig. 

Then search for an Ethernet Adapter vEthernet (Internal) and write down the Autoconfiguration IPv4 Address. You will need it in a few minutes.

WinS01E02_Step1c1

 

Step 2 – Create a junction pointing to a removable drive letter on the host

Insert your USB storage media or mount a VeraCrypt File container.

For the example I use drive P on the host. Please replace P everywhere with the driveletter of your removable media.

Now find a folder on a fixed drive, e.g. where you store your Hyper V Virtual Machines. In the example I will use E:\HYPER_V\SHARES

Start a command prompt and type

mklink /j “E:\HYPER_V\SHARES\P” “P:\”

 

Replace the foldername and the drive letter with your values.
The output of the command window should confirm that the junction was made:
WinS01E02_Step2a

People who have read my previous post in my Windows User Series might remember that I preached that it is not recommended to make junctions pointing to removable drives. Well, this post proves that there is at least one exception to this rule of thumb!

Step 3 – Share that junction to one or more Windows users on the host.

Open the Properties of the junction E:\HYPER_V\SHARES\P and select tab Sharing. Press Advanced Sharing.

WinS01E02_Step3a

 

 

Select Share this folder and press Permissions.

WinS01E02_Step3b

Remove Everyone from Share Permissions and add at least one user from the host. Give this user the appropriate permissions.

Tip: if possible, use a (special) account from the host of which the password never changes, for reasons I will tell below.

WinS01E02_Step3c

 

Step 4 – Create a mapped network drive on the guest

Now, in Hyper V Manager, start the Virtual machine.

Once the Virtual machine is started, log in, start a Windows Explorer and choose Map Network Drive from the menu.

Choose a Drive (can be the same letter as on the host, but this is not necessary).

For Folder, type \\<Host Internal IP Address>\<Share Name>

(or \\<Host name from hosts file>\<Share Name> if you have modified the hosts file)

Check Reconnect at sign-in (if you think this is convenient).

If you do not want a permanently mapped drive, you could create a cmd file with a net use command, and execute it when you need to map the drive. 

Check Connect using different credentials.

WinS01E02_Step4a

In the Windows Security screen, enter a username of the host that has permissions on the share you created earlier.

Note: to indicate that you use a username of the host machine, type the host computer name and a backslash in front of the user name .

If you check Remember my credentials, the credentials are stored in the Windows Credential Manager of the guest (Virtual Machine), so you don’t have to provide them every time you access the mapped drive. But don’t forget, that when you change the password from the account on the host, you have to change the saved password also in the Credential Manager of the guest (accessible through Control Panel). When you are using names from the hosts file instead of the IP address, the same credentials will even occur multiple times, e.g. for “host1”, “host2”, etc! Therefore, it is best to use a (special) account from the host of which the password never changes.

WinS01E02_Step4b

That’s it!

WinS01E02_Step4c

Remember, as soon as you plug in the removable drive on the host, it will be available immediately in the Virtual Machine.

If you get this message, you know that the removable drive is not available on the host (yet). You have to plug it in first.

WinS01E02_Step4d

Appendix – Only needed if you want to map more than one drive: edit the hosts file.

Following steps 1 to 4 you can map only one drive, because if you try to connect a second drive, your will get a “Multiple connections to a server or shared resource by the same user, using more than one user name, are not allowed… ” error as shown below.

WinS01E02_Limitations

To map multiple drives, you have to do a little trick so that your guest thinks you are connecting to different computers ..

Start Windows explorer on the guest

Go to your virtual machine, start Windows Explorer, and go to the folder C:\Windows\System32\drivers\etc.

 Copy and adjust the hosts file

Copy the file “hosts” (it does not have a file name extension) to a different temporary folder, e.g. your desktop folder.

Open the file with a text editor, e.g. Notepad and add the Internal IPv4 adress of the host, followed by a space and then a name. The trick really is to add multiple lines with the same IP adress but a different name. I just keep it simple by numbering the names but you can do whatever you want, as long as the names are different.

169.254.151.11 host1
169.254.151.11 host2

Finally your hosts file should look similar to the one below (I have added a few more so my hosts file is “future proof”):

WinS01E02_Step5b

Save and close the file and copy it back to the original folder C:\Windows\System32\drivers\etc. Confirm to replace the existing file.

Now walk trough Steps 2 to 4 for each network drive you want to map. Instead of using the IP address, use a name from the hosts file, for each mapped drive a different name, and you will not get the “Multiple connections” error.

Here you will see an example of the endresult: I mapped drives H, P and Z using names host1, host2 and host3.

WinS01E02_Step53

Conclusion / wrap up

In this blog post, I have explained how to map a removable drive on the host operating system as a network drive in a Hyper V Virtual Machine.

By using a junction on a fixed drive on the host, a Windows problem forgetting that a removable drive was shared, can be circumvented.

By using an Internal network connection, there is no router round trip, nor a dependency to use a remote desktop connection to start up the guest. Also multiple virtual machines could have access to the same drive at the same time.

Finally by changing the hosts file, you can connect to multiple (removable) drives on the host computer.

(c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.

Windows User Series

Windows drive full? Use junctions! (Windows User Series)

3 Comments

Introduction

Microsoft Windows is not so bad, is it? But in my experience it can be greedy as it comes to disk usage. Not that this is necessarily always Windows to blame, but also third party software or other reasons.

Whatever the reason is, if it happens you have a problem. But don’t panic, luckily there is something you can do about it!

Disk consumption in Windows is like a glass of water being filled until it would overflow ..
Disk consumption in Windows can be like a glass of water being filled until it would overflow ..

 

Use cases

But what situations are we talking about? I can give you some examples:

  • Windows updates eating disk space (does the folder “SoftwareDistribution” ring a bell?)
  • Visual Studio 2015 with full mobile development installation taking 40GB for that part only!
  • Software for navigation equipment that does need temporary space for downloading an update but has no way to choose the folder to download it to (my experience was with Garmin).
  • Just lots of huge files in “My music” or “My videos”, where basicly of course, there is no one to blame but you.
  • On a computer with a large amount of memory, having “hybernate” enabled. If you put a computer in “hybernate” all the memory contents is saved to disk, so when you have 16GB of RAM, you will need this amount of free disk space.
  • You have partitioned your disk to a “Windows” and a “Data” partition (aka a C- and a D-drive), but your underestimated the disk space required for the C-drive.
  • Your disk is not full but you want to store user data (documents, pictures, music and so on) on a different drive, e.g. so you can easily restore an image of Windows without copying your user data back after the restore.

 

Ways to free up disk space on drive C

The are more ways to free up disk space. However I want to focus on one solution, here is a (non-complete) list of things you could do:

  • Compress folders that you do not use frequently. This will save disk space but will cost cpu power when accessing the files in that folder(s).
  • If you have partitioned your disk, increase the size of the C-drive (and therewith decrease the size of the D-drive).
  • Using Windows utilities to free up disk space (can help but is not always you can regain enough diskspace with this)
  • Uninstall programs you do no longer need.
  • Use junctions. This is the solution I will dive into in this blog post.

 

Using junctions – prerequisites

There is one important requirement when it comes to junctions: you need to have more than one (fixed) disk or partition in your computer. If it is a second physical disk or a second partition on one disk does not matter. And of course you need to have free disk space on that second disk or partition.

So when you have a notebook with only a small sized SSD, and it is full, junctions can not do much for you.

WARNING! Don’t make junctions pointing to folders on removable drives, like USB pen drives, SD cards and so on for system-critical folders, otherwise applications might stop working or Windows would not even start!

Using junctions – how it works

Definition

First of all, what the heck is a junction?

I got to know junctions (also called a soft link) as a shortcut for a folder. This shortcut behaves like a folder but redirects all applications that access that folder to a different folder. Cool, hey?

If you want to know more: read more on MSDN

A junction in the Netherlands (Rotterdam)
A traffic junction in the Netherlands (Rotterdam)

Examples of use

An application taking up too much space

Okay, suppose you have Garmin software installed, you have only a few gigabytes free disk space and want to install a Garmin update on your device. Before starting to download and install the update, this is what you can do:

  1. Create a cmd script and store it on your computer (call it e.g. create_junctions_garmin.cmd)
  2. Paste this into the empty cmd file. Adjust the bold folder names if needed.

@echo off
echo — STEP 1 – CREATE TARGET FOLDER(S) —
pause
mkdir D:\Junctions
mkdir D:\Junctions\ProgramData
mkdir D:\Junctions\ProgramData\Garmin
echo Please check that the previous step was completed succesfully . . .
echo If errors occurred close the command window WITHOUT PRESSING ENTER ! ! !
pause
echo — STEP 2 – COPY FOLDER AND ALL SUBFOLDERS AND FILES —
pause
xcopy “C:\ProgramData\Garmin” “D:\Junctions\ProgramData\Garmin” /E /K /X
echo Please check that the previous step was completed succesfully . . .
echo If errors occurred close the command window WITHOUT PRESSING ENTER ! ! !
pause
echo — STEP 3 – REMOVE SOURCE FOLDER —
pause
rmdir /s /q “C:\ProgramData\Garmin“
echo Please check that the previous step was completed succesfully . . .
echo If errors occurred close the command window WITHOUT PRESSING ENTER ! ! !
pause
echo — STEP 4 – CREATE JUNCTION FOR SOURCE FOLDER —
pause
mklink /j “C:\ProgramData\Garmin” “D:\Junctions\ProgramData\Garmin“
pause

3. Run the cmd file as Administrator and check that it runs without errors. Alternatively you could also move the folder including subfolders and files manually and create a cmd script that only contains STEP 4 – CREATE JUNCTION FOR SOURCE FOLDER

If files are locked, maybe you have to reboot and start Windows in Safe mode, then run the cmd script again.

Moving userdata to a different drive

However Windows offers to some extend the possibility to move these folders, in my experience still downloaded files, pictures or documents sometimes are stored on the C-drive. By creating junctions this is not the case.

I recommend to move this user folders manually and create only the junctions with a cmd script.

Warning / disclaimer: However this works fine for me on Windows 7 and 10 and I do not expect any problems if you follow the procedure, please try this at your own risk.

Note: this works also on non-English versions of Windows as the displayed localized folder names (e.g. in Dutch: “Documenten” for “Documents”) are not the actual folder names. So for the junctions you need to use the English names.

This is what you can do (adjust the bold folder names: replace Hans with your Windows username and replace D:\USERDATA with some other folder if you like)

  • Create a folder D:\USERDATA\Hans
  • Create a cmd script and store it on your computer (call it e.g. create_junctions_userdata.cmd)
  • Paste this into the empty cmd file

Note that you can select the folders of your choice, this list is just an example.

mklink /j “C:\Users\Hans\Downloads” “D:\USERDATA\Hans\Downloads”
mklink /j “C:\Users\Hans\Contacts” “D:\USERDATA\Hans\Contacts”
mklink /j “C:\Users\Hans\Favourites” “D:\USERDATA\Hans\Favourites”
mklink /j “C:\Users\Hans\Music” “D:\USERDATA\Hans\Music”
mklink /j “C:\Users\Hans\OneDrive” “D:\USERDATA\Hans\OneDrive”
mklink /j “C:\Users\Hans\Pictures” “D:\USERDATA\Hans\Pictures”
mklink /j “C:\Users\Hans\Videos” “D:\USERDATA\Hans\Videos”
mklink /j “C:\Users\Hans\Searches” “D:\USERDATA\Hans\Searches”
pause

  • Use Windows Explorer to go to the folder C:\Users\Hans
  • Select the following folders while pressing the CTRL key: Contacts, Documents, Downloads, Favourites, Music, OneDrive, Pictures, Searches, Videos.

Note that you can select the folders of your choice, but make sure every folder selected is in your cmd script.

  • Press CTRL+X (Cut)
  • Navigate to the folder D:\USERDATA\Hans
  • Press CTRL+V (Paste)
  • Run the cmd file as Administrator and check that the junctions are created.

User_junctions_cmd

For your interest, on the Windows drive the little arrow in the corner of the folder icon reveals that these “folders” are now actually junctions.

User_junctions

Conclusion / Wrap up

With junctions you can replace a Windows folder with a “shortcut” to a different folder.

This can amongst others be handy when you have lack of disk space on the Windows drive.

It is strongly recommended to create only junctions that redirect to folders on fixed drives.

I have explained some examples of how you can use junctions, but you can use junctions for other applications as well. Beware that in some cases files in folders you want to make a junction for can be locked. Stopping the Windows Services or applications accessing those files can help, or starting Windows in Safe Mode (I must admit I did not try this but in theory it should work).

(c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.