USE DATABASE;
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
DBCC CHECKIDENT (''?'', RESEED, 1)'
GO
IF you want to make it Cursor Follw me on this Link
https://mostafaelmasry.wordpress.com/2011/11/30/reset-the-identity-coulmn/
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
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
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
Introduction
DAC [Dedicated Administrator Connection ] is feature added from SQL Server 2005 version . Database administrator use this fetaure to connect to a SQL Server instance When the database engine not responding to the regular connection so the database administrator use this fetaure to connect to the instance to solve the problem Instead of rebooting the SQL Server.
By Defult this feature is disable in All SQL Server version From 2005 to the New version 2012.
Note : your SQL Server browser must be Runing
enable the DAC using below T-SQL command
Use master
GO
sp_configure ‘show advanced options’ , 1
GO
sp_configure ‘remote admin connections’, 1
G O RECONFIGURE
GO
enable the DAC using SSMS
1- write click on your SQL Server Probirties >> Factes >> Service Area Configuration >> RemoteDacEnabled
Change it from False To True
Now try to connect by write ADMN: before your instance name like this
ADMIN:Mostafa-PC\SQLSERVER2008
then your User name and your password or connect windows Authentication but if you try to connect by the default way SQL Server will give you this Error
Dedicated administrator connections are not supported. (ObjectExplorer)
So if you want to connect with this Feature you must make this steps
Go to File >>> New >>> Database Engine Query >>>> Admin:InstanceName
then your Session will open
Note : You can not open 2 Session
DB Cloud TECH 