MS SQL 2005 - Dump SQL

[tom_newton]

Linux user in windowsland here

Want to do the equivalent of mysqldump to MSSQL, in order to back up the sql (create table statements, inserts, etc.)

What's the usual way to do this, or should i be backing up in some other fashion?

Ta.

[Jona]

Here we do it with a "Job" which is configured in Server Management Studio

If you right click a database and select Task > Backup You can create a SSI file which will do a backup to a .sql file and you can then set this to run automatically via a job.

I havn't ever investigated if there is a way to do it from the commandline (a-la my-sql but I have just found this: http://sqldump.sourceforge.net/).

Hope that helps

Cheers
Jona

[zag]

Or you can do it manually using myphpadmin.

[Jona]

Or you can do it manually using myphpadmin.

Isn't that for MySQL rather than MSSQL ??

[tom_newton]

Cheers Jona. I'll pass this to my colleague who's doing the backup

[zag]

Isn't that for MySQL rather than MSSQL ??

Yep I totally misread the original post

[ZeroHour]

The SQL command we use is:

BACKUP DATABASE {database} to DISK='\\{SERVER}\{SHARED}\{something.bak}' with init

The file created is not text based but with that file you can then run MSSQL's "RESTORE" command and it will rebuild a database. 2005's management tools allow you to restore files like these using a wizard without commands.
We use the SQL Agent to run a Job which basically just runs that command at set intervals.

[JamesC]

Hah! as if! I knocked up a quick script in kixtart last week that backs up a mssql server live. If anyone is interested lemme know

I'm not at a machine I can access it from at the moment

EDIT:/ btw its a kixtart script that I run as a scheduled task that dumps a certain database to a file, overwriting the existing backup if it already exists.