SQL Backup in SQL Server 2005 Express
I am looking to run a daily automated backup for SQL Server 2005 Express.
I created a batch file that references a .SQL file. I am getting an error.
The batch file and .sql file, along with the error are below:
Batch File:
sqlcmd -S "C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\sql-d
.SQL File:
BACKUP DATABASE [DatabaseName] TO DISK = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Backu
GO
ERROR that is generaed:
C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL>sqlcm
Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\sql-d
HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
I checked under the default instance and allow remote connections is enabled. Any ideas?
I created a batch file that references a .SQL file. I am getting an error.
The batch file and .sql file, along with the error are below:
Batch File:
sqlcmd -S "C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\sql-d
.SQL File:
BACKUP DATABASE [DatabaseName] TO DISK = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Backu
GO
ERROR that is generaed:
C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL>sqlcm
Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\sql-d
HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
I checked under the default instance and allow remote connections is enabled. Any ideas?
ASKER
When I run the SQLCMD from the cmd prompt I do get an error (see below) -- however I don't see where to reset the allow remote connections. I checked under the default instance properties and it looks like it is enabled.
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
Can you connect using the SA account (or whatever you renamed it to:
SQLCMD -U SA
SQLCMD -U SA
ASKER
I tried that command and it is prompting me for the SA password which I don't have.
Do you need to run this under SA?
Do you need to run this under SA?
No. I was just fishing for a userid/password that is likely to work. Try any userid/password that you know can connect to the server.
My theory is that authentication is either preventing you from connecting to the server or using the database. At this stage, we are just trying to prove that you can connect to the server.
My theory is that authentication is either preventing you from connecting to the server or using the database. At this stage, we are just trying to prove that you can connect to the server.
ASKER
When I try to use my id I get the same error about checking to see if SQL Server allows remote connections, which I already verified the setting to allow remote connections is checked in Management Studio. I am able to login to Management Studio and have full access to the database except when trying to run the backup from a command prompt with SQLCMD.
If your OS is Win Vista and above then run bat file as administrator.
To run this need administrator permission that's why execute as administrator.
To run this need administrator permission that's why execute as administrator.
ASKER
It's not a batch file permission problem. It is definately within the SQL. I get the error listed above when I try to run SQLCMD.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
dqmq thats for the info. If do that with the naming the server and sql instance explicity I am able to run the SQLCMD utility. However, when I add the next like to execute the backup in the batch file it doesn't run, it stops at line 1. What do I need to ad to have the SQLCMD launch and then execute the BACKUP.
Thanks.
Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Finally got it to work. thanks for hanging in until i got this fixed.
Here is what the batch file looks like:
sqlcmd -S Server\SQLInstance -i backup.sql -o output.txt
Here is what the batch file looks like:
sqlcmd -S Server\SQLInstance -i backup.sql -o output.txt
So you get the same error if you just type "SQLCMD" from a cmd prompt? If yes, then your windows login may not have an SQL Server login. If no, then your sql server login may not have access to the database.