question

Katie 1 avatar image
Katie 1 asked

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!

sql-server-2008t-sqladministrationscript
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

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

8 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DaniSQL avatar image DaniSQL commented ·
Kev: I was trying to answer but the connection keep timing out. whats happening? did u change the website to singe user mode: :-)
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
I have no such powers!
0 Likes 0 ·
DaniSQL avatar image DaniSQL commented ·
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:
0 Likes 0 ·
DaniSQL avatar image DaniSQL commented ·
SELECT HOST_NAME() AS HostName, SUSER_NAME() LoggedInUser
0 Likes 0 ·
Katie 1 avatar image Katie 1 commented ·
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.
0 Likes 0 ·
Show more comments
Grant Fritchey avatar image
Grant Fritchey answered

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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

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...

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.