Renaming a logfile via cmdshell 1

[JaybeeinTekTips]

Hi all,

Can anyone tell me whether the syntax will work for renaming a logfile by appending the current date/time to the end, and what the "112" specifies?

Thanks,

JB

/****************************************************************************/

declare @s varchar(8000)

select @s = 'rename "E:\LBBSVS01\LOGS\MSSQL$LBBSQL01\Logs\aspstate_log.LDF" "E:\LBBSVS01\LOGS\MSSQL$LBBSQL01\Logs\aspstate_log(' + convert(varchar, getdate(), 112) + ').LDF"'

xp_cmdshell @s


/****************************************************************************/

 

[ptheriault]

Jay,
You can't rename a .ldf file while it is attached to the sql instance.
If you are trying to rename the database you can use sp_renamedb

- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

Lookup "Convert" in Books Online. 112 is a style of the conversion.

And no, I don't think the above code will work for you. Try rewording it like this:

declare @s varchar(8000)

Set @s = 'rename E:\LBBSVS01\LOGS\MSSQL$LBBSQL01\Logs\aspstate_log.LDF aspstate_log(' + convert(varchar(8), getdate(), 112) + ').LDF'

Exec Master..xp_cmdshell @s

I don't recommend using Parens in file names, BTW. Also, you don't restate the path name in the second part of the rename statement. Lastly, you need to EXEC the xp_cmdshell proc or it won't work.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

Oh, and as Paul said, you have to detach the db before you can rename it. Or it will fail.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"