SQL backups

BACKUP DATABASE ExternusrList TO SQL2005_ExternusrList_back   WITH  INIT

 

declare @backupName varchar(50)

declare @filepath varchar(200)

set @backupName ='ProjectServer6142_' + convert(varchar,getdate(),12) + '.bak'

set @filepath = 'C:\SQL_Backups\Archeive\' + @backupName

 

EXEC sp_addumpdevice 'disk', @backupName,  @filepath

BACKUP DATABASE ProjectServer6142 TO @backupName WITH  INIT

exec sp_dropdevice @backupName

 

 Disable the trigger.

ALTER TABLE trig_example DISABLE TRIGGER trig1

 

 sp_attach, sp_detach

/* if true UPDATE STATISTICS is skipped. If false it runs*/

EXEC sp_detach_db 'databasename', 'true'

   EXEC sp_attach_db @dbname = N'testdab',

   @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\testdab.mdf',

   @filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\testdab_log.ldf';

 

CREATE PARTITION FUNCTION companies (int)

     AS RANGE LEFT FOR VALUES (1, 100, 1000);

 

To see memory allocation :

select     type, sum(multi_pages_kb)  from 

    sys.dm_os_memory_clerks 

where      multi_pages_kb != 0 

group by type

link :http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

 

to see server out of memory messages :

 

select record  from sys.dm_os_ring_buffers 

where ring_buffer_type = 'RING_BUFFER_OOM'

 

 

For the purpose of this example, before running the DMV query, we clear the buffer pool and the procedure

cache by running the following commands.

 

checkpoint

dbcc freeproccache

dbcc dropcleanbuffers

-

 

The DELAY keyword followed by a time_to_pass before completing the WAITFOR statement. The time to wait before

 completing the WAITFOR statement can be up to 24 hours.

 

The following example uses the DELAY keyword to wait for two seconds before performing a SELECT statement:

 

 Copy Code

WAITFOR DELAY '00:00:02'

SELECT EmployeeID FROM AdventureWorks.HumanResources.Employee;

 

 

The TIME keyword followed by a time_to_execute, which specifies the time that the WAITFOR statement completes.

 

The following example uses the TIME keyword to wait until 10 P.M. to perform a check of the AdventureWorks database to

make sure that all pages are correctly allocated and used:

 

 Copy Code

USE AdventureWorks;

GO

BEGIN

    WAITFOR TIME '22:00';

    DBCC CHECKALLOC;

END;

GO

 

File Group

SELECT  a.TABLE_NAME,

        CASE    WHEN a.TABLE_TYPE = 'VIEW' THEN 'View'

                ELSE 'Table'

                END,

        ISNULL(b.[name], 'NA'),

        CASE    WHEN b.indid = 0 THEN 'Table Row Data (table has no clustered index)'

                WHEN b.indid = 1 THEN 'Clustered Index (and table row data)'

                WHEN b.indid = 255 THEN 'TEXT/NTEXT/IMAGE/XML Column Data'

                ELSE 'Non Clustered Index'

        END,

        b.indid,

        FILEGROUP_NAME(b.groupid),

        b.groupid

FROM INFORMATION_SCHEMA.TABLES a WITH (NOLOCK)

        INNER JOIN sysindexes b WITH (NOLOCK)

        ON OBJECT_ID(a.TABLE_NAME) = b.[id]

WHERE INDEXPROPERTY(b.[id], b.[name], 'IsAutoStatistics') = 0

 

 

See indexes on witch group :

SELECT  a.TABLE_NAME,

       /* CASE    WHEN a.CONSTRAINT_TYPE = 'VIEW' THEN 'View'

                ELSE 'Table'

                END,*/

a.CONSTRAINT_TYPE,

        ISNULL(b.[name], 'NA'),

        CASE    WHEN b.indid = 0 THEN 'Table Row Data (table has no clustered index)'

                WHEN b.indid = 1 THEN 'Clustered Index (and table row data)'

                WHEN b.indid = 255 THEN 'TEXT/NTEXT/IMAGE/XML Column Data'

                ELSE 'Non Clustered Index'

        END,

        b.indid,

        FILEGROUP_NAME(b.groupid),

        b.groupid

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a WITH (NOLOCK)

        INNER JOIN sysindexes b WITH (NOLOCK)

        ON OBJECT_ID(a.TABLE_NAME) = b.[id]

WHERE INDEXPROPERTY(b.[id], b.[name], 'IsAutoStatistics') = 0

order by a.TABLE_NAME desc

-Anable and disable

 disable the check_sale constraint in the employee table

ALTER TABLE tessuser NOCHECK CONSTRAINT all

 enable the check_sale constraint in the employee table

ALTER TABLE tessuser CHECK CONSTRAINT all

disable index, recreate

 

The following example shows how to disable an index named IX_CustDOB on the Customer table in the Sales schema.

ALTER INDEX IX_CustDOB ON Sales.Customer DISABLE

 

The following example shows how to rebuild an index named PK_CustID on the Customer table.

ALTER INDEX PK_CustId ON Sales.Customer REBUILDThe following example shows how to rebuild all indexes on the Customer table.

 

ALTER INDEX ALL ON Sales.Customer REBUILD

Reorganizing an index The REORGANIZE clause is equivalent to the DBCC INDEXDEFRAG statement,

providing an efficient way to reorganize the index leaf level.

-moving cluster index to another file group is moving the table

Enterprise Manager moves a table from one filegroup to another without using an undocumented task,

but T-SQL doesn't have a command that does the same thing. The easiest way to move a table to another filegroup

 is to create a clustered index on the table. If the table already has a clustered index, you can use the CREATE INDEX

 command's WITH DROP_EXISTING clause to recreate the clustered index and move it to a particular filegroup.

When a table has a clustered index, the leaf level of the index and the data pages of the table essentially become one

and the same. The table must exist where the clustered index exists, so if you create or recreate a clustered index—

placing the index on a particular filegroup—you're moving the table to the new filegroup as well. . . .

 

DBCC SHOWCONTIG replaced by-

DBCC SHOWCONTIG :Displays fragmentation information for the data and indexes of the specified table

 

Important: 

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work,

 and plan to modify applications that currently use this feature. Use sys.dm_db_index_physical_stats instead.

Last Stats Date

The following example returns the date of the last time that the statistics were updated for the specified object.

GO

SELECT 'Index Name' = i.name, 'Statistics Date' = STATS_DATE(i.object_id, i.index_id)

FROM sys.objects o

JOIN sys.indexes i ON o.name = 'tessuser' AND o.object_id = i.object_id;

 

STATISTICS IO ON

to Set SET STATISTICS IO ON before query return the disk statistics

 

get disk read and write :

SELECT @@TOTAL_READ AS 'Reads', @@TOTAL_WRITE AS 'Writes', GETDATE() AS 'As of'

 

 

SQLCMD SQLCMD SQLCMD SQLCMD sql COMMAND

 

 

 

 Stop start SQL server

net start MSSQLSERVER

net stop MSSQLSERVER

 

To start the default instance of SQL Server(stop use stop )

net start MSSQLSERVER

 

To start a named instance of SQL Server(stop use stop )

net start MSSQL$instancename

 

To start SQL Server with startup options(stop use stop )

net start MSSQLSERVER /f /m

 

To pause the default instance of SQL Server(stop use stop )

net pause "SQL Server (MSSQLSERVER)"

 

To pause a named instance of SQL Server(stop use stop )

net pause MSSQL$instancename

 

To resume a paused default instance of SQL Server(stop use stop )

net continue MSSQLSERVER

 

To resume a paused named instance of SQL Server(stop use stop )

net continue MSSQL$instancename

 

To start the default instance of SQL Server Agent

net start SQLSERVERAGENT

 

To start a named instance of SQL Server Agent

net start SQLAgent$ <instancename>

 

 

 

SHOWCONTIG

 

USE tess

DECLARE @TableName sysname

DECLARE cur_showfragmentation CURSOR FOR

SELECT table_name,* FROM information_schema.tables WHERE table_type = 'base table'

OPEN cur_showfragmentation

FETCH NEXT FROM cur_showfragmentation INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

  SELECT 'Show fragmentation for the ' + @TableName + ' table'

  DBCC SHOWCONTIG (@TableName)

  FETCH NEXT FROM cur_showfragmentation INTO @TableName

END

CLOSE cur_showfragmentation

DEALLOCATE cur_showfragmentation

 

-DBREINDEX (@TableName)

CREATE PROC ind_rebuild

AS

DECLARE @TableName sysname

DECLARE cur_reindex CURSOR FOR

SELECT table_name

  FROM information_schema.tables

  WHERE table_type = 'base table'

OPEN cur_reindex

FETCH NEXT FROM cur_reindex INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

  PRINT 'Reindexing ' + @TableName + ' table'

  DBCC DBREINDEX (@TableName, ' ', 80)

  FETCH NEXT FROM cur_reindex INTO @TableName

END

CLOSE cur_reindex

DEALLOCATE cur_reindex

GO

DBCC INDEXDEFRAG (pubs, @TableName, @indid)-

You can use it while system running

 

USE tess

DECLARE @TableName sysname

DECLARE @indid int

DECLARE cur_tblfetch CURSOR FOR

SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'

OPEN cur_tblfetch

FETCH NEXT FROM cur_tblfetch INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE cur_indfetch CURSOR FOR

SELECT indid FROM sysindexes WHERE id = OBJECT_ID (@TableName) and keycnt > 0

OPEN cur_indfetch

FETCH NEXT FROM cur_indfetch INTO @indid

WHILE @@FETCH_STATUS = 0

BEGIN

  SELECT 'Derfagmenting index_id = ' + convert(char(3), @indid) + 'of the '

          + rtrim(@TableName) + ' table'

  IF @indid <> 255 DBCC INDEXDEFRAG (pubs, @TableName, @indid)

  FETCH NEXT FROM cur_indfetch INTO @indid

END

CLOSE cur_indfetch

DEALLOCATE cur_indfetch

  FETCH NEXT FROM cur_tblfetch INTO @TableName

END

CLOSE cur_tblfetch

DEALLOCATE cur_tblfetch

 

 

-

CREATE PROC AUTOAGENT

AS

EXEC XP_CMDSHELL 'NET START SQLSERVERAGENT'

go

SP_PROCOPTION AUTOAGENT, STARTUP, TRUE

 

To start the default instance of SQL Server from a command prompt

From a command prompt, enter: sqlservr.exe

 

To start a named instance of SQL Server from a command prompt

From a command prompt, enter the following command, replacing <instancename> with

the name of the instance you wish to start in the format servername$instancename.

 sqlservr.exe -s <instancename>

 

To start the default instance of SQL Server in single-user mode from a command prompt

From a command prompt, enter the following command:sqlservr.exe -m

 

To start a named instance of SQL Server in single-user mode from a command prompt

From a command prompt, enter the following command:

sqlservr.exe - m -s <instancename>

 

To start the default instance of SQL Server with minimal configuration

From a command prompt, enter the following command to start the default instance of

 SQL Server in minimum configuration mode:

sqlservr.exe -f

 

To start a named instance of SQL Server with minimal configuration

From a command prompt, enter the following command to start a named instance of SQL Server as a service:

sqlservr.exe -f -s <instancename

 

CREATE procedure sp_TECreindex

@table varchar(100) = null

/*

 Created by : Mnaouar Ben Khelifa:28 july 2006

 This procedure will Reindex all tables in the TESS database

 

*/

 

AS

set nocount on

declare @st varchar(100)

 

if isnull(@table,'')<> ''

begin

     set @st = ' ALTER INDEX ALL ON ' + @table + '  REBUILD '

   execute (@st)

end

 

else

begin

 

DECLARE @TableName sysname

DECLARE cur_reindex CURSOR FOR

SELECT table_name

  FROM information_schema.tables

  WHERE table_type = 'base table' and TABLE_SCHEMA ='dbo'

OPEN cur_reindex

FETCH NEXT FROM cur_reindex INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

  PRINT 'Reindexing ' + @TableName + ' table'

  DBCC DBREINDEX (@TableName, ' ', 80)

     set @st = ' ALTER INDEX ALL ON ' + @TableName + '  REBUILD'

   execute (@st)

 

  FETCH NEXT FROM cur_reindex INTO @TableName

END

CLOSE cur_reindex

DEALLOCATE cur_reindex

 

end