Read XMLColumn value and Get it in Table format

for Example the XML value is Like :


bbb
vvv
aaa


SELECT r1.value ('./@id', 'varchar(100)') as Attribute, '' as Value
FROM [Table Name] CROSS APPLY [ColumnName].nodes ('Root/D') AS x (r1)

SQL Function for Comma seperator value to return in Table format

Create FUNCTION List_to_vc_table
(@list varchar(8000))
RETURNS @tbl Table (number varchar(1000) not null)
AS
--Returns The Given Comma Specified list in Varchar Table
BEGIN
DECLARE @ID varchar(20), @Pos int

SET @list = LTRIM (RTRIM (@list) )+ ','
SET @Pos = CHARINDEX(',' , @list, 1)
IF REPLACE (@list, ',', ' ') <> ''
BEGIN
WHILE @Pos >0
BEGIN
SET @ID = LTRIM(RTRIM(LEFT(@list, @Pos - 1)))
IF @ID <> ' '
BEGIN
INSERT @tbl (number) values (@ID)
END
SET @list = RIGHT(@list, LEN (@list) - @Pos)
SET @Pos = CHARINDEX(',' , @list, 1)
END
END
return
END

XML Serialization/Deserialization of Collection of Objects

• Make one sEmployee Class with their Property. And one Employeecollection class of that sEmployee Class.

public class sEmployee
{
public string FirstName;
public string LastName;
public int Age;
public sEmployee()
{
}
public sEmployee(string first, string last, int age)
{
this.FirstName = first;
this.LastName = last;
this.Age= age;
}
}

• Implement the CollectionBase for adding multiple employee item in collection by override their Add Method.

public class EmployeeCollection : CollectionBase
{
public EmployeeCollection() { }
public EmployeeCollection(EmployeeCollection value) { this.AddRange(value); }
public EmployeeCollection(sEmployee[] value) { this.AddRange(value); }
public sEmployee this[int index] { get { return ((sEmployee)(List[index])); } set { List[index] = value; } }
public int Add(sEmployee value) { return List.Add(value); }
public void AddRange(sEmployee[] value) { for (int i = 0; i < i =" 0;">

• Now create one sample page for serialization and Deserialization.here i have took one .aspx page and code behind i have wrote this simple code,and saving that data into XML file.

Serialization :

EmployeeCollection empCollection = new EmployeeCollection();
empCollection.Add(new sEmployee("Paresh0", "Patel0", 20));
empCollection.Add(new sEmployee("Paresh1", "Patel1", 21));
empCollection.Add(new sEmployee("Paresh2", "Patel2", 22));

string op = string.Empty;
using (StringWriter writer = new StringWriter(new StringBuilder()))
{
XmlSerializer xs = new XmlSerializer(typeof(EmployeeCollection));
xs.Serialize(writer, empCollection);
op = writer.ToString();
}
File.WriteAllText("c:\\emp.xml", op);

Deserialization :

string ip = string.Empty;
EmployeeCollection collection = new EmployeeCollection();
ip = File.ReadAllText("c:\\emp.xml");
using (StringReader reader = new StringReader(ip))
{
XmlSerializer xs = new XmlSerializer(typeof(EmployeeCollection));
collection = (EmployeeCollection)xs.Deserialize(reader);
}

Get Comma Seperator Value in one Filed

select STUFF ( (SELECT ',' + [Column_name]
FROM [TableName] WHERE [Criteria]
FOR XML PATH('')), 1, 1, '') AS [DIsplay Name]

Automatically Restore Database ( Backup databse)

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE [dbo].[DBA_Recovery_FULL]
as
/*
Table Used :- master.dbo.sysdatabases
*/
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----Restore Full Backup of OldDatabase Database to another Database
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Begin
declare
@spid as nvarchar(4),
@killcommand as nvarchar(255) ,@unc as nvarchar(255)
while (select count(*) from master.dbo.sysprocesses pc
inner join master.dbo.sysdatabases sd on pc.dbid=sd.dbid
where pc.loginame<>'sa' and pc.spid <> @@SPID and sd.name = 'NewDatabase')<>0
BEGIN
set @spid = (select top 1 pc.spid from master.dbo.sysprocesses pc
inner join master.dbo.sysdatabases sd on pc.dbid=sd.dbid
where pc.loginame <> 'sa' and pc.spid <> @@SPID and sd.name = 'NewDatabase')
set @killcommand = 'kill ' + @spid
--print 'Killing ' + @spid
exec(@killcommand)
END

SELECT top 1 @unc = M.physical_device_name
FROM [msdb].[dbo].[backupset] S
JOIN [msdb].[dbo].[backupmediafamily] M ON M.media_set_id=S.media_set_id
WHERE S.database_name = 'OldDatabse'
and S.type ='D'
and M.physical_device_name like 'S:\TempBackup\Full_Backup%'
and convert(char(10),S.backup_start_date,120) = convert(char(10),getdate() ,120)
order by s.backup_finish_date desc

restore database NewDatabase
from disk = @unc
with replace,
move 'oldDatabase' to 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\NewDatabse.mdf',
move 'oldDatabase_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\NewDatabase_log.ldf'

ENd

Sql Server (Differential Backup)

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE [dbo].[DBA_Backup_DIFFERENTIAL]
as
/*
Table Used :- master.dbo.sysdatabases
*/
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----Create Backup of Specified Database and Delete Backup files older than two days
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
begin
Set NoCount On
Declare
@vCount Int,
@vNumDBs Int,
@vDBName Varchar(255),
@vBackupPath Varchar(255),
@vFileName Varchar(100),
@vBackupString Varchar(1000),
@vDeleteString Varchar(1000),
@vNewPath Varchar(1000),
@vErrorString Varchar(1000),
@vDate varchar(10),
@vTime varchar(5) ;
select @vTime = replace(convert(varchar(5), getdate(),114),':','_');
select @vDate = convert(varchar(10), getdate(),112) ;

Set @vBackupPath = 'S:\TempBackup\Diff_Backup' --path where Backup file is going to be Created

Declare
@vDBList Table(
DBID INT NOT NULL IDENTITY(1, 1),
DatabaseName Varchar(256)
)
Insert Into @vDBList
Select Name From master.dbo.SysDatabases where dbid in (7) --List of Backup Database IDS
Set @vNumDBs = @@RowCount
Set @vCount = 1
While @vCount <= @vNumDBs
Begin
Select @vDBName = DatabaseName From @vDBList Where DBID = @vCount
Set @vNewpath = @vBackupPath --+ '\' --+ @vDBName
Set @vDeleteString = 'Del "' + @vNewPath + '\'+ @vDBName + '_' + Convert(Varchar(8), DateAdd(Day, -1, GetDate()), 112) + '_' + @vTime + '_Diff' + '.bak"' + ' /F /Q'
print @vDeleteString
Exec xp_CMDShell @vDeleteString, NO_OUTPUT
Set @vFileName = @vDBName + '_' + @vDate + '_' + @vTime + '_Diff' +'.bak'
Set @vBackupString = 'BACKUP DATABASE [' + @vDBName + '] TO DISK = ''' + @vNewPath + '\'+@vFileName +
''' WITH DIFFERENTIAL , NOFORMAT , NAME = N''' + @vDBName + ''', SKIP, REWIND, NOUNLOAD, STATS = 10'
Print @vBackupString
Exec (@vBackupString)
-- If @@Error <> 0
-- Begin
-- Set @vCount = @vNumDBs
-- Set @vErrorString = 'net send * SQL Backup failed on (' + @vDBName + ')! Please view event log!'
-- ---Exec XP_CMDShell @vErrorString
-- End
Set @vCount = @vCount + 1
End
END

Sql Server (Automatic Database Backup)

Automatic Full Backup on Schedule.

1. Create This stored procedure in Master database.
2. Create one job in sql server agent in steps put this sp as command with exec .
3. make schedule for that.
4. write sp_configure 'xp_cmdshell' ,1 and execute it.
   

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Create PROCEDURE [dbo].[Temp_Beckup_copy]
as
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----Create Backup of Specified Database and Delete Backup files older than two days
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
begin
Set NoCount On
Declare
@vCount Int,
@vNumDBs Int,
@vDBName Varchar(255),
@vBackupPath Varchar(255),
@vFileName Varchar(100),
@vBackupString Varchar(1000),
@vDeleteString Varchar(1000),
@vNewPath Varchar(1000),
@vErrorString Varchar(1000)
Set @vBackupPath = 'D:\TempBackup\Build' --path where Backup file is going to be Created
--Set @vBackupPath = 'c:' --path where Backup file is going to be Created
Declare
@vDBList Table(
DBID INT NOT NULL IDENTITY(1, 1),
DatabaseName Varchar(256)
)
Insert Into @vDBList
Select Name From master.dbo.SysDatabases where dbid in (10) --List of Backup Database IDS
Set @vNumDBs = @@RowCount
Set @vCount = 1
While @vCount <= @vNumDBs
Begin
Select @vDBName = DatabaseName From @vDBList Where DBID = @vCount
Set @vNewpath = @vBackupPath --+ '\' --+ @vDBName
Set @vDeleteString = 'Del "' + @vNewPath + '\'+ @vDBName + '' + Convert(Varchar(8), DateAdd(Day, -4, GetDate()), 112) + '.bak"' + ' /F /Q'
-- print @vDeleteString
Exec xp_CMDShell @vDeleteString, NO_OUTPUT
Set @vFileName = @vDBName + '' + CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak'
Set @vBackupString = 'BACKUP DATABASE [' + @vDBName + '] TO DISK = ''' + @vNewPath + '\'+@vFileName +
''' WITH NOFORMAT, NOINIT, NAME = N''' + @vDBName + ''', SKIP, REWIND, NOUNLOAD, STATS = 10 , COPY_ONLY '
-- Print @vBackupString
Exec (@vBackupString)
-- If @@Error <> 0
-- Begin
-- Set @vCount = @vNumDBs
-- Set @vErrorString = 'net send * SQL Backup failed on (' + @vDBName + ')! Please view event log!'
-- ---Exec XP_CMDShell @vErrorString
-- End
Set @vCount = @vCount + 1
End
END