====== SQL Server ======
===== Install  SQL server =====
==== install sql server 2012 with Manage studio tools ====
Download and Install SQLEXPRWT_x64_ENU.exe(SQL management studio was integrated in this package)
  * Authentication: **mixed mode**(authenticate with windows authentication and sql server authentication)
  * Change Data directories:{{:windows:sqlserver-directory.png|}} => change **data root directory** to d:\data
  * In SQL Server Configuration:  go to SQL Server Network Configuration→Protocols for MSSQLServer→TCP/IP, click  right mouse and select enable TCP/IP:
    * from {{:windows:sqlserver-network-disable.png|}}
    * to {{:windows:sqlserver-network-enable.png|}}
  *   * In SQL Server Configuration:  go to SQL Server Network Configuration→Protocols for MSSQLServer→TCP/IP, Config port 1433(not dynamic port) for TCP/IP{{:windows:sqlserver-ipconfig.png|}}
==== install SQL server 2008  ====
  - step1: SQLEXPR_x64_ENU.exe  2008
  - step2: install SQLManagementStudio_x64_ENU.exe
===== Basic Config with SQL Server Configuration Tool =====
==== Change config TCP/IP connection ====
  * Step1: Before config, we get IP information of windows: <code bat>
ipconfig
</code> => output<code>
Windows IP Configuration
Ethernet adapter Ethernet:
   Connection-specific DNS Suffix  . :
   Link-local IPv6 Address . . . . . : fe80::502a:c8d9:c74:606%12
   IPv4 Address. . . . . . . . . . . : 192.168.1.100
   Subnet Mask . . . . . . . . . . . : 255.255.255.0
   Default Gateway . . . . . . . . . : 192.168.1.1
</code>
  * Step2: Using SQL Server Configuration Tool, go to **SQL Server Network Configuration**->**Protocols for MSSQLServer**->**TCP/IP**{{:windows:sqlserver-ipconfig.png|}}
==== view database config(data config, log config...) ====
  * step1: select database as below image:
{{:windows:database-config.png|}}
  * step2: right-click the database and click **properties** to see the database settings as below image:
{{:windows:database-setting.png|}}
==== reset password ====
===== Backup and restore =====
==== backup and restore bak file ====
  * to restore: right click **databases** and select restore database
  * to backup:  right click **database name** and select **tasks -> backup**
==== backup and restore sql scripts ====
  * backup: right click **database name** and select **tasks->generate scripts**
==== attach database file(mdf file) ====
===== debug in sql server =====
==== query log in sql server ====
  * to view query log in sql server, we use sql server profiler
  * with sql server express, we use expressprofiler(because in this version, the sql server profiler not exists): https://expressprofiler.codeplex.com/
==== The actual stored procedure line number from an error message ====
Error line numbers may not match the number returned in the error always, this is because when **the SQL server executes a stored proc, it has its own compilation & execution methodology**, so the line numbers vary based on the execution order it takes
==== Debug stored procedure with break point ====