Twitter

Follow SQLQuill on Twitter

Subscribe

MN Pass

How to: Find the backup history of a DB – TSQL Style

There’s been many times I’ve tried to find the backup history of a database.  Questions like when was the last backup taken?  Where was it backed up to?  etc.  I googled a script for this and I used MSSQLTips as a baseline – found here – then altered it to this:

Declare @DBName varchar(255)

Set @DBName = '<<Database Name Here>>'

SELECT  top 10
@@ServerName AS Server,
BUS.database_name,
BUS.backup_finish_date,
CASE BUS.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
BUS.backup_size,
BUMF.physical_device_name
FROM   msdb.dbo.backupmediafamily  BUMF
INNER JOIN msdb.dbo.backupset BUS ON BUMF.media_set_id = BUS.media_set_id
WHERE BUS.database_name = @DBName
And BUS.type = 'D'
ORDER BY BUS.backup_finish_date desc
September 23rd, 2010 | Tags: , | Category: HowTo, Scripts | Leave a comment

How to: Find the restore history of a DB – TSQL Style

There’s been many times I’ve tried to find the restore history of a database.  Questions like when was the backup taken?  Can we re-restore and re-do this?  Can we create a copy of the original database?  etc.  Most of the times I use jobs to restore, but sometimes I’ve got to use LiteSpeed, etc.  I had to find the source of a backup this morning – here’s what I came up with for a script:

Declare @DBName varchar(255)
Set @DBName = '<<Database Name Here>>'
Select top 10
     destination_database_name,
     RH.restore_date,
     BUS.server_name as 'SourceServer',
     BUS.database_name as 'SourceDB',
     BUS.backup_start_date,
     BUS.type,
     BMF.physical_device_name
from msdb.dbo.restorehistory RH
Join msdb.dbo.backupset BUS on BUS.backup_set_id = RH.backup_set_id
Join msdb.dbo.backupmediafamily BMF on BMF.media_set_id  = BUS.media_set_id
where RH.destination_database_name = @DBName
Order by RH.restore_date desc

Note – Here’s Technet’s documents on the Backup and Restore Tables in MSDB.

August 25th, 2010 | Tags: , | Category: HowTo, Scripts | Leave a comment

SQL 2008 System Generated Names for Defaults

I had to create a script for dropping constraints on a table – no problem if the constaint is a named default, but if the default has a system generated name, it gets a bit hairy.   Here’s an example of how the defaults get created:

Create table TestTable1
	(TestConst datetime default Getdate())
Go

I googled this and found Pinal Dave had a good script to find the constraints in database located here. 
Here’s what I came up with for a script to find these constraints:

Declare @TableName varchar(500),
	@ColumnName varchar(500),
	@DFName varchar(500),
	@SchemaName varchar(500),
	@SQL varchar(2000)

--Change these for the table and column you are looking for--
Set @TableName = 'TestTable1'
Set @ColumnName = 'TestConst'

SELECT OBJECT_NAME(SO.OBJECT_ID) AS NameofConstraint,
	SCHEMA_NAME(SO.schema_id) AS SchemaName,
	OBJECT_NAME(SO.parent_object_id) AS TableName,
	SC.name as ColumnName,
	SO.type_desc AS ConstraintType
FROM sys.objects SO
join sys.columns SC on SC.default_object_id = SO.object_id
WHERE SO.type_desc LIKE '%CONSTRAINT'
and SC.object_id = object_id(@TableName)
and SC.name = @ColumnName

Finally, here’s the script to show and drop it:

Declare @TableName varchar(500),
	@ColumnName varchar(500),
	@DFName varchar(500),
	@SchemaName varchar(500),
	@SQL varchar(2000)

--Change these for the table and column you are looking for--
Set @TableName = 'TestTable1'
Set @ColumnName = 'TestConst'

SELECT OBJECT_NAME(SO.OBJECT_ID) AS NameofConstraint,
	SCHEMA_NAME(SO.schema_id) AS SchemaName,
	OBJECT_NAME(SO.parent_object_id) AS TableName,
	SC.name as ColumnName,
	SO.type_desc AS ConstraintType
FROM sys.objects SO
join sys.columns SC on SC.default_object_id = SO.object_id
WHERE SO.type_desc LIKE '%CONSTRAINT'
and SC.object_id = object_id(@TableName)
and SC.name = @ColumnName

If exists (Select 1 from sys.objects SO join sys.columns SC on SC.default_object_id = SO.object_id
		where SC.object_id = object_id(@TableName)
		and SC.name = @ColumnName)
Begin
	Select @DFName = SO.name,
		@SchemaName = SCHEMA_NAME(SO.schema_id)
	from sys.objects SO join sys.columns SC on SC.default_object_id = SO.object_id
	where SC.object_id = object_id(@TableName)
	and SC.name = @ColumnName

	Select @SQL = 'ALTER TABLE ' + @SchemaName + '.' + @TableName + ' DROP CONSTRAINT ' + @DFName
	EXEC (@SQL)
End
March 11th, 2010 | Tags: , , | Category: Scripts, SQL2008 | Leave a comment