Simplest backup

Question

Post reply

Simplest backup

Dean Reedy

SSChasing Mays

Points: 602
More actions
January 19, 2007 at 8:19 am

#368866

Backup ground: Small consignment shop database. Will have approx 20 tables and a few views, approx 100,0000 records.
What is the simplest backup strategy for MSSQL 2005 Express? I would like to be able to write the backup data to CD's or USB sticks. I need something simple for Mom.
thanks,
Dean

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

MohammedU SSCoach Points: 19644 More actions · Answer 1

Run the backup to disk and copy the backup file to CD or USB.

MohammedU
Microsoft SQL Server MVP

Dean Reedy SSChasing Mays Points: 602 More actions · Answer 2

My question is how do I backup a database(The process)?

deenadayalan Say Hey Kid Points: 670 More actions · Answer 3

Refer SQL Server Books online to know how to configure an automatic backup using SQL Server Agent. You can create an SQL backup job.

Dean Reedy SSChasing Mays Points: 602 More actions · Answer 4

Actually with SQL Server 2005 express the job agents functionality has been disabled.

Tim Mitchell SSCoach Points: 15681 More actions · Answer 5

Your best route for this is to create a text file containing your backup command, and create a batch file that will call osql using your text file as an input file. Use the built-in Windows scheduler to run the batch file at desired intervals.

hth,

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

l0n3i200n Ten Centuries Points: 1091 More actions · Answer 6

DECLARE @PathName VARCHAR(1024)

SET @PathName = ''\\[Server]\Backups\[FileName]'' + CONVERT(VARCHAR,GETDATE(),112) + ''.bak''

BACKUP DATABASE [DATABASE_NAME]

TO DISK = @PathName

WITH

NOFORMAT,

NOINIT,

NAME = N''DBBackup'',

SKIP,

STATS = 10;

GO

SCRIPT TO BACK UP

jezemine SSCrazy Eights Points: 8059 More actions · Answer 7

the example shown has you backing up directly to a network share. generally that's a bad idea because if the network goes down, your backup fails. backup failing is bad because then you have no backup!

better practice is to backup to a local disk on the server, and then copy the backup to the network share.

---------------------------------------
elsasoft.org

d viz Ten Centuries Points: 1092 More actions · Answer 8

I have a simple application built with VB6 and ADO especially for backing SQL server express. If anyne is interested I can upload source + executable or mail the whole thing. Just drop me a note.

I use it with two cmds that run daily (full an differential backup) and also rar the backup file to save space...

dec_obrien Default port Points: 1486 More actions · Answer 9

Hi,

I'm sure this is a really silly question but how do I create a batch file to call osql to run the contents of the textfile?

Thanks

jezemine SSCrazy Eights Points: 8059 More actions · Answer 10

use the -i flag.

if you are using 2005, better to use sqlcmd instead of osql. osql is deprecated.

---------------------------------------
elsasoft.org

dec_obrien Default port Points: 1486 More actions · Answer 11

Hi,

Thanks for teh reply. I'm fairly new to this so forgive me for the simple questions but how do I use this -i flag?

I presume what I need to do is something like:

Make a batch file and save it on C: then make a scheduled task in Windows to run this batch file.

What does my batch file need to say?

If I use sqlcmd instead, what does my batch file need to say?

Thanks again.

Gift Peddie SSC Guru Points: 73570 More actions · Answer 12

Try these

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

http://www.lazycoding.com/products.aspx

http://www.sqldbatips.com/showarticle.asp?ID=29

http://www.sqldbatips.com/showarticle.asp?ID=27

Kind regards,
Gift Peddie

jezemine SSCrazy Eights Points: 8059 More actions · Answer 13

Gift: not sure why you posted those links, none of them really show you how to use sqlcmd or osql...

anyway, the best reference in all things related to SQL Server is the docs that come with it: books online. Here's the entry on sqlcmd:

http://msdn2.microsoft.com/en-us/library/ms162773.aspx

and here's a tutorial:

http://msdn2.microsoft.com/en-us/library/ms170207.aspx

a sample bat file that executes a script would look like this:

sqlcmd -i "c:\myfile.sql" -S MYSERVER -E -d MyDatabase

the -E means to use windows auth. to use sql auth, use the -U and -P flags instead. This cmd would execute the script at c:\myfile.sql in the context of MyDatabase on the server MYSERVER.

there's much more detail about sqlcmd in books online, at the links above.

---------------------------------------
elsasoft.org

dec_obrien Default port Points: 1486 More actions · Answer 14

Hi,

I think that's exactly what I'm looking for. I'll give it a go later on this morning.

Much appreciated.