Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
fcoreyesv
Starting Member
18 Posts |
 Posted - 2006-04-24 : 13:52:44
|
| Hello,I am working on a project to port an entire application in Visual Basic from Access to SQL Server 2000. Before, for the application was easy to create a new database, it was matter of copying the access database file and renaming it. With SQL Server, I am trying to create new databases using an empty database as a template, copying the file into the data folder and attaching it to the server.I have the general idea how to do it but I have the following situation. The Server does not "see" the file with the same path than the client application and this "could be" the cause of the following error message:[Microsoft][ODBC SQL Server Driver][SQL Server]Device activation error. The physical file name 'R:\SQLSER~1\MSSQL\Data\SWCS40~2.MDF' may be incorrect.The path SQL server "sees" is "e:\general data\sql server\MSSQL\data\...."Is there another way to do what I am trying to do?Any comments will be greatly appreciated.Jose Reyes |
|
|
gpl
Posting Yak Master
195 Posts |
Posted - 2006-04-25 : 08:53:12
|
| JoseWhat we (and I suppose others) do is create an sql script that creates the database, tables, views procedures etc, then populates the tables with data (lookups etc). That way during testing, the database can be torn down and rebuilt very simply to ensure that a standard starting point is always achieved.If each of the database/table/view etc script is kept in a separate file, this can be held under source control for extra security.When you come to deploy your application, your scripts will then be in place to create your final production database.Graham |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-25 : 10:45:03
|
| I suppose "attaching" is OK - but you will need to tell SQL Server to Attach the DB, simply putting it in the right folder is not enough.However, when you attach a database you are kinda forced to live with the logical names (unless that's changed of late).So my suggestion would be that youa) Backup your pristine Gold Master databaseb) Restore that backup to a suitably named database on the Target serverYou may optionally create a database before the Restore or you may overwrite an existing database as part of the Restore operationExamples of Restore syntax here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20example,RestoreKristen |
|
|
|
|
|
|
|
|
attach new database