I came back to a machine to work on it today and noticed that my batch files no longer can back up a copy of my database using the backup database command.
The server is 2008R2 for both windows server and sql server.
No more than a month ago this same batch file would back up my database. Now when I manually run my sql script I get a "Operating System Error 3 - cannot find file specified".
Here's my script:
DECLARE @FileName varchar(50), @Date varchar(20)
set @Date = REPLACE ((CONVERT (VARCHAR(10), GETDATE(), 101)), '/', '-')
SET @FileName = ('C:\mybkfolder\BackupSQLData\db_dnt_' + @Date + '.bak')
BACKUP DATABASE db_dnt TO DISK = @FileName
Go
I have verified that sql server is running under the system account. I can manually make a backup from sql management studio to the folder in question. But if I run my script as a query in management studio I get this error.
I went to this folder and set the security permission so that the SYSTEM account would have full access. Then I went to task scheduler and made sure my scheduled task (run a batch. batch calls a .sql file which contains script above) was configured to run under SYSTEM. Still nothing is backing up.
Other than windows patches nobody has messed with the machine really. What causes this to just stop working? This is an express version of sql server and since you can't create jobs on the express version like the real one I have to use a two batch file system where one backs up and the other purges to keep the folder under control.
This turned out to be an issue with a path that no longer existed. My task called a batch, the batch called a .sql script. I had forgotten to update the path in the sql script even though I remembered to do so in my batch file.