query database table

Friday, March 30, 2012

How to rename physical file name

I have a database, which consists of two physical files db1.mdf,
db1_log.ldf, both locating on the same folder. I want to copy both the files
to db2.mdf, db2_log.ldf (in the same folder) and attach as DB2. I know the
commands to attach a database with different name. I want to know, how to
change db1.mdf to db2.mdf
TIA
J JustinJustin
I prefer RESTORE DATABASE from QA rather than EM. In the QA just before a
running change the names of the files
RESTORE DATABASE MyDatabase FROM DISK='C:\MyDatabase20050206.bak'
WITH RECOVERY,
MOVE MyDatabase_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\ChangeItHere.mdf',
MOVE MyDatabase _Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\ChangeItHere_log.ldf'
"J Justin" <justin@.gjsoft.com> wrote in message
news:eRxy9uXdFHA.3184@.TK2MSFTNGP15.phx.gbl...
> I have a database, which consists of two physical files db1.mdf,
> db1_log.ldf, both locating on the same folder. I want to copy both the
files
> to db2.mdf, db2_log.ldf (in the same folder) and attach as DB2. I know the
> commands to attach a database with different name. I want to know, how to
> change db1.mdf to db2.mdf
> TIA
> J Justin
>|||Thanks for your quick reply. I don't have BAK file, rather I have the raw
mdf and ldf files. Is there any command now?
Regards
J Justin
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%233lXN0XdFHA.2688@.TK2MSFTNGP14.phx.gbl...
> Justin
> I prefer RESTORE DATABASE from QA rather than EM. In the QA just before a
> running change the names of the files
> RESTORE DATABASE MyDatabase FROM DISK='C:\MyDatabase20050206.bak'
> WITH RECOVERY,
> MOVE MyDatabase_Data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ChangeItHere.mdf',
> MOVE MyDatabase _Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ChangeItHere_log.ldf'
>
>
> "J Justin" <justin@.gjsoft.com> wrote in message
> news:eRxy9uXdFHA.3184@.TK2MSFTNGP15.phx.gbl...
>> I have a database, which consists of two physical files db1.mdf,
>> db1_log.ldf, both locating on the same folder. I want to copy both the
> files
>> to db2.mdf, db2_log.ldf (in the same folder) and attach as DB2. I know
>> the
>> commands to attach a database with different name. I want to know, how to
>> change db1.mdf to db2.mdf
>> TIA
>> J Justin
>>
>|||No, after dettaching database rename the files manual .
"J Justin" <justin@.gjsoft.com> wrote in message
news:%23$K7r9XdFHA.1684@.TK2MSFTNGP09.phx.gbl...
> Thanks for your quick reply. I don't have BAK file, rather I have the raw
> mdf and ldf files. Is there any command now?
> Regards
> J Justin
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%233lXN0XdFHA.2688@.TK2MSFTNGP14.phx.gbl...
> > Justin
> > I prefer RESTORE DATABASE from QA rather than EM. In the QA just before
a
> > running change the names of the files
> >
> > RESTORE DATABASE MyDatabase FROM DISK='C:\MyDatabase20050206.bak'
> > WITH RECOVERY,
> > MOVE MyDatabase_Data' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\ChangeItHere.mdf',
> > MOVE MyDatabase _Log' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\ChangeItHere_log.ldf'
> >
> >
> >
> >
> >
> > "J Justin" <justin@.gjsoft.com> wrote in message
> > news:eRxy9uXdFHA.3184@.TK2MSFTNGP15.phx.gbl...
> >> I have a database, which consists of two physical files db1.mdf,
> >> db1_log.ldf, both locating on the same folder. I want to copy both the
> > files
> >> to db2.mdf, db2_log.ldf (in the same folder) and attach as DB2. I know
> >> the
> >> commands to attach a database with different name. I want to know, how
to
> >> change db1.mdf to db2.mdf
> >>
> >> TIA
> >>
> >> J Justin
> >>
> >>
> >
> >
>

How to rename instance

Hi,
I installed sql05 on a new box and I don't like the name I chose for the
instance.
How do I change it?
TIA,
Mike
Backup your databases, uninstall SQL Server, install it properly, then
restore your databases. There is no sp_rename_instance.
"Mike" <mikenospam@.yahoo.es> wrote in message
news:691977F7-8CB6-4107-B71E-6584B15311D4@.microsoft.com...
> Hi,
> I installed sql05 on a new box and I don't like the name I chose for the
> instance.
> How do I change it?
> TIA,
> Mike
>

How to rename instance

Hi,
I installed sql05 on a new box and I don't like the name I chose for the
instance.
How do I change it?
TIA,
MikeBackup your databases, uninstall SQL Server, install it properly, then
restore your databases. There is no sp_rename_instance.
"Mike" <mikenospam@.yahoo.es> wrote in message
news:691977F7-8CB6-4107-B71E-6584B15311D4@.microsoft.com...
> Hi,
> I installed sql05 on a new box and I don't like the name I chose for the
> instance.
> How do I change it?
> TIA,
> Mike
>

how to rename files before send files?

I'm using script task to rename files inside foreach loop files,code like shown below

Dim file As New System.IO.FileInfo(CStr(Dts.Variables("User::FileName").Value))

dim newname as string = Split(file.Name, "_")(0) & ".jpg"
Dts.Variables("User::outputname").Value = file.DirectoryName & "\" & newname

Then,i use ftp task to send files,but prompt "the variables User::outputname doesn't contains file path(s)"

I tried to use file system task to perform that,but failed either

Rename file operation in file system only can rename a file in a specified location,who can help me?

TIA

Have you looked at what outputname does contain? Does the file specified exist as it should for the FTP task?

The File System Task can use variables, which themselves can be used to supply expressions, see the EvaluateAsExpression property and set the Expression property. This way you can use dynamic paths in the File System Task.

sql

How to Rename Database Files

Is there a way to totally rename a databases? I would like to change the
name of the database - like with sp_renamedb and the assosciated files so
that they still match. Is there an easy way to do this? I would really like
a TSQL script to do this if possible.
Thanks in advance.Hi Tim
What version are you using? Please always mention that right up front.
In SQL Server 2000 you can change the logical file names using ALTER
DATABASE, but in SQL Server 7 you cannot.
To change the physical files names, there is no way to do this in TSQL
(except for tempdb), so it would be tricky to do this in a script.
The steps to change physical file names are:
Detach the database
rename the files at the operating system
Attach the database, specifying the new file names
Check the docs for sp_attachdb for full details.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tim Bird" <tsbdoulos@.nospambtopenworld.com> wrote in message
news:uDWhPZUKEHA.556@.TK2MSFTNGP10.phx.gbl...
> Is there a way to totally rename a databases? I would like to change the
> name of the database - like with sp_renamedb and the assosciated files so
> that they still match. Is there an easy way to do this? I would really
like
> a TSQL script to do this if possible.
> Thanks in advance.
>|||Hi John
Note that this will not change the logical file names. That must be done
separately and in SQL Server 7 it is not possible to change the logical file
names at all.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
news:851FB97A-324C-40DD-95D9-3704D66EB621@.microsoft.com...
> Tim
> Detach the database. Rename the windows files and attach as whatever name
you like.
> Regards
> John|||Hi,
In SQL 2000 the logical file name can be changed by using the below command.
The below step is not possible in SQL 7
ALTER DATABASE
MODIFY FILE
(NAME = logical_file_name,
NEWNAME = new_logical_name)
How to change the physical name in both SQL 2000 / SQL 7
1. detach the database using sp_detach_db <dbname>
2. Rename the physical files to new name
3. attach the database using sp_attach_db 'dbname','new physical MDF name
with path','new physical LDF name with path'
Thanks
Hari
MCDBA
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e$uJwLVKEHA.2756@.TK2MSFTNGP10.phx.gbl...
> Hi John
> Note that this will not change the logical file names. That must be done
> separately and in SQL Server 7 it is not possible to change the logical
file
> names at all.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
> news:851FB97A-324C-40DD-95D9-3704D66EB621@.microsoft.com...
> > Tim
> >
> > Detach the database. Rename the windows files and attach as whatever
name
> you like.
> >
> > Regards
> >
> > John
>|||Yes, I just said that John's approach would not change the logical file
names. Earlier in the thread I told the original poster he could use ALTER
DATABASE to change the logical file names.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:urPajkVKEHA.3704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> In SQL 2000 the logical file name can be changed by using the below
command.
> The below step is not possible in SQL 7
> ALTER DATABASE
> MODIFY FILE
> (NAME = logical_file_name,
> NEWNAME = new_logical_name)
> How to change the physical name in both SQL 2000 / SQL 7
> 1. detach the database using sp_detach_db <dbname>
> 2. Rename the physical files to new name
> 3. attach the database using sp_attach_db 'dbname','new physical MDF name
> with path','new physical LDF name with path'
> Thanks
> Hari
> MCDBA
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e$uJwLVKEHA.2756@.TK2MSFTNGP10.phx.gbl...
> > Hi John
> >
> > Note that this will not change the logical file names. That must be done
> > separately and in SQL Server 7 it is not possible to change the logical
> file
> > names at all.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
> > news:851FB97A-324C-40DD-95D9-3704D66EB621@.microsoft.com...
> > > Tim
> > >
> > > Detach the database. Rename the windows files and attach as whatever
> name
> > you like.
> > >
> > > Regards
> > >
> > > John
> >
> >
>|||Kale
Maybe I am missing something here, I did what I described and it worked just fine. (This is in 2000, I did not have a 7 environment available.) I created a database called 'Fred'. I detached 'Fred'. I renamed the MDF and LDF files to 'Pete'. I attached database as 'Pete'. Database 'Pete' seems to be just fine. What did I miss
Regard
John|||It depends. I understood the OP to say he wanted to change the file names to
match the new database name, and I assume he wanted the logical filenames to
match also. What good is having a database named Pete, with physical files
named Pete, but logical files named Fred?
I'm not surprised the db is working fine for you, but I think your logical
file names are still Fred. I understood the OP wanted all names changed. He
didn't just want it to 'work', he wanted it to 'look right' also.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
news:0C49A43F-4B84-472B-88C0-29F561A56244@.microsoft.com...
> Kalen
> Maybe I am missing something here, I did what I described and it worked
just fine. (This is in 2000, I did not have a 7 environment available.) I
created a database called 'Fred'. I detached 'Fred'. I renamed the MDF and
LDF files to 'Pete'. I attached database as 'Pete'. Database 'Pete' seems to
be just fine. What did I miss?
> Regards
> John|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e%23LFfB$KEHA.620@.TK2MSFTNGP10.phx.gbl...
> It depends. I understood the OP to say he wanted to change the file names
to
> match the new database name, and I assume he wanted the logical filenames
to
> match also. What good is having a database named Pete, with physical files
> named Pete, but logical files named Fred?
> I'm not surprised the db is working fine for you, but I think your logical
> file names are still Fred. I understood the OP wanted all names changed.
He
> didn't just want it to 'work', he wanted it to 'look right' also.
You are correct. That was my intention. Though it really had to be a program
based solution that an end user of our system could perform. In the end I
have gone for a slightly different solution. Unless anybody can think of a
way to get the SQL Server itself to rename files. As this is will be run
from a client machine and not the server.
Thanks for all your replies anyone who has helped.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
> news:0C49A43F-4B84-472B-88C0-29F561A56244@.microsoft.com...
> > Kalen
> >
> > Maybe I am missing something here, I did what I described and it worked
> just fine. (This is in 2000, I did not have a 7 environment available.)
I
> created a database called 'Fred'. I detached 'Fred'. I renamed the MDF and
> LDF files to 'Pete'. I attached database as 'Pete'. Database 'Pete' seems
to
> be just fine. What did I miss?
> >
> > Regards
> >
> > John
>|||Tim
Once the database is detached, SQL Server can rename the physical files
using xp_cmdshell. For an end-user to use this, it would have to be in a
stored procedure that you have granted the user permission to execute.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tim Bird" <tsbdoulos@.nospambtopenworld.com> wrote in message
news:OBDshsCLEHA.1348@.TK2MSFTNGP12.phx.gbl...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e%23LFfB$KEHA.620@.TK2MSFTNGP10.phx.gbl...
> > It depends. I understood the OP to say he wanted to change the file
names
> to
> > match the new database name, and I assume he wanted the logical
filenames
> to
> > match also. What good is having a database named Pete, with physical
files
> > named Pete, but logical files named Fred?
> >
> > I'm not surprised the db is working fine for you, but I think your
logical
> > file names are still Fred. I understood the OP wanted all names changed.
> He
> > didn't just want it to 'work', he wanted it to 'look right' also.
> You are correct. That was my intention. Though it really had to be a
program
> based solution that an end user of our system could perform. In the end I
> have gone for a slightly different solution. Unless anybody can think of a
> way to get the SQL Server itself to rename files. As this is will be run
> from a client machine and not the server.
> Thanks for all your replies anyone who has helped.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
> > news:0C49A43F-4B84-472B-88C0-29F561A56244@.microsoft.com...
> > > Kalen
> > >
> > > Maybe I am missing something here, I did what I described and it
worked
> > just fine. (This is in 2000, I did not have a 7 environment available.)
> I
> > created a database called 'Fred'. I detached 'Fred'. I renamed the MDF
and
> > LDF files to 'Pete'. I attached database as 'Pete'. Database 'Pete'
seems
> to
> > be just fine. What did I miss?
> > >
> > > Regards
> > >
> > > John
> >
> >
>|||Thanks. That is pretty much what I came up with. Anoyingly I have got all
this figured out just as the people who decide have decided that this
feature is no longer needed in the software. Isn't that always the way
?!!!!!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e2FSTXELEHA.2556@.TK2MSFTNGP11.phx.gbl...
> Tim
> Once the database is detached, SQL Server can rename the physical files
> using xp_cmdshell. For an end-user to use this, it would have to be in a
> stored procedure that you have granted the user permission to execute.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Tim Bird" <tsbdoulos@.nospambtopenworld.com> wrote in message
> news:OBDshsCLEHA.1348@.TK2MSFTNGP12.phx.gbl...
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:e%23LFfB$KEHA.620@.TK2MSFTNGP10.phx.gbl...
> > > It depends. I understood the OP to say he wanted to change the file
> names
> > to
> > > match the new database name, and I assume he wanted the logical
> filenames
> > to
> > > match also. What good is having a database named Pete, with physical
> files
> > > named Pete, but logical files named Fred?
> > >
> > > I'm not surprised the db is working fine for you, but I think your
> logical
> > > file names are still Fred. I understood the OP wanted all names
changed.
> > He
> > > didn't just want it to 'work', he wanted it to 'look right' also.
> >
> > You are correct. That was my intention. Though it really had to be a
> program
> > based solution that an end user of our system could perform. In the end
I
> > have gone for a slightly different solution. Unless anybody can think of
a
> > way to get the SQL Server itself to rename files. As this is will be run
> > from a client machine and not the server.
> >
> > Thanks for all your replies anyone who has helped.
> >
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "John Bandettini" <anonymous@.discussions.microsoft.com> wrote in
message
> > > news:0C49A43F-4B84-472B-88C0-29F561A56244@.microsoft.com...
> > > > Kalen
> > > >
> > > > Maybe I am missing something here, I did what I described and it
> worked
> > > just fine. (This is in 2000, I did not have a 7 environment
available.)
> > I
> > > created a database called 'Fred'. I detached 'Fred'. I renamed the MDF
> and
> > > LDF files to 'Pete'. I attached database as 'Pete'. Database 'Pete'
> seems
> > to
> > > be just fine. What did I miss?
> > > >
> > > > Regards
> > > >
> > > > John
> > >
> > >
> >
> >
>

Friday, March 30, 2012

How to rename physical file name

How to rename instance

How to rename instance

how to rename files before send files?

How to Rename Database Files

How to Rename Database Files

How to Rename Database Files

query database table

Blog Archive

About Me