Renaming database and physical database files

1.Rename a Database using Management Studio:

• Renaming Database:

Right Click the Database Name in Object Explorer and Select "Rename", now you can specify new name:

However, this does not change the database file names:

SQL Query: SELECT      name, physical_name

           FROM        [SqlAndMe].sys.database_files

Result Set:

name            physical_name

--------        ----------------

MyDatabase     C:\…\MyDatabase.mdf

MyDatabase_log C:\…\MyDatabase_log.LDF

 

(2 row(s) affected)

• Renaming Files:

To change filenames, Right Click on Database in Object Explorer and Select "Properties", Then, go to "Files" Tab:

Here, you can change the logical filenames for DATA and LOG files.

2. Renaming a Database using T-SQL:

• Renaming Database:

To Rename a database using T-SQL, use below script:

 USE [master]

--Set Database to Single-User Mode

ALTER DATABASE [MyDatabase] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE




--Rename Database

ALTER DATABASE [MyDatabase] MODIFY Name = [SqlAndMe]




--Set Database to Multi-User Mode

ALTER DATABASE [SqlAndMe] SET  MULTI_USER WITH ROLLBACK IMMEDIATE

Result Set:

The database name 'SqlAndMe' has been set.

 

•  Renaming Files:

--Rename Logical File Names

ALTER DATABASE [SqlAndMe]

            MODIFY FILE (NAME=N'MyDatabase', NEWNAME=N'SqlAndMe')

ALTER DATABASE [SqlAndMe]

           MODIFY FILE (NAME=N'MyDatabase_log',NEWNAME=N'SqlAndMe_log')

Result Set:

The file name 'SqlAndMe' has been set.

The file name 'SqlAndMe_log' has been set.

 

However, none of these two methods can change the physical database file name, you can check this using sys.database_files:

SQL Query:SELECT      name, physical_name

FROM        [SqlAndMe].sys.database_files

 

Result Set:

name          physical_name

——---        —————------------

SqlAndMe      C:\…\MyDatabase.mdf

SqlAndMe_log  C:\…\MyDatabase_log.LDF


  

(2 row(s) affected)

 

Sometimes we also need to rename the physical database files, this cannot be done via two methods described above.

3. Renaming a Database using detach/attach:


1. Detach database:
You can detach a database either using Management Studio or T-SQL, To detach a database using Management Studio, right-click on database > go to "Tasks" > "Detach", click OK to detach a database,
To detach a database using T-SQL, you can use master..sp_detach_db procedure:

--Detach Database using sp_detach_db

SQL Query:USE [master]

          ALTER DATABASE [MyDatabase]

          SET SINGLE_USER WITH ROLLBACK IMMEDIATE




        EXEC master.dbo.sp_detach_db @dbname = N'MyDatabase'

 2.Rename Physical files:
Once the database is detached, SQL Server releases the locks on physical files, now you can rename the physical files using Windows Explorer, or command prompt:

 

 

 3. Attaching database with New Name:
Once, the physical files have been renamed, you can attach the database using new database and file names:

-– Attach Database

SQL Query:USE [master]

          CREATE DATABASE [SqlAndMe] ON

          ( FILENAME = N'C:\…\SqlAndMe.mdf'),

          ( FILENAME = N'C:\…\SqlAndMe_log.LDF')

          FOR ATTACH

 

--Rename Logical file names

SQL Query: USE [SqlAndMe]

           ALTER DATABASE [SqlAndMe]

         MODIFY FILE (NAME=N'MyDatabase', NEWNAME=N'SqlAndMe')

          ALTER DATABASE [SqlAndMe]

         MODIFY FILE (NAME=N'MyDatabase_log', NEWNAME=N'SqlAndMe_log')




SQL Query: SELECT      name, physical_name

FROM        [SqlAndMe].sys.database_files

Result Set:

name          physical_name

————  —————–

SqlAndMe      C:\…\SqlAndMe.mdf

SqlAndMe_log  C:\…\SqlAndMe_log.LDF


 

(2 row(s) affected)

SPACE function will return maximum of 8000 spaces only

• We can use SPACE function to replicate spaces for a string. Consider the following statement 

 SQL Query: select 'A'+space(10)+'B'
The result is:  A          B

• As you see space function adds 10 spaces between the string A and B. Note that the space function though accepts any positive number, it will return maximum of 8000 spaces only which you can confirm from the following example  

SQL Query: select datalength(space(10000))

• The above returns 8000. So you should be aware of this. The alternate solution to have more spaces is to use replicate function as shown below

SQL Query: select datalength(replicate(cast(' ' as varchar(max)),10000))  

• Which returns 10000. Also note that replicate returns maximum 8000 characters so we need to convert the string to varchar(max) datatype to have more chanracters. 

• Varchar(max) datatype and Replicate function

  SQL Query: declare @v varchar(max)
             set @v=replicate('a',50000)
             select len(@v),datalength(@v)
  • Note that the result is not 50000 but 8000 because by default the result is limited to the maximum size of 8000 for varchar/char datatype

  To get a correct result, you need to convert the expression to the type of varchar(max)

  SQL Query:declare @v varchar(max)

            set @v=replicate(convert(varchar(max),'a'),50000)

            select len(@v),datalength(@v)

  • Now the result is 50000 as expected.

  So you need to be aware of this implicit convertion when using Replicate function to assingn value to the column of varchar(max) datat

   

   

Deterministic Functions and Nondeterministic Functions

Deterministic functions: always returns the same output result all the time it is executed for same input values.

             Exp: ABS, DATEDIFF, ISNULL etc.

Nondeterministic functions: may return different results each time they are executed. 

             Exp: NEWID, RAND, @@CPU_BUSY etc. 

Functions that call extended stored procedures are nondeterministic. User-defined functions that create side effects on the database are not recommended.