Multiple Database Files

[Dave Akers]

Usually, our in house ERP software has 1 database and 1 database file. After an upgrade from MS SQL 6.5 to MS SQL 7.0 I have a database who's properties show that it is made up of multiple datafiles. What is the easiest and safest method to return this database to only have 1 datafile?

[BL]

Backup the database and do a RESTORE WITH MOVE. You can redefine the single logical file which has multiple physical files back to one physical file:

restore database MyDB
from disk = 'E:BackupsMyDB.FBK'
with move 'MyDB_Dat' to 'E:MSSQL7DataMyDB.mdf',
move 'MyDB_Log' to 'E:MSSQL7DataMyDB.ldf'


------------
Dave Akers at 9/13/00 11:38:55 AM

Usually, our in house ERP software has 1 database and 1 database file. After an upgrade from MS SQL 6.5 to MS SQL 7.0 I have a database who's properties show that it is made up of multiple datafiles. What is the easiest and safest method to return this database to only have 1 datafile?

Apparently the Restore Database with the WITH MOVE only works from one device to a new one. It does not let me restore a database that has multiple database devices into a new database that reside in a single device. The previous suggestion yields "Cannot create file MyNewDB_Data.MDF because it already exits". Here are the specifics:

Current situation:
=================
Database: MyDB

Database Devices :
MyDB_Data1 on c:MyDB_Data1.MDF
MyDB_Data2 on c:MyDB_Data2.MDF
MyDB_Data3 on c:MyDB_Data3.MDF
MyDB_Data4 on c:MyDB_Data4.MDF

Log:
MyLog on c:MyDB_Log.LDF

Would like to have:
=================
Database: MyNewDB

Database Device:
Anything_Data on c:MyNewDB_Data.MDF

Log:
Anything_Log on c:MyNewDB_Log.LDF



------------
BL at 9/13/00 3:38:27 PM

Backup the database and do a RESTORE WITH MOVE. You can redefine the single logical file which has multiple physical files back to one physical file:

restore database MyDB
from disk = 'E:BackupsMyDB.FBK'
with move 'MyDB_Dat' to 'E:MSSQL7DataMyDB.mdf',
move 'MyDB_Log' to 'E:MSSQL7DataMyDB.ldf'


------------
Dave Akers at 9/13/00 11:38:55 AM

Usually, our in house ERP software has 1 database and 1 database file. After an upgrade from MS SQL 6.5 to MS SQL 7.0 I have a database who's properties show that it is made up of multiple datafiles. What is the easiest and safest method to return this database to only have 1 datafile?

[andy]

I think you can create a databse with on data file and on log file.
and restore the old database to overwrite new db


------------
at 9/14/00 9:15:55 AM

Apparently the Restore Database with the WITH MOVE only works from one device to a new one. It does not let me restore a database that has multiple database devices into a new database that reside in a single device. The previous suggestion yields "Cannot create file MyNewDB_Data.MDF because it already exits". Here are the specifics:

Current situation:
=================
Database: MyDB

Database Devices :
MyDB_Data1 on c:MyDB_Data1.MDF
MyDB_Data2 on c:MyDB_Data2.MDF
MyDB_Data3 on c:MyDB_Data3.MDF
MyDB_Data4 on c:MyDB_Data4.MDF

Log:
MyLog on c:MyDB_Log.LDF

Would like to have:
=================
Database: MyNewDB

Database Device:
Anything_Data on c:MyNewDB_Data.MDF

Log:
Anything_Log on c:MyNewDB_Log.LDF



------------
BL at 9/13/00 3:38:27 PM

Backup the database and do a RESTORE WITH MOVE. You can redefine the single logical file which has multiple physical files back to one physical file:

restore database MyDB
from disk = 'E:BackupsMyDB.FBK'
with move 'MyDB_Dat' to 'E:MSSQL7DataMyDB.mdf',
move 'MyDB_Log' to 'E:MSSQL7DataMyDB.ldf'


------------
Dave Akers at 9/13/00 11:38:55 AM

Usually, our in house ERP software has 1 database and 1 database file. After an upgrade from MS SQL 6.5 to MS SQL 7.0 I have a database who's properties show that it is made up of multiple datafiles. What is the easiest and safest method to return this database to only have 1 datafile?

[Sam Moayedi]

First you need to add another file with
ALTER DATABASE database
{ ADD FILE <filespec> [,...n] [TO FILEGROUP filegroup_name]
Second step should be use the DBCC SHRINKFILE statement and specify the EMPTYFILE clause. SQL Server no longer allows data to be placed on the file, thereby allowing it to be deleted by using the ALTER DATABASE statement or the property page within SQL Server Enterprise Manager.
Third step ALTER DATABASE database
REMOVE FILEGROUP filegroup_name.
This should reslove your issue





------------
Dave Akers at 9/13/00 11:38:55 AM

Usually, our in house ERP software has 1 database and 1 database file. After an upgrade from MS SQL 6.5 to MS SQL 7.0 I have a database who&#39;s properties show that it is made up of multiple datafiles. What is the easiest and safest method to return this database to only have 1 datafile?

[Dan Wunder]

Another [longer] alternative might be to script out your existing database and, using a text editor, do a Find & Replace on any &#39;ON [filegroup]&#39; statements so that they read &#39;ON [PRIMARY]&#39;. Create a new empty database on a single file, run the scripts against it, and then use DTS to transfer the contents of the old database to the new one. You can then drop the old db and use sp_renamedb to get your database to have the same name as the old one.


------------
Dave Akers at 9/13/00 11:38:55 AM

Usually, our in house ERP software has 1 database and 1 database file. After an upgrade from MS SQL 6.5 to MS SQL 7.0 I have a database who&#39;s properties show that it is made up of multiple datafiles. What is the easiest and safest method to return this database to only have 1 datafile?

[Lon Ramsey]

Use DBCC SHRINKFILE ( file_name, EMPTYFILE )
and then drop the file.

Good Luck,
Lon



------------
Dave Akers at 9/13/00 11:38:55 AM

Usually, our in house ERP software has 1 database and 1 database file. After an upgrade from MS SQL 6.5 to MS SQL 7.0 I have a database who&#39;s properties show that it is made up of multiple datafiles. What is the easiest and safest method to return this database to only have 1 datafile?

[Lon Ramsey]

Use DBCC SHRINKFILE ( file_name, EMPTYFILE )
and then drop the file.

Good Luck,
Lon



------------
Dave Akers at 9/13/00 11:38:55 AM

Usually, our in house ERP software has 1 database and 1 database file. After an upgrade from MS SQL 6.5 to MS SQL 7.0 I have a database who&#39;s properties show that it is made up of multiple datafiles. What is the easiest and safest method to return this database to only have 1 datafile?

[Lon Ramsey]

Use DBCC SHRINKFILE ( FILE_NAME, EMPTYFILE)
ALTER DATABASE Test1

REMOVE FILE FILE_NAME

Good Luck,
Lon



------------
Dave Akers at 9/13/00 11:38:55 AM

Usually, our in house ERP software has 1 database and 1 database file. After an upgrade from MS SQL 6.5 to MS SQL 7.0 I have a database who&#39;s properties show that it is made up of multiple datafiles. What is the easiest and safest method to return this database to only have 1 datafile?

[Lon Ramsey]

Use DBCC SHRINKFILE ( FILE_NAME, EMPTYFILE)
ALTER DATABASE Test1

REMOVE FILE FILE_NAME

Good Luck,
Lon




------------
Dave Akers at 9/13/00 11:38:55 AM

Usually, our in house ERP software has 1 database and 1 database file. After an upgrade from MS SQL 6.5 to MS SQL 7.0 I have a database who&#39;s properties show that it is made up of multiple datafiles. What is the easiest and safest method to return this database to only have 1 datafile?

[Lon Ramsey]

Use DBCC SHRINKFILE ( FILE_NAME, EMPTYFILE)
ALTER DATABASE Test1

REMOVE FILE FILE_NAME

Good Luck,
Lon




------------
Dave Akers at 9/13/00 11:38:55 AM

Usually, our in house ERP software has 1 database and 1 database file. After an upgrade from MS SQL 6.5 to MS SQL 7.0 I have a database who&#39;s properties show that it is made up of multiple datafiles. What is the easiest and safest method to return this database to only have 1 datafile?

[Lon Ramsey]

Use DBCC SHRINKFILE ( FILE_NAME, EMPTYFILE)
ALTER DATABASE Test1

REMOVE FILE FILE_NAME

Good Luck,
Lon




------------
Dave Akers at 9/13/00 11:38:55 AM

Usually, our in house ERP software has 1 database and 1 database file. After an upgrade from MS SQL 6.5 to MS SQL 7.0 I have a database who&#39;s properties show that it is made up of multiple datafiles. What is the easiest and safest method to return this database to only have 1 datafile?