asked on

How to Create a Backup Script to Backup SQL Express 2005.

Hi,
I would like to create a backup script to backup a SQL Express Database to an external HD. Could Somebody either point me to a site where i can find info on writing scripts to do these backups or give me some example script? I know how to create a SQL script (notepad with the sql extension), and know how to set it to run as a sceduled task, just dont know the actual sript.

Here is what I want to do.

SQL DATABASE LOCATION: c:\jager\qtnew.mdf
SQL DATABASE BACKUP DESTINATION: e:\backup\ ( qtnew.mdf? )

instance: SQLExpress
userID: QTuser
password: QTpwd

Help would be appreciated

Nightman

The T-SQL would look like this:

BACKUP DATABASE qtnew TO DISK='e:\backup\qtnew.bak'

You can use SQL Agent (from SQL Management console) to automate this for you. Create a new backup maintenance plan and work through the wizard.

Mike_Plug

ASKER

Thanks for the response Nightman. Apparently I know even less about this topic than I thought (I am knew to SQL Databases). A couple more Questions....

1) Should I be able to test the script you provided me with by going into sqlcmd and typing { BACKUP DATABASE qtnew TO DISK='e:\backup\qtnew.bak' } ?
- I tried this and nothing happened.

2) If I am correct, MS SQL Express does not have the capability to use an agent to do an automated backup... is this right??

Nightman

1) Yes, if you have already successfully connected to the database.
Have a look at:
http://msdn2.microsoft.com/en-us/ms170207.aspx
http://msdn2.microsoft.com/en-us/library/ms170207.aspx

Tutorials on SQLCMD.

2) Not sure (don't have it installed on this pc) but it should. UNLESS it's the BETA (yes, Beta) that comes with early versions of VS2005. You should download the RTM version from Microsoft.

Mike_Plug

ASKER

Nightman, take a look at this:

http://msdn2.microsoft.com/en-us/library/ms165636.aspx
It appears that the SQL Agent is NOT available in SQL Server Express.

I am still trying to get the backup working, and will post my results.

Mike_Plug

ASKER

Hi, I did some checking, and I dont see where I am going wrong. I am fairly sure that I am successfully connected to the databse (from what I understand when 'sqlcmd' is typed at the command prompt then it connects to the default instance of the database). After sqlcmd is typed, I get a 1>. I type BACKUP DATABASE qtnew TO DISK='e:\backup\qtnew.bak' and nothing happens. The only thing that happens is that I get a 2>. any Ideas?

Nightman

try
1. BACKUP DATABASE qtnew TO DISK='e:\backup\qtnew.bak' <Enter>
2. Go <Enter>

David Todd

Hi,

the other thing to add to the backup command is
with stats = 5

ie

BACKUP DATABASE qtnew TO DISK='e:\backup\qtnew.bak' with stats = 5

Will give a message each 5 percent. Then you do know that something is happening.

Regards
David

Mike_Plug

ASKER

thanks for all the answers... still fighting this issue however. Located in c:\program files\microsoft sql server\mssql.1\mssql\data there is a database called 'master'. For testing purposes, i tried to back this up. I typed:

sqlcmd
1> BACKUP DATABASE master TO DISK='e:\backup\master.bak' with stats = 5
2> go

and the file was successfully copied showing progress every 5%.

Now, why cant I backup c:\jager\qtnew.mdf ? When I type:

sqlcmd
1> BACKUP DATABASE qtnew TO DISK='e:\backup\qtnew.bak' with stats = 5
2> go

this is the message I get:

" Msg 911, level 16, state 1, Server SERVER, line 1
Could not locate entry in sysdatabases for databse 'qtnew'. No entry found with
that name. Make sure that the name is entered correctly.
Msg 3013, level 16, State 1, Server SERVER, line 1.
BACKUP DATABASE is terminating abnormally. "

Why would this be happening?

ASKER CERTIFIED SOLUTION

Nightman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike_Plug

ASKER

I followed your suggestion.... and I do not see any 'qtnew' result.

My results are all located in C:\program files\microsoft sql server\mssql\data

The different databases are:

master (\master.mdf)
model (\model.mdf)
msdb (\msdbdata.mdf)
Northwind (\northwnd.mdf)
pubs (\pubs.mdf)
tempdb (\tempdb.mdf)

I do not recognise any of these databases (thinking back, I think I may have "renamed" the qtnew database to refer to it as "qt2007".

Still,
BACKUP DATABASE qt2007 TO DISK='e:\backup\qt2007.bak' with stats = 5
does nothing anyway.
Am I maybe connected to the wrong instance... if so, how do i connect to the right one?

Mike_Plug

ASKER

Thanks for all the help. I did just find the solution. I was connected to the wrong instance. After I was connected the right instance,
BACKUP DATABASE qtnew TO DISK='e:\backup\qtnew.bak' with stats = 5
still did not work. The database name and file name where different.

BACKUP DATABASE qt2007 TO DISK='e:\backup\qt2007.bak' with stats = 5
was what I had to do. Thanks again.

Nightman

lol - sorry, had to go out for a while.

I spend 2 hours trying to debug some code a few weeks ago, and couldn't see my changes on the DB, only to find it was the wrong instance ... been there. Glad it's working now.

David Todd

Hi Mike,

The filename shouldn't have made any difference. If the 'wrong' filename was used, SQL would have created another backup file, thats all.

Of course, the name _might_ mean something to you and other humans, and being sure of which backup is which is important, especially when it comes to restoring production systems.

Regards
David