Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Tips for Microsoft SQL Server Express Noobs
- ===========================================
- Install SQL Server Express with Chocolatey or PackageManagement module:
- ```
- choco install 'mssqlserver2014express
- # or
- Install-Package -Name 'mssqlserver2014express' -ProviderName 'chocolateyget'
- ```
- However SQL Express cannot install into a folder that is NTFS compressed, so the
- here's a good script to make sure that the default installation directories are
- not NTFS compressed. It relies on the Carbon module to supply
- `Disable-NtfsCompression`. Alternatively `compact.exe` can be used.
- ```
- New-Item -ItemType Directory -Force -Path "${Env:ProgramFiles}\Microsoft SQL Server"
- New-Item -ItemType Directory -Force -Path "${Env:ProgramFiles(x86)}\Microsoft SQL Server"
- Disable-NtfsCompression -Path "${Env:ProgramFiles}\Microsoft SQL Server"
- Disable-NtfsCompression -Path "${Env:ProgramFiles(x86)}\Microsoft SQL Server"
- if ($Force) {
- Install-Package -Name 'mssqlserver2014express' -ProviderName 'chocolateyget' -Force
- } else {
- Install-Package -Name 'mssqlserver2014express' -ProviderName 'chocolateyget'
- }
- Set-Service -Name 'MSSQL$SQLEXPRESS' -StartupType Manual
- Set-Service -Name 'SQLWriter' -StartupType Manual
- Stop-Service -Name 'MSSQL$SQLEXPRESS'
- Stop-Service -Name 'SQLWriter'
- ```
- The above sets the service not to run on startup, and stops the service.
- To restart the service use:
- ```
- Restart-Service -Name 'MSSQL$SQLEXPRESS'
- ```
- To get started using SQL Server Express, you must enable Named Pipes and TCP/IP
- connections via `Sql Server Configuration Manager`, and the settings are located
- at `SQL Server Network Configuration/Protocols for SQLEXPRESS`. When enabling
- the TCP/IP connection, make sure to check the properties so it is not listening
- on all IPs. Instead individually enable the interfaces you want SQL Server to
- listen on. In the beginning, only enable `127.0.0.1` and `::1` interfaces. Make
- sure to set the `TCP port` to `1433` (for both `127.0.0.1` and `::1`) and blank
- out the `TCP Dynamic Ports` field.
- The installation of the package several new system PATHs. So after restarting
- Powershell, you can run `sqlcmd -S .\SQLEXPRESS`. This may not work inside a
- Cygwin environment, so you need a real Powershell terminal.
- This launchs `sqlcmd` interactive session while connecting the default SQL
- Server Express instance called `SQLEXPRESS`, alternatively you can address it
- as `localhost\SQLEXPRESS`. Note that the real SQL Server's default instance will
- be `MSSQLSERVER`. It's possible to have multiple instances running, each
- listening on a different port. However for development purposes, we are just
- going to use the default instance.
- When in the interactive environment try this (yes the command terminator is a `GO` line...):
- ```
- >> sp_databases;
- >> GO
- ```
- Or if running directly from the terminal:
- ```
- > sqlcmd -S .\SQLEXPRESS -Q "sp_databases;"
- ```
- The above command shows all the databases available to SQL Server.
- There will be some default databases already available, these are the system
- databases, you won't be adding application tables to this database. However
- if you accidentally add lots of data to your master database (which is the
- default database) it is possible to reset your system databases by running:
- ```
- .\setup.exe /ACTION=REBUILDDATABASE /INSTANCENAME=SQLEXPRESS /SQLSYSADMINACCOUNTS="POLYHACK-SURFB1\CMCDragonkai"
- ```
- Note that the value for `/SQLSYSADMINACCOUNTS` depends on what your current
- account is. Mine is the `COMPUTERNAME\USERNAME`.
- The `.\setup.exe` depends on the version you installed SQL Express at. Currently
- mine is at: `C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014\setup.exe`.
- To use a different database, first create one then run `USE database;` then `GO`.
- `sqlcmd` has actually a pretty poor user experience, you really have to use a
- GUI tool. I suggest DBeaver. To connect via DBeaver, follow these instructions:
- > To create a "Microsoft Driver" connection in DBeaver
- > Create a new connection, specifying MS SQL Server | Microsoft Driver
- > Fill out the info on the first (General) tab, without specifying User name and Password (leave them blank).
- > Go to the Driver properties tab and set integratedSecurity=true.
- > And, once again, just to emphasize where to enter this: it's entered on the "Driver properties" tab -- not in the dialog box you get to by clicking the "Edit Driver Settings" button that's on the General tab.
- > Click the "Test Connection..." button to make sure it works, click Next a couple of times, then click Finish.
- To run a T-SQL script file use:
- ```
- sqlcmd -S .\SQLEXPRESS -d 'database-name' -i .\t-sql-script.sql
- ```
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement