Alter Database with Database and Files rename and Relocating Files in SQL Server – Part 3

UDEMY DISCOUNTED ASP.NET CORE 3.1 BLAZOR COURSE (VALID FIVE DAYS)
(Valid Till 04/19/2020 01:04 AM PDT (GMT -7)) DISCOUNTED UDEMY COURSE LINKS VALID ONLY 4 DAYS (Expires 04/16/2020 19:04 PM PDT (GMT -7))


This is part 3 of the SQL playlist series. In this tutorial we shall learn:
How do we alter a database name (rename) on SQL Server?
Alter database logical and physical file names
Moving database physical files to New Locations. In general this video covers in detail as follows:
in MSSQL, we can use either all capital or mixed casing for key words like CREATE, SELECT, ALTER, FROM, WHERE etc.
SQL statements can be terminated by a ; (or you could drop the semi-colon
A) Rename a Database
1. Use SSMS to rename database manually
2. Use the command to alter database name like so:
Use Master
Alter Database MyDB2 Modify Name = MyDB1

3. Use sp_renameDB like so:
Exec sp_renamedb 'MyDB2', 'MyDB1'

B) Rename Logical Database and Physical Database File Name
SQL Server database files have 2 different names 1) Logical file name and 2) Physical file name
Logical file name : The logical_file_name is the name used to refer to the physical file in all Transact-SQL statements.
Physical file name: It is the name of the physical file including the directory path
By running the following query, we can see both the files as follows:
SELECT name AS logical_name, physical_name AS CurrentLocation
FROM MyDB.sys.database_files -----------------------------(1)
When we want to do any SQL related operation (also known as T-SQL or transact SQL), you’ll have to use the logical name in our SQL Server commands. Although some people like to keep the logical filename unique across their SQL Server instance, that’s however not a requirement from SQL Server. Change the logical database file name (I will use MyDB as database)
Use master
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE MyDB SET OFFLINE
Next we’ll have to go to the file system and rename the file to a new name. I shall change the data file name.
ALTER DATABASE MyDB MODIFY FILE(Name = ‘MyDB_Data’, FILENAME = ‘G:\SQL2016_Enterprise_ServicePack1\MSSQL13.MSSQLSERVER\MSSQL\DATA\MyDB_Data.mdf’)
We can run the following command once the above runs successfully, to bring back the database online and in multi user mode
ALTER DATABASE MyDB SET ONLINE
ALTER DATABASE MyDB SET MULTI_USER
C) Move physical files to other location
Sometimes it is necessary to relocate the physical files that comprise a SQL Server database. The recommended approach to moving the location of database files is using ALTER DATABASE commands.

1. Determine the current location of the files
SELECT name, physical_name AS CurrentLocation
FROM MyDB.sys.database_files
We can once more run the query to check the logical and physical file names
2. Then take the database off-line by right clicking the database and click task -- off-line or by query
ALTER DATABASE MyDB SET OFFLINE
3. Move the mdf and ldf files to the new location say : G:\SQL2016\DB Files
4. Run the query :
Alter Database MyDB
Modify File (Name = MyDB, FileName = ‘G:\SQL2016\DB Files\MyDB.mdf’)
Alter Database MyDB
Modify File (Name = MyDB_log, FileName = ‘G:\SQL2016\DB Files\MyDB_log.ldf’)
5. Then take the database on-line by right clicking the database and click task and then on-line or by query
ALTER DATABASE MyDB SET ONLINE
6. Verify the file change by once again running the following:
SELECT name, physical_name AS CurrentLocation
FROM MyDB.sys.database_files

Alter Database SQL Server,Alter Database MS-SQL Tutorial,Rename Database by ALTER DATABASE,SQL rename logical file,SQL Rename Physical Files,Rename Logical Database and Physical Database File Name,Move physical files to other location,SQL Tutorial,T-SQL Statements,T-SQL,Taking Database Offline,Database Online,Single User With Rollback,SINGLE_USER WITH ROLLBACK IMMEDIATE,Multi-User SQL Database,MS-SQL Tutorial,kaushik roy chowdhury,database,