AutoMatic Backup

Question

AutoMatic Backup

Trouble Shooter

SSC Eights!

Points: 902
More actions
August 19, 2009 at 5:50 am

#225145

how to take AutoMatic Backup in sql server

Viewing 11 posts - 1 through 10 (of 10 total)

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

Grant Fritchey SSC Guru Points: 398913 More actions · Answer 1

Use SQL Agent to create a job with a schedule that automatically backs up your databases.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 2

Add a maintenance plan. A job will be automatically created and you can schedule it.

Steve Jones - SSC Editor SSC Guru Points: 736603 More actions · Answer 3

I'd second the maintenance plan if you're not familiar with SQL Server and how backups work.

JF1081 SSC Eights! Points: 982 More actions · Answer 4

Can anybody point me to a tutorial or set of instructions on how to create a simple nightly backup for SQL server 2008 express? It is not a large database and all I want to do is simply back it up to a local drive and overwrite it on a nightly basis.

Derrick Smith SSChampion Points: 10833 More actions · Answer 5

I'm pretty sure SQL Express doesn't have the SQL Agent, so it can't schedule automatic jobs..but I've never used it for anything, so I could be wrong.

Steve Jones - SSC Editor SSC Guru Points: 736603 More actions · Answer 6

Get a script from here on SQL Server, or write a short stored procedure that does a backup of your database(s).

Use SQLCMD to run the stored procedure from the command line.

Use Windows Scheduler to run the SQLCMD command. This can be in the task or in a batch file.

homebrew01 SSC Guru Points: 55548 More actions · Answer 7

I found a freeware gui to use for scheduling jobs in Express ... works fine.

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

JF1081 (9/16/2010)
I want to do is simply back it up to a local drive and overwrite it on a nightly basis.

instead take backup with datetime suffix and delete the old backups files from local drive (decide retention also) , this will help you to have different copies for every day which provide you liberty to have data for any specific date

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

homebrew01 SSC Guru Points: 55548 More actions · Answer 9

Bhuvnesh (9/17/2010)
JF1081 (9/16/2010)
I want to do is simply back it up to a local drive and overwrite it on a nightly basis.
instead take backup with datetime suffix and delete the old backups files from local drive (decide retention also) , this will help you to have different copies for every day which provide you liberty to have data for any specific date

Yes, retain as many backups as you can. I keep recent ones on disk, and older ones go to tape. Sometimes you still can't have enough. 2 days ago I needed to find a file from June 28, but didn't have it.... oh well

codedawgred Grasshopper Points: 11 More actions · Answer 10

You can create an automatic backup in express via a windows automated task to run a sql script. Create the following script in a notepad and call it sqlbackup.sql. Notice mssql.1 is my instance, you may need to specify a different instance.(i.e. mssql.2 or mssql.3) save it somewhere.

DECLARE @datet varchar(200)

set @datet='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\' + convert(nvarchar(10),getdate(),102) + '.bak'

BACKUP DATABASE yourDatabaseNameHere

TO DISK = @datet

WITH FORMAT;

GO

Now create a new scheduled task in windows

In your run line call

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE" -S instanceNameHere -i "c:\pathToYourScript\sqlBackup.sql" -o "c:\output.txt"

in the start in line "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\"

supply admin account credentials and a schedule and now you have an automated backup going into SQL Servers default backup folder.