How to create a single 2005 .mdf file from a 2000 .mdf and multiple .ndf data files ?

Greetings,
We're migrating one of our older databases from Sql Server 2000 to Sql
Server 2005.
The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
This was basically because we backed the files up to a UNIX system that had
a 2G file size limit.
Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
backup system and it would seem preferable to append all the 2G .ndf files
into one 10G .mdf file on the new system.
Unfortunately all of the Restore, Move, etc options that I've explored want
to move and recreate the original 2G .mdf and the 6 additional 2G .ndf files
on the new Sql Server 2005 server.
Is there some tool or method I can use that will allow me to create a single
.mdf on the Sql Server 2005 side that consists of the original .mdf and
appended .ndf Sql Server 2000 data files '
Thanks in advance.
Barry
in Oregon"frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in
message news:e8GUoGCNHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Greetings,
> We're migrating one of our older databases from Sql Server 2000 to Sql
> Server 2005.
> The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
> This was basically because we backed the files up to a UNIX system that
> had a 2G file size limit.
> Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
> backup system and it would seem preferable to append all the 2G .ndf files
> into one 10G .mdf file on the new system.
>
There isn't any pressing need to do that.
> Unfortunately all of the Restore, Move, etc options that I've explored
> want to move and recreate the original 2G .mdf and the 6 additional 2G
> .ndf files on the new Sql Server 2005 server.
> Is there some tool or method I can use that will allow me to create a
> single .mdf on the Sql Server 2005 side that consists of the original .mdf
> and appended .ndf Sql Server 2000 data files '
>
No. You can empty and remove each ndf file once the database is restored
using DBCC SHRINKFILE and ALTER DATABASE.
David|||John,
Thanks for the quick reply. Its very much appreciated. Still have a lot to
learn about Sql Server.
Will be reading up on DBCC SHRINKFILE.
Many thanks!
Barry
in Oregon
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ECF459F6-381C-43AF-B577-0F02F4952048@.microsoft.com...
> Hi Barry
> "frostbb" wrote:
>> Greetings,
>> We're migrating one of our older databases from Sql Server 2000 to Sql
>> Server 2005.
>> The old sql server 2000 data files were limited to 2G .mdf and .ndf
>> files.
>> This was basically because we backed the files up to a UNIX system that
>> had
>> a 2G file size limit.
>> Now we're migrating to a brand new Sql Server 2005 system with a 400G
>> tape
>> backup system and it would seem preferable to append all the 2G .ndf
>> files
>> into one 10G .mdf file on the new system.
> There may still be advantages to having two files especially if they are
> on
> different sets of discs. If they are the same filegroup the allocation of
> space to each file will be balanced. If the files are different filegroups
> then you can partition your tables/indexes to separate I/O
>> Unfortunately all of the Restore, Move, etc options that I've explored
>> want
>> to move and recreate the original 2G .mdf and the 6 additional 2G .ndf
>> files
>> on the new Sql Server 2005 server.
> It is probably easier to do this re-configuration on the new server. If
> they
> are the same file group you can use DBCC SHRINKFILE with the EMPTYFILE
> option. if they are different filegroups in each file, then changing the
> clustered index so that it is on the primary filegroup will move it to the
> .mdf file. If you have text columns defined to be on the second file group
> then you might be able to get away with altering the column to
> varchar(MAX),
> failing that you would need to create a new table (with text in the other
> filegroup), move the data, remove the original table and then rename the
> new
> table.
>> Is there some tool or method I can use that will allow me to create a
>> single
>> ..mdf on the Sql Server 2005 side that consists of the original .mdf and
>> appended .ndf Sql Server 2000 data files '
> Once the files are empty you can use ALTER DATABASE to remove the file.
>> Thanks in advance.
>> Barry
>> in Oregon
> HTH
> John|||David,
Many thanks for the quick response. I've got plenty to learn about Sql
Server.
Will read up on DBCC Shrinkfile. Still trying to get my 'head around' how
Sql Server 'thinks'
about it's data & log files.
Barry
in Oregon
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ODTolWCNHHA.320@.TK2MSFTNGP06.phx.gbl...
>
> "frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in
> message news:e8GUoGCNHHA.3268@.TK2MSFTNGP04.phx.gbl...
>> Greetings,
>> We're migrating one of our older databases from Sql Server 2000 to Sql
>> Server 2005.
>> The old sql server 2000 data files were limited to 2G .mdf and .ndf
>> files. This was basically because we backed the files up to a UNIX system
>> that had a 2G file size limit.
>> Now we're migrating to a brand new Sql Server 2005 system with a 400G
>> tape backup system and it would seem preferable to append all the 2G .ndf
>> files into one 10G .mdf file on the new system.
> There isn't any pressing need to do that.
>> Unfortunately all of the Restore, Move, etc options that I've explored
>> want to move and recreate the original 2G .mdf and the 6 additional 2G
>> .ndf files on the new Sql Server 2005 server.
>> Is there some tool or method I can use that will allow me to create a
>> single .mdf on the Sql Server 2005 side that consists of the original
>> .mdf and appended .ndf Sql Server 2000 data files '
> No. You can empty and remove each ndf file once the database is restored
> using DBCC SHRINKFILE and ALTER DATABASE.
> David
>
>

How to create a single 2005 .mdf file from a 2000 .mdf and multiple .ndf data files ?

Greetings,
We're migrating one of our older databases from Sql Server 2000 to Sql
Server 2005.
The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
This was basically because we backed the files up to a UNIX system that had
a 2G file size limit.
Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
backup system and it would seem preferable to append all the 2G .ndf files
into one 10G .mdf file on the new system.
Unfortunately all of the Restore, Move, etc options that I've explored want
to move and recreate the original 2G .mdf and the 6 additional 2G .ndf files
on the new Sql Server 2005 server.
Is there some tool or method I can use that will allow me to create a single
..mdf on the Sql Server 2005 side that consists of the original .mdf and
appended .ndf Sql Server 2000 data files ?
Thanks in advance.
Barry
in Oregon
"frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in
message news:e8GUoGCNHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Greetings,
> We're migrating one of our older databases from Sql Server 2000 to Sql
> Server 2005.
> The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
> This was basically because we backed the files up to a UNIX system that
> had a 2G file size limit.
> Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
> backup system and it would seem preferable to append all the 2G .ndf files
> into one 10G .mdf file on the new system.
>
There isn't any pressing need to do that.

> Unfortunately all of the Restore, Move, etc options that I've explored
> want to move and recreate the original 2G .mdf and the 6 additional 2G
> .ndf files on the new Sql Server 2005 server.
> Is there some tool or method I can use that will allow me to create a
> single .mdf on the Sql Server 2005 side that consists of the original .mdf
> and appended .ndf Sql Server 2000 data files ?
>
No. You can empty and remove each ndf file once the database is restored
using DBCC SHRINKFILE and ALTER DATABASE.
David
|||Hi Barry
"frostbb" wrote:

> Greetings,
> We're migrating one of our older databases from Sql Server 2000 to Sql
> Server 2005.
> The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
> This was basically because we backed the files up to a UNIX system that had
> a 2G file size limit.
> Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
> backup system and it would seem preferable to append all the 2G .ndf files
> into one 10G .mdf file on the new system.
There may still be advantages to having two files especially if they are on
different sets of discs. If they are the same filegroup the allocation of
space to each file will be balanced. If the files are different filegroups
then you can partition your tables/indexes to separate I/O
> Unfortunately all of the Restore, Move, etc options that I've explored want
> to move and recreate the original 2G .mdf and the 6 additional 2G .ndf files
> on the new Sql Server 2005 server.
It is probably easier to do this re-configuration on the new server. If they
are the same file group you can use DBCC SHRINKFILE with the EMPTYFILE
option. if they are different filegroups in each file, then changing the
clustered index so that it is on the primary filegroup will move it to the
..mdf file. If you have text columns defined to be on the second file group
then you might be able to get away with altering the column to varchar(MAX),
failing that you would need to create a new table (with text in the other
filegroup), move the data, remove the original table and then rename the new
table.

> Is there some tool or method I can use that will allow me to create a single
> ..mdf on the Sql Server 2005 side that consists of the original .mdf and
> appended .ndf Sql Server 2000 data files ?
>
Once the files are empty you can use ALTER DATABASE to remove the file.

> Thanks in advance.
> Barry
> in Oregon
>
HTH
John
|||John,
Thanks for the quick reply. Its very much appreciated. Still have a lot to
learn about Sql Server.
Will be reading up on DBCC SHRINKFILE.
Many thanks!
Barry
in Oregon
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ECF459F6-381C-43AF-B577-0F02F4952048@.microsoft.com...
> Hi Barry
> "frostbb" wrote:
> There may still be advantages to having two files especially if they are
> on
> different sets of discs. If they are the same filegroup the allocation of
> space to each file will be balanced. If the files are different filegroups
> then you can partition your tables/indexes to separate I/O
> It is probably easier to do this re-configuration on the new server. If
> they
> are the same file group you can use DBCC SHRINKFILE with the EMPTYFILE
> option. if they are different filegroups in each file, then changing the
> clustered index so that it is on the primary filegroup will move it to the
> .mdf file. If you have text columns defined to be on the second file group
> then you might be able to get away with altering the column to
> varchar(MAX),
> failing that you would need to create a new table (with text in the other
> filegroup), move the data, remove the original table and then rename the
> new
> table.
> Once the files are empty you can use ALTER DATABASE to remove the file.
> HTH
> John
|||David,
Many thanks for the quick response. I've got plenty to learn about Sql
Server.
Will read up on DBCC Shrinkfile. Still trying to get my 'head around' how
Sql Server 'thinks'
about it's data & log files.
Barry
in Oregon
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ODTolWCNHHA.320@.TK2MSFTNGP06.phx.gbl...
>
> "frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in
> message news:e8GUoGCNHHA.3268@.TK2MSFTNGP04.phx.gbl...
> There isn't any pressing need to do that.
>
> No. You can empty and remove each ndf file once the database is restored
> using DBCC SHRINKFILE and ALTER DATABASE.
> David
>
>

How to create a single 2005 .mdf file from a 2000 .mdf and multiple .ndf data files ?

Greetings,
We're migrating one of our older databases from Sql Server 2000 to Sql
Server 2005.
The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
This was basically because we backed the files up to a UNIX system that had
a 2G file size limit.
Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
backup system and it would seem preferable to append all the 2G .ndf files
into one 10G .mdf file on the new system.
Unfortunately all of the Restore, Move, etc options that I've explored want
to move and recreate the original 2G .mdf and the 6 additional 2G .ndf files
on the new Sql Server 2005 server.
Is there some tool or method I can use that will allow me to create a single
.mdf on the Sql Server 2005 side that consists of the original .mdf and
appended .ndf Sql Server 2000 data files '
Thanks in advance.
Barry
in Oregon"frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in
message news:e8GUoGCNHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Greetings,
> We're migrating one of our older databases from Sql Server 2000 to Sql
> Server 2005.
> The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
> This was basically because we backed the files up to a UNIX system that
> had a 2G file size limit.
> Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
> backup system and it would seem preferable to append all the 2G .ndf files
> into one 10G .mdf file on the new system.
>
There isn't any pressing need to do that.

> Unfortunately all of the Restore, Move, etc options that I've explored
> want to move and recreate the original 2G .mdf and the 6 additional 2G
> .ndf files on the new Sql Server 2005 server.
> Is there some tool or method I can use that will allow me to create a
> single .mdf on the Sql Server 2005 side that consists of the original .mdf
> and appended .ndf Sql Server 2000 data files '
>
No. You can empty and remove each ndf file once the database is restored
using DBCC SHRINKFILE and ALTER DATABASE.
David|||David,
Many thanks for the quick response. I've got plenty to learn about Sql
Server.
Will read up on DBCC Shrinkfile. Still trying to get my 'head around' how
Sql Server 'thinks'
about it's data & log files.
Barry
in Oregon
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ODTolWCNHHA.320@.TK2MSFTNGP06.phx.gbl...
>
> "frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in
> message news:e8GUoGCNHHA.3268@.TK2MSFTNGP04.phx.gbl...
> There isn't any pressing need to do that.
>
> No. You can empty and remove each ndf file once the database is restored
> using DBCC SHRINKFILE and ALTER DATABASE.
> David
>
>

How to create a new SINGLE file .mdf in SSMS?

It is easy to create a new .mdf in VS "add new items" or via the connection GUI.

How do I do that in the full version of SSMS(connecting to the SSE instance of course)?

What about if there was no SSE, only a full version server instance. I know I can attach an existing .mdf, but what about creating a new one?

Right click on the databases folder in Object Explorer and select New Database...

The default in the UI is to create a database using a single .mdf data file and a single .ldf log file. It isn't possible to create a database without a log.

If you want to do this outside the UI, the T-SQL for creating a database in SQL Server 2005 is just "create database db_name". This will result in a database with default settings, a single .mdf file and a single .ldf file.

Hope this helps,

Steve

|||

Steve,

Would you mind clarifying some things that, from looking in the forums, are muddled in the heads of a lot of other people.

This is the script I use:

CREATE DATABASE [MyDB] ON PRIMARY
( NAME = N'MyDB', FILENAME = N'C:\MyFolder\MyDB.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON ( NAME = N'MyDB_log', FILENAME = N'C:\MyFolder\MyDB.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

1. If I try to use the "New Database" GUI in SSMS it does not let me specify a file path at all. It just puts all the .mdf/.ldf files in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\

2. Even though I could track both an .mdf and an .ldf in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\, it is not even clear that, when working with SSMS, the data and other objects are neatly packaged in a single file. ARE they?

3.Is it the same as SSExpress when managed through visual studio, which, although much more limited, clearly allows me to specify the file path, whether it be "Add New Item" in the asp.net project, or the do-all connection GUI. Since I cannot find any discussion of this in BOL or MSDN I am not sure if the db objects are not scattered all over the place and the single file being a feature of ONLY Express dbs?

4. Is there something special about the path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ ? Am I breaking any rules by specifying my own folder in the script. There must be a reason the GUI does not let us specify our own path or is it just an oversight?

|||

1. You can change the folder for the file by scrolling over to the Path column in the database files grid and clicking in the cell to start editing it. You can also click the "..." button to the right of the path to bring up a dialog to browse directories on the server machine.

2. If you only have one data file then all your database objects are in it.

3. SQL Server Express works exactly the same as SQL Server 2005 with respect to data and log files.

4. ...\MSSQL.X is just the default file location for that server instance. You can put your database files anywhere you like on server. SQL Server isn't able to open data files kept on another server though.

Hope this helps,

Steve

|||

Thanks Steve!

The docs really go around in circles about this. And the issue is not exciting enough for the bloggers to cover.

|||

I would like to drop in on this topic..

I follow Steve's directions above - but in the SSMS when selecting properties on the database I wish to move to another location - I select Files - and out to the right - under Path - I find the two files location - situated in the default folder on the C drive..

But I cannot edit the path - nor is there a '..' button to browse..

Shouldn't it be possible to edit the path..?

|||

Once the database is created, you can't move its files using the database properties dialog. Instead, you have to detach the database, move the files, and then attach the database again. It's a good idea to perform a full backup before you move the database just in case anything goes wrong.

Detaching a database can be done in Management Studio by right clicking on the database and selecting Tasks > Detach... in the context menu.

You can move the file using Windows Explorer. You probably want to move both the data files (.mdf and .ndf files) and the transaction log (.ldf) file.

You can attach a database by right clicking on the databases folder in Object Explorer (in Management Studio) and selecting the Attach... context menu item. You can change the paths to files in the attach database dialog if it can't find some file.

Thanks,

Steve

|||

Thanks for the reply - Steve..

As soon as I get some time to look into this - I 'll give it a try..

Your advise was exactly what I've been looking for - for quite some days - but just couldn't figure out.. My whole idea is to have the databases on another drive - so as to when I do a complete re-format and re-install - which I do often (to try out different scenarios) - I can then just install sql and etc. - and then connect (attach..?) to the same databases again..

Thanks

How to create a new SINGLE file .mdf in SSMS?

It is easy to create a new .mdf in VS "add new items" or via the connection GUI.

How do I do that in the full version of SSMS(connecting to the SSE instance of course)?

What about if there was no SSE, only a full version server instance. I know I can attach an existing .mdf, but what about creating a new one?

Right click on the databases folder in Object Explorer and select New Database...

The default in the UI is to create a database using a single .mdf data file and a single .ldf log file. It isn't possible to create a database without a log.

If you want to do this outside the UI, the T-SQL for creating a database in SQL Server 2005 is just "create database db_name". This will result in a database with default settings, a single .mdf file and a single .ldf file.

Hope this helps,

Steve

|||

Steve,

Would you mind clarifying some things that, from looking in the forums, are muddled in the heads of a lot of other people.

This is the script I use:

CREATE DATABASE [MyDB] ON PRIMARY
( NAME = N'MyDB', FILENAME = N'C:\MyFolder\MyDB.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON ( NAME = N'MyDB_log', FILENAME = N'C:\MyFolder\MyDB.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

1. If I try to use the "New Database" GUI in SSMS it does not let me specify a file path at all. It just puts all the .mdf/.ldf files in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\

2. Even though I could track both an .mdf and an .ldf in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\, it is not even clear that, when working with SSMS, the data and other objects are neatly packaged in a single file. ARE they?

3.Is it the same as SSExpress when managed through visual studio, which, although much more limited, clearly allows me to specify the file path, whether it be "Add New Item" in the asp.net project, or the do-all connection GUI. Since I cannot find any discussion of this in BOL or MSDN I am not sure if the db objects are not scattered all over the place and the single file being a feature of ONLY Express dbs?

4. Is there something special about the path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ ? Am I breaking any rules by specifying my own folder in the script. There must be a reason the GUI does not let us specify our own path or is it just an oversight?

|||

1. You can change the folder for the file by scrolling over to the Path column in the database files grid and clicking in the cell to start editing it. You can also click the "..." button to the right of the path to bring up a dialog to browse directories on the server machine.

2. If you only have one data file then all your database objects are in it.

3. SQL Server Express works exactly the same as SQL Server 2005 with respect to data and log files.

4. ...\MSSQL.X is just the default file location for that server instance. You can put your database files anywhere you like on server. SQL Server isn't able to open data files kept on another server though.

Hope this helps,

Steve

|||

Thanks Steve!

The docs really go around in circles about this. And the issue is not exciting enough for the bloggers to cover.

|||

I would like to drop in on this topic..

I follow Steve's directions above - but in the SSMS when selecting properties on the database I wish to move to another location - I select Files - and out to the right - under Path - I find the two files location - situated in the default folder on the C drive..

But I cannot edit the path - nor is there a '..' button to browse..

Shouldn't it be possible to edit the path..?

|||

Once the database is created, you can't move its files using the database properties dialog. Instead, you have to detach the database, move the files, and then attach the database again. It's a good idea to perform a full backup before you move the database just in case anything goes wrong.

Detaching a database can be done in Management Studio by right clicking on the database and selecting Tasks > Detach... in the context menu.

You can move the file using Windows Explorer. You probably want to move both the data files (.mdf and .ndf files) and the transaction log (.ldf) file.

You can attach a database by right clicking on the databases folder in Object Explorer (in Management Studio) and selecting the Attach... context menu item. You can change the paths to files in the attach database dialog if it can't find some file.

Thanks,

Steve

|||

Thanks for the reply - Steve..

As soon as I get some time to look into this - I 'll give it a try..

Your advise was exactly what I've been looking for - for quite some days - but just couldn't figure out.. My whole idea is to have the databases on another drive - so as to when I do a complete re-format and re-install - which I do often (to try out different scenarios) - I can then just install sql and etc. - and then connect (attach..?) to the same databases again..

Thanks

How to create a new SINGLE file .mdf in SSMS?

It is easy to create a new .mdf in VS "add new items" or via the connection GUI.

How do I do that in the full version of SSMS(connecting to the SSE instance of course)?

What about if there was no SSE, only a full version server instance. I know I can attach an existing .mdf, but what about creating a new one?

Right click on the databases folder in Object Explorer and select New Database...

The default in the UI is to create a database using a single .mdf data file and a single .ldf log file. It isn't possible to create a database without a log.

If you want to do this outside the UI, the T-SQL for creating a database in SQL Server 2005 is just "create database db_name". This will result in a database with default settings, a single .mdf file and a single .ldf file.

Hope this helps,

Steve

|||

Steve,

Would you mind clarifying some things that, from looking in the forums, are muddled in the heads of a lot of other people.

This is the script I use:

CREATE DATABASE [MyDB] ON PRIMARY
( NAME = N'MyDB', FILENAME = N'C:\MyFolder\MyDB.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON ( NAME = N'MyDB_log', FILENAME = N'C:\MyFolder\MyDB.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

1. If I try to use the "New Database" GUI in SSMS it does not let me specify a file path at all. It just puts all the .mdf/.ldf files in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\

2. Even though I could track both an .mdf and an .ldf in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\, it is not even clear that, when working with SSMS, the data and other objects are neatly packaged in a single file. ARE they?

3.Is it the same as SSExpress when managed through visual studio, which, although much more limited, clearly allows me to specify the file path, whether it be "Add New Item" in the asp.net project, or the do-all connection GUI. Since I cannot find any discussion of this in BOL or MSDN I am not sure if the db objects are not scattered all over the place and the single file being a feature of ONLY Express dbs?

4. Is there something special about the path C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ ? Am I breaking any rules by specifying my own folder in the script. There must be a reason the GUI does not let us specify our own path or is it just an oversight?

|||

1. You can change the folder for the file by scrolling over to the Path column in the database files grid and clicking in the cell to start editing it. You can also click the "..." button to the right of the path to bring up a dialog to browse directories on the server machine.

2. If you only have one data file then all your database objects are in it.

3. SQL Server Express works exactly the same as SQL Server 2005 with respect to data and log files.

4. ...\MSSQL.X is just the default file location for that server instance. You can put your database files anywhere you like on server. SQL Server isn't able to open data files kept on another server though.

Hope this helps,

Steve

|||

Thanks Steve!

The docs really go around in circles about this. And the issue is not exciting enough for the bloggers to cover.

|||

I would like to drop in on this topic..

I follow Steve's directions above - but in the SSMS when selecting properties on the database I wish to move to another location - I select Files - and out to the right - under Path - I find the two files location - situated in the default folder on the C drive..

But I cannot edit the path - nor is there a '..' button to browse..

Shouldn't it be possible to edit the path..?

|||

Once the database is created, you can't move its files using the database properties dialog. Instead, you have to detach the database, move the files, and then attach the database again. It's a good idea to perform a full backup before you move the database just in case anything goes wrong.

Detaching a database can be done in Management Studio by right clicking on the database and selecting Tasks > Detach... in the context menu.

You can move the file using Windows Explorer. You probably want to move both the data files (.mdf and .ndf files) and the transaction log (.ldf) file.

You can attach a database by right clicking on the databases folder in Object Explorer (in Management Studio) and selecting the Attach... context menu item. You can change the paths to files in the attach database dialog if it can't find some file.

Thanks,

Steve

|||

Thanks for the reply - Steve..

As soon as I get some time to look into this - I 'll give it a try..

Your advise was exactly what I've been looking for - for quite some days - but just couldn't figure out.. My whole idea is to have the databases on another drive - so as to when I do a complete re-format and re-install - which I do often (to try out different scenarios) - I can then just install sql and etc. - and then connect (attach..?) to the same databases again..

Thanks

how to create a mdf db with sql server 2005 ctp?

hi,

i have installed the sql server 2005 ctp cause i couldn't install the sql server 2005 express on my computer. when i now create a database under the sql server 2005 management studio and connect from vs 2005 beta1 everything works fine for me.

but i want to create and work with the single mdf files wich are created by sql server express edition via ADD NEW ITEM -> DATABASE -> DB.MDF .

is it possible to create such a db file for xcopy deploayment use?

thanks
Yavuz BogazciYavuz,

The functionality you speak of is only available with SQL Server Express. What problems did you encounter while installing Express?

Dan

how to create "sysobjects" table

Hello everyone.

I'm facing to a serious problem.

I wanted to migrate SQL 2000 -> SQL 2005.

First of all, I tried to attach my *.mdf file to SQL 2000. but It was not only failed but also SQL 2005 failed to recover my *mdf file.

Now, I think my *.MDF file has system tables for SQL 2005 because when trying to attach *.mdf to SQL 2000 engine send the following message :

-----------------------
Msg 906, Level 22, State 2, Line 1
Could not locate row in sysobjects for system catalog 'Sysobjects' in database 'rudol.net'. This system catalog should exist in all databases. Run DBCC CHECKTABLE on sysobjects in this database.
-----------------------

Also, I tried again to attach the *.mdf to SQL 2005 but SQL 2005 doesn't really works.

Stupidly, I didn't back up the *.mdf file.

How do I attach it to SQL 2005 or recovery "sysobjects" table for SQL 2000?
Please answer.

P.S.
Forgive my terrible English. English is not my mother tongue.Without a backup or a copy of the files, you are screwed. When you attached the file to 2005, it started making internal changes that cannot be reversed by attempting to reattach it to 2000.

You will have to go to the latest backup that you have, restore it to 2000, and add back any missing transactions from the one that got hosed.

Always, always make a backup before changing stuff in a database!|||Your English isn't bad at all... You don't write things the way that a native speaker would write them, but you certainly do write things so that a native speaker can read them!

The solution to your problem gets complex for a number of reasons.

The MDX file is only part of a SQL Server database, it is the "root store" for all data. There is always an LDF file that stores the log information. There can also be NDF files too, which are additional data storage when you have more than one data file in your database. You need to have ALL of these files in order for a reattach to work in most cases, attaching only one file of a set will usually fail and will always be incomplete.

The cheapest, easiest solution is to restore a current backup. My guess is that you probably don't have a current enough backup to make this practical, but it is by far your best answer.

If you really need to salvage what you can from the MDF file, I see a couple of choices, all of which are bad in some way.

To get the most usable data the fastest, I would suggest that you call Microsoft Premier Support Services. This will cost less than $1000 USD, probably take less than a day, and will recover as much data as you are likely to get from just the MDF file. As a side effect, it will get some routine analysis of your system configuration and a lot of comments that will help you improve that configuration as part of the process of salvaging your data.

If you have more or less unlimited time to spend on the salvage operation, you can try to "do it yourself" with help from the internet. There are a number of folks that have salvaged data in similar situations, and we can probably talk you through a significant recovery somewere between a week and a month.

There are other choices, but these are the only ones that make sense to propose at this point. As the discussion moves on further, we may want to explore some of the other options.

-PatP

How to copy..........

I have 2 files with its same name (at folder C:\a\ and C:\b\, both Name
files is SData.mdf). I wanna access the files in enterprise manager at the
time. Can I do it? Because I wanna copy a object (table, store procedure,
etc) from C:\a\Sdata.mdf to C:\b\Sdata.mdf.
How to do it efficiently?Hi
You cannot 'access/open' them . Its data files SQL Server used for. RESTORE
DATABASE with a different names and make your copies
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:eLt2paOTGHA.4384@.tk2msftngp13.phx.gbl...
>I have 2 files with its same name (at folder C:\a\ and C:\b\, both Name
> files is SData.mdf). I wanna access the files in enterprise manager at the
> time. Can I do it? Because I wanna copy a object (table, store procedure,
> etc) from C:\a\Sdata.mdf to C:\b\Sdata.mdf.
> How to do it efficiently?
>