Create database script question

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!

Answer 1

0

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

answered Apr 21 '10 at 12:53 PM

Kev Riley ♦♦
46.1k ● 38 ● 43 ● 69

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 '10 at 01:02 PM DaniSQL

I have no such powers!

Apr 21 '10 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 '10 at 01:09 PM DaniSQL

SELECT HOST_NAME() AS HostName, SUSER_NAME() LoggedInUser

Apr 21 '10 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 FilesMicrosoft SQL ServerMSSQL10.EXPRESSMSSQLDATAISO_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 '10 at 01:12 PM Katie 1

(comments are locked)

Answer 2

0

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.

answered Apr 21 '10 at 01:10 PM

Grant Fritchey ♦♦
62.4k ● 12 ● 20 ● 66

(comments are locked)

Answer 3

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

Create database script question

Follow this question

Related Questions