question on o/s commands in a sql job

[kodaksmile]

I have a sql job that has 2 lines of code to rename 2 separate files. The problem I am having is the job only runs the first line of code and then reports success. If I put the code in a bat file it runs fine.

move /Y S:\"Microsoft SQL Server"\MSSQL.1\MSSQL\Backup\mssqlsystemresource\mssqlsystemresource.mdf S:\"Microsoft SQL Server"\MSSQL.1\MSSQL\Backup\mssqlsystemresource\mssqlsystemresource_mdf.bak
move /Y S:\"Microsoft SQL Server"\MSSQL.1\MSSQL\Backup\mssqlsystemresource\mssqlsystemresource.ldf S:\"Microsoft SQL Server"\MSSQL.1\MSSQL\Backup\mssqlsystemresource\mssqlsystemresource_ldf.bak

This is really starting to drive me nuts.. I don't want to have to maintain a bat file on the o/s side, I'd rather keep it in the job. I realize I could just create a separate step for each command but, shouldn't I be able to have it in one step?

 

[bborissov]

MOVE database and LOG file?
And you expect success?
If they are attached you can't move them.
Why not just BACKUP database?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[kodaksmile]

This is the mssqlsystemresource db that came along with sql 2005. You cannot back it up using the normal database backup commands. Per Microsoft (and yes I've tested it) you copy the .mdf and .ldf files while sql is running to a new location. I have done that and am using the move command to rename the files because we have exceptions in our backup software for .mdf and .ldf files and I want these to go to tape. The move command works fine as I said in my original post - the issue is when I have 2 lines of code in the sql job only the first one runs and the job reports success. My work around is a 3 step job - 1 to copy the .mdf and .ldf files and 1 to rename the .mdf file and 1 to rename the .ldf file.

 

[RiverGuy]

Why don't you just use two separate job steps?

 

[kodaksmile]

I can do that and it works but, I am trying to understand why it doesn't work as one step with 2 lines of code in teh sql job as I may need to know for a future sql job (it works fine in the bat file)

 

[RiverGuy]

A .bat file is a batch file, so it's made to execute a batch of commands or multiple commands. I agree, though, that it would be nice to be able to enter multiple commands. I have a data source with an ODBC driver which does not work well with SSIS. So I have a custom exe written in .Net which connects and loads data from my data source. I have probably 30 individual job steps. When time permits, I think I'm going to look into learning PowerShell as I believe it will support a full script in the step editor.

 

[kodaksmile]

Yeah I was just trying to keep it simple and clean while still having all the code in the job. You're right though - probably time to start learning PowerShell. I was hoping I was just missing something.