Category: SQL Server


Dropping and recreating databases in Microsoft SQL Server

If you are unable to drop and create database in SQL server, this code will close all active connections to the database and then drop it WHILE EXISTS(select NULL from sys.databases where name=‘yourDBname’) BEGIN DECLARE @SQL varchar(max) SELECT @SQL = COALESCE(@SQL,”) + ‘Kill ‘ + Convert(varchar, SPId) + ‘;’ FROM MASTER..SysProcesses WHERE DBId = DB_ID(N ‘yourDBname’) AND SPId <> @@SPId EXEC(@SQL) DROP DATABASE ‘yourDBname’ END GO CREATE DATABASE yourDBname GO /*********************************************************************************                                                         OR *********************************************************************************/ DECLARERead More

SQL Server Keyboard Shortcuts

Install Adventure Works database from backup

The Adventure Works 2014 Data Warehouse database can be installed by restoring a database backup. 1. Download Adventure Works DW 2014 Full Database . 2. From File Download, click Save. Once it is saved, open the folder. 3. Extract the AdventureWorks2014.bak file to a location on your local server. Note: The default 64-bit path is C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup. Use C:\Program Files (x86)\… for 32-bit SQL Server 2014. 4. From SQL Server Management StudioRead More