Running the Script, made by generate script on different server gives error.

Question

the following is a script which was generated using generate script option.

the wizard says that this can be used to copy, create db on different servers

so its made for it, then why does this error occur.??

the error is

Msg 5170, Level 16, State 1, Line 2
Cannot create file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SECOND\MSSQL\DATA\Script Me.mdf' because it already exists. Change the file path or the file name, and retry the operation.
Msg 1802, Level 16, State 4, Line 2

and

could not lacate entry for "script me" in sysdatabes

confused,

why does it try to create on the same server, i mean why the script is written like that??

the script

USE [master]
GO
/****** Object:  Database [Script Me]    Script Date: 10/23/2010 12:38:57 ******/
CREATE DATABASE [Script Me] ON  PRIMARY 
( NAME = N'Script Me', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SECOND\MSSQL\DATA\Script Me.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Script Me_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SECOND\MSSQL\DATA\Script Me_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [Script Me] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Script Me].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [Script Me] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [Script Me] SET ANSI_NULLS OFF
GO
ALTER DATABASE [Script Me] SET ANSI_PADDING OFF
GO
ALTER DATABASE [Script Me] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [Script Me] SET ARITHABORT OFF
GO
ALTER DATABASE [Script Me] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [Script Me] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [Script Me] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [Script Me] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [Script Me] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [Script Me] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [Script Me] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [Script Me] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [Script Me] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [Script Me] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [Script Me] SET  DISABLE_BROKER
GO
ALTER DATABASE [Script Me] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [Script Me] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [Script Me] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [Script Me] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [Script Me] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [Script Me] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [Script Me] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [Script Me] SET  READ_WRITE
GO
ALTER DATABASE [Script Me] SET RECOVERY FULL
GO
ALTER DATABASE [Script Me] SET  MULTI_USER
GO
ALTER DATABASE [Script Me] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [Script Me] SET DB_CHAINING OFF
GO
EXEC sys.sp_db_vardecimal_storage_format N'Script Me', N'ON'
GO
USE [Script Me]
GO
/****** Object:  Table [dbo].[TableOne]    Script Date: 10/23/2010 12:38:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TableOne](
 [id] [nchar](10) NOT NULL,
 [name] [nchar](10) NULL,
 CONSTRAINT [PK_TableOne] PRIMARY KEY CLUSTERED 
(
 [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

what am i doing

• creating the script of db from one server
• trying to run it on other so that the same table be created there too

Answer 1

Because you have the create database portion turned on and the error indicates that database already exists. Just run the last bit:

USE [Script Me] 
GO 
/****** Object:  Table [dbo].[TableOne]    Script Date: 10/23/2010 12:38:59 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 
CREATE TABLE [dbo].[TableOne]( 
 [id] [nchar](10) NOT NULL, 
 [name] [nchar](10) NULL, 
 CONSTRAINT [PK_TableOne] PRIMARY KEY CLUSTERED  
( 
 [id] ASC 
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] 
) ON [PRIMARY] 
GO 

UPDATE: If your SQL Server is a production box, then hopefully the database files are not here: "'C:\Program Files\Microsoft SQL Server"

Change the database path in your script to point to the folder location of the database files for a particular machine.

Answer 2

The script is written to replace itself. Any changes are OK, but they are up to you. You should always (no matter which tool you use to create your scripts) read over your script to make sure it does what you want it to do (and where you want it to do).

You should modify the script to replace the following things with something that you want:

CREATE DATABASE [Script Me] ON  PRIMARY 
( NAME = N'Script Me', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SECOND\MSSQL\DATA\Script Me.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Script Me_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SECOND\MSSQL\DATA\Script Me_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB ,     FILEGROWTH = 10%)
GO

Here, you should change the path and the filenames. The path is up to you, but the filenames usually follow this naming:

• Data: {database name}.mdf
• Log: {database name}_log.ldf

You should also change Script Me to your new database name everywhere it exists in the script file.