Renaming Database or files

Hi all, I have a dev database on a dev machine that is the same name as a database on a production server.

I would like to attach the dev database to the production server but I can't because there would be two database with the same name. I tried just renaming my dev .mdf to another name but that doesn't work., I think I need to rename the database files but I don't know how.

Can anyone tell me the solution to this problem?

Thanks

This will rename pubs to pubs2

EXEC sp_renamedb 'pubs', 'pubs2'

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Thanks SQL_Menace, I'll give that a try

Renaming Database Files

How can I go about safely renaming the data and log files for a
database?
Thanks.Hello ExcelMan,
I would go this way:
- Detach the database
- Rename the files
- Attach the database
Ekrem Önsoy
"ExcelMan" <sfarkas@.sjfcg.com> wrote in message
news:1188583811.122857.12230@.g4g2000hsf.googlegroups.com...
> How can I go about safely renaming the data and log files for a
> database?
> Thanks.
>|||On Aug 31, 11:31 am, Ekrem =D6nsoy <ek...@.btegitim.com> wrote:
> Hello ExcelMan,
> I would go this way:
> - Detach the database
> - Rename the files
> - Attach the database
> --
> Ekrem =D6nsoy
> "ExcelMan" <sfar...@.sjfcg.com> wrote in message
> news:1188583811.122857.12230@.g4g2000hsf.googlegroups.com...
>
> > How can I go about safely renaming the data and log files for a
> > database?
> > Thanks.- Hide quoted text -
> - Show quoted text -
That's what I tried and it does not work. The database will not
reattach if you change the names. When I changed them back to what I
started with it did reattach.
Any other ideas?|||Did you specify the new names when you executed sp_attach_db?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"ExcelMan" <sfarkas@.sjfcg.com> wrote in message
news:1188588512.215040.28540@.22g2000hsm.googlegroups.com...
On Aug 31, 11:31 am, Ekrem Önsoy <ek...@.btegitim.com> wrote:
> Hello ExcelMan,
> I would go this way:
> - Detach the database
> - Rename the files
> - Attach the database
> --
> Ekrem Önsoy
> "ExcelMan" <sfar...@.sjfcg.com> wrote in message
> news:1188583811.122857.12230@.g4g2000hsf.googlegroups.com...
>
> > How can I go about safely renaming the data and log files for a
> > database?
> > Thanks.- Hide quoted text -
> - Show quoted text -
That's what I tried and it does not work. The database will not
reattach if you change the names. When I changed them back to what I
started with it did reattach.
Any other ideas?|||There must be something you miss.
Tom could be right. How about trying this process via SSMS?
--
Ekrem Önsoy
"ExcelMan" <sfarkas@.sjfcg.com> wrote in message
news:1188588512.215040.28540@.22g2000hsm.googlegroups.com...
On Aug 31, 11:31 am, Ekrem Önsoy <ek...@.btegitim.com> wrote:
> Hello ExcelMan,
> I would go this way:
> - Detach the database
> - Rename the files
> - Attach the database
> --
> Ekrem Önsoy
> "ExcelMan" <sfar...@.sjfcg.com> wrote in message
> news:1188583811.122857.12230@.g4g2000hsf.googlegroups.com...
>
> > How can I go about safely renaming the data and log files for a
> > database?
> > Thanks.- Hide quoted text -
> - Show quoted text -
That's what I tried and it does not work. The database will not
reattach if you change the names. When I changed them back to what I
started with it did reattach.
Any other ideas?|||Logical or physical ones?
Whatever you try, first make a full backup.
See "alter database" statement in BOL. One way could be doing a backup and
restore it using options "replace" and "move".
create database test
go
select [name], physical_name
from sys.master_files
where database_id = db_id('test')
go
backup database test
to disk = 'c:\temp\test.bak'
go
alter database test
set single_user with rollback immediate
go
restore database test
from disk = 'c:\temp\test.bak'
with replace,
move 'test' to 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\test1.mdf',
move 'test_log' to 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\test1_log.ldf'
go
alter database test
modify file (name = 'test', newname = 'test1')
go
alter database test
modify file (name = 'test_log', newname = 'test1_log')
go
select [name], physical_name
from sys.master_files
where database_id = db_id('test')
go
drop database test
go
I played a little bit trying to change the physical file names usainf "alter
database" but there is a msg saying:
"The new path will be used the next time the database is started."
but I do not know how to start the db programatically. I tried setting it to
offline and then online but it fails. Here is the script.
use master
go
create database test
go
select [name], physical_name
from sys.master_files
where database_id = db_id('test')
go
alter database test
set single_user with rollback immediate
go
alter database test
modify file (name = 'test', newname = 'test1')
go
alter database test
modify file (name = 'test_log', newname = 'test1_log')
go
alter database test
modify file (name = 'test1', filename = 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\test1.mdf')
go
alter database test
modify file (name = 'test1_log', filename = 'C:\Program Files\Microsoft SQL
Server\MSSQL.2\MSSQL\DATA\test1_log.mdf')
go
alter database test
set online
go
select [name], physical_name
from sys.master_files
where database_id = db_id('test')
go
alter database test
set multi_user
go
drop database test
go
AMB
"ExcelMan" wrote:
> How can I go about safely renaming the data and log files for a
> database?
> Thanks.
>

Renaming an Access table in an SSIS package

My current project requires me to both rename the MDB file for an Access database and rename the table it contains. The Access files comes in with random names, each containing one table with a specific name. Based on the table name it contains, I rename both the file and the interior table to a standard name which a later package in the process references.

A foreach container loops through all the mdb files in the applicable directory, containing a script task and a file system task. The script task uses GetOleDbSchemaTable to extract the table name, then loops through an array of table names from the client's configuration, comparing it to a similar array of constant names and getting the matching one. The file system task then uses that found name (or the original table name if a conversion is not found) to rename the file to match that standard name. So far, so good.

Now I have to rename the table within the file as well. All of the examples of code I'm finding on the 'net refernce ADOX, but I haven't been able to figure out how to use that in a script task, assuming that's what I want to do in the first place.

Anyone have any experience with doing things like this?

Approach 1elect into a new table then drop the old table.

Approach 2: Keep the old "standard" import database and delete from the standard table, then select into it from the new database (that is, instead of renaming the existing object, just select into the desired destination object) then delete/archive the random-named database.

In the past I would have used DAO and the tabledefs collection therein to rename the table, but that is rather old-school these days. No guarantee that it would work.

|||

Thanks, Dylan. I may give the DAO a try just for giggles, because the alternative is (for now) each client having their own copy of a relatively complex package.

rename physical files?

Is it possible to rename physical MDF and LDF files? I have been searching
the net for a few hours and I thought I got close with an ALTER DB command.
But I got errors. I think that is only for logical names. Anyhow, here is
what I tried:
alter database pre_8live_hc modify file (name = 'restoredb', newname =
'pre_8live_hc')
alter database pre_8live_hc modify file (name = 'restoredb_log', newname =
'pre_8live_hc_log')
This is what I got back:
Server: Msg 5041, Level 16, State 1, Line 1
MODIFY FILE failed. File 'restoredb' does not exist.
Server: Msg 5041, Level 16, State 1, Line 2
MODIFY FILE failed. File 'restoredb_log' does not exist.
The DB name is pre_8live_hc
Filenames are: (logical / physical)
MDF = NTI_hc_data / d:\program files\microsoft sql
server\msssql\data\restoredb.mdf
LDF = NTI_hc_data_log / d:\program files\microsoft sql
server\msssql\data\restoredb_log.mdf
I have seen some stuff about restore with replace and also attachdb, but
could not get definitive answer. And the commands I tried did not work.
Thanks,
Bryan
You can detach the database, move the physical files (and/or rename them)
and then attach the database. If you can't get that to work, show us the
syntax you are using for sp_attach_db and we'll try to see what's wrong.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"IT Department" <it@.ntihc.com> wrote in message
news:r6amd.10958$Ae.9201@.newsread1.dllstx09.us.to. verio.net...
> Is it possible to rename physical MDF and LDF files? I have been
> searching the net for a few hours and I thought I got close with an ALTER
> DB command. But I got errors. I think that is only for logical names.
> Anyhow, here is what I tried:
> alter database pre_8live_hc modify file (name = 'restoredb', newname =
> 'pre_8live_hc')
> alter database pre_8live_hc modify file (name = 'restoredb_log', newname =
> 'pre_8live_hc_log')
> This is what I got back:
> Server: Msg 5041, Level 16, State 1, Line 1
> MODIFY FILE failed. File 'restoredb' does not exist.
> Server: Msg 5041, Level 16, State 1, Line 2
> MODIFY FILE failed. File 'restoredb_log' does not exist.
> The DB name is pre_8live_hc
> Filenames are: (logical / physical)
> MDF = NTI_hc_data / d:\program files\microsoft sql
> server\msssql\data\restoredb.mdf
> LDF = NTI_hc_data_log / d:\program files\microsoft sql
> server\msssql\data\restoredb_log.mdf
>
> I have seen some stuff about restore with replace and also attachdb, but
> could not get definitive answer. And the commands I tried did not work.
> Thanks,
> Bryan
>
|||Hi,
Follow kelens method. What you did is to rename the Logicalfile name. TO
rename the logical database name you have to be in that database.
Thanks
Hari
SQL Server MVP
"IT Department" <it@.ntihc.com> wrote in message
news:r6amd.10958$Ae.9201@.newsread1.dllstx09.us.to. verio.net...
> Is it possible to rename physical MDF and LDF files? I have been
> searching the net for a few hours and I thought I got close with an ALTER
> DB command. But I got errors. I think that is only for logical names.
> Anyhow, here is what I tried:
> alter database pre_8live_hc modify file (name = 'restoredb', newname =
> 'pre_8live_hc')
> alter database pre_8live_hc modify file (name = 'restoredb_log', newname =
> 'pre_8live_hc_log')
> This is what I got back:
> Server: Msg 5041, Level 16, State 1, Line 1
> MODIFY FILE failed. File 'restoredb' does not exist.
> Server: Msg 5041, Level 16, State 1, Line 2
> MODIFY FILE failed. File 'restoredb_log' does not exist.
> The DB name is pre_8live_hc
> Filenames are: (logical / physical)
> MDF = NTI_hc_data / d:\program files\microsoft sql
> server\msssql\data\restoredb.mdf
> LDF = NTI_hc_data_log / d:\program files\microsoft sql
> server\msssql\data\restoredb_log.mdf
>
> I have seen some stuff about restore with replace and also attachdb, but
> could not get definitive answer. And the commands I tried did not work.
> Thanks,
> Bryan
>
|||Thanks guys. The sp_attach_db worked. I will have to admit I didn't try
just renaming the physical files. For some reason I thought I had tried
that in the past and I kep getting errors saying the file didn't exist, but
now that I think back, I was trying to delete one of two transaction logs
and it wouldn't attach back without both logs.
Thanks a bunch!
Bryan
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OeLusr5yEHA.3408@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Follow kelens method. What you did is to rename the Logicalfile name. TO
> rename the logical database name you have to be in that database.
> --
> Thanks
> Hari
> SQL Server MVP
>
> "IT Department" <it@.ntihc.com> wrote in message
> news:r6amd.10958$Ae.9201@.newsread1.dllstx09.us.to. verio.net...
>

Rename MDF file

Is there a way to rename the mdf and ldf files associated with a databse.
Also can I rename the fil nemae that is created for these when the databse
gets created.you can detach the database
rename the files
attach the database by specifying the new file names
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:25A21625-0C18-465F-ADCF-D0AB6B66F002@.microsoft.com...
> Is there a way to rename the mdf and ldf files associated with a databse.
> Also can I rename the fil nemae that is created for these when the databse
> gets created.|||To add to Jj's response, you can rename logical file names with ALTER
DATABASE. For example:
ALTER DATABASE MyDatabase
MODIFY FILE(
NAME='MyOldLogicalName',
NEWNAME='MyNewLogicalName')
See the Books online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:25A21625-0C18-465F-ADCF-D0AB6B66F002@.microsoft.com...
> Is there a way to rename the mdf and ldf files associated with a databse.
> Also can I rename the fil nemae that is created for these when the databse
> gets created.

Rename MDF file

Is there a way to rename the mdf and ldf files associated with a databse.
Also can I rename the fil nemae that is created for these when the databse
gets created.
you can detach the database
rename the files
attach the database by specifying the new file names
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:25A21625-0C18-465F-ADCF-D0AB6B66F002@.microsoft.com...
> Is there a way to rename the mdf and ldf files associated with a databse.
> Also can I rename the fil nemae that is created for these when the databse
> gets created.
|||To add to Jj's response, you can rename logical file names with ALTER
DATABASE. For example:
ALTER DATABASE MyDatabase
MODIFY FILE(
NAME='MyOldLogicalName',
NEWNAME='MyNewLogicalName')
See the Books online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:25A21625-0C18-465F-ADCF-D0AB6B66F002@.microsoft.com...
> Is there a way to rename the mdf and ldf files associated with a databse.
> Also can I rename the fil nemae that is created for these when the databse
> gets created.

Rename MDF file

Is there a way to rename the mdf and ldf files associated with a databse.
Also can I rename the fil nemae that is created for these when the databse
gets created.you can detach the database
rename the files
attach the database by specifying the new file names
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:25A21625-0C18-465F-ADCF-D0AB6B66F002@.microsoft.com...
> Is there a way to rename the mdf and ldf files associated with a databse.
> Also can I rename the fil nemae that is created for these when the databse
> gets created.|||To add to Jéjé's response, you can rename logical file names with ALTER
DATABASE. For example:
ALTER DATABASE MyDatabase
MODIFY FILE(
NAME='MyOldLogicalName',
NEWNAME='MyNewLogicalName')
See the Books online for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:25A21625-0C18-465F-ADCF-D0AB6B66F002@.microsoft.com...
> Is there a way to rename the mdf and ldf files associated with a databse.
> Also can I rename the fil nemae that is created for these when the databse
> gets created.

rename logical file

Hi,
I have 3 databases running on Production server. All the databases have
names like AA, BB and CC with their Logical Data Files and Log files named as
AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
rename it as CC_Data and CC_Log to make it consistent. Would this renaming
affect anything on the server?
You can change the logical file names, using ALTER DATABASE, without taking
the DB's offline.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
Hi,
I have 3 databases running on Production server. All the databases have
names like AA, BB and CC with their Logical Data Files and Log files named
as
AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
rename it as CC_Data and CC_Log to make it consistent. Would this renaming
affect anything on the server?
|||Hi,
Would it affect anything / any process on the production server?
"Tom Moreau" wrote:

> You can change the logical file names, using ALTER DATABASE, without taking
> the DB's offline.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> Hi,
> I have 3 databases running on Production server. All the databases have
> names like AA, BB and CC with their Logical Data Files and Log files named
> as
> AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
> rename it as CC_Data and CC_Log to make it consistent. Would this renaming
> affect anything on the server?
>
|||No. It goes quickly.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
Hi,
Would it affect anything / any process on the production server?
"Tom Moreau" wrote:

> You can change the logical file names, using ALTER DATABASE, without
> taking
> the DB's offline.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> Hi,
> I have 3 databases running on Production server. All the databases have
> names like AA, BB and CC with their Logical Data Files and Log files named
> as
> AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
> rename it as CC_Data and CC_Log to make it consistent. Would this renaming
> affect anything on the server?
>
|||Hi Tom,
Can you please explain to me what exactly is the purpose of a logical
Filename? When we have more than one databases on one SQL Server, can we have
the same logical name for all the databases? Thanks in advance.
"Tom Moreau" wrote:

> No. It goes quickly.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
> Hi,
> Would it affect anything / any process on the production server?
> "Tom Moreau" wrote:
>
>
|||The logical filename is just an easy way to refer to it when using ALTER
DATABASE. They are unique to the DB - not the server - so you can have a
number of DB's with the same logical filenames, but with different physical
filenames. This is convenient is a scenario where you have one DB per
client and what all of your DB maintenance scripts to do the same thing to
each customer DB.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:82F4AE24-BD99-4CD4-8411-CCFCB79882E5@.microsoft.com...
Hi Tom,
Can you please explain to me what exactly is the purpose of a logical
Filename? When we have more than one databases on one SQL Server, can we
have
the same logical name for all the databases? Thanks in advance.
"Tom Moreau" wrote:

> No. It goes quickly.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
> Hi,
> Would it affect anything / any process on the production server?
> "Tom Moreau" wrote:
>
>
|||Does changing the logical name also change the physical (file) name? If not,
is there a method of doing that too?
"Tom Moreau" wrote:

> The logical filename is just an easy way to refer to it when using ALTER
> DATABASE. They are unique to the DB - not the server - so you can have a
> number of DB's with the same logical filenames, but with different physical
> filenames. This is convenient is a scenario where you have one DB per
> client and what all of your DB maintenance scripts to do the same thing to
> each customer DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:82F4AE24-BD99-4CD4-8411-CCFCB79882E5@.microsoft.com...
> Hi Tom,
> Can you please explain to me what exactly is the purpose of a logical
> Filename? When we have more than one databases on one SQL Server, can we
> have
> the same logical name for all the databases? Thanks in advance.
> "Tom Moreau" wrote:
>
>
|||> Does changing the logical name also change the physical (file) name?
No.

> If not,
> is there a method of doing that too?
Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to change
the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this very
useful, so I prefer detach and attach better in 2005 as well.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Yabe" <Yabe@.discussions.microsoft.com> wrote in message
news:85B0F865-7ABB-44FC-8690-145907510A05@.microsoft.com...[vbcol=seagreen]
> Does changing the logical name also change the physical (file) name? If not,
> is there a method of doing that too?
>
> "Tom Moreau" wrote:
|||Thank you.
"Tibor Karaszi" wrote:

> No.
>
> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to change
> the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this very
> useful, so I prefer detach and attach better in 2005 as well.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
|||"Tibor Karaszi" wrote:

> No.
>
> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to change
> the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this very
> useful, so I prefer detach and attach better in 2005 as well.
Hi Tibor,
I've been experimenting with detach/attach and I can't (for the life of me)
figure out how to change the name on the fly -- in MSS 2005 (or earlier
release for that matter). Maybe I'm going about this all wrong ... all on
the same server ... Here's my situation ... I have a database that I want to
clone and rename on the same server.
I have one database named "Database1" (c:\data1\d1.mdf, c:\data1\d1.ldf).
I detach the original database and copy both files to a new directory
x:\data2\d1.mdf, x:\data2\d1.ldf).
I re-attach the original database Database1 using MSS Management Studio.
Then I attempt to attach the 2nd database, I'll browse to the new directory
and select the duplicate d1.mdf, but it won't let me change the database
name, so I abort.
Then I manually rename both the (copied) database and the log files from d1
to d2 (x:\data2\d2.mdf and x:\data2\d2.ldf) and try to attach again.
When I attach the newly renamed files, MSS remembers both the original file
names and the original database name "Database1" and will complain if I click
OK to try and save the 2nd database to the host/instance. I don't see how to
1) change the original database name when attaching or 2) force the MSS
system to let me change the new database name on the fly as I attach it.
Sorry if the answer is looking me in the face

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi

rename logical file

Hi,
I have 3 databases running on Production server. All the databases have
names like AA, BB and CC with their Logical Data Files and Log files named as
AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
rename it as CC_Data and CC_Log to make it consistent. Would this renaming
affect anything on the server?You can change the logical file names, using ALTER DATABASE, without taking
the DB's offline.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
Hi,
I have 3 databases running on Production server. All the databases have
names like AA, BB and CC with their Logical Data Files and Log files named
as
AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
rename it as CC_Data and CC_Log to make it consistent. Would this renaming
affect anything on the server?|||Hi,
Would it affect anything / any process on the production server?
"Tom Moreau" wrote:
> You can change the logical file names, using ALTER DATABASE, without taking
> the DB's offline.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> Hi,
> I have 3 databases running on Production server. All the databases have
> names like AA, BB and CC with their Logical Data Files and Log files named
> as
> AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
> rename it as CC_Data and CC_Log to make it consistent. Would this renaming
> affect anything on the server?
>|||No. It goes quickly.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
Hi,
Would it affect anything / any process on the production server?
"Tom Moreau" wrote:
> You can change the logical file names, using ALTER DATABASE, without
> taking
> the DB's offline.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> Hi,
> I have 3 databases running on Production server. All the databases have
> names like AA, BB and CC with their Logical Data Files and Log files named
> as
> AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
> rename it as CC_Data and CC_Log to make it consistent. Would this renaming
> affect anything on the server?
>|||Hi Tom,
Can you please explain to me what exactly is the purpose of a logical
Filename? When we have more than one databases on one SQL Server, can we have
the same logical name for all the databases? Thanks in advance.
"Tom Moreau" wrote:
> No. It goes quickly.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
> Hi,
> Would it affect anything / any process on the production server?
> "Tom Moreau" wrote:
> > You can change the logical file names, using ALTER DATABASE, without
> > taking
> > the DB's offline.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> > Hi,
> >
> > I have 3 databases running on Production server. All the databases have
> > names like AA, BB and CC with their Logical Data Files and Log files named
> > as
> > AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
> > rename it as CC_Data and CC_Log to make it consistent. Would this renaming
> > affect anything on the server?
> >
> >
>|||The logical filename is just an easy way to refer to it when using ALTER
DATABASE. They are unique to the DB - not the server - so you can have a
number of DB's with the same logical filenames, but with different physical
filenames. This is convenient is a scenario where you have one DB per
client and what all of your DB maintenance scripts to do the same thing to
each customer DB.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:82F4AE24-BD99-4CD4-8411-CCFCB79882E5@.microsoft.com...
Hi Tom,
Can you please explain to me what exactly is the purpose of a logical
Filename? When we have more than one databases on one SQL Server, can we
have
the same logical name for all the databases? Thanks in advance.
"Tom Moreau" wrote:
> No. It goes quickly.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
> Hi,
> Would it affect anything / any process on the production server?
> "Tom Moreau" wrote:
> > You can change the logical file names, using ALTER DATABASE, without
> > taking
> > the DB's offline.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> > Hi,
> >
> > I have 3 databases running on Production server. All the databases have
> > names like AA, BB and CC with their Logical Data Files and Log files
> > named
> > as
> > AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want
> > to
> > rename it as CC_Data and CC_Log to make it consistent. Would this
> > renaming
> > affect anything on the server?
> >
> >
>|||Does changing the logical name also change the physical (file) name? If not,
is there a method of doing that too?
"Tom Moreau" wrote:
> The logical filename is just an easy way to refer to it when using ALTER
> DATABASE. They are unique to the DB - not the server - so you can have a
> number of DB's with the same logical filenames, but with different physical
> filenames. This is convenient is a scenario where you have one DB per
> client and what all of your DB maintenance scripts to do the same thing to
> each customer DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:82F4AE24-BD99-4CD4-8411-CCFCB79882E5@.microsoft.com...
> Hi Tom,
> Can you please explain to me what exactly is the purpose of a logical
> Filename? When we have more than one databases on one SQL Server, can we
> have
> the same logical name for all the databases? Thanks in advance.
> "Tom Moreau" wrote:
> > No. It goes quickly.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > SQL Server MVP
> > Toronto, ON Canada
> > https://mvp.support.microsoft.com/profile/Tom.Moreau
> >
> >
> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
> > Hi,
> >
> > Would it affect anything / any process on the production server?
> >
> > "Tom Moreau" wrote:
> >
> > > You can change the logical file names, using ALTER DATABASE, without
> > > taking
> > > the DB's offline.
> > >
> > > --
> > > Tom
> > >
> > > ----
> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> > > SQL Server MVP
> > > Toronto, ON Canada
> > > https://mvp.support.microsoft.com/profile/Tom.Moreau
> > >
> > >
> > > "sharman" <sharman@.discussions.microsoft.com> wrote in message
> > > news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> > > Hi,
> > >
> > > I have 3 databases running on Production server. All the databases have
> > > names like AA, BB and CC with their Logical Data Files and Log files
> > > named
> > > as
> > > AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want
> > > to
> > > rename it as CC_Data and CC_Log to make it consistent. Would this
> > > renaming
> > > affect anything on the server?
> > >
> > >
> >
> >
>|||> Does changing the logical name also change the physical (file) name?
No.
> If not,
> is there a method of doing that too?
Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to change
the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this very
useful, so I prefer detach and attach better in 2005 as well.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Yabe" <Yabe@.discussions.microsoft.com> wrote in message
news:85B0F865-7ABB-44FC-8690-145907510A05@.microsoft.com...
> Does changing the logical name also change the physical (file) name? If not,
> is there a method of doing that too?
>
> "Tom Moreau" wrote:
>> The logical filename is just an easy way to refer to it when using ALTER
>> DATABASE. They are unique to the DB - not the server - so you can have a
>> number of DB's with the same logical filenames, but with different physical
>> filenames. This is convenient is a scenario where you have one DB per
>> client and what all of your DB maintenance scripts to do the same thing to
>> each customer DB.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:82F4AE24-BD99-4CD4-8411-CCFCB79882E5@.microsoft.com...
>> Hi Tom,
>> Can you please explain to me what exactly is the purpose of a logical
>> Filename? When we have more than one databases on one SQL Server, can we
>> have
>> the same logical name for all the databases? Thanks in advance.
>> "Tom Moreau" wrote:
>> > No. It goes quickly.
>> >
>> > --
>> > Tom
>> >
>> > ----
>> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> > SQL Server MVP
>> > Toronto, ON Canada
>> > https://mvp.support.microsoft.com/profile/Tom.Moreau
>> >
>> >
>> > "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> > news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
>> > Hi,
>> >
>> > Would it affect anything / any process on the production server?
>> >
>> > "Tom Moreau" wrote:
>> >
>> > > You can change the logical file names, using ALTER DATABASE, without
>> > > taking
>> > > the DB's offline.
>> > >
>> > > --
>> > > Tom
>> > >
>> > > ----
>> > > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> > > SQL Server MVP
>> > > Toronto, ON Canada
>> > > https://mvp.support.microsoft.com/profile/Tom.Moreau
>> > >
>> > >
>> > > "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> > > news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
>> > > Hi,
>> > >
>> > > I have 3 databases running on Production server. All the databases have
>> > > names like AA, BB and CC with their Logical Data Files and Log files
>> > > named
>> > > as
>> > > AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want
>> > > to
>> > > rename it as CC_Data and CC_Log to make it consistent. Would this
>> > > renaming
>> > > affect anything on the server?
>> > >
>> > >
>> >
>> >
>>|||Thank you.
"Tibor Karaszi" wrote:
> > Does changing the logical name also change the physical (file) name?
> No.
>
> > If not,
> > is there a method of doing that too?
> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to change
> the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this very
> useful, so I prefer detach and attach better in 2005 as well.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi|||"Tibor Karaszi" wrote:
> > Does changing the logical name also change the physical (file) name?
> No.
>
> > If not,
> > is there a method of doing that too?
> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to change
> the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this very
> useful, so I prefer detach and attach better in 2005 as well.
Hi Tibor,
I've been experimenting with detach/attach and I can't (for the life of me)
figure out how to change the name on the fly -- in MSS 2005 (or earlier
release for that matter). Maybe I'm going about this all wrong ... all on
the same server ... Here's my situation ... I have a database that I want to
clone and rename on the same server.
I have one database named "Database1" (c:\data1\d1.mdf, c:\data1\d1.ldf).
I detach the original database and copy both files to a new directory
x:\data2\d1.mdf, x:\data2\d1.ldf).
I re-attach the original database Database1 using MSS Management Studio.
Then I attempt to attach the 2nd database, I'll browse to the new directory
and select the duplicate d1.mdf, but it won't let me change the database
name, so I abort.
Then I manually rename both the (copied) database and the log files from d1
to d2 (x:\data2\d2.mdf and x:\data2\d2.ldf) and try to attach again.
When I attach the newly renamed files, MSS remembers both the original file
names and the original database name "Database1" and will complain if I click
OK to try and save the 2nd database to the host/instance. I don't see how to
1) change the original database name when attaching or 2) force the MSS
system to let me change the new database name on the fly as I attach it.
Sorry if the answer is looking me in the face :)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi|||I normally drop the db, then copy it to the new location - data in the
data directory, logs to their directory. Rename the files I copied my
test.mdf and test.ldf to test2.mdf and test2.ldf
Then in SMS do a attach database and browse to the mdf file. Select the
new one. If necessary do the same for the log (you'll have to do this
if you moved it).
* In the top window check the "Attach AS" and change it to whatever you
want - for me test2
* In the bottom window under "Current File Path" browse to the new file
and select the one you renamed.- do for mdf and ldf files. For mdf I
selected test2.mdf and for ldf test2.ldf
* In top window change owner if necessary.
* Click OK
Now go to the database in object explorer and right click->properties.
Check the files window. You'll see the new files selected and if you
want you can change the logical names.
Yabe wrote:
> "Tibor Karaszi" wrote:
>> Does changing the logical name also change the physical (file) name?
>> No.
>>
>> If not,
>> is there a method of doing that too?
>> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to change
>> the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this very
>> useful, so I prefer detach and attach better in 2005 as well.
> Hi Tibor,
> I've been experimenting with detach/attach and I can't (for the life of me)
> figure out how to change the name on the fly -- in MSS 2005 (or earlier
> release for that matter). Maybe I'm going about this all wrong ... all on
> the same server ... Here's my situation ... I have a database that I want to
> clone and rename on the same server.
> I have one database named "Database1" (c:\data1\d1.mdf, c:\data1\d1.ldf).
> I detach the original database and copy both files to a new directory
> x:\data2\d1.mdf, x:\data2\d1.ldf).
> I re-attach the original database Database1 using MSS Management Studio.
> Then I attempt to attach the 2nd database, I'll browse to the new directory
> and select the duplicate d1.mdf, but it won't let me change the database
> name, so I abort.
> Then I manually rename both the (copied) database and the log files from d1
> to d2 (x:\data2\d2.mdf and x:\data2\d2.ldf) and try to attach again.
> When I attach the newly renamed files, MSS remembers both the original file
> names and the original database name "Database1" and will complain if I click
> OK to try and save the 2nd database to the host/instance. I don't see how to
> 1) change the original database name when attaching or 2) force the MSS
> system to let me change the new database name on the fly as I attach it.
> Sorry if the answer is looking me in the face :)
>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>|||Probably just the SSMS GUI that is messing with you. I suggest you read about sp_attach_db in Books
Online. Since the mdf has the path and name of the original ldf (and other db files), you need to
specify all file names, like:
EXEC sp_attach_db 'NewDbName', 'C:\NewPath\NewFileName.mdf', 'C:\NewPath\NewFileName.ldf'
Above from memory, so please check Books Online for syntax.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Yabe" <Yabe@.discussions.microsoft.com> wrote in message
news:23B5D429-5E6A-4209-BCC2-CB9C20C0C94B@.microsoft.com...
>
> "Tibor Karaszi" wrote:
>> > Does changing the logical name also change the physical (file) name?
>> No.
>>
>> > If not,
>> > is there a method of doing that too?
>> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use ALTER DATABASE to
>> change
>> the physical name, stop SQL Server, copy the file, and then start SQL Server. I don't find this
>> very
>> useful, so I prefer detach and attach better in 2005 as well.
> Hi Tibor,
> I've been experimenting with detach/attach and I can't (for the life of me)
> figure out how to change the name on the fly -- in MSS 2005 (or earlier
> release for that matter). Maybe I'm going about this all wrong ... all on
> the same server ... Here's my situation ... I have a database that I want to
> clone and rename on the same server.
> I have one database named "Database1" (c:\data1\d1.mdf, c:\data1\d1.ldf).
> I detach the original database and copy both files to a new directory
> x:\data2\d1.mdf, x:\data2\d1.ldf).
> I re-attach the original database Database1 using MSS Management Studio.
> Then I attempt to attach the 2nd database, I'll browse to the new directory
> and select the duplicate d1.mdf, but it won't let me change the database
> name, so I abort.
> Then I manually rename both the (copied) database and the log files from d1
> to d2 (x:\data2\d2.mdf and x:\data2\d2.ldf) and try to attach again.
> When I attach the newly renamed files, MSS remembers both the original file
> names and the original database name "Database1" and will complain if I click
> OK to try and save the 2nd database to the host/instance. I don't see how to
> 1) change the original database name when attaching or 2) force the MSS
> system to let me change the new database name on the fly as I attach it.
> Sorry if the answer is looking me in the face :)
>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>

rename logical file

Hi,
I have 3 databases running on Production server. All the databases have
names like AA, BB and CC with their Logical Data Files and Log files named a
s
AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
rename it as CC_Data and CC_Log to make it consistent. Would this renaming
affect anything on the server?You can change the logical file names, using ALTER DATABASE, without taking
the DB's offline.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
Hi,
I have 3 databases running on Production server. All the databases have
names like AA, BB and CC with their Logical Data Files and Log files named
as
AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
rename it as CC_Data and CC_Log to make it consistent. Would this renaming
affect anything on the server?|||Hi,
Would it affect anything / any process on the production server?
"Tom Moreau" wrote:

> You can change the logical file names, using ALTER DATABASE, without takin
g
> the DB's offline.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> Hi,
> I have 3 databases running on Production server. All the databases have
> names like AA, BB and CC with their Logical Data Files and Log files named
> as
> AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
> rename it as CC_Data and CC_Log to make it consistent. Would this renaming
> affect anything on the server?
>|||No. It goes quickly.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
Hi,
Would it affect anything / any process on the production server?
"Tom Moreau" wrote:

> You can change the logical file names, using ALTER DATABASE, without
> taking
> the DB's offline.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:E0134C03-9EF7-44D3-A9E9-452C315CA1AA@.microsoft.com...
> Hi,
> I have 3 databases running on Production server. All the databases have
> names like AA, BB and CC with their Logical Data Files and Log files named
> as
> AA_Data and AA_Log, BB_Data and BB_Log except for the third one. I want to
> rename it as CC_Data and CC_Log to make it consistent. Would this renaming
> affect anything on the server?
>|||Hi Tom,
Can you please explain to me what exactly is the purpose of a logical
Filename? When we have more than one databases on one SQL Server, can we hav
e
the same logical name for all the databases? Thanks in advance.
"Tom Moreau" wrote:

> No. It goes quickly.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
> Hi,
> Would it affect anything / any process on the production server?
> "Tom Moreau" wrote:
>
>|||The logical filename is just an easy way to refer to it when using ALTER
DATABASE. They are unique to the DB - not the server - so you can have a
number of DB's with the same logical filenames, but with different physical
filenames. This is convenient is a scenario where you have one DB per
client and what all of your DB maintenance scripts to do the same thing to
each customer DB.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:82F4AE24-BD99-4CD4-8411-CCFCB79882E5@.microsoft.com...
Hi Tom,
Can you please explain to me what exactly is the purpose of a logical
Filename? When we have more than one databases on one SQL Server, can we
have
the same logical name for all the databases? Thanks in advance.
"Tom Moreau" wrote:

> No. It goes quickly.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:F5688817-57C3-40CD-88ED-B401B2E5438F@.microsoft.com...
> Hi,
> Would it affect anything / any process on the production server?
> "Tom Moreau" wrote:
>
>|||Does changing the logical name also change the physical (file) name? If not
,
is there a method of doing that too?
"Tom Moreau" wrote:

> The logical filename is just an easy way to refer to it when using ALTER
> DATABASE. They are unique to the DB - not the server - so you can have a
> number of DB's with the same logical filenames, but with different physica
l
> filenames. This is convenient is a scenario where you have one DB per
> client and what all of your DB maintenance scripts to do the same thing to
> each customer DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:82F4AE24-BD99-4CD4-8411-CCFCB79882E5@.microsoft.com...
> Hi Tom,
> Can you please explain to me what exactly is the purpose of a logical
> Filename? When we have more than one databases on one SQL Server, can we
> have
> the same logical name for all the databases? Thanks in advance.
> "Tom Moreau" wrote:
>
>|||> Does changing the logical name also change the physical (file) name?
No.

> If not,
> is there a method of doing that too?
Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use A
LTER DATABASE to change
the physical name, stop SQL Server, copy the file, and then start SQL Server
. I don't find this very
useful, so I prefer detach and attach better in 2005 as well.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Yabe" <Yabe@.discussions.microsoft.com> wrote in message
news:85B0F865-7ABB-44FC-8690-145907510A05@.microsoft.com...[vbcol=seagreen]
> Does changing the logical name also change the physical (file) name? If n
ot,
> is there a method of doing that too?
>
> "Tom Moreau" wrote:
>|||Thank you.
"Tibor Karaszi" wrote:

> No.
>
> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use
ALTER DATABASE to change
> the physical name, stop SQL Server, copy the file, and then start SQL Serv
er. I don't find this very
> useful, so I prefer detach and attach better in 2005 as well.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi|||"Tibor Karaszi" wrote:

> No.
>
> Yes. Easiest, IMO, is detach and attach the database. In 2005, you can use
ALTER DATABASE to change
> the physical name, stop SQL Server, copy the file, and then start SQL Serv
er. I don't find this very
> useful, so I prefer detach and attach better in 2005 as well.
Hi Tibor,
I've been experimenting with detach/attach and I can't (for the life of me)
figure out how to change the name on the fly -- in MSS 2005 (or earlier
release for that matter). Maybe I'm going about this all wrong ... all on
the same server ... Here's my situation ... I have a database that I want to
clone and rename on the same server.
I have one database named "Database1" (c:\data1\d1.mdf, c:\data1\d1.ldf).
I detach the original database and copy both files to a new directory
x:\data2\d1.mdf, x:\data2\d1.ldf).
I re-attach the original database Database1 using MSS Management Studio.
Then I attempt to attach the 2nd database, I'll browse to the new directory
and select the duplicate d1.mdf, but it won't let me change the database
name, so I abort.
Then I manually rename both the (copied) database and the log files from d1
to d2 (x:\data2\d2.mdf and x:\data2\d2.ldf) and try to attach again.
When I attach the newly renamed files, MSS remembers both the original file
names and the original database name "Database1" and will complain if I clic
k
OK to try and save the 2nd database to the host/instance. I don't see how t
o
1) change the original database name when attaching or 2) force the MSS
system to let me change the new database name on the fly as I attach it.
Sorry if the answer is looking me in the face

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi

Rename Files using sql/t-sql

Is it posssible to rename files using SQL/T-SQL? One method could be the xp_cmdshell command but I want to rename files on the netowrk and xp_cmdshell commands do not support UNC paths.
Any ideas?
ThanksOriginally posted by vmlal
Is it posssible to rename files using SQL/T-SQL? One method could be the xp_cmdshell command but I want to rename files on the netowrk and xp_cmdshell commands do not support UNC paths.

Any ideas?

Thanks

This works fine:

xp_cmdshell 'copy \\server1\backup\backup.sql \\server1\backup\backup2.sql'

xp_cmdshell 'copy \\server1\backup\backup.sql \\server2\backup\backup2.sql'|||you can create a bat file on your local machine in which you can enter all your commands. You can then execute this with the xp_cmdshell.

Rename Files

Is it possible to rename the mdf and log file names?Not while a DB on the server is using those files. (Not that I can
think of off the top of my head anyway.) You'd have to detach the
database (that uses those files), rename the files on the file system
and then reattach those renamed files.
*mike hodgson*
http://sqlnerd.blogspot.com
scott wrote:

>Is it possible to rename the mdf and log file names?
>
>|||Scott,
you could use the following scrip as a template:
USE master;
GO
ALTER DATABASE The_DB MODIFY FILE
(NAME = The_DB_Data,
FILENAME='C:\SQLSVRDB\The_DB_new.mdf');
GO
ALTER DATABASE The_DB SET OFFLINE;
EXEC master..xp_cmdshell 'rename C:\SQLSVRDB\The_DB_old.mdf The_DB_new.mdf';
ALTER DATABASE DB SET ONLINE;
GO
Andrey Odegov
avodeGOV@.yandex.ru
(remove GOV to respond)

Rename dos file name

Just a shoot in the dark here...
When SQL Server exports procs to individual files, it creatin them like
dbo.sproc.prc
Needless to say this filename is causing fits...
I've tried to do a rename to no avail
any suggestions?You mean that you don't like the way that Enterprise Mangler names the files produced by SQL-DMO ?!?! You can easily roll your own (http://support.microsoft.com/default.aspx?scid=kb;en-us;233392) if you like, or you can write a short script to rename the bundles of joy delivered by EM. While the rename script is less work, I prefer the roll your own approach myself.

-PatP|||Maybe a better question is who is the file naming causing fits, and why is it causing them those fits ?!?! Maybe I ought to finger out the problem before I start fixin' on it, eh?

-PatP

Removing protected beta files

I cannot install SQL SErver 2005 because of files still on my hard disk from beta version. These include sqlmgmprovider.dll and svrenumapi.dll. The uninstall program is no longer available. How do I get rid of whatever remains of the old beta installation so that the released version of SQL Server 2005 will install?

There are a few different threads on this, so I'm copy/pasting a couple of the best:

1. Uninstall all visible Beta components in Add/Remove Programes.

2. Detect the Beta components infromation in the the log file located in %Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup*_%machinename%_Core(Local).log. * stands for a number. Check the latest log file.

3. Search the whole registry keys, remove those registy keys related to those Beta components. This is time-consuming.

4. Kill all Beta SQL Services (including SQL, OLAP, RS, SQLBROWSER, SQLAGENT, etc.) if they are existing with command sc.

5. Reboot the machine.

6. Start Setup again.

If these don't work, check out this thread using some of the zap tools:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=89919&SiteID=1

Thanks,
Sam Lester (MSFT)

|||

Thanks, Sam,

The zap tool worked and I was able to get past the objection to the old beta files. However, the install was not completely successful. In fact the main part of it did not install successfully. Do you have any hints on how to diagnose this problem?

Allen

(moontube)

|||

What SKU are you trying to install and on what OS? If you're going through a command line install, can you supply the command line string? If you're going through UI, where is it failing?

Go ahead and search your install logs in %Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG for the text string "value 3" and then copy/paste the 10-20 or so lines above that message. This should give us a more descript error message.

Thanks,
Sam

|||

Hi Sam,

Here is the LOG information you asked for.

MSI (s) (B0:04) [22:58:52:948]: Executing op: UpgradeCodePublish(UpgradeCode={929C9FEC-8873-4A1A-A209-9AF432E8E1D1})
MSI (s) (B0:04) [22:58:52:948]: Executing op: SourceListPublish(,,DiskPromptTemplate=Please insert next disk,PackagePath=\Setup\,NumberOfDisks=1)
MSI (s) (B0:04) [22:58:52:948]: Note: 1: 1402 2: UNKNOWN\Installer\Products\1EB3A031CC585314E87AA527E46EECC2\SourceList 3: 2
MSI (s) (B0:04) [22:58:52:968]: Executing op: ProductPublishClient(,,)
MSI (s) (B0:04) [22:58:52:968]: Executing op: SourceListRegisterLastUsed(SourceProduct={130A3BE1-85CC-4135-8EA7-5A724EE6CE2C},LastUsedSource=F:\Setup\)
MSI (s) (B0:04) [22:58:52:968]: Entering CMsiConfigurationManager::SetLastUsedSource.
MSI (s) (B0:04) [22:58:52:968]: Machine policy value 'AllowLockdownMedia' is 0
MSI (s) (B0:04) [22:58:52:978]: Specifed source is already in a list.
MSI (s) (B0:04) [22:58:52:978]: User policy value 'SearchOrder' is 'nmu'
MSI (s) (B0:04) [22:58:52:978]: Machine policy value 'DisableBrowse' is 0
MSI (s) (B0:04) [22:58:52:978]: Machine policy value 'AllowLockdownBrowse' is 0
MSI (s) (B0:04) [22:58:52:978]: Adding new sources is allowed.
MSI (s) (B0:04) [22:58:52:978]: Set LastUsedSource to: F:\Setup\.
MSI (s) (B0:04) [22:58:52:978]: Set LastUsedType to: m.
MSI (s) (B0:04) [22:58:52:978]: Set LastUsedIndex to: 1.
MSI (s) (B0:04) [22:58:52:978]: Executing op: ActionStart(Name=CreateShortcuts,Description=Creating shortcuts,Template=Shortcut: [1])
MSI (s) (B0:04) [22:58:52:978]: Executing op: SetTargetFolder(Folder=23\Microsoft SQL Server 2005\Configuration Tools\)
MSI (s) (B0:04) [22:58:52:978]: SHELL32::SHGetFolderPath returned: E:\Documents and Settings\All Users\Start Menu\Programs
MSI (s) (B0:04) [22:58:52:998]: Executing op: ShortcutCreate(Name=SqlSAC|SQL Server Surface Area Configuration,,,FileName=E:\Program Files\Microsoft SQL Server\90\Shared\SqlSAC.exe,,,,,,ShowCmd=1,)
MSI (s) (B0:04) [22:58:53:038]: Verifying accessibility of file: SQL Server Surface Area Configuration.lnk
MSI (s) (B0:04) [22:58:53:178]: Executing op: ShortcutCreate(Name=SqlWtsn|SQL Server Error and Usage Reporting,,,FileName=E:\Program Files\Microsoft SQL Server\90\Shared\SqlWtsn.exe,,,,,,ShowCmd=1,)
MSI (s) (B0:04) [22:58:53:188]: Verifying accessibility of file: SQL Server Error and Usage Reporting.lnk
MSI (s) (B0:04) [22:58:53:218]: Executing op: ShortcutCreate(Name=SqlConfM|SQL Server Configuration Manager,,,FileName=E:\WINDOWS\system32\mmc.exe,Arguments=/32 "E:\WINDOWS\system32\SQLServerManager.msc",,,,,ShowCmd=1,)
MSI (s) (B0:04) [22:58:53:228]: Verifying accessibility of file: SQL Server Configuration Manager.lnk
MSI (s) (B0:04) [22:58:53:248]: Executing op: ActionStart(Name=Set_CommitFlag.D20239D7_E87C_40C9_9837_E70B8D4882C2,Description=Completing Commit,)
MSI (s) (B0:04) [22:58:53:609]: Executing op: CustomActionSchedule(Action=Set_CommitFlag.D20239D7_E87C_40C9_9837_E70B8D4882C2,ActionType=1537,Source=BinaryData,Target=Set_CommitFlag,CustomActionData=010Completing Commit500001)
MSI (s) (B0:04) [22:58:53:669]: Executing op: End(Checksum=0,ProgressTotalHDWord=0,ProgressTotalLDWord=360632758)
MSI (s) (B0:04) [22:58:54:009]: Assembly Error:The process cannot access the file because it is being used by another process.

MSI (s) (B0:04) [22:58:54:019]: Note: 1: 1935 2: {4C466AC6-57EA-4944-A4CB-795A9E01FA61} 3: 0x80070020 4: IAssemblyCacheItem 5: Commit 6: Microsoft.SqlServer.Replication,Version="9.0.242.0",Culture="neutral",processorArchitecture="x86",PublicKeyToken="89845dcd8080cc91",FileVersion="2005.90.1399.0"
MSI (s) (B0:04) [23:06:15:815]: Product: Microsoft SQL Server 2005 -- Error 1935. An error occurred during the installation of assembly 'Microsoft.SqlServer.Replication,Version="9.0.242.0",Culture="neutral",processorArchitecture="x86",PublicKeyToken="89845dcd8080cc91",FileVersion="2005.90.1399.0"'. Please refer to Help and Support for more information. HRESULT: 0x80070020. assembly interface: IAssemblyCacheItem, function: Commit, component: {4C466AC6-57EA-4944-A4CB-795A9E01FA61}

Error 1935. An error occurred during the installation of assembly 'Microsoft.SqlServer.Replication,Version="9.0.242.0",Culture="neutral",processorArchitecture="x86",PublicKeyToken="89845dcd8080cc91",FileVersion="2005.90.1399.0"'. Please refer to Help and Support for more information. HRESULT: 0x80070020. assembly interface: IAssemblyCacheItem, function: Commit, component: {4C466AC6-57EA-4944-A4CB-795A9E01FA61}
MSI (s) (B0:04) [23:06:15:865]: User policy value 'DisableRollback' is 0
MSI (s) (B0:04) [23:06:15:865]: Machine policy value 'DisableRollback' is 0
Action ended 23:06:15: InstallFinalize. Return value 3.

|||

So the error is:

Error 1935. An error occurred during the installation of assembly 'Microsoft.SqlServer.Replication,

Typically this is a failure when trying to install a file to the GAC. Did you remove the beta version of SQL Native Client? That sometimes causes the problem. If you didn't remove it, can you uninstall it and try the install again?

Thanks,
Sam

|||

Hi Sam,

Thanks. your suggestion did the trick. I unistalled the corrupted version of SQL Server that I had just installed, rebooted, and installed it again. This time it worked.

Allen

|||Great to hear and thanks for replying back! For future reference, was it the SQL Native Client uninstall that fixed it for you?

Thanks,
Sam|||

Hi Sam,

I uninstalled everything related it SQL Server, including the Native Client. The only thing I didn't unistall is the books, because I could not uninstall them once SQL Server had been uninstalled.

Allen

Removing protected beta files

I cannot install SQL SErver 2005 because of files still on my hard disk from beta version. These include sqlmgmprovider.dll and svrenumapi.dll. The uninstall program is no longer available. How do I get rid of whatever remains of the old beta installation so that the released version of SQL Server 2005 will install?

There are a few different threads on this, so I'm copy/pasting a couple of the best:

1. Uninstall all visible Beta components in Add/Remove Programes.

2. Detect the Beta components infromation in the the log file located in %Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files\SQLSetup*_%machinename%_Core(Local).log. * stands for a number. Check the latest log file.

3. Search the whole registry keys, remove those registy keys related to those Beta components. This is time-consuming.

4. Kill all Beta SQL Services (including SQL, OLAP, RS, SQLBROWSER, SQLAGENT, etc.) if they are existing with command sc.

5. Reboot the machine.

6. Start Setup again.

If these don't work, check out this thread using some of the zap tools:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=89919&SiteID=1

Thanks,
Sam Lester (MSFT)

|||

Thanks, Sam,

The zap tool worked and I was able to get past the objection to the old beta files. However, the install was not completely successful. In fact the main part of it did not install successfully. Do you have any hints on how to diagnose this problem?

Allen

(moontube)

|||

What SKU are you trying to install and on what OS? If you're going through a command line install, can you supply the command line string? If you're going through UI, where is it failing?

Go ahead and search your install logs in %Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG for the text string "value 3" and then copy/paste the 10-20 or so lines above that message. This should give us a more descript error message.

Thanks,
Sam

|||

Hi Sam,

Here is the LOG information you asked for.

MSI (s) (B0:04) [22:58:52:948]: Executing op: UpgradeCodePublish(UpgradeCode={929C9FEC-8873-4A1A-A209-9AF432E8E1D1})
MSI (s) (B0:04) [22:58:52:948]: Executing op: SourceListPublish(,,DiskPromptTemplate=Please insert next disk,PackagePath=\Setup\,NumberOfDisks=1)
MSI (s) (B0:04) [22:58:52:948]: Note: 1: 1402 2: UNKNOWN\Installer\Products\1EB3A031CC585314E87AA527E46EECC2\SourceList 3: 2
MSI (s) (B0:04) [22:58:52:968]: Executing op: ProductPublishClient(,,)
MSI (s) (B0:04) [22:58:52:968]: Executing op: SourceListRegisterLastUsed(SourceProduct={130A3BE1-85CC-4135-8EA7-5A724EE6CE2C},LastUsedSource=F:\Setup\)
MSI (s) (B0:04) [22:58:52:968]: Entering CMsiConfigurationManager::SetLastUsedSource.
MSI (s) (B0:04) [22:58:52:968]: Machine policy value 'AllowLockdownMedia' is 0
MSI (s) (B0:04) [22:58:52:978]: Specifed source is already in a list.
MSI (s) (B0:04) [22:58:52:978]: User policy value 'SearchOrder' is 'nmu'
MSI (s) (B0:04) [22:58:52:978]: Machine policy value 'DisableBrowse' is 0
MSI (s) (B0:04) [22:58:52:978]: Machine policy value 'AllowLockdownBrowse' is 0
MSI (s) (B0:04) [22:58:52:978]: Adding new sources is allowed.
MSI (s) (B0:04) [22:58:52:978]: Set LastUsedSource to: F:\Setup\.
MSI (s) (B0:04) [22:58:52:978]: Set LastUsedType to: m.
MSI (s) (B0:04) [22:58:52:978]: Set LastUsedIndex to: 1.
MSI (s) (B0:04) [22:58:52:978]: Executing op: ActionStart(Name=CreateShortcuts,Description=Creating shortcuts,Template=Shortcut: [1])
MSI (s) (B0:04) [22:58:52:978]: Executing op: SetTargetFolder(Folder=23\Microsoft SQL Server 2005\Configuration Tools\)
MSI (s) (B0:04) [22:58:52:978]: SHELL32::SHGetFolderPath returned: E:\Documents and Settings\All Users\Start Menu\Programs
MSI (s) (B0:04) [22:58:52:998]: Executing op: ShortcutCreate(Name=SqlSAC|SQL Server Surface Area Configuration,,,FileName=E:\Program Files\Microsoft SQL Server\90\Shared\SqlSAC.exe,,,,,,ShowCmd=1,)
MSI (s) (B0:04) [22:58:53:038]: Verifying accessibility of file: SQL Server Surface Area Configuration.lnk
MSI (s) (B0:04) [22:58:53:178]: Executing op: ShortcutCreate(Name=SqlWtsn|SQL Server Error and Usage Reporting,,,FileName=E:\Program Files\Microsoft SQL Server\90\Shared\SqlWtsn.exe,,,,,,ShowCmd=1,)
MSI (s) (B0:04) [22:58:53:188]: Verifying accessibility of file: SQL Server Error and Usage Reporting.lnk
MSI (s) (B0:04) [22:58:53:218]: Executing op: ShortcutCreate(Name=SqlConfM|SQL Server Configuration Manager,,,FileName=E:\WINDOWS\system32\mmc.exe,Arguments=/32 "E:\WINDOWS\system32\SQLServerManager.msc",,,,,ShowCmd=1,)
MSI (s) (B0:04) [22:58:53:228]: Verifying accessibility of file: SQL Server Configuration Manager.lnk
MSI (s) (B0:04) [22:58:53:248]: Executing op: ActionStart(Name=Set_CommitFlag.D20239D7_E87C_40C9_9837_E70B8D4882C2,Description=Completing Commit,)
MSI (s) (B0:04) [22:58:53:609]: Executing op: CustomActionSchedule(Action=Set_CommitFlag.D20239D7_E87C_40C9_9837_E70B8D4882C2,ActionType=1537,Source=BinaryData,Target=Set_CommitFlag,CustomActionData=010Completing Commit500001)
MSI (s) (B0:04) [22:58:53:669]: Executing op: End(Checksum=0,ProgressTotalHDWord=0,ProgressTotalLDWord=360632758)
MSI (s) (B0:04) [22:58:54:009]: Assembly Error:The process cannot access the file because it is being used by another process.

MSI (s) (B0:04) [22:58:54:019]: Note: 1: 1935 2: {4C466AC6-57EA-4944-A4CB-795A9E01FA61} 3: 0x80070020 4: IAssemblyCacheItem 5: Commit 6: Microsoft.SqlServer.Replication,Version="9.0.242.0",Culture="neutral",processorArchitecture="x86",PublicKeyToken="89845dcd8080cc91",FileVersion="2005.90.1399.0"
MSI (s) (B0:04) [23:06:15:815]: Product: Microsoft SQL Server 2005 -- Error 1935. An error occurred during the installation of assembly 'Microsoft.SqlServer.Replication,Version="9.0.242.0",Culture="neutral",processorArchitecture="x86",PublicKeyToken="89845dcd8080cc91",FileVersion="2005.90.1399.0"'. Please refer to Help and Support for more information. HRESULT: 0x80070020. assembly interface: IAssemblyCacheItem, function: Commit, component: {4C466AC6-57EA-4944-A4CB-795A9E01FA61}

Error 1935. An error occurred during the installation of assembly 'Microsoft.SqlServer.Replication,Version="9.0.242.0",Culture="neutral",processorArchitecture="x86",PublicKeyToken="89845dcd8080cc91",FileVersion="2005.90.1399.0"'. Please refer to Help and Support for more information. HRESULT: 0x80070020. assembly interface: IAssemblyCacheItem, function: Commit, component: {4C466AC6-57EA-4944-A4CB-795A9E01FA61}
MSI (s) (B0:04) [23:06:15:865]: User policy value 'DisableRollback' is 0
MSI (s) (B0:04) [23:06:15:865]: Machine policy value 'DisableRollback' is 0
Action ended 23:06:15: InstallFinalize. Return value 3.

|||

So the error is:

Error 1935. An error occurred during the installation of assembly 'Microsoft.SqlServer.Replication,

Typically this is a failure when trying to install a file to the GAC. Did you remove the beta version of SQL Native Client? That sometimes causes the problem. If you didn't remove it, can you uninstall it and try the install again?

Thanks,
Sam

|||

Hi Sam,

Thanks. your suggestion did the trick. I unistalled the corrupted version of SQL Server that I had just installed, rebooted, and installed it again. This time it worked.

Allen

|||Great to hear and thanks for replying back! For future reference, was it the SQL Native Client uninstall that fixed it for you?

Thanks,
Sam|||

Hi Sam,

I uninstalled everything related it SQL Server, including the Native Client. The only thing I didn't unistall is the books, because I could not uninstall them once SQL Server had been uninstalled.

Allen

Removing old transaction log backup files

My organization is running SQL Server 2000 on a Windows 2000 server. We have
a maintenance plan set up to perform a daily backup of several database
files. The full database backup runs each day at 2:00 a.m. The transaction
log backup runs every four hours. It is setup in the maintenance plan that
the checkbox for both the data file and transaction log that says "Remove
files older than" is checked and set for 2 days. However, the old transaction
log backup files (*.TRN) are not being automatically removed. The regular
database files (*.BAK) are removed just fine. Any suggestions?
Hello
You should have some more information in the Maintenance Plan output file.
You can configure the Maint plan to produce output from the Maint Plan
properties window.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Below KB might help:
http://support.microsoft.com/default...&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AscentSQLAdmin" <AscentSQLAdmin@.discussions.microsoft.com> wrote in message
news:9A971D9F-D439-4F0F-B1E9-BAB6F42D73B3@.microsoft.com...
> My organization is running SQL Server 2000 on a Windows 2000 server. We have
> a maintenance plan set up to perform a daily backup of several database
> files. The full database backup runs each day at 2:00 a.m. The transaction
> log backup runs every four hours. It is setup in the maintenance plan that
> the checkbox for both the data file and transaction log that says "Remove
> files older than" is checked and set for 2 days. However, the old transaction
> log backup files (*.TRN) are not being automatically removed. The regular
> database files (*.BAK) are removed just fine. Any suggestions?

Removing old transaction log backup files

My organization is running SQL Server 2000 on a Windows 2000 server. We have
a maintenance plan set up to perform a daily backup of several database
files. The full database backup runs each day at 2:00 a.m. The transaction
log backup runs every four hours. It is setup in the maintenance plan that
the checkbox for both the data file and transaction log that says "Remove
files older than" is checked and set for 2 days. However, the old transactio
n
log backup files (*.TRN) are not being automatically removed. The regular
database files (*.BAK) are removed just fine. Any suggestions?Hello
You should have some more information in the Maintenance Plan output file.
You can configure the Maint plan to produce output from the Maint Plan
properties window.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Below KB might help:
http://support.microsoft.com/defaul...2&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AscentSQLAdmin" <AscentSQLAdmin@.discussions.microsoft.com> wrote in message
news:9A971D9F-D439-4F0F-B1E9-BAB6F42D73B3@.microsoft.com...
> My organization is running SQL Server 2000 on a Windows 2000 server. We ha
ve
> a maintenance plan set up to perform a daily backup of several database
> files. The full database backup runs each day at 2:00 a.m. The transaction
> log backup runs every four hours. It is setup in the maintenance plan that
> the checkbox for both the data file and transaction log that says "Remove
> files older than" is checked and set for 2 days. However, the old transact
ion
> log backup files (*.TRN) are not being automatically removed. The regular
> database files (*.BAK) are removed just fine. Any suggestions?

Removing old .bak & .trn files

I've run into a problem I didn't expect with my backup program. It seems
that Windows admins are very lax about writing scripts to remove old backup
files after copying them to tape, even though they say its easy, they don't
do it for all database backups on all servers in the farm.
So, I'm wondering first, if I should do it from my backup program (based on
RealSQLGuy's) and second, how I should go about it.
My rough thought is to do an xm_cmdshell('dir') in the backup directory into
a table variable, parse the date from the filename into a datetime column,
select the old files and issue an xp_cmdshell('del <file>') command via
dynamic sql.
Thanks,
JayHello Jay!
In SQL Server 2005, you could use Maintenance Plan Wizard to create a
Maintenance Cleanup Task to delete old bak and trn files.
Ekrem Önsoy
"Jay" <spam@.nospam.org> wrote in message
news:eQNEUct9HHA.5404@.TK2MSFTNGP02.phx.gbl...
> I've run into a problem I didn't expect with my backup program. It seems
> that Windows admins are very lax about writing scripts to remove old
> backup files after copying them to tape, even though they say its easy,
> they don't do it for all database backups on all servers in the farm.
> So, I'm wondering first, if I should do it from my backup program (based
> on RealSQLGuy's) and second, how I should go about it.
> My rough thought is to do an xm_cmdshell('dir') in the backup directory
> into a table variable, parse the date from the filename into a datetime
> column, select the old files and issue an xp_cmdshell('del <file>')
> command via dynamic sql.
> Thanks,
> Jay
>
>|||Maintenance plan in 2005?
Besides, I do all my maintenance through SQLagent after writing the scripts
myself.
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:OOO8bft9HHA.5424@.TK2MSFTNGP02.phx.gbl...
> Hello Jay!
>
> In SQL Server 2005, you could use Maintenance Plan Wizard to create a
> Maintenance Cleanup Task to delete old bak and trn files.
>
> --
> Ekrem Önsoy
>
> "Jay" <spam@.nospam.org> wrote in message
> news:eQNEUct9HHA.5404@.TK2MSFTNGP02.phx.gbl...
>> I've run into a problem I didn't expect with my backup program. It seems
>> that Windows admins are very lax about writing scripts to remove old
>> backup files after copying them to tape, even though they say its easy,
>> they don't do it for all database backups on all servers in the farm.
>> So, I'm wondering first, if I should do it from my backup program (based
>> on RealSQLGuy's) and second, how I should go about it.
>> My rough thought is to do an xm_cmdshell('dir') in the backup directory
>> into a table variable, parse the date from the filename into a datetime
>> column, select the old files and issue an xp_cmdshell('del <file>')
>> command via dynamic sql.
>> Thanks,
>> Jay
>>
>|||This is just me but I would create a batch file to delete the files and give
it to the Windows group to execute it after they do the tape backup. Make
sure that the batch file execution step depends on the tape backup so that
you are not without your backups if the tape backup fails.
"Jay" wrote:
> Maintenance plan in 2005?
> Besides, I do all my maintenance through SQLagent after writing the scripts
> myself.
> "Ekrem Ã?nsoy" <ekrem@.btegitim.com> wrote in message
> news:OOO8bft9HHA.5424@.TK2MSFTNGP02.phx.gbl...
> > Hello Jay!
> >
> >
> > In SQL Server 2005, you could use Maintenance Plan Wizard to create a
> > Maintenance Cleanup Task to delete old bak and trn files.
> >
> >
> > --
> > Ekrem Ã?nsoy
> >
> >
> > "Jay" <spam@.nospam.org> wrote in message
> > news:eQNEUct9HHA.5404@.TK2MSFTNGP02.phx.gbl...
> >> I've run into a problem I didn't expect with my backup program. It seems
> >> that Windows admins are very lax about writing scripts to remove old
> >> backup files after copying them to tape, even though they say its easy,
> >> they don't do it for all database backups on all servers in the farm.
> >>
> >> So, I'm wondering first, if I should do it from my backup program (based
> >> on RealSQLGuy's) and second, how I should go about it.
> >>
> >> My rough thought is to do an xm_cmdshell('dir') in the backup directory
> >> into a table variable, parse the date from the filename into a datetime
> >> column, select the old files and issue an xp_cmdshell('del <file>')
> >> command via dynamic sql.
> >>
> >> Thanks,
> >> Jay
> >>
> >>
> >>
> >
>
>

removing logs

I have over 200 log files with a date and timestamp in the filename in the
Reporting Services\LogFiles folder that are 33MB each. This is a development
machine. How do I get rid of them? Can I just delete them?
Thanks,
--
Dan D.You can delete, it creates again, but I dont think you require this much log
files for your development server. For production yes you need to have a
backup. Before deleting keep a backup or Zip all the log files and keep it in
a seperate place.
If it is so important, you can run a program which is available in the
samples to read the log files and reporting.
and ofcourse schedule it to remove. default it keeps it for 60 days.
Amarnath
"Dan D." wrote:
> I have over 200 log files with a date and timestamp in the filename in the
> Reporting Services\LogFiles folder that are 33MB each. This is a development
> machine. How do I get rid of them? Can I just delete them?
> Thanks,
> --
> Dan D.|||I never thought about log files for reportserver. Most of the files are
small, under 50K. I don't know what was in the files that were so large.
I removed them.
Thanks,
--
Dan D.
"Amarnath" wrote:
> You can delete, it creates again, but I dont think you require this much log
> files for your development server. For production yes you need to have a
> backup. Before deleting keep a backup or Zip all the log files and keep it in
> a seperate place.
> If it is so important, you can run a program which is available in the
> samples to read the log files and reporting.
> and ofcourse schedule it to remove. default it keeps it for 60 days.
> Amarnath
> "Dan D." wrote:
> > I have over 200 log files with a date and timestamp in the filename in the
> > Reporting Services\LogFiles folder that are 33MB each. This is a development
> > machine. How do I get rid of them? Can I just delete them?
> >
> > Thanks,
> > --
> > Dan D.

Removing log files from SQL Database

Need help .

There are 3 log files attached to a SQL Database . I would like to remove one of them as it was created by Previous DBA for temp use (Don'r ask me why?) If I run DBCC ShrinkFile with EMPTYFILE , would it let me drop that file or is there any command to do it? OR is it not possible at allYes, you can even do it using SQL Enterprise Mangler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_design_28mx.asp) if you like!

-PatP|||This is what I needed