sql server 2008 full bakup every night

Posted on 2012-08-21
Last Modified: 2012-08-26
hello experts, how I can make a full bakup every night. In addition to the name of the backup has the date and time
Question by:enrique_aeo
    LVL 1

    Assisted Solution

    Hi Enrique. hopefully the link below can help you out
    LVL 9

    Assisted Solution

    1. Create to a job and configure schedule to run the job daily at specific time.

    The script to run everyday to take back is:

    DECLARE @name VARCHAR(50) -- database name 
    DECLARE @path VARCHAR(256) -- path for backup files 
    DECLARE @fileName VARCHAR(256) -- filename for backup 
    DECLARE @fileDate VARCHAR(20) -- used for file name 
    SET @path = 'C:\Backup\' 
    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
    DECLARE db_cursor CURSOR FOR 
    SELECT name 
    FROM master.dbo.sysdatabases 
    WHERE name='databasename'
    OPEN db_cursor 
    FETCH NEXT FROM db_cursor INTO @name 
    SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
    BACKUP DATABASE @name TO DISK = @fileName 
    FETCH NEXT FROM db_cursor INTO @name 
    CLOSE db_cursor 
    DEALLOCATE db_cursor 

    Open in new window

    Also refer the following docs.

    Author Comment

    can you explain me
    1. Create to a job and configure schedule to run the job daily at specific time.
    LVL 9

    Accepted Solution

    Go through the following link to create a job.

    Expand the SQL server agent
    Right click on 2nd right click on jobs --> select create new job
    1) Give the name of the job
    2)In step, click on new give the name of the step and copy the above command in command box
    3)In schedules give name and in the schedule type select "Recurring".
    In the Occurs, select Daily and provide the details etc.
    If you want configure remaing(alerts,notifications,Targets) options, otherwise click ok.

    This job will be executed everyday at scheduled time.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now