MS SQL Server 2005 Express edition (Back-Up)

Question

 

Hi,

 

I am using MS SQL Server 2005 Express edition with my application.

 

I am new to SQL Server.. Can any body help me in solving the following:

 

1) Is there any automatic back-up provision available in MS SQL Express edition ?
2) How can i take the back-up/archive my data ?
3) How to implement Data aging & Data pruning by using MS SQL Express edition 2005 ?

 

Thanks in advance

Answer 1

There is no 'built in' capability with SQL Server Express to automate tasks (such as Backups.)

 

You could use a combination of the Windows Scheduler service, SQLCmd.exe, and stored procedures.

 

OR, you could explore this project and determine if it is useful for your tasks.

 

   http://www.codeproject.com/vb/net/SQLAgent.asp

 

For detailed syntax and usage samples, Refer to Books Online, Topic: BACKUP

Data aging and pruning would require you to create your own processes.

Answer 2

Check this article for Scheduled Backup in Express

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

 

Madhu

 

Answer 3

I tried to take the backup of my SQL Server 2005 database <Vista> by running the following query.  It works fine on all local drives except C:\<Program files> folder

 

On C:\drive
BACKUP DATABASE [Vista] TO DISK = N'c:\Temp\bu_2_26_.bak' WITH NOFORMAT, NOINIT, NAME = N'Vista-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

 

The same was tried on a network drive which has R&W permission with enough space.

On Network drive (Z:\)
BACKUP DATABASE [Vista] TO DISK = N'f:\Test\bu_2_26_.bak' WITH NOFORMAT, NOINIT, NAME = N'Vista-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

 

It fails complaining "Operating system error 3 (The system cannot find the path specified.)

 

When I tried to take the backup manually by selecting <Vista> database > Right Click > Tasks > Back up... option we could find only local hard drives on the destination folder selection dialog.

 

(I am using Microsoft SQL Server 2005 Express Edition & Microsoft SQL Server Management Studio Express)

 

Can anybody help/advice me in solving this issue.

 

Thanks in advance...

Answer 4

Might be some permission related issue, while backing up to network try to use the UNC path and perform backup as,

BACKUP DATABASE [Vista] TO DISK = N'\\Share name\Test\bu_2_26_.bak' WITH NOFORMAT, NOINIT, NAME = N'Vista-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

Refer the link http://support.microsoft.com/kb/141941 for more info I think your drive is not mapped. Ensure that your service account is changed to some other user account since local system cannot access network resources and also verify the permissions for sql service account.

I feel that the best way is to backup your database to a local drive, then moving the backup to a remote location of your choice once the backup has finished. The main advantage of doing this is that it keeps the backup process as quick as possible and therefore tends to help keep resource utilization due to the backup process to a minimum.

- Deepak

Answer 5

 

You might wanna check if the user account that running the sql services has permission on the share folder.

 

Good Luck

Answer 6

SQL Server Express Edition lacks of SQL Agent which is the responsible for scheduling maintenance jobs such as backups. Anyways you can still automate your backups tasks by one of the following ways:

- Create a custom script and run it from windows scheduler.

- Trying some third party tool. I recommend you to try SQL Backup and FTP which has a free edition that lets you schedule up to 2 databases daily. The tool is very easy to use, especially if you don't have so much experience.