How to back up MSSQL without Microsoft Enterprise Manager

[engine]

We all know how important backups are, right!

Does anyone know a solution to back up the data in an MSSQL database, easily, without Microsoft Enterprise Manager?

It needs to be straighforward and simple as the person I want to run the backups is the site owner (non-tech).

[syber]

You could just do a tape backup of the .mdf and .ndf files as a minimal backup scheme. It would be best to stop the mssqlserver service before doing the backup to flush the buffer cache to the harddrive. You should be able to set the whole thing up as a batch file. I'm assuming that you are not worried about preserving the log file.

Also, remember to keep a recent tape backup offsite.

[johnhh]

Assuming he has access to the database why not let him have a copy of the MSQLManager, with a connection to remote (?) database and create a backup script as a scheduled event to a local drive - if that is what is required. Desktop version is free(?)- need to check that.

If you just stop the service and copy the files across I have had maybe 80% success rate in re-attaching the files from a straight file copy. Normal MSSQL backup is compressed etc.

Also this way you could schedule a reindex and other tasks at the same time as required.

You should also backup the Master database as well really.

[aspdaddy]

I dont know if you can do this withut EM, but have you tried executing backup SQL over ADODB like this?

BACKUP DATABASE [MyDatabase]
TO DISK =N'E:\MyDatabase.bak'
WITH INIT,NOUNLOAD,NAME=N'MyBackup',NOSKIP,STATS=10,NOFORMAT

[dataguy]

Why not schedule a backup every day, and have them only saved for one day. Then your guy can just file copy the backup data like any other fileset and he won't need EM?

This is how I have it set up, and then I run a scheduled task from a backup server to copy the backup off that server, and then upload it to a remote location.