Networks

Question

Gravatar
0 Votes
Locked

Automatic Backup for SQL Express 2005

By Azgarde ·
Hi, I was looking for a way to automate a backup for SQL Server 2005 Express. First, I created a batch file that contains:


c:
cd backup
sqlcmd -i c:\backup\backup.sql


Basically, what it does is just change the directory to C:\Backup then it launches the Backup.sql

Backup.sql contains:

EXEC sp_addumpdevice 'disk', 'dbbu',
'c:\Backup\dbbu.bak'
BACKUP database dbtest TO dbbu
GO

What it does is only "backuping" the database Dbtest to the file Dbbu.back in the path C:\Backup


However, when I launch it, I get an error telling me that An error occured while establishing connection to the server, that might be caused by SQL Server not accepting remote connection, although that I am running the operation locally.

Any ideas ?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

gravatar
Collapse -

you forgot a step

by brian In reply to Automatic Backup for SQL ...

This is part one...i see that you did not turn off the sql server so you can have access to the DB's...you can do this VIA a simple net stop command in your batch file (net stop [name of sql stuff]) do this for each sql service. After your backup batch runs, call another batch file that will run the net start command for the sql services that you turned off (net start [name of sql stuff]). Start there and see what happens

gravatar
Collapse -

Same Error

by Azgarde In reply to you forgot a step

I did as asked, with adding:
net stop SQL Server (SQL Express)etc.

However, I still get the same error telling me that my SQL Server doesn't allow remote connexions. This is kinda weird since I'm doing all of that locally.

gravatar
Collapse -

Shouldn't need to do that unless you are doing a straight

by Tony Hopkinson In reply to you forgot a step

file based back up.
In fact if you turn the service off it can't connect and back itself up.

gravatar
Collapse -

interesting

by brian In reply to Shouldn't need to do that ...

Not saying that what you said isn't right, but that was the only way i could get one of my db's to backup via batch file (then again, this system is known for extremely STRANGE problems)...oh well, the good is that it works for me, and works for the person who started the thread. To each system, its own way to "make it" work...lol

gravatar
Collapse -

It's not a good idea anymore with SQL server.

by Tony Hopkinson In reply to interesting

Unless you change the recovery model for your databases to simple, if you don't use SQL's backup command the tranaction logs just keep growing, and growing, and growing. Unless you restore the file image, and then you lose them...

gravatar
Collapse -

The user your batch file is running as doesn't

by Tony Hopkinson In reply to Automatic Backup for SQL ...

necessarily have the same privileges as you.

This might help.

http://www.mssqltips.com/tip.asp?tip=1174

gravatar
Collapse -

Got it working, here's how

by Azgarde In reply to Automatic Backup for SQL ...

I finally got it working.
It seems a batch file wouldn't work, so what I had to do is create my .sql file with the exact settings listed in my previous post, then in the Task Scheduler of Windows I created a new task with SQLCMD.exe . Then, went into the properties and replaced the "Run as" field with " sqlcmd -S ServerName -E -i C:\Backup\Backup.sql ".

Turned to work awesomely.

Back to Networks Forum

Start or search

Related Discussions

Related Forums