Skip to content

Rename logical & physical MSSQL files

This post will provide guidance on how to amend the logical and physical file names of a MSSQL database.

When a copy of a database is restored as a new database, the logical file names will remain the same as the source database.

Firstly, check the current logical and physical file names:

USE master
GO
SELECT name          AS [Logical_name],
       physical_name AS [File_Path],
       type_desc     AS [File_Type],
       state_desc    AS [State]
FROM sys.master_files
WHERE database_id = DB_ID(N'Database_name')
GO

Running this query against a database called ‘SSMATEST’ on one of my database servers brings back the following:

Logical_name File_Path                File_Type State
DIRUT        D:\Data\DIRUT.mdf     ROWS      ONLINE
DIRUT_log    D:\Logs\DIRUT_log.ldf LOG       ONLINE

As can be seen, the physical names and logical names don’t match up the name of the database.

Let’s start with the logical names…

ALTER DATABASE [SSMATEST] MODIFY FILE (NAME='DIRUT', NEWNAME='SSMATEST');
GO
ALTER DATABASE [SSMATEST] MODIFY FILE (NAME='DIRUT_log', NEWNAME='SSMATEST_log');
GO

We use pass the current name of the logical file – NAME – and then name that we wish to use as the new name – NEWNAME.

The changes can be verified by running the query at the beginning of the post, the results will show:

Logical_name File_Path                File_Type State
SSMATEST     D:\Data\DIRUT.mdf     ROWS      ONLINE
SSMATEST_log D:\Logs\DIRUT_log.ldf LOG       ONLINE

So, that’s starting to look better, let’s move on to the physical file names.

First, take the database offline, thanks to Perry Whittle for suggesting the use of one ALTER DATABASE statement to achieve the same result as two!

It should be pointed out that you will need to carry this out during a maintenance window if the database is part of a live/production system.

ALTER DATABASE [SSMATEST] SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO

Now rename the files from DIRUT.mdf and DIRUT_log.ldf to SSMATEST.mdf and SSMATEST_log.ldf in the file system via File Explorer or DOS. Once that is done, return to SSMS.

Update the records in the system catalog.

ALTER DATABASE [SSMATEST] MODIFY FILE (Name='SSMATEST', FILENAME='D:\Data\SSMATEST.mdf')
GO
ALTER DATABASE [SSMATEST] MODIFY FILE (Name='SSMATEST_log', FILENAME='D:\Logs\SSMATEST_log.ldf')
GO

Check the message to ensure that there were no problems.

The file "SSMATEST" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "SSMATEST_log" has been modified in the system catalog. The new path will be used the next time the database is started.

Bring the database back online.

ALTER DATABASE [SSMATEST] SET ONLINE;
GO

Again, use the query at the top of the post to verify the changes are all good.

Logical_name File_Path                File_Type State
SSMATEST     D:\Data\SSMATEST.mdf     ROWS      ONLINE
SSMATEST_log D:\Logs\SSMATEST_log.ldf LOG       ONLINE

There we have it!

Both the logical and physical file names have been updated to reflect the name of our database.

Advertisements

SnipIT No 7: Change colour when using “ls”

SnipIT is a series of short blogs containing useful information in an easily digestible format.

Listing the files and directories in a PuTTY session when connected to Red Hat recently, I realised how bad the contrast is between the default dark blue on black.

ls_colour_01

To change this to something more usable, we can edit the bashrc file.

$ vi ~/.bashrc

With the file open in vi, press “i” to enter insert mode and move to the end of the document.

Add the following line:

LS_COLORS=$LS_COLORS:'di=0;37:'; export LS_COLORS

Press “:” and then “wq” to write the changes, if you want to quit the file without saving the changes then press “:” and “q!”.

For the changes to take affect in the current session, type:

$ source ~/.bashrc

Let’s try that out with a “ls -al”

ls_colour_02

That’s much better!

if you prefer a different colour then you can substitute “37” for another value, the “0” is a switch between standard and bold (“1”).

 

Puppet – Introduction

Puppet – Introduction

Puppet is a configuration management tool which can be help to address the following challenges:

  • Deploying servers with a consistent configuration.
  • Addressing inconsistent configurations (config drift).
  • Application deployment.
  • Automation – as part of CI & CD, get it down faster!

Before the likes of Puppet and Chef, SysOps typically would have a repository of scripts used to configure servers. Despite the arrival of configuration management tools, the use of scripts is still common place. So, why not use scripts?

  • Scripts take time to write, test & debug.
  • Scripts are procedural and focus on how something is done rather than the end result. This can involve high levels of complexity.
  • Scripts are idiosyncratic – there are many ways to achieve the same objective. As people have their own approach, it may not be easy for someone else to understand what is happening.
  • Scripts need to handle different Operating Systems and versions such as package handles (yum/apt-get).

Why use Puppet?

Puppet uses definition files (manifests) which are declarative – they describe the desired configuration state (DCS) rather than how to get there. The manifests are easier to read and understand, less complex (few lines and less logic), plus are pretty much self-documenting.

Puppet also abstracts the resources which means that services can be defined once and then configured across multiple servers e.g. the configuration of Apache can be defined in a manifest which is then used on different *nix platforms such as Red Hat or Solaris. Other resources include patches, software packages, file system objects (files & folders) and user accounts/groups.

With the availability of alternatives, why use Puppet?

According to a review of leading configuration management tools by InfoWorld (2013), Puppet achieved maximum points across six categories outscoring Ansible, Chef and SaltStack.

Puppet will help you perform the following tasks:

  • Enforce consistent configuration across all servers.
  • Deploy applications as frequently as desired.
  • Manage all of the application configurations.
  • Spend less time writing, testing and debugging.
  • Minimise configuration mistakes and downtime.
  • Automate all of the above.

Docker and Containers: The Big Picture

These are my notes from Nigel Poulton’s course on PluralSight, “Docker and Containers: The Big Picture“. Do check out his videos, he’s an easy guy to listen to and explains the subject matter in a digestible manner. Yo can also follow him on Twitter – @nigelpoulton

What are containers?

Hypervisor

  • Takes slice of physical server resource to run OS and apps.
  • OS uses RAM, CPU & disk space just to run.
  • Licence costs for OS for each virtual server.
  • Resource and budget cost – CAPEX (Capital Expenditure) and OPEX (Operational Expenditure).
  • Requirement to manage and patch each VM.

Containers

  • One physical server, one OS then e.g. four containers hosting four apps.

Container Demo

  • Download container image.
  • Create app from image.
  • Start container, stop container.
  • Able to map port of host to port within the container.

Containers = Virtualisation 2.0

The Docker Project

  • Open Source, not owned by Docker Inc.
  • Build better, Ship better and Deploy better.
  • Docker Hub
    • Store and retrieve Docker images.
    • >240k repositories.
    • Public and Private repositories.

Preparing to Thrive

  • Get hands on with it.
  • Start making it official: CI/CD workflows.
  • Distributed apps/services – run some in containers, some  in VMs (infra services).
  • Tools: orchestrate deployments, look at logs.
  • Don’t neglect infrastructure to support containers: orchestration, clustering, management, monitoring and logging.
  • Datadog stats (datadoghq.com): companies who adopted containers tripled the number in use within six months.

What Kind of Work Will Containers Do?

  • Stateless: does not keep any changes or data, e.g. web server.
  • Stateful: keeps changes and data, e.g. database server.
  • Containers can do both.
  • Can put legacy apps in containers but missing the point – opportunity to rethink: micro services (many services combine to create one app).
  • Docker containers persist data just the same as a VM does.

Docker Hub (Container Registries)

  • Place to store and retrieve container images.
  • docker pull <image name> e.g. docker pull Mongo
    • pulls Mongo container image.
  • Registry can have one or many repositories.
  • Images can be public or private.
    • Public images can be pulled by anyone but only pushed by authorised users.
  • Can create private repositories – within corporate firewall.
  • Docker Trusted Registry (DTR).

Automated Workflow

  • App Update -> Software Repo -> Testing -> Container Registry -> Deployment -> On Premise/Cloud
Docker Automated Workflow

Docker Automated Workflow

Ready for Production & Enterprise?

  • Docker Engine available in different versions:
    • Experimental
    • Stable
    • Commercially Supported (CR)
  • Docker Swarm (clustering) is laid on top of several Docker engines.
  • Docker Content Trust

Container Orchestration

Orchestration: taking something that was manual and automating it.

  • Apps compromise multiple parts.
  • Define services/components that make up the app.
  • Define how they fit together: networking and API calls.
  • Deploy to containers.
  • Docker Machine: Provisions Docker hosts/engines.
  • Docker Compose: Compose multi-container apps.
  • Docker Swarm: Schedule containers over multiple Docker engines.
  • Tutum: Sits across Machine, Compose & Swarm providing a UI.

Removing objects

This is a useful feature of Photoshop.

There are two phases in removing an object from an image:

  1. The removal of the object
  2. Filling the area left by the removed object

1 – The removal of the object

  • Selecting the Rectangular Marquee tool.
  • Creating a marquee (click and drag action) around the object that is to be removed.
  • Selecting the Zoom tool
  • Zooming in on the selected content.
  • Selecting the Pen tool.
  • Selecting the Paths item on the toolbar.
  • Using the Path tool we must click at very short intervals till we surround the content to be removed. The shortest the intervals of the clicks the more detailed the outcome.
  • Clicking the box that started the selected path (first selection) when the surrounding area is complete.
  • Right-clicking in the selected area (path) to access the Make Selection dialog box.
  • Selecting Anti-Aliased that provides a smooth outline around the selected object.
  • Clicking OK in the dialog box creates a path around the object.
  • Clicking the Image -> Cut button removes the selected object.
  • Choosing the Select ( Deselect menu item removes the selection.

2 – Filling the area left by the removed object

  • Selecting the Lasso tool.
  • Creating a selection similar to the one that was removed, ensuring the selection contains elements of the image that are expected to be in the place of the removed object (e.g. background plantation). This step requires dragging with the Lasso tool.
  • Choosing the Select -> Modify -> Feather menu item.
  • Entering 5 in the radius field will help feathering to blend the photograph patch with the rest of the photograph.
  • Clicking OK to confirm the feathering
  • Clicking on the selection that is created and holding the Command key will allow to move the selection till the removed area is covered.
  • Choosing Select -> Deselect menu item to complete the move.
  • Repeating the process till the missing part of the image is filled.
  • Zoom out to view the finished results.

Further info on copying while moving from Adobe.

Black and white conversion

Black and white conversion may be required when producing black & white printed material or to provide an ageing effect.

  • Choosing the Image -> Duplicate menu item.
  • Using the Duplicate Image dialog box.
  • Entering a name for the duplicate image to be created.
  • Using the duplicate image we are ready to convert it to black and white.
  • Choose the Image -> Mode -> Grayscale menu item.
  • Discarding any colour information when prompted.

Image Cropping

Using the crop tool it is possible to remove excess pace from the image.

  • Selecting the Crop tool.
  • Highlighting (click and drag motion) the portion of the photograph that we wish to keep.
  • Adjusting the edges of the highlighted area (click and drag motion on the top, bottom, right and left of the highlighted area).
  • Adjusting the highlighted area proportionately.
  • Choosing the menu item Image -> Crop.