Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to create an SQL Server 2008 database from script

Tags:

sql-server

I'm trying to do an Entity Framework walkthrough so I:

  • downloaded SQL script here: http://www.learnentityframework.com
  • in SQL Server Management Studio, I right-clicked Database, Create Database, named it
  • right-clicked on the new database, New Query
  • clicked on "Open File" and opened the script file: Create_ProgrammingEFDB1_SQLServer2008.sql
  • clicked "! Execute"
  • But the script (756K) has been running for 10 minutes now and still says "executing..."

My questions are:

  • Is this the standard way to read in an SQL script into SQL Server?
  • Is it supposed to take this long? This is how I would do it in MySQL/PHPMyAdmin it it might take a couple seconds, so I assume I'm not doing something right.

Here is the beginning of the script, I changed the file paths so they point to the right .mdf and .ldf files:

****/

--PART ONE  CREATE THE DATABASE. Note the file paths in the first few commands. 
--Change them for your own computer.--
USE [master]
GO

    /****** Object:  Database [ProgrammingEFDB1]    Script Date: 01/28/2009 10:17:44 ******/
    CREATE DATABASE [ProgrammingEFDB1] ON  PRIMARY 
    ( NAME = N'ProgrammingEFDB1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\ProgrammingEFDB1.mdf' , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
     LOG ON 
    ( NAME = N'ProgrammingEFDB1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\ProgrammingEFDB1_log.LDF' , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
    GO

    ALTER DATABASE [ProgrammingEFDB1] SET COMPATIBILITY_LEVEL = 90
    GO

    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [ProgrammingEFDB1].[dbo].[sp_fulltext_database] @action = 'disable'
    end
    ...

ANSWER:

I had already created a database with the same name so it was trying to create a database that was already there which made it hang for some reason. I deleted that database, reran the script and it completed successfully in 3 seconds.

like image 594
Edward Tanguay Avatar asked Mar 02 '09 15:03

Edward Tanguay


2 Answers

I don't know what does your script do exactly in the next 754K, but the lines you posted seem quite harmless.

Try adding the following to your script:

SET STATISTICS TIME ON

This will show queries execution times as they run, and it will help you to locate the problem more exactly.

like image 193
Quassnoi Avatar answered Nov 05 '22 07:11

Quassnoi


But the script (756K)

Must be a lot more than just a CREATE DATABASE in the script, so very hard to say when the script is doing.

You can write progress reports from the script back to the client, or use SQL Profiler to see what commands are being executed.

like image 38
Richard Avatar answered Nov 05 '22 06:11

Richard