Today was the proof that I have a lot to learn about DBA tasks. I took on a somewhat simple task of renaming a database that turned into a 30-minute ordeal. Hopefully, this post will save you some time by avoiding issues that I ran into. Below I outlined one of the possible ways of getting this task done. Please note that this can result in outage and should not be done in production (live) environment, unless you are confident in what you are doing and it is done during a maintenance window.
Step 1
Before we can rename DB we need to identify Logical and Physical file names. I recommend copying result to notepad or somewhere else as it will be easier to paste it into later queries. To get names we can query system table in master DB. In my scenario, my Database Name is "VladTesting"
USE [master]
GO
/* Important to get logical and physical file names */
SELECT name AS [Logical Name]
,physical_name AS [DB File Path]
,type_desc AS [File Type]
,state_desc AS [State]
FROM sys.master_files
WHERE database_id = DB_ID(N'VladTesting')
GO
Logical Name
DB File Path
File Type
State
VladTesting
C:\SQL Server Data Files\MSSQL11\Data\VladTesting.mdf
ROWS
ONLINE
VladTesting_log
C:\SQL Server Data Files\MSSQL11\Log\VladTesting_log.ldf
LOG
ONLINE
Step 2
Now we are ready to start the process. Before logic names can be renamed we need to have exclusive rights to DB.
ALTER DATABASE VladTesting
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Step 3
Now when we execute the following statement it will rename logical file names.
ALTER DATABASE [VladTesting] MODIFY FILE
(NAME=N'VladTesting', NEWNAME=N'VladsAwesomeDB')
GO
ALTER DATABASE [VladTesting] MODIFY FILE
(NAME=N'VladTesting_log', NEWNAME=N'VladsAwesomeDB')
GO
Step 4
At this point we have several options, the following is one of the easiest. We need to DETACH database, rename files and than attach it with new name later.
Another option is to take DB Offline and rename files and later rename DB.
EXEC master.dbo.sp_detach_db @dbname = N'VladTesting'
GO
Step 5
Now to rename the files it can be done from SSMS with TSQL or directly on file system. NOTE: When I ran the following command it messed up file permissions, this should be used with caution. It is safer to ask System Admins or someone else with proper file system permissions to rename files directly on file system, then it is possible to shift the blame if something goes wrong (just kidding).
Most of the time XP_CMDSHELL is not enable as it is considered dangerous, to enable it, just run the following code.
EXEC sp_configure 'show advanced options' ,1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell' ,1;
GO
RECONFIGURE;
GO
Step 6
Now to actually rename the files, we simply provide old file name with path and new filename to the XP_CMDSHELL command.
In order to execute rename command we first need to enable XP_CMDSHELL if it is not already enabled.
EXEC xp_cmdshell 'RENAME "C:\SQL Server Data Files\MSSQL11\Data\VladTesting.mdf", "NewDatabaseName.mdf"'
GO
EXEC xp_cmdshell 'RENAME "C:\SQL Server Data Files\MSSQL11\Log\VladTesting_log.ldf", "NewDatabaseName_log.ldf"'
GO
Step 7
Now for the last step. We attach DB with new name and new physical file names that we just renamed.
CREATE DATABASE VladsAwesomeDB ON
( FILENAME = N'C:\SQL Server Data Files\MSSQL11\Data\VladsAwesomeDB.mdf' ),
( FILENAME = N'C:\SQL Server Data Files\MSSQL11\Log\VladsAwesomeDB_log.ldf' ) FOR ATTACH
GO
ALTER DATABASE VladsAwesomeDB SET MULTI_USER
GO
Step 8 - Housekeeping
Since xp_cmdshell considered dangerous it is best to disable before we forget that it had to be enabled. First command will disable xp_cmdshell and second command will hide advanced options.
EXEC sp_configure 'xp_cmdshell' ,0;
GO
RECONFIGURE;
GO
EXEC sp_configure 'show advanced options' ,0;
GO
RECONFIGURE;
GO
So now you know how to rename database. Usually this should only be done when there is specific business reason to do so. If command sp_renamedb or ALTER DATABASE Modify Name is used, it does not rename physical or logical files which can cause issues later when someone tries to create new database with same name as last one or someone does restore of backup without changing options.
This topic takes me back to my first SQL Server database class I had in college. Back then it was extremely difficult subject for me. Partly because I have worked very little with databases and secondary, because of the way it was presented. Taking all that into account let's try to attack it in most basic manner. SQL JOIN in its basic form is just a way to connect two tables together. There are several types of JOINs that can be defined: LEFT, RIGHT, FULL, INNER, CROSS. There are time and place for each one, but most of the time I can get away with using only LEFT and INNER.
Its is great to talk about JOINs, but I'm sure that there are other people who like me don't learn but hearing, they learn by seeing and trying it them self. To do that, we need to create two basic tables and populate it with some data.
First, let us create two tables to store data for our tests. Since the concept of student and teacher is most easily understood, I'm creating Teacher table and Student table with a key to link back to Teacher Table.
Now that we got some data loaded into tables let the JOINing commence. First will look at 'INNER' JOIN
SELECT t.FullName AS TeacherFullName
,s.FullName AS StudentFullName
FROM Teacher t
INNER JOIN Student s
ON s.TeacherID = t.ID
INNER JOIN throws out all records that do not match. In our case Teacher "Sonnie Davin" and Student "Dion Hayden" do not appear in result because there was no way to connect those records.
TeacherFullName
StudentFullName
Roy Chad
Jepson Balfour
Roy Chad
Milburn Brett
Dudley Goddard
Clinton Schuylerr
Dudley Goddard
Norbert Kemp
Raphael Philander
Meriwether Kennedy
Raphael Philander
Braith Cornelius
LEFT OUTER JOIN, returns all records from main table and attempts to match records from secondary table.
SELECT t.FullName AS TeacherFullName
,s.FullName AS StudentFullName
FROM Teacher t
LEFT OUTER JOIN Student s
ON s.TeacherID = t.ID
As seen in this result list, Teacher "Sonnie Davin" appears in the list but does not have any student records associated. LEFT JOIN is most usefull when ever you not sure if you all records from main table are matched in secondary table. Almost every time I write query I use LEFT JOIN the first I'm JOINing tables together to know for sure that I'm not excluding any records that I wanted to include.
TeacherFullName
StudentFullName
Roy Chad
Jepson Balfour
Roy Chad
Milburn Brett
Dudley Goddard
Clinton Schuylerr
Dudley Goddard
Norbert Kemp
Raphael Philander
Meriwether Kennedy
Raphael Philander
Braith Cornelius
Sonnie Davin
NULL
RIGHT OUTER JOIN returns all records from RIGHT table and matches records from left table. The following two queries can be written as RIGHT or LEFT join with same results.
SELECT t.FullName AS TeacherFullName
,s.FullName AS StudentFullName
FROM Teacher t
RIGHT OUTER JOIN Student s
ON s.TeacherID = t.ID
SELECT t.FullName AS TeacherFullName
,s.FullName AS StudentFullName
FROM Student s
LEFT OUTER JOIN Teacher t
ON s.TeacherID = t.ID
In this result set because we reversed the condition of LEFT JOIN above, we can see record from Student table "Dion Hayden" who is not matched with a teacher record.
TeacherFullName
StudentFullName
Roy Chad
Jepson Balfour
Roy Chad
Milburn Brett
Dudley Goddard
Clinton Schuylerr
Dudley Goddard
Norbert Kemp
Raphael Philander
Meriwether Kennedy
Raphael Philander
Braith Cornelius
NULL
Dion Hayden
FULL OUTER JOIN attempts to match records from both tables just like INNER JOIN and also returns all additional records that do not match from ether table.
SELECT t.FullName AS TeacherFullName
,s.FullName AS StudentFullName
FROM Teacher t
FULL OUTER JOIN Student s
ON s.TeacherID = t.ID
In the this result set we have all records that we had from INNER JOIN, plus extra record from LEFT JOIN and one record from RIGHT JOIN. Teacher "Sonnie Davin" appears in result set without student and Student "Dion Hayden" also appears in result set without teach. This join is most usefull when need to get all records no matter if match exists or not.
TeacherFullName
StudentFullName
Roy Chad
Jepson Balfour
Roy Chad
Milburn Brett
Dudley Goddard
Clinton Schuylerr
Dudley Goddard
Norbert Kemp
Raphael Philander
Meriwether Kennedy
Raphael Philander
Braith Cornelius
Sonnie Davin
NULL
NULL
Dion Hayden
CROSS JOIN, returns all records from main table and matches them to every record from secondary table.
SELECT t.FullName AS TeacherFullName
,s.FullName AS StudentFullName
FROM Teacher t
CROSS JOIN Student s
What we get back is the following result set that has every record from both table. Since no condition has be specified every record appears in result set. Number of records in result set will always be equal to Number of records in TableA multiple by number of record in TableB. In our case we had 4 Teachers and 7 Students therefore we got 28 records in result. But what if we had 4 million teachers and 70 million students. This JOIN has worst performance and will require most memory to complete the operation.
TeacherFullName
StudentFullName
Roy Chad
Jepson Balfour
Roy Chad
Milburn Brett
Roy Chad
Clinton Schuylerr
Roy Chad
Norbert Kemp
Roy Chad
Meriwether Kennedy
Roy Chad
Braith Cornelius
Roy Chad
Dion Hayden
Dudley Goddard
Jepson Balfour
Dudley Goddard
Milburn Brett
Dudley Goddard
Clinton Schuylerr
Dudley Goddard
Norbert Kemp
Dudley Goddard
Meriwether Kennedy
Dudley Goddard
Braith Cornelius
Dudley Goddard
Dion Hayden
Raphael Philander
Jepson Balfour
Raphael Philander
Milburn Brett
Raphael Philander
Clinton Schuylerr
Raphael Philander
Norbert Kemp
Raphael Philander
Meriwether Kennedy
Raphael Philander
Braith Cornelius
Raphael Philander
Dion Hayden
Sonnie Davin
Jepson Balfour
Sonnie Davin
Milburn Brett
Sonnie Davin
Clinton Schuylerr
Sonnie Davin
Norbert Kemp
Sonnie Davin
Meriwether Kennedy
Sonnie Davin
Braith Cornelius
Sonnie Davin
Dion Hayden
This concludes the first look at logical JOIN operations. Just like I mentioned in beginning of the post, just about every time I write query it ends up being LEFT or INNER JOIN. Do I ever had to use others? Yes, but with specific business cases.
Please leave a comment with questions and/or feedback on post.
