Logical filenames when restoring to a new database?

We need to restore an older copy of a database back on to the same SQL
2000 server. I need the older version to recover some records. One
thing that confuses me is the logical file names.
Basically I'm following an example from the help files which I have
included at the end of this message. Using the restore I'm bother by
the fact that I can't change the logical file names. The database
MyNwind2_Test will have the same logical filenames as the orginal. Is
there a way to change the logical filenames? Can two databases in the
same sql instance have the same logical filenames?
Thank You,
Randy K
wawork@.hotmail.com
USE master
GO
-- First determine the number and names of the files in the backup.
-- MyNwind_2 is the name of the backup device.
RESTORE FILELISTONLY
FROM MyNwind_2
-- Restore the files for MyNwind2_Test.
RESTORE DATABASE MyNwind2_Test
FROM MyNwind_2
WITH RECOVERY,
MOVE 'MyNwind2_data' TO 'D:\MyData\MyNwind2_Test_data.mdf',
MOVE 'MyNwind2_log' TO 'D:\MyData\MyNwind2_Test_log.ldf'Yes 2 databases can have the same logical filenames with no problems.
However to change them you can use ALTER DATABASE dbname MODIFY FILE (NAME =logical_file_name, NEWNAME = new_logical_name...) See BOL for details
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Randy K" <wawork@.hotmail.com> wrote in message
news:3fbd470e.23662406@.msnews.microsoft.com...
We need to restore an older copy of a database back on to the same SQL
2000 server. I need the older version to recover some records. One
thing that confuses me is the logical file names.
Basically I'm following an example from the help files which I have
included at the end of this message. Using the restore I'm bother by
the fact that I can't change the logical file names. The database
MyNwind2_Test will have the same logical filenames as the orginal. Is
there a way to change the logical filenames? Can two databases in the
same sql instance have the same logical filenames?
Thank You,
Randy K
wawork@.hotmail.com
USE master
GO
-- First determine the number and names of the files in the backup.
-- MyNwind_2 is the name of the backup device.
RESTORE FILELISTONLY
FROM MyNwind_2
-- Restore the files for MyNwind2_Test.
RESTORE DATABASE MyNwind2_Test
FROM MyNwind_2
WITH RECOVERY,
MOVE 'MyNwind2_data' TO 'D:\MyData\MyNwind2_Test_data.mdf',
MOVE 'MyNwind2_log' TO 'D:\MyData\MyNwind2_Test_log.ldf'

Logical Disk Fragmentation on Database Drive

We are using Disk Keeper Server from Executive Software version 7.0.410.0.
I am trying to minimize the logical fragmentation on the drive where our
database (.MDF) is located.
The problem I am having is that I do not have the drive space to move
the .MDF to another drive in order to defragment.
I have tried to stop the MSSQLSERVER and SQLSERVERAGENT services and
then attempt to defrag without moving the file and this has not been
successful.
I thought I may be doing something wrong with Diskeeper or SQL 2000.
Any thoughts or ideas would be very helpful.
TIA,
-Ben
Ben wrote:
> We are using Disk Keeper Server from Executive Software version
> 7.0.410.0.
> I am trying to minimize the logical fragmentation on the drive where
> our database (.MDF) is located.
> The problem I am having is that I do not have the drive space to move
> the .MDF to another drive in order to defragment.
> I have tried to stop the MSSQLSERVER and SQLSERVERAGENT services and
> then attempt to defrag without moving the file and this has not been
> successful.
> I thought I may be doing something wrong with Diskeeper or SQL 2000.
> Any thoughts or ideas would be very helpful.
> TIA,
> -Ben
Diskeeper requires a good amount of free drive space to best defrag
files. Since the MDF file you are defragging is large (I assume) and
there is little drive space left, Diskeeper appears to be having
trouble. How much free space do you have and how much space is there in
total? Do you know for a fact that the MDF file is heavily fragmented?
You could try shrinking the file using DBCC SHRINKFILE.
Run sp_spaceused on the database in question and see if there is
unallocated space. If so, shrink the file size, defrag, and then expand
the database again from SQL EM before returning the database to
production.
David G.

Logical Disk Fragmentation on Database Drive

We are using Disk Keeper Server from Executive Software version 7.0.410.0.
I am trying to minimize the logical fragmentation on the drive where our
database (.MDF) is located.
The problem I am having is that I do not have the drive space to move
the .MDF to another drive in order to defragment.
I have tried to stop the MSSQLSERVER and SQLSERVERAGENT services and
then attempt to defrag without moving the file and this has not been
successful.
I thought I may be doing something wrong with Diskeeper or SQL 2000.
Any thoughts or ideas would be very helpful.
TIA,
-BenBen wrote:
> We are using Disk Keeper Server from Executive Software version
> 7.0.410.0.
> I am trying to minimize the logical fragmentation on the drive where
> our database (.MDF) is located.
> The problem I am having is that I do not have the drive space to move
> the .MDF to another drive in order to defragment.
> I have tried to stop the MSSQLSERVER and SQLSERVERAGENT services and
> then attempt to defrag without moving the file and this has not been
> successful.
> I thought I may be doing something wrong with Diskeeper or SQL 2000.
> Any thoughts or ideas would be very helpful.
> TIA,
> -Ben
Diskeeper requires a good amount of free drive space to best defrag
files. Since the MDF file you are defragging is large (I assume) and
there is little drive space left, Diskeeper appears to be having
trouble. How much free space do you have and how much space is there in
total? Do you know for a fact that the MDF file is heavily fragmented?
You could try shrinking the file using DBCC SHRINKFILE.
Run sp_spaceused on the database in question and see if there is
unallocated space. If so, shrink the file size, defrag, and then expand
the database again from SQL EM before returning the database to
production.
David G.

Logical Disk Fragmentation on Database Drive

We are using Disk Keeper Server from Executive Software version 7.0.410.0.
I am trying to minimize the logical fragmentation on the drive where our
database (.MDF) is located.
The problem I am having is that I do not have the drive space to move
the .MDF to another drive in order to defragment.
I have tried to stop the MSSQLSERVER and SQLSERVERAGENT services and
then attempt to defrag without moving the file and this has not been
successful.
I thought I may be doing something wrong with Diskeeper or SQL 2000.
Any thoughts or ideas would be very helpful.
TIA,
-BenBen wrote:
> We are using Disk Keeper Server from Executive Software version
> 7.0.410.0.
> I am trying to minimize the logical fragmentation on the drive where
> our database (.MDF) is located.
> The problem I am having is that I do not have the drive space to move
> the .MDF to another drive in order to defragment.
> I have tried to stop the MSSQLSERVER and SQLSERVERAGENT services and
> then attempt to defrag without moving the file and this has not been
> successful.
> I thought I may be doing something wrong with Diskeeper or SQL 2000.
> Any thoughts or ideas would be very helpful.
> TIA,
> -Ben
Diskeeper requires a good amount of free drive space to best defrag
files. Since the MDF file you are defragging is large (I assume) and
there is little drive space left, Diskeeper appears to be having
trouble. How much free space do you have and how much space is there in
total? Do you know for a fact that the MDF file is heavily fragmented?
You could try shrinking the file using DBCC SHRINKFILE.
Run sp_spaceused on the database in question and see if there is
unallocated space. If so, shrink the file size, defrag, and then expand
the database again from SQL EM before returning the database to
production.
David G.