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 |
|
nikke
Starting Member
7 Posts |
 Posted - 2003-11-12 : 10:35:38
|
| Hi, I have a question about datafiles in SQL Server. This is what I'm doing:I copy a datafile to another name.Run the script: CREATE DATABASE newbase ON (NAME='newName', FILENAME='C:\...\...\newname.mdf') FOR ATTACH.Everything works fine and the database i created. But if I right click on the database in Enterprise Manager - Properties - Data Files-tab and look at the column "File Name" it is the same as the original datafile but the Location is the new file I've copied.The question is, is this a problem? Is it a bad way to do this?Thanks in advance/Nicke |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-12 : 14:36:39
|
| No, it isn't a problem. And you can change those things if you want to.I have always used sp_attach_single_file_db or sp_attach_db. You copy the file to where you want it located, then run the stored procedure (run single_file_db if you don't have LDF file).Tara |
 |
|
|
nikke
Starting Member
7 Posts |
Posted - 2003-11-13 : 02:10:04
|
| Thanks for your answer Tara! I'll change my program to use the stored procedure but please help me with this two question that came up:1.How do I change the File name? I suppose the "NAME='newName'" in my example referes to that but it doesn't change, is it another stored procedure that achives that?2.If I change my program so I use "sp_attach_single_file_db" how do I create the database in first place? Can I create the database without any datafile and later attach a data file? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-13 : 12:15:47
|
| 1. I'll have to look in BOL for this one. I can't remember the name.2. You don't need to create the database first, but you do need the MDF file. Just run the stored proc with the MDF file and it'll create the database and an LDF file for you.Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-13 : 12:35:21
|
| Found answer to 1. You'll want to run ALTER DATABASE MODIFY FILE after the MDF has been attached.Here is an example:ALTER DATABASE DBNameGoesHereMODIFY FILE (NAME = CurrentName, NEWNAME = NewName)For more information, please see ALTER DATABASE in SQL Server Books Online.Tara |
|
|
|
nikke
Starting Member
7 Posts |
Posted - 2003-11-13 : 14:24:56
|
| Many thanks to you Tara! =) |
|
|
|
|
|
|
Data files