[MSSQL 2012] Rename Database .mdf and .ldf File Names

There are a couple of ways you can achieve this which includes:

1. While restoring the database
2. After restoring the database

Lets take a look at each.

While Restoring the Database

Create a new database by right click Databases in the Object Explorer > New Databases. Name the database <dbName>, and press OK, This will create two files as <dbName>.mdf and <dbName_log>.ldf representing both mdf and ldf files respectively.

From Object Explorer, right click the <dbName> and select Tasks > Restore > Database.

General Tab: 

• select the Source as existing Database or from some backup from device.
• from the Destination database, select the <dbName>

Options Tab:

• Check Overwrite the Existing Database (WITH REPLACE) - because we're using the newly created database.

Files Tab:

• For Row Data file type, select ... from 'Restore As' column and from open dialog, select <dbName>.mdf, or type in the name you like
• For Log file type, select ... from 'Restore As' column and from open dialog, select <dbName_log>.ldf or type in the name you like

Press OK. This will restore the database using the file name of your choice.

NOTE: For future restoration, you may required to rename the logical name of the database. To do this, right click the <dbName> and select Properties. On Files tab, under Database Files, rename the logical names for both Row Data and Log types to <dbName> and <dbName_log>, if the names are different from your selected database.

After Restoring the Database

For <dbName> database, if it is restored with different file name <diffName>.mdf and <diffName>_log.ldf and would like to rename it as <dbName>.mdf and <dbName>.ldf, we would do the following:

--1# View Database file types and relevant physical file name with absolute path

SELECT      name, physical_name

FROM        <dbName>.sys.database_files;

We'll use the retrieved names to rename the files. Lets say the logical names are <diffLogicalName> and <diffLogicalName>_log

--2# Turn the database offline

ALTER DATABASE <dbName> SET OFFLINE;

3# Now from windows explorer, navigate to actual physical file location and rename the files manually.

--4# Modify the file name with absolute path

ALTER DATABASE <dbName>

MODIFY FILE (NAME = <diffLogicalName>, 

FILENAME = 'D:\...\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\<dbName>.mdf')

GO

ALTER DATABASE <dbName>

MODIFY FILE (NAME = <diffLogicalName>_log, 

FILENAME = 'D:\...\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\<dbName>_log.ldf')

GO

--5# Turn the database offline

ALTER DATABASE <dbName> SET ONLINE;

This will rename the files of your choice.

Enjoy!

[MSSQL 2012] Drop User Who Owns a Schema

While dropping a user, you may get an error stating:

Drop failed for User '<user_name>'.  (Microsoft.SqlServer.Smo)

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

The error appears when the user owns a schema in database. The drop such user, you're required to check the schema's that this user own.You can do this by using following query:

SELECT s.name

FROM sys.schemas s

WHERE s.principal_id = USER_ID('<user_name>');

Note: <user_name> is your actual user name whom you're trying to drop.

This will return the list of schema that user own. 

Taking each schema one by one, you can change the ownership to another user using following query:

ALTER AUTHORIZATION ON SCHEMA::<schema_name> TO <user2_name>;

Note: <schema_name> is the name of schema returned from previous query and <user2_name> is another existing user, which will own that schema after this change.

After changing the ownership for all the schema returned from first query, if you re-run the same you'll get no result. After this you can easily drop the user.

[Oracle] Some Useful Commands

--Drop User
drop user <user-name> CASCADE;

--Create User
CREATE USER <user-name> PROFILE "DEFAULT" IDENTIFIED BY <password> TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK ;
GRANT "RESOURCE", "CONNECT", "DBA" TO <user-name>;

--Database character set
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';

-- User constraints
SELECT * FROM user_constraints WHERE 
 table_name = '<table-name>' AND constraint_type = 'U'
 and constraint_name = '<constraint-name>';

-- Indexes
SELECT * FROM   user_indexes WHERE  index_name = '<index-name>'

-- See User session
select sid,serial#,status,osuser,machine, program, logon_time, username , 'ALTER SYSTEM DISCONNECT SESSION '''||SID||','||SERIAL#||''' IMMEDIATE;' Kill_Command
from v$session where username in( '<user1>','<user2>') And program not in ('SQL Developer','sqlplus.exe');

-- See locked resources/objects
select object_name, s.sid, s.serial#, p.spid, 'ALTER SYSTEM DISCONNECT SESSION '''||s.sid||','||s.serial#||''' IMMEDIATE;' Kill_Command
from v$locked_object l, dba_objects o, v$session s, v$process p
where l.object_id = o.object_id and l.session_id = s.sid and s.paddr = p.addr;

-- See Version
SELECT * FROM V$VERSION

-- BLOB to Text Conversion
select utl_raw.cast_to_varchar2(dbms_lob.substr(value)) from assumption where assumptionsetid = 84;

-- Search table by column name
select distinct owner, table_name 
from all_tab_columns 
where column_name in ('lic%');

SELECT * FROM COLS WHERE COLUMN_NAME = 'lic%'

[Oracle] Import Database Dumps using "impdp" command

Below are few commands to import database dump in Oracle 11g.

--1. Drop existing user, if already exists:
DROP USER <dest_db> CASCADE;

--2. Create user and schema and grant rights:
CREATE USER <dest_db> PROFILE "DEFAULT" IDENTIFIED BY <password> 
     TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK ;
GRANT "RESOURCE", "CONNECT", "DBA" TO <dest_db>;

--3. test if impdp is support, from command prompt, just write:
impdp

/*it should say:
Import: Release 11.2.0.1.0 - Production on Sat Feb 16 21:13:27 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
*/

--4. To import through dmp file, from command prompt, just write:
impdp <Dest_db>/<password> remap_schema=<source_db>:<dest_db> directory=DATA_PUMP_DIR EXCLUDE=STATISTICS dumpfile=<source_db_dumpfile>.dmp

--where, DATA_PUMP_DIR is the default Oracle directory. To verify write:
select * from ALL_DIRECTORIES

--5. If it says tablespace <tablespace> doesnot exists, then use:
impdp <dest_db>/<password> remap_schema=<source_db>:<dest_db> remap_tablespace=<dest_tablespace>:<source_tablespace> directory=DATA_PUMP_DIR EXCLUDE=STATISTICS dumpfile=<source_db_dumpfile>.dmp

-- where USERS is the table space of destination user, to verify:
select default_tablespace from dba_users where username='<dest_db>';

--6. If it says "only one COMPRESS or NOCOMPRESS clause may be specified", then use following command:
impdp <dest_db>/<password> remap_schema=<source_db>:<dest_db> remap_tablespace=<dest_tablespace>:<source_tablespace> directory=DATA_PUMP_DIR EXCLUDE=STATISTICS dumpfile=<source_db_dumpfile>.dmp transform=segment_attributes:n 

--7. Verify if import is successful by getting the object count both valid/invalid.
select count(*) from user_objects;
select count(*) from user_objects where status='INVALID';

Enjoy!