September 7, 2016 at 5:32 pm
Hello,
I'm trying to set up a scheduled task to backup my database once a day. I have it running a .bat file that looks like this:
// Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='C:\SQLBackups\', @databaseName='RiskAliveDev', @backupType='F'"
But when I run this, I get the error:
Could not find stored procedure 'sp_BackupDatabases'.
In SQL Server Management Studio (Express), I find sp_BackupDatabases under Databases > System Databases > master > Programmability > Stored Procedures > dbo.sp_BackupDatabases
Why can't it find it?
September 8, 2016 at 12:00 am
Hi,
which user is executing the batch-file? I think you have no rights to find/execute the stored procedure to backup databases with this user.
Check your account, or add the right credentials to your batch....
.....Sqlcmd -U SAMPLE_USER -P SAMPLE_PASSWORD
Regards,
Andreas
September 8, 2016 at 10:29 am
I'm using the user I'm logged in as.
In SQL Server Management Studio, I log in using Windows Authentication and that seems to work to allow me to see the sp_BackupDatabases. Does it work differently when running a batch file?
Running the batch file as an administrator doesn't work either.
September 8, 2016 at 11:36 am
Have you tried fully qualifying the call to the stored procedure, i.e.
EXEC master.dbo.sp_BackupDatabases
Sue
September 8, 2016 at 11:38 am
Presuming you've got the Task set to use your login / password for the task, it should run as you and work.
As it's not, I'd likely try combining kreuzer and Sue_H's recommendations. Add the login information to the SQLCMD and fully qualify the name of the SP.
September 9, 2016 at 9:48 am
Ok, here's my new string:
sqlcmd -U [domain]\[username] -P [password] -S (localdb)\ProjectsV12 -Q "EXEC master.dbo.sp_BackupDatabases @backupLocation='C:\SQLBackups\', @databaseName='RiskAliveDev', @backupType='F'"
A few things that are different:
* Using my Windows credentials.
* Connecting to (localdb)\ProjectsV12 instead of .\SQLEXPRESS
* Took out the -E option.
* Referencing master.dbo.sp_BackupDatabases instead of just sp_BackupDatabases.
I took out -E because it told me that the -U/-P options are mutually exclusive with the -E option.
I changed to (localdb)\ProjectsV12 because the problem I'm getting now is that it's telling me it can't locate the server/instance. I know that in SQL Server Management Studio, I always connection to (localdb)\ProjectsV12, which contains the database I want to back up, so I put that in verbatim.
I'm still getting the same error though:
HResult 0xFFFFFFFF, Level 16, State 1
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.
September 9, 2016 at 10:23 am
junk.mail291276 (9/9/2016)
Ok, here's my new string:
sqlcmd -U [domain]\[username] -P [password] -S (localdb)\ProjectsV12 -Q "EXEC master.dbo.sp_BackupDatabases @backupLocation='C:\SQLBackups\', @databaseName='RiskAliveDev', @backupType='F'"
A few things that are different:
* Using my Windows credentials.
* Connecting to (localdb)\ProjectsV12 instead of .\SQLEXPRESS
* Took out the -E option.
* Referencing master.dbo.sp_BackupDatabases instead of just sp_BackupDatabases.
I took out -E because it told me that the -U/-P options are mutually exclusive with the -E option.
I changed to (localdb)\ProjectsV12 because the problem I'm getting now is that it's telling me it can't locate the server/instance. I know that in SQL Server Management Studio, I always connection to (localdb)\ProjectsV12, which contains the database I want to back up, so I put that in verbatim.
I'm still getting the same error though:
HResult 0xFFFFFFFF, Level 16, State 1
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.
For the credentials, user name and password are used for SQL Logins. A trusted connection using -E is for Windows logins. So what you use depends on the type of login you are using. Since it looks like you want your windows credentials used, using -E would be correct.
The server name you use depends on what version of Express you are running. I don't run express and don't know which versions use what. I had thought Express 2012 used (LocalDB)\v11.0 but not sure on that. I'm wondering if you are running two different versions of SQL Express though.
Sue
September 9, 2016 at 11:55 am
I've attached two screen shots, one showing the services running on my machine (highlighting SQL Server (SQLExpress) and the other showing the version of SQL Server from the About menu item in SQL Server Management Studio.
September 9, 2016 at 1:41 pm
Thanks but those don't really give the instances. And the other gives the version of SSMS.
But...going back to your original post, the problem was not finding the stored procedure. So just change the stored procedure to be fully qualified since it did login. Looks like it's YourMachineName\SQLExpress.
// Sqlbackup.bat
sqlcmd -S .\SQLEXPRESS -E -Q "EXEC master.dbo.sp_BackupDatabases @backupLocation='C:\SQLBackups\', @databaseName='RiskAliveDev', @backupType='F'"
Just run that and see what the results are.
Sue
September 12, 2016 at 8:22 am
Unfortunately, that doesn't work either. It still tells me that it can't find the stored procedure.
September 12, 2016 at 9:56 am
Execute the command to do the backups from a query window (outside of the batch file) yourself.
Just this part:
EXEC master.dbo.sp_BackupDatabases @backupLocation='C:\SQLBackups\', @databaseName='RiskAliveDev', @backupType='F'
If you still get the error then your account does not have access to the stored procedure - maybe your created it using a different login. Or it's not really in master. Or it has a slightly different name. Or it's not really in the dbo schema.
Sue
September 13, 2016 at 8:55 am
EXEC master.dbo.sp_BackupDatabases @backupLocation='C:\SQLBackups\', @databaseName='RiskAliveDev', @backupType='F'
That works in SQL Server Management Studio (I right click on the database connection containing master.dbo.sp_BackupDatabases and also RiskAliveDev and say 'New Query' and paste that line and run it). I get the DB backup in C:\SQLBackups.
So far, I've gotten three kinds of errors:
1) Error locating server/instance.
2) Login failure.
3) Cannot find sp_BackupDatabases.
I'm assuming this is the order in which the errors should occur assuming I'm making headway (i.e. it needs to be able to log in before it can find sp_BackupDatabases, and it needs to find the server/instance before it can attempt to log in). Correct?
So I'm better off getting error #3 than either of the other two errors, correct?
September 13, 2016 at 10:55 am
junk.mail291276 (9/13/2016)
EXEC master.dbo.sp_BackupDatabases @backupLocation='C:\SQLBackups\', @databaseName='RiskAliveDev', @backupType='F'
That works in SQL Server Management Studio (I right click on the database connection containing master.dbo.sp_BackupDatabases and also RiskAliveDev and say 'New Query' and paste that line and run it). I get the DB backup in C:\SQLBackups.
So far, I've gotten three kinds of errors:
1) Error locating server/instance.
2) Login failure.
3) Cannot find sp_BackupDatabases.
I'm assuming this is the order in which the errors should occur assuming I'm making headway (i.e. it needs to be able to log in before it can find sp_BackupDatabases, and it needs to find the server/instance before it can attempt to log in). Correct?
So I'm better off getting error #3 than either of the other two errors, correct?
Yes it's a better error in that sense. Pretty sure we can get this one working.
The login failure was just due to trying to use your Windows login for a user name and login. That's just using the wrong connection string parameters. And the not finding the instance was pretty much the same - just not using the correct server name in the connection string.
So all I had you do was just eliminate those by just running the command without any connection through the batch file. And then that pulls up error underneath everything so you can figure out the problem. It doesn't do any good to work on the connection string if the command itself isn't working.
From your screenshots before, you do have more than one instance running. Are you a sysadmin on all of the instances you have running? You should also double check for which instance actually has the stored procedure - that could be one reason it can't find the stored procedure.
When you are connected to an instance, you can execute this to see what instance you are connected to:
select @@servername
If you think you have found the stored procedure, you can open up a query windows and set the context to that database with:
USE DatabaseName. So for example, if you find it in master, just execute this in the query window:
USE Master
That will set your database context (the database you want to be in) to the master database.
Next you can run the following to verify the name, instance, schema of the stored procedure:
select name, SCHEMA_NAME(schema_id) as [Schema],
DB_NAME() as [Database],
@@servername as [Instance]
from sys.procedures
where name like 'sp_BackupDatabases'
So now you to verify your account has the correct permissions (generally you would want to be a sysadmin on instances running on your PC) and then need to find the location of the stored procedure and verify it by running the above query to give you the information you need in the connection string (instance, schema, database for the stored procedure).
Sue
September 13, 2016 at 12:46 pm
I'm attaching a screen shot of the results on this query.
The instance appears to be ACMCAL-LT160\LOCALDB#F1BD7ACE.
I put that into the backup command like so:
sqlcmd -S ACMCAL-LT160\LOCALDB#F1BD7ACE -E -Q "EXEC sp_BackupDatabases @backupLocation='C:\SQLBackups\', @databaseName='RiskAliveDev', @backupType='F'"
But I get the "Error locating server/instance" error.
September 13, 2016 at 1:34 pm
When you run a batch file - or powershell script - from the task scheduler you have to identify what account the task scheduler job will use. This needs to be an account that has access to SQL Server - and should not be your personal account.
If you open a command window from Windows (not SSMS) and execute the batch file - it will be run using your credentials.
The issue you are having is all related to how you are executing that batch file from task scheduler. The best option is to have a domain service account created - grant that service account the necessary privileges in SQL Server and use that account in the task scheduler. The next best option is to create a local service account on that machine - setup that user in SQL Server - and use that account in the task scheduler.
The last option would be to create a SQL account and assign a password - then use that SQL account in your batch file with the -U and -P parameters to pass the username and password.
This account should have minimum privileges - only what is needed to run the procedure and backup the databases.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply