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 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 the user.
    │      ├─Under [Default Schema] type dbo
    │      ├─Click [OK]
    └──┴─Repeat for the other User.
  3. You must grant permissions to sqlengine and sqlagent to control the SQL Services.
    ┌──[Step 3]
    │      ├─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. Next, we need to give access to the folder for both our [SQLEngine] and [SQLAgent]
    ┌──[Step 4]
    │      ├─(This part is 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 semicolon.
    │      ├─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. We have to enable [xp_cmdshell] to allow SQL Server to perform a backup.
    ┌──[Step 5]
    Right-click on the Server Node | Open a [New Query] window. Copy and paste the following code, and run it.
    [xp_cmdshell]
    CFFCS | CarrzSynEdit: | SQL Script 
    
EXEC sp_configure 'show advanced options', '1'

RECONFIGURE
-- this enables xp_cmdshell

EXEC sp_configure 'xp_cmdshell', '1' 

RECONFIGURE

  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«