Backup and Restore split files

Sometimes we need to split backup files.

–Script to take backup

Backup Database AdventureWorks2012

To Disk = 'D:\SQL Server\AdventureWorks2012_1.bak',

Disk = 'D:\SQL Server\AdventureWorks2012_2.bak',

Disk = 'D:\SQL Server\AdventureWorks2012_3.bak'

 

This script generates 3 files backup of AdventureWorks2012 Database with equal size.

–Script to restore split file to database.

Restore Database AdventureWorks2012

From Disk = 'D:\SQL Server\AdventureWorks2012_1.bak',

Disk = 'D:\SQL Server\AdventureWorks2012_2.bak',

Disk = 'D:\SQL Server\AdventureWorks2012_3.bak'

You must follow a file sequence.

Split file backup and restore

Sometimes we need to split backup files.

Script to take backup

Backup Database AdventureWorks2012

To Disk = ‘D:\SQL Server\AdventureWorks2012_1.bak’,

Disk = ‘D:\SQL Server\AdventureWorks2012_2.bak’,

Disk = ‘D:\SQL Server\AdventureWorks2012_3.bak’

This script generates 3 files backup of AdventureWorks2012 Database with equal size.

–Script to restore split file to database.

Restore Database AdventureWorks2012

From Disk = ‘D:\SQL Server\AdventureWorks2012_1.bak’,

Disk = ‘D:\SQL Server\AdventureWorks2012_2.bak’,

Disk = ‘D:\SQL Server\AdventureWorks2012_3.bak’

You must follow a file sequence.

Get data from mirrored database

CREATE DATABASE NewDatabase
ON (NAME = 'Your Database Name', FILENAME = 'C:\Backups\NewDatabase.SNP')
   AS SNAPSHOT OF YourDatabaseName
GO
Select * from NewDatabase.dbo.test
GO

Get Port Number for all Instance

Set NoCount On
Declare @CurrID int,@ExistValue int, @MaxID int, @SQL nvarchar(1000)
Declare @TCPPorts Table (PortType nvarchar(180), Port int)
Declare @SQLInstances Table (InstanceID int identity(1, 1) not null primary key,
InstName nvarchar(180),
Folder nvarchar(50),
StaticPort int null,
DynamicPort int null,
Platform int null);
Declare @Plat Table (Id int,Name varchar(180),InternalValue varchar(50), Charactervalue varchar (50))
Declare @Platform varchar(100)
Insert into @Plat exec xp_msver platform
select @Platform = (select 1 from @plat where charactervalue like ‘%86%’)
If @Platform is NULL
Begin
Insert Into @SQLInstances (InstName, Folder)
Exec xp_regenumvalues N’HKEY_LOCAL_MACHINE’,
N’SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL’;
Update @SQLInstances set Platform=64
End
else
Begin
Insert Into @SQLInstances (InstName, Folder)
Exec xp_regenumvalues N’HKEY_LOCAL_MACHINE’,
N’SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL’;
Update @SQLInstances Set Platform=32
End

Declare @Keyexist Table (Keyexist int)
Insert into @Keyexist
Exec xp_regread’HKEY_LOCAL_MACHINE’,
N’SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL’;
select @ExistValue= Keyexist from @Keyexist
If @ExistValue=1
Insert Into @SQLInstances (InstName, Folder)
Exec xp_regenumvalues N’HKEY_LOCAL_MACHINE’,
N’SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL’;
Update @SQLInstances Set Platform =32 where Platform is NULL

Select @MaxID = MAX(InstanceID), @CurrID = 1
From @SQLInstances
While @CurrID <= @MaxID
Begin
Delete From @TCPPorts

Select @SQL = ‘Exec xp_instance_regread N”HKEY_LOCAL_MACHINE”,
N”SOFTWARE\Microsoft\\Microsoft SQL Server\’ + Folder + ‘\MSSQLServer\SuperSocketNetLib\Tcp\IPAll”,
N”TCPDynamicPorts”’
From @SQLInstances
Where InstanceID = @CurrID

Insert Into @TCPPorts
Exec sp_executesql @SQL

Select @SQL = ‘Exec xp_instance_regread N”HKEY_LOCAL_MACHINE”,
N”SOFTWARE\Microsoft\\Microsoft SQL Server\’ + Folder + ‘\MSSQLServer\SuperSocketNetLib\Tcp\IPAll”,
N”TCPPort”’
From @SQLInstances
Where InstanceID = @CurrID

Insert Into @TCPPorts
Exec sp_executesql @SQL

Select @SQL = ‘Exec xp_instance_regread N”HKEY_LOCAL_MACHINE”,
N”SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\’ + Folder + ‘\MSSQLServer\SuperSocketNetLib\Tcp\IPAll”,
N”TCPDynamicPorts”’
From @SQLInstances
Where InstanceID = @CurrID

Insert Into @TCPPorts
Exec sp_executesql @SQL

Select @SQL = ‘Exec xp_instance_regread N”HKEY_LOCAL_MACHINE”,
N”SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\’ + Folder + ‘\MSSQLServer\SuperSocketNetLib\Tcp\IPAll”,
N”TCPPort”’
From @SQLInstances
Where InstanceID = @CurrID

Insert Into @TCPPorts
Exec sp_executesql @SQL

Update SI
Set StaticPort = P.Port,
DynamicPort = DP.Port
From @SQLInstances SI
Inner Join @TCPPorts DP On DP.PortType = ‘TCPDynamicPorts’
Inner Join @TCPPorts P On P.PortType = ‘TCPPort’
Where InstanceID = @CurrID;

Set @CurrID = @CurrID + 1
End

Select serverproperty(‘ComputerNamePhysicalNetBIOS’) as ServerName, InstName, StaticPort, DynamicPort,Platform
From @SQLInstances
Set NoCount Off

Get Next Schedule Time for Job

SELECT
JOBS.Name AS JobName,
CAST(
CONVERT(CHAR(8), next_run_date, 112) + ‘ ‘ + STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), next_run_time), 6), 5, 0, ‘:’), 3, 0, ‘:’)
AS DateTime) AS NextRunTime
FROM Msdb.dbo.SysJobs JOBS
INNER JOIN Msdb.dbo.SysJobSchedules SCHED ON JOBS.Job_Id = SCHED.Job_Id
AND JOBS.Enabled = 1 and next_run_date <> 0

Get List of Compressed Tables

–List Compressed Tables
SELECT * FROM sys.partitions WHERE data_compression <> 0

SELECT OBJECT_NAME(OBJECT_ID) AS ObjName,data_compression_desc AS CompressionType
FROM sys.partitions
WHERE OBJECTPROPERTY(OBJECT_ID,’ismsshipped’) <> 0

Direct Insert from Stored Procedure

Drop table #MyTempTable
SELECT * INTO #MyTempTable FROM OPENROWSET(‘SQLNCLI’, ‘Server=InstanceName;Trusted_Connection=yes;’,
‘EXEC sp_who’)

SELECT * FROM #MyTempTable

Find Job Name From Command

use msdb
go
select sj.name as job_name, st.command
from sysjobs sj
join sysjobsteps st
on sj.job_id = st.job_id
where st.command like ‘%Job Name%’

Type Wise Backup Count

BEGIN
SET NOCOUNT ON;
Declare @LinkedServer varchar(500)
Declare @DBName varchar(500)
Declare @InstanceName nvarchar(500)
Declare @DatabasePathMDF nvarchar(500)
Declare @DatabasePathLDF nvarchar(500)
Declare @DBSize nvarchar(500)
Declare @DBBackupSize nvarchar(500)
Declare @FirstDate DATETIME
set @FirstDate = DATEADD(DD,0,DATEDIFF(DD,0,GETDATE()))

Declare @DB_Details Table (
Instance_Name varchar(50),
Database_Name varchar(50),
Full_Backup int,
Diff_Backup int,
Trn_Backup int
)

DECLARE server_name_cursor Cursor READ_ONLY For
SELECT name from sys.servers where name like ‘LKDS[_]%’

Declare @cur_stat_Server smallint
select @cur_stat_Server = CURSOR_STATUS(‘global’,’server_name_cursor’)
if @cur_stat_Server = -1
OPEN server_name_cursor
Try_ServerName:
FETCH Next From server_name_cursor Into @LinkedServer
Begin Try

WHILE @@Fetch_status =0
Begin
Declare @Task varchar(1000)
set @Task = ‘select
(select * from openquery(‘+ @LinkedServer +’,”select @@ServerName”)) as ServerName,
bs.database_name as DBname ,
count (case type when ”D” Then 1 End)as FullBackup,
count (case type when ”I” Then 1 End)as DiffBackup,
count (case type when ”L” Then 1 End)as LogBackup
from ‘+@LinkedServer+’.msdb.dbo.backupset as bs
where DATEADD(DD,0,DATEDIFF(DD,0,backup_start_date)) = DATEADD(DD,-1,DATEDIFF(DD,0,GETDATE()))
Group by bs.database_name’
Print @Task
insert into @DB_Details
Exec (@Task)

Fetch Next from server_name_cursor Into @LinkedServer
End
End Try
Begin Catch
GoTo Try_ServerName
End Catch
CLOSE server_name_cursor
DEALLOCATE server_name_cursor

select * from @DB_Details
END

Mail Count

select
@@SERVERNAME as ServerName,
Date as MailDate,
max(totalmails) as TotalMails,
MAX(SentMails) AS SentMails,
MAX(FailedMails) AS FailedMails
from
(SELECT convert(varchar,last_mod_date,101) AS Date,
count(*) AS TotalMails,
” AS SentMails,
” AS FailedMails
from msdb..sysmail_allitems
where convert(varchar,last_mod_date,101)= convert(varchar,getdate()-1,101)
group by
convert(varchar,last_mod_date,101)
union all
SELECT convert(varchar,last_mod_date,101),
”,
sum(CASE When sent_status=’sent’ THEN 1 END ),
sum(CASE When sent_status=’failed’ THEN 1 END)
from msdb..sysmail_allitems
where convert(varchar,last_mod_date,101)= convert(varchar,getdate()-1,101)
group by Convert(varchar,last_mod_date,101),sent_status)T
Group by Date