Previous Code Entry
Left Arrow
Difference between UNION ALL and UNION
Current Code Entry
Next Code Entry
Schedule to Backup SQL Server Transaction Logs
Right Arrow
How to perform a Backup of SQL Server Transaction Logs and SQL Server Databases.
Article Entry Date: February 9,2023 @ 11:51:32 / Last Updated On: February 10,2023 @ 12:44:10
How to schedule a backup of SQL Server Transaction Logs and Databases.
I've been doing this for a few years since running our servers in-house. If this is not performed regularly with both a Transaction Backup and Database Backup, you will run into significant issues, like your Log Files filling up and growing HUGE; when I say huge, I mean, Gigs in size. The most extensive our Log file ever grew was over 20 Gigs.
Doing the following two Backups is crucial when running a Live SQL Server in a Production Environment, especially if you are running an [Always On High Availability Group] or [Always On]

  1. [Step 1]
    From your Windows [Domain Controller].
    Open [Active Directory Users and Computers]
    Expand the [Domain.Local]
    Click on [Users]
    Right-click on the [Users] folder
    Choose [New], then [User]
    When the [New object - User] window opens.
    Fill this page out.
    Give the [Username]: sqlagent
    click [Next >]
    Give a good password. (I choose User cannot change Password & Password never expires)
    Click [Next >]
    Then click [Finish].
    Double-click on the new SQLAgent User.
    Click on [Member Of].
    Click [Add] and copy and paste the following names in the window.
    Administrators;Domain Admins;Domain Computers;Domain Users;Server Operators
    Click on [Apply] then [OK]. Next
    Right-click on the sqlagent and choose [Copy]
    Give the [Username]:
    Fill out the rest of the information.
    Click [Next >]
    Type in a [password].
    Click [OK].
  2. [Step 2]
    Add both users to SQL Server Logins.
    Click on ServerName\InstanceName
    Click on [Security]
    Click to expand [Logins].
    Right-click on Logins
    Choose [New Login]
    Give Login Name: DomainName\sqlagent
    Click on [Windows Authentication]
    Click on [Server Roles] from the Left-panel.
    Check the following [Server roles].
    dbcreator
    public (default checked)
    serveradmin
    sysadmin
    Next, click on [User Mapping]
    Click on Master from the top panel (User mapped to this login).
    You will see the new User we added selected as User.
    Under [Default Schema] type dbo
    Click [OK]
    Repeat for the other User.
  3. [Step 3]
    You must give permissions for the sqlengine and sqlagent to control the SQL Services.
    Open [Control Panel]
    Then [Administration Tools]
    Scroll down to [SQL Server Agent]
    [Right-click] and choose [Properties] (or) [Double-click] to open [Properties Window].
    From [Startup Type:] Automatic
    Click on [Start]
    Click on the [Log On] tab.
    Type in the following for the login.
    This account: domainname\sqlengine
    Password
    Confirm password:
    Click [OK]
    [Next]
    Double-click on [SQL Server (SQLInstanceName)]
    Perform what we did for SQLAgent.
    From [Startup Type:] Automatic
    Click on [Start]
    Click on the [Log On] tab.
    This account: domainname\sqlagent
    Password
    Confirm password:
    Click [OK]
  4. [Step 4]
    Next, we need to give access to the folder for both our SQLEngine and SQLAgent
    (This part of for those who are running Windows Core Servers)
    Using [Computer Management]
    Right-click on [Computer Management]
    Choose [Connect to another computer...]
    [Another computer]: ServerName
    Expand [System Tools]
    Then expand [Shared Folders]
    Right-click in the Shared Folders window and choose [New Share]
    Click [Next >]
    Click Browse:
    [Create New Folder] for G:\SQLServer\Backup
    [Next >]
    Choose [Customize permissions] click [Custom...]
    Choose [Add...]
    Type in both users separated by a semi-colon.
    Authenticated Users (You can also add an admin user here as well for monitoring the folder(s))
    Give [Full Control] to/All users.
    Click [OK]
    Click [Finish]
    Click [Finish]
  5. [Step 5]
    We have to enable [xp_cmdshell] to allow SQL Server to perform a backup.
    Open a [New Query ] window. Copy and Paste the following code, and run it.
  6. [Step 6]
    From SQL Server, Right-click on [SQLListener,2433] (Or your SQL Server Name) [ServerName\InstanceName]
    Choose [Facets]
    From the [Facet] list, choose [Server Security]
    Make sure [XPCmdShellEnabled] = [True]
    Click [OK]
  7. [Step 7]
    Open SQL Server Management Studio (SSMS for short)
    Open [SQLListener,2433] (Or your SQL Server Name) [ServerName\InstanceName]
    Right-click on your database and choose [Properties]
    Choose [Options] from the Left-panel.
    Change [Recovery model]: to [Full]. Click [OK]
  8. [Step 8]
    Scroll down to [SQL Server Agent]
    Right-click and choose [Start] (If it is already started, leave it alone.)
  9. Next Step. Follow the two Related links below to schedule backups for your Trans and Databases.


Other Articles Related to this Entry.
Perform these in order. You have to create a [Database Backup] before a [Trans Backup.]
( First ) - Schedule to Backup SQL Server Databases«
( Second ) - Schedule to Backup SQL Server Transaction Logs«