Automation of taking backup of all databases within single SQL Server instance
Below scripts will enable you to take backup of every single database except Model & TempDB databases, by using cursors on it. Strongle advise not to run this with very large databases or in OLTP environments where the systems are heavily used.
DECLARE @dbName VARCHAR(50)
DECLARE @backupPath VARCHAR(256)
DECLARE @backupFileName VARCHAR(256)
SET @backupPath =
‘F:\’
DECLARE All_Backup_Cursor CURSOR FOR
SELECT Name FROM sys.databases WHERE Name NOT IN (‘Model’,’TempDB’);
OPEN All_Backup_Cursor
FETCH NEXT FROM All_Backup_Cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @backupFileName = @backupPath + @dbName
BACKUP DATABASE @dbName TO DISK = @backupFileName
FETCH NEXT FROM All_Backup_Cursor INTO @dbname
END
CLOSE All_Backup_Cursor
DEALLOCATE All_Backup_Cursor
Job Activity Monitor
After you have created your new job following this article, you can view all the new created jobs and monitor the health of the jobs like whether it succeeded or failed, how long it took to run, reasons if it has failed, what time it started and what time it completed, when is the next run scheduled, etc.,
Once you have opened your SSMS follow the JOBS section and you can see JOB ACTIVITY MONITOR once you expand it. Double click on this and you can find below screen, which gives overall status.
If you want to take further action on each job you can right click on any of the listed jobs and you will find more options on this like below screen. In this you have options to Start the job from any step level, stop the job, enable/disable the job, refresh to see the current job running status, delete the job, view the whole history of job run and properties to further edit by opening the job itself.
Create new SQL Server Job
Whatever developments you have done, may require a job to get it scheduled to ensure the data is always up to date for your reporting purpose. The schedule to update the job is achieved using SQL SERVER AGENT JOBS. Below is the screenshot that shows how you can create a new job. Open SSMS and connect to your server and navigate down to SQL Server Agent and expand it. Here you will find jobs and right click on it to get a new menu and choose “New Job…”
After this a new pop-up screen appears and you need to key in your new job name. I am using below screenshot from one my colleague’s computer and hence you see a different name on it.
In the same pop-up you have to go step by step tab in the left side. Go to steps and then provide the path to your SSIS package, but in the below example, I am giving an option to run SQL Script. And then go to schedules tab and choose how frequent you want this job to run, like hourly / daily / weekly / monthly / quarterly / yearly.
Explore DMVs
You can explore DMV’s within SQL Server Management Studio. A sample picture is provided below.
Also you can right click on any DMV’s and then select top 1000 rows how you do like every other table in your local databases. Below image provides information on selecting DM_OS_BUFFER_DESCRIPTORS DMV.
Dynamic Management Views
Microsoft have released many DMV & DMF for every release of its new SQL Server release. This has helped every administrator and developers a VERY BIG HAND in sorting out many problems. The days in writing our own scripts are now over and these DMV’s also provide support for almost all areas no matter whether it is to do with sessions, tables, indexes, Operating system, I/O. All are now taken care. Below I am providing some of the DMV’s that might be helpful for you.
• sys.dm_exec_connections
• sys.dm_exec_sessions
• sys.dm_exec_requests
• sys.dm_exec_cached_plans
• sys.dm_exec_query_plans
• sys.dm_exec_sql_text
• sys.dm_exec_query_stats
• sys.dm_db_index_physical_stats
• sys.dm_db_index_usage_stats
• sys.dm_db_index_operational_stats
• sys.dm_db_missing_index_details
• sys.dm_db_missing_index_groups
• sys.dm_db_missing_index_group_stats
• sys.dm_db_missing_index_columns
• sys.dm_os_performance_counters
• sys.dm_os_schedulers
• sys.dm_os_nodes
• sys.dm_os_waiting_tasks
• sys.dm_os_wait_stats
• sys.dm_io_virtual_file_stats
• sys.dm_io_pending_io_requests
• sys.dm_io_cluster_shared_drives
Change Database Recovery Model
In this post, I am going to provide a simple demo on how to change the Database Recovery Model. For this example I have chosen the AdventureWorkdDW2008R2 database and you can download it from here.
Once you have opened your SQL Server Management Studio, expand the databases and right click on our AdventureWorkdDW2008R2 database and choose properties. The below image will appear. In here go to the Options tab from the left and then you can see “Recovery Model” options which has Full, Bulk-logged, Simple. You can simply select the required recovery model and click on OK button at the bottom of the pop-up box. That’s it, your recovery model will be changed.
Differential Database Backup
This post is to cover differential database backup in Microsoft SQL Server. You can try this in your local system and for this demo, I am using AdventureWorksDW2008R2 database. You can download this from this link.
In the below image, you can see the database backup options that are available by navigating to the Database and then right click and choose Tasks => Back Up…
Following this, a new pop-up appears which gives much options to choose the available backup options. In here, I have highlighted “Backup type” and selected “Differential”. If you click on the drop down box, you will then see options for other types of backups.
In “Backup set”, I am giving a name for the differential backup. And then at last choosing the “Destination”, where the backup file will be placed. In here you also have options to Add new location, remove the default location.
Also you can use below T-SQL scripts to take backups instead of using GUI interface.
BACKUP DATABASE [AdventureWorksDW2008R2]
TO DISK = N’F:\\AdventureWorksSimpleRM.bak’ WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N’AdventureWorksDW2008R2-Differential Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
NOTE: Full Database backup should be taken before taking differential backup.
Give a try at your end.
Full Database Backup
This post is to cover some of the backup options that we have. You can try this in your local system and for this demo, I am using AdventureWorksDW2008R2 database. You can download this from this link.
In the below image, you can see the database backup options that are available by navigating to the Database and then right click and choose Tasks => Back Up…
Following this, a new pop-up appears which gives much options to choose the available backup options. In here, I have highlighted “Backup type” and you see “Full” is selected. If you click on the other options you will then see options for other types of backups to be chosen.
Then I have highlighted “Backup set”, which means I am giving a name for it. And then at last choosing the “Destination”, where the backup file will be placed. In here you also have options to Add new location, remove the provided default location.
You can also use below T-SQL Scripts to do the same manually instead of using GUI screens.
–Syntax for Full Database Backup
BACKUP DATABASE databasename
TO backup_device [ ,…n ] –Device and Location
[ WITH with_options [ ,…o ] ] ;
–Simple Script to take Full Database Backup
BACKUP DATABASE [AdventureWorksDW2008R2]
TO DISK = ‘F:\AdventureBackup.bak’
GO
–Craete a Full database backup by using WITH FORMAT to overwrite any exisiting backups and Create a new media set
BACKUP DATABASE [AdventureWorksDW2008R2]
TO DISK = ‘F:\\AdventureWorksSimpleRM.bak’
WITH FORMAT;
GO
Happy going.