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

Follow

Get every new post delivered to your Inbox.