BCP Command

There are various methods available for bulk data operations.

1. BCP utility
2. BULK INSERT
3. Using OPENROWSET
4. Import/Export wizard

The BCP (Bulk Copy Program) utility is a command line that program that bulk-copies data between a SQL instance and a data file using a special format file. The BCP utility can be used to import large numbers of rows into SQL Server or export SQL Server data into files. The BCP data files don’t include any schema details or format information

BCP  DatabaseName.SchemaName.[TableName | ViewName] | Query

     [IN datafile | OUT datafile | queryout datafile | format] 

     [Options]

First enble CMDSHELL

Use Master

GO

EXEC master.dbo.sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 1

RECONFIGURE WITH OVERRIDE

GO

EXEC master..xp_cmdshell 'BCP [AdventureWorks2012].[Person].[Address] OUT c:\Person.xls -T -c'

NOT IN and NULL ( a dangerous combination)

Since NULL represents an unknown value, looking for values in the outer table that are not unknown (i.e. employees’ ids that are not in a list that contains NULL values) would return 0 rows.

ALTER USER DEFINED DATATYPE

We cannot alter the User-defined types after creation.

PROCEDURE

CIS Benchmark : Center for internet security

1 Installation, Updates and Patches
 Ensure Latest SQL Server Service Packs and Hotfixes are Installed

SELECT SERVERPROPERTY('ProductLevel') as SP_installed, SERVERPROPERTY('ProductVersion') as Version;

2. Ensure Single-Function Member Servers are Used

Ensure that no other roles are enabled for the underlying operating system and that no excess tooling is installed, per enterprise policy.

3.Surface Area Reduction 

Ensure 'Ad Hoc Distributed Queries' Server Configuration Option is set to '0'

SELECT name, CAST(value as int) as value_configured, CAST(value_in_use as int) as value_in_use FROM sys.configurations WHERE name = 'Ad Hoc Distributed Queries';

EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE; EXECUTE sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
 GO
EXECUTE sp_configure 'show advanced options', 0; RECONFIGURE;

4 Ensure 'CLR Enabled' Server Configuration Option is set to '0'

SELECT name, CAST(value as int) as value_configured, CAST(value_in_use as int) as value_in_use FROM sys.configurations WHERE name = 'clr enabled';

 EXECUTE sp_configure 'clr enabled', 0; RECONFIGURE;

Move Database

(A) Move USER database :3 methods

1 Backup and restore on new location

2. attach and detach database

3. Alter Command

First check current location

sp_helpdb 'database name'
or
SELECT name,physical_name FROM AdventureWorks.sys.database_files

ALTER DATABASE AdventureWorks SET OFFLINE

ALTER DATABASE AdventureWorks
MODIFY FILE (NAME = AdventureWorks_Data, FILENAME ='D:\MSSQL\Data\databasename.mdf');
ALTER DATABASE AdventureWorks
MODIFY FILE (NAME = AdventureWorks_Log, FILENAME ='D:\MSSQL\Log\databasename_Log.ldf');

ALTER DATABASE AdventureWorks SET ONLINE

(B) Move master Database to another drive

(C) Move Temp Database to another drive