x

Create database script question

Guys,

I am actually scripting the database from the scratch and I tried it on my local machine. and it was fine.

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

But I have to create a script on the development server and the server is a vitual server and the only information I have is the I.P of the machine. I have admin privileges to the machine. The server on which the sql server is installed does not have any physical name as such that I am aware of. I am actually not sure as to how to show the path of the server.. any ideas as to how to handle this issue.

thanks!

more ▼

asked Apr 21, 2010 at 12:37 PM in Default

Katie 1 gravatar image

Katie 1
1.4k 132 163 202

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Do you mean you want to run this script from one server and create a database on another server (the one without a physical name)?

Why wouldn't you just connect a query window onto the target server, and run this script?

Also once you have connected via IP address, and opened a query window, you could run

select @@servername

or

select serverproperty('ServerName')

or

select serverproperty('ComputerNamePhysicalNetBIOS')

to get the server name

more ▼

answered Apr 21, 2010 at 12:53 PM

Kev Riley gravatar image

Kev Riley ♦♦
52.7k 47 49 76

Kev: I was trying to answer but the connection keep timing out. whats happening? did u change the website to singe user mode: :-)
Apr 21, 2010 at 01:02 PM DaniSQL
I have no such powers!
Apr 21, 2010 at 01:03 PM Kev Riley ♦♦
I know u dont:-) Any way I still cant post answers and I dont know why but here was what I was trying to post:
Apr 21, 2010 at 01:09 PM DaniSQL
SELECT HOST_NAME() AS HostName, SUSER_NAME() LoggedInUser
Apr 21, 2010 at 01:10 PM DaniSQL
Kev.. i tried doing that .. as per your suggestion as to run it on the target server. and got this error message. Msg 5133, Level 16, State 1, Line 3 Directory lookup for the file "c:\Program Files\Microsoft SQL Server\MSSQL10.EXPRESS\MSSQL\DATA\ISO_DB.mdf" failed with the operating system error 3(The system cannot find the path specified.). Msg 1802, Level 16, State 1, Line 3 CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Apr 21, 2010 at 01:12 PM Katie 1
(comments are locked)
10|1200 characters needed characters left

Once you connect to the server, and you have to be able to connect to the server, you can look at the server properties and see where it's been set up to store it's files. It's highly likely that the paths you have are not the same as what it has, so you'll need to edit those, but even though it's a virtual machine, it's still running the Windows Operating system and it's still running SQL SErver. So you'll see drive letters and paths that look like what you expect.

more ▼

answered Apr 21, 2010 at 01:10 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
97.9k 19 21 74

(comments are locked)
10|1200 characters needed characters left

For what it's worth, my schema comparison tool generates filegroup DDL like this:

DECLARE @sql nvarchar(4000)

SET @sql = 'ALTER DATABASE [' + DB_NAME() + '] ADD FILEGROUP [CONFIG]';
EXEC (@sql);

GO
DECLARE @sql nvarchar(4000)
DECLARE @db_path nvarchar(4000)

SET @db_path = (SELECT TOP 1 [physical_name] FROM [sys].[database_files] WITH (NOLOCK) where [type] = 0)

WHILE RIGHT(@db_path,1) != '\' AND LEN(@db_path) > 1
BEGIN
  SET @db_path = LEFT(@db_path, LEN(@db_path) - 1)
END

SET @sql = 'ALTER DATABASE [' + db_name() + '] ADD FILE (
  NAME = ''CONFIG_DATA'',
  FILENAME = ''' + @db_path + db_name() + '_CONFIG.ndf'')
TO FILEGROUP [CONFIG]';

EXEC (@sql);

GO

So it just places files with relevant names in the same place as the PRIMARY filegroup is placed by default on the server. Not sure if that's what you're after? Might help...

more ▼

answered Apr 21, 2010 at 03:19 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1832
x984
x126
x67

asked: Apr 21, 2010 at 12:37 PM

Seen: 2475 times

Last Updated: Apr 21, 2010 at 12:50 PM