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]
- [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].
- [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.
- [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]
- [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]
- [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.
- [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]
- [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]
- [Step 8]
Scroll down to [SQL Server Agent]
Right-click and choose [Start] (If it is already started, leave it alone.)
- Next Step. Follow the two Related links below to schedule backups for your Trans and Databases.