Learn SQL With Bru
Bru Medishetty's SeekWell Blogs
Backup Information for a Database
Mar 16th
How do I find the backup information about a particular database ?
Some one asked me this question in the past and since it is being repeatedly asked in forums and to me personally , I decided to write this.
Having the backup information of a database is very important for a database administrator. In case of disaster, the DBA has to be aware when was the last time that database was backed up. If a SQL Server is inherited from another Admin and the schedule of the backups and the backup types performed is not known it is easy to find by querying the backup related tables in MSDB.
One of those tables is msdb.dbo.backupset. It has the backup information related to all databases on that instance. information such as backup size, backup type and the start and end time of the backup to name a few.
The following script is one such script that gives the backup info of a specific database with the most recent one first.
The value in bkup_type column indicates the type of backup performed. D indicates FULL backup, I indicates Differential and L indicates Log backup.
Use the following Script.
DECLARE @DBNAME VARCHAR(50)
SET @DBNAME = ‘LSWB’
SELECT CEILING(((backup_size / 1024)/1024)/1024) Bkup_Size_In_GB,
CEILING(((backup_size / 1024)/1024)) Bkup_Size_In_MB, [type] Bkup_Type,
backup_start_date StartTime,backup_finish_date
FROM msdb.dbo.backupset
WHERE DATABASE_NAME = @DBNAME
ORDER BY backup_start_date DESC
– Bru Medishetty
Listed in SQL PASS Blog Directory
Mar 13th
I wanted to share some news about this Blog. Last week this blog was added to the list of blogs in SQL PASS Blog Directory.
I am thrilled to have this blog listed in it, since the list contains highly regarded SQL Server experts. Follow the Blog Directory at SQL PASS here http://www.sqlpass.org/Community/BlogDirectory.aspx
– Bru Medishetty
Finding Shared Drives on a Cluster
Mar 10th
When working in a clustered environment, it is a common practice to open the Cluster Administrator to look at the shared drives for the SQL Server instance. This can be performed from one of the participating nodes in the cluster. Using SQL Server Dynamic Management View sys.dm_io_cluster_shared_drives we can query and retrieve the shared drives for the SQL Server.
Note that the DMV returns only one column DriveName. The script returns no values when run on a standalone installation of SQL Server.
For the convenience of users, the script can be copied from the text at the end of the post.
In the above image, there are 7 rows indicating the 7 shared drives for the SQL Server fail-over cluster.
select * from sys.dm_io_cluster_shared_drives
– Bru Medishetty
Recent Comments