We are using SQL and have a script file to generate the Database and tables. I am trying to write a generic script which will execute on all dev/prod machines The file name which i am giving is FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\DBname.mdf'
It works on my machine but not on all the dev machine since all have different path for SQL. Is there a way to make this setting as generic? I want to make this script to Run on all machine which has SQL server installed.
I guess the problem is that on different machines you have instances of SQL Server in different folders. You can check in Windows Register what is the folder name of your instance:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL
Example entry
Name - Data
MSSQLSERVER - MSSQL.1
SQL2k5 - MSSQL.2
And then in another branch, for example:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup
you can find path to the directory with data files.
If you need to differentiate certain parts of your scripts based on where you are running them, you can use SQLCMD.
I've had success with this approach in the past when I needed to perform a database restore and I knew the data paths differed between boxes.
Something like the following should work:
SQLCMD -SMyDatabaseServer
-Q "RESTORE DATABASE $(DatabaseName) FROM DISK = '$(BackupLocation)\$(DatabaseName).bak' WITH NORECOVERY, STATS=10, REPLACE;"
-v
DatabaseName = MyDatabase
BackupLocation = "C:\SomeLocation"
-d master
-u [User]
-p [Password]
This is an inline query version. If you need to work with SQL script files, change the -Q "..." section to -i yourFile.sql and copy and paste the query itself into the sql file. SQLCMD will take your variables defined by the -v switch and replace their counterparts in your query defined either by -Q or -i.
For a more detailed reference, see: http://msdn.microsoft.com/en-us/library/ms180944.aspx.