System Administrator Blog And Scripts

How to setup email notifications for the SQL server backup : 1. Open SQL Management console and connect to the database server  using windows credentials. 2. Click on Management and select Database Mail and Right+Click and Select Configure Database Mail.   3. Click Next and Select Configuration Task  select Set up Database Mail by performing the following tasks:     4. Create a New Profile   Specify the profile name and description.   5. Click Add in SMTP accounts.   6. Give any Account  name and Provide the E-mail Address from which account you want to send the emails. 7. Give any Display name to identify the Job 8. Provide the SMPT Server name and specify the port (Ex: 127.0.0.1 for local loop back address as I am using my SQL server as SMTP server) 9. Click Ok, and Click Next. 10. Select the profile SQL Job Notification as Public and Select Default Profile as Yes   11. Click Next . 12. Select Database Mail Exe

Creating Database Backup Plans   1. Create the folders to store the Database backups in the drive, we want to store with the following folders.(Ex: C:\SQL_backup\Full) 2. Open SQL Server Management Studio and connect using windows credentials. 3. Click Management   and select Maintenance Plans .   4. Right-Click on Maintenance Plans and select New Maintenance Plans , Give naming Convention as Full_DB_Backup . 5. In Left side of Server Management Studio there will be a Tool Box within Maintenance Plan Tasks 6. Drag and drop the Back Up Database Task and Maintenance Clean Task in the Full_DB_Backup Design area . 7. Right Click on the Back Up Database Task and select Edit .   8. Select the Type of Backup you want to do as Full   9. Select the Database In Database(s)   in these database list (Ex: TestDB.)     10. Select Create a Backup file for every database , select Create a sub-directory for each database 11. Give the path where w