User Tools

Site Tools


windows:sqlserver

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: ⇒ 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
    • to
  • * In SQL Server Configuration: go to SQL Server Network Configuration→Protocols for MSSQLServer→TCP/IP, Config port 1433(not dynamic port) for TCP/IP

install SQL server 2008

  1. step1: SQLEXPR_x64_ENU.exe 2008
  2. 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:
    ipconfig

    ⇒ output

    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
  • Step2: Using SQL Server Configuration Tool, go to SQL Server Network ConfigurationProtocols for MSSQLServerTCP/IP

view database config(data config, log config...)

  • step1: select database as below image:

  • step2: right-click the database and click properties to see the database settings as below image:

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

windows/sqlserver.txt · Last modified: 2017/01/12 09:12 (external edit)