select @@servernameASServerName
Select @@VERSIONAsSQLServerVerjion
go
selectserverproperty(‘Edition’)AsServerEdition
selectserverproperty(‘ProductLevel’)ServerProductLevel
selectserverproperty(‘BuildClrVersion’)BuildVersion
list down all SQL Server backups
Dwenload Script : http://hotfile.com/dl/149375389/c747113/List_Dowen_SQL_Server_Backups.rar.html
/*——————————————————————————————————————————-
— Description : This SQL Script will list down all SQL Server backups which were not backup in last 2 days
— Copyright 2012 – DBATAG
— Author : DBATAG
— Created on : 02/06/2012
— Version : 1.1
— Dependencies :
— Compatibility : This will work on SQL Server 2000, 2005, 2008, 2008 R2 and even in SQL Server 2012
—————————————————————————————————————————-*/
SELECT
CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,
bs.database_name,
MAX(bs.backup_finish_date) AS last_db_backup_date,
DATEDIFF(hh, MAX(bs.backup_finish_date), GETDATE()) AS [Backup Age (Hours)]
FROM msdb.dbo.backupset bs
WHERE bs.type = ‘D’
GROUP BY bs.database_name
HAVING (MAX(bs.backup_finish_date) < DATEADD(hh, – 48, GETDATE()))
UNION
–Databases without any backup history
SELECT
CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,
master.dbo.sysdatabases.NAME AS database_name,
NULL AS [Last Data Backup Date],
9999 AS [Backup Age (Hours)]
FROM
master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset bs
ON master.dbo.sysdatabases.name = bs.database_name
WHERE bs.database_name IS NULL AND master.dbo.sysdatabases.name <> ‘tempdb’
ORDER By bs.database_name
————————————————————————————————————————————————
/*——————————————————————————————————————————-
Description : This SQL Script will list down all SQL Server backups which were taken in last 10 days with details
— Copyright 2012 – DBATAG
— Author : DBATAG
— Created on : 02/06/2012
— Version : 1.0
— Dependencies :
— Compatibility : This will work on SQL Server 2000, 2005, 2008, 2008 R2 and even in SQL Server 2012
—————————————————————————————————————————-*/
SELECT
CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
bs.expiration_date,
CASE bs.type
WHEN ‘D’ THEN ‘Full Database’
WHEN ‘L’ THEN ‘Log’
WHEN ‘I’ THEN ‘Differential’
WHEN ‘F’ THEN ‘File Level’
WHEN ‘G’ THEN ‘File Level Differential’
WHEN ‘P’ THEN ‘Partial’
WHEN ‘Q’ THEN ‘Differential partial’
END AS backup_type,
convert(varchar,cast(bs.backup_size/1024/1024 as money),10) as ‘Backup Size in MB’,
bmf.logical_device_name,
bmf.physical_device_name,
bs.name AS backupset_name,
bs.description
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
WHERE (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() – 10)
ORDER BY
bs.database_name,bs.backup_finish_date
Sequences in SQL Server 2012
introducation
you can easily generate auto-incrementing numbers With Sequence, it will be a different object which you can attach to a table column while inserting
Creating a Sequence in SQL Server Coding
/****** Create Sequence Object ******/
CREATE SEQUENCE SEquence2012
START WITH 1
INCREMENT BY 2;
/****** Create Table ******/
Create TABLE Employee
( ID int NOT NULL PRIMARY KEY,
FullName nvarchar(100) NOT NULL );
/****** Insert Some Data ******/
INSERT Employee (ID, FullName)
VALUES (NEXT VALUE FOR SEquence2012, ‘Mostafa’),
(NEXT VALUE FOR SEquence2012, ‘Mohamed’),
(NEXT VALUE FOR SEquence2012, ‘Elmasry’);
go
Creating a Sequence in SQL Server Wizerd
OFFSET … FETCH NEXT IN SQL SERVER 2012 RC0
This is New Feature in SQL Server 2012 i see this feature is very good .
OFFSET command —- provides a starting point for the SELECT statement , Form where the Select will begin
FETCH command —– provides how many records to return at a time
in SQL Sever 2012
Use AdventureWork
GO
SELECT Name,ModifiedDate FROM Production.Culture
ORDER BY name
offset 2 rows ——- Select the rows from tha table expected the 2 rows
FETCH NEXT 3 ROWS ONLY — show the first 3 rows from the result return after the OFFSET Command
IF you want to make this statment in Oldest Version you will make it like this
Use AdventureWork
GO
SELECT top 3 Name,ModifiedDate FROM Production.Culture
where name not in (‘Arabic’,’Chinese’)
ORDER BY name
DB Cloud TECH 