Solved

SQL Backup in SQL Server 2005 Express

Posted on 2011-09-24
13
539 Views
Last Modified: 2012-05-12
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-daily-backup.sql"
 
.SQL File:
BACKUP DATABASE [DatabaseName] TO  DISK = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DatabaseName.bak' WITH NOFORMAT, NOINIT,  NAME = N'DatabaseName-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

ERROR that is generaed:
C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL>sqlcmd -S "C:\Program
Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\sql-daily-backup.sql"
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?
0
Comment
Question by:fjkaykr11
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
13 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 36594359
The error can also be security related.  Possibly, the windows login does not have permission to access a database called "[DatabaseName]".  Substitute the name of the database you are trying to backup and make sure your windows login has permission to access it.

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.
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 36594382
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.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36595364
Can you connect using the SA account (or whatever you renamed it to:

SQLCMD -U SA
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 3

Author Comment

by:fjkaykr11
ID: 36595696
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?
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36595718
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.

 
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 36596056
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.  
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36598489
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.
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 36599120
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.
0
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 36600147
Try naming the server explicitly:


SQLCMD -S .\SQLEXPRESS -U userid

or

SQLCMD -S servername\SQLEXPRESS -U userid


Then, try without the -U option

0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 36707579
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.
0
 
LVL 3

Assisted Solution

by:fjkaykr11
fjkaykr11 earned 0 total points
ID: 36707600

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
0
 
LVL 3

Author Closing Comment

by:fjkaykr11
ID: 36895928
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
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question