AutoMatic Backup

  • how to take AutoMatic Backup in sql server

  • 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
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

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

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

  • 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.

  • 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.

  • 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.

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

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

  • 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;-)

  • 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

  • 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.

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

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