Check out the virtual lab over here:
Virtual Lab: What's New in SQL Server 2008 for Database Administrators
Virtual Lab - What's new in SQL Server 2008 for Database Administrators
Microsoft has released a new virtual lab for Sql Server 2008 Administrators. As mentioned in the technet site, "In this virtual lab you will be introduced to some of the new features for database administrators in SQL Server 2008. You will work with the new date data type, implement a DMF policy and look at new features added to database mirroring. You will also implement and work with table valued parameters and look at database changes using change data capture (CDC)."
Check out the virtual lab over here:
Virtual Lab: What's New in SQL Server 2008 for Database Administrators
Check out the virtual lab over here:
Virtual Lab: What's New in SQL Server 2008 for Database Administrators
Stored Procedure creation and updated date using Sql Server 2005
In order to find out the Creation and Updation date of a stored procedure, use this query :
-- Find Creation and Modification Date of all stored procs
USE Northwind;
GO
SELECT [Name] as StoredProcName,
type_desc as TypeDesc,
create_date as CreationDate,
modify_date as ModificationDate
FROM sys.objects
WHERE [TYPE] = 'P'
-- Find Creation and Modification Date of a particular stored proc
USE Northwind;
GO
SELECT [Name] as StoredProcName,
type_desc as TypeDesc,
create_date as CreationDate,
modify_date as ModificationDate
FROM sys.objects
WHERE TYPE = 'P'
AND NAME = 'CustOrderHist'
GO
Similary to find out creation and modification date of tables, use TYPE = 'U'
-- Find Creation and Modification Date of all stored procs
USE Northwind;
GO
SELECT [Name] as StoredProcName,
type_desc as TypeDesc,
create_date as CreationDate,
modify_date as ModificationDate
FROM sys.objects
WHERE [TYPE] = 'P'
-- Find Creation and Modification Date of a particular stored proc
USE Northwind;
GO
SELECT [Name] as StoredProcName,
type_desc as TypeDesc,
create_date as CreationDate,
modify_date as ModificationDate
FROM sys.objects
WHERE TYPE = 'P'
AND NAME = 'CustOrderHist'
GO
Similary to find out creation and modification date of tables, use TYPE = 'U'
Insert a value into Identity Column
When columns have the identity property set on them, the value automatically increases on every row, depending on the seed and incrementing value.
So let us create a table with an identity column
CREATE TABLE [dbo].[TableA](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SomeName] [char](10) NULL
) ON [PRIMARY]
And now let us add some data in that table:
INSERT TableA(SomeName) VALUES ('A')
INSERT TableA(SomeName) VALUES ('B')
INSERT TableA(SomeName) VALUES ('C')
INSERT TableA(SomeName) VALUES ('D')
If you say
SELECT * FROM TableA ; you will see that the ID column contains value from 1 to 4.
Now let us say that the user goes ahead and deletes ID 2
DELETE FROM TableA where ID = 2 ;
If you now do a SELECT * FROM TableA
There is a gap in between ID 1 and 3. If you try to explicitly add a value to the identity column to fill up the gap using the statement:
INSERT TableA(ID,SomeName) VALUES(2,'R')
You will get an error “Cannot insert explicit value for identity column in table 'TableA' when IDENTITY_INSERT is set to OFF”
In order to insert a value into an Identity column, set Identity to ON. Use the code below to do so:
SET IDENTITY_INSERT TableA ON
INSERT TableA(ID,SomeName) VALUES(2,'R')
SET IDENTITY_INSERT TableA OFF
Setting Identity_Insert to ON removes this check. Once you have inserted the record, set it back to OFF again.
So let us create a table with an identity column
CREATE TABLE [dbo].[TableA](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SomeName] [char](10) NULL
) ON [PRIMARY]
And now let us add some data in that table:
INSERT TableA(SomeName) VALUES ('A')
INSERT TableA(SomeName) VALUES ('B')
INSERT TableA(SomeName) VALUES ('C')
INSERT TableA(SomeName) VALUES ('D')
If you say
SELECT * FROM TableA ; you will see that the ID column contains value from 1 to 4.
Now let us say that the user goes ahead and deletes ID 2
DELETE FROM TableA where ID = 2 ;
If you now do a SELECT * FROM TableA
There is a gap in between ID 1 and 3. If you try to explicitly add a value to the identity column to fill up the gap using the statement:
INSERT TableA(ID,SomeName) VALUES(2,'R')
You will get an error “Cannot insert explicit value for identity column in table 'TableA' when IDENTITY_INSERT is set to OFF”
In order to insert a value into an Identity column, set Identity to ON. Use the code below to do so:
SET IDENTITY_INSERT TableA ON
INSERT TableA(ID,SomeName) VALUES(2,'R')
SET IDENTITY_INSERT TableA OFF
Setting Identity_Insert to ON removes this check. Once you have inserted the record, set it back to OFF again.
Compare data between two tables
The 'tablediff' utility is a new feature in SQL Server 2005 used to compare differences of schema and data between source and destination table or view. This utility is particularly useful while replicating data.
You can run this utility from the command line or a batch file.
Example:
Let us use the Customers table of the Northwind database as an example. Do the following :
1. Create another table called CustomerTemp.
SELECT * INTO CustomersTemp FROM Customers
2. Now change data in the CustomersTemp table
UPDATE CustomersTemp
SET City = 'Bern'
WHERE CUSTOMERID = 'ALFKI'
3. Run the TableDiff utility
The syntax for running this utility is as follows :
C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "SUPROTIM"-sourcedatabase "Northwind" -sourcetable "Customers" -destinationserver "SUPROTIM" -destinationdatabase "Northwind" -destinationtable "CustomersTemp"
where SUPROTIM is the servername, Northwind is the database, Customers is the source table and CustomerTemp is the destination table
The output is:
Microsoft (R) SQL Server Replication Diff ToolCopyright (C) 1988-2005 Microsoft Corporation. All rights reserved.
User-specified agent parameter values:-sourceserver SUPROTIM-sourcedatabase Northwind-sourcetable Customers-destinationserver SUPROTIM-destinationdatabase Northwind-destinationtable CustomersTemp
Table [Northwind].[dbo].[Customers] on SUPROTIM and Table [Northwind].[dbo].[CustomersTemp] on SUPROTIM have 1 differences.Err CustomerIDMismatch 'ALFKI'The requested operation took 0.244125 seconds.
If you want to generate the SQL scripts for the differences found, use the -f parameter with the file name:
Example :
C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "SUPROTIM"-sourcedatabase "Northwind" -sourcetable "Customers" -destinationserver "SUPROTIM" -destinationdatabase "Northwind" -destinationtable "CustomersTemp" -f "C:\temp"
You can run this utility from the command line or a batch file.
Example:
Let us use the Customers table of the Northwind database as an example. Do the following :
1. Create another table called CustomerTemp.
SELECT * INTO CustomersTemp FROM Customers
2. Now change data in the CustomersTemp table
UPDATE CustomersTemp
SET City = 'Bern'
WHERE CUSTOMERID = 'ALFKI'
3. Run the TableDiff utility
The syntax for running this utility is as follows :
C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "SUPROTIM"-sourcedatabase "Northwind" -sourcetable "Customers" -destinationserver "SUPROTIM" -destinationdatabase "Northwind" -destinationtable "CustomersTemp"
where SUPROTIM is the servername, Northwind is the database, Customers is the source table and CustomerTemp is the destination table
The output is:
Microsoft (R) SQL Server Replication Diff ToolCopyright (C) 1988-2005 Microsoft Corporation. All rights reserved.
User-specified agent parameter values:-sourceserver SUPROTIM-sourcedatabase Northwind-sourcetable Customers-destinationserver SUPROTIM-destinationdatabase Northwind-destinationtable CustomersTemp
Table [Northwind].[dbo].[Customers] on SUPROTIM and Table [Northwind].[dbo].[CustomersTemp] on SUPROTIM have 1 differences.Err CustomerIDMismatch 'ALFKI'The requested operation took 0.244125 seconds.
If you want to generate the SQL scripts for the differences found, use the -f parameter with the file name:
Example :
C:\Program Files\Microsoft SQL Server\90\COM>tablediff -sourceserver "SUPROTIM"-sourcedatabase "Northwind" -sourcetable "Customers" -destinationserver "SUPROTIM" -destinationdatabase "Northwind" -destinationtable "CustomersTemp" -f "C:\temp"
Free introductory SQL Server 2008 learning materials
Some SQL Server 2008 training material has been released by Microsoft. Check them out over here :
Free Sql Server 2008 E-book
Microsoft E-Learning Clinic
Sql Server 2008 Classroom Training
Download the SQL Server 2008 November CTP
Free Sql Server 2008 E-book
Microsoft E-Learning Clinic
Sql Server 2008 Classroom Training
Download the SQL Server 2008 November CTP
How to find out the Sql Server version and service pack
@@VERSION returns information about the version, processor, build date, copyright info and the operating system of the current SQL Server installation
SELECT @@VERSION as VERSION
returns 'Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 6.0 (Build 6000: ) ' on my machine
SERVERPROPERTY displays information about the server instance
We can use the SERVERPROPERTY with the 'propertyname' to get information about the SQL SERVER instance.
For eg:
To get the product version, use :
SELECT SERVERPROPERTY('productversion') as ProductVersion
returns '9.00.3042.00' on my machine
To get the Service Pack information or the level of version, use :
SELECT SERVERPROPERTY ('productlevel') as Level
returns 'SP2' on my machine
To get the product edition, use :
SELECT SERVERPROPERTY('edition') as EDITION
returns 'Developer Edition' on my machine
Similary you can retrieve the LicenseType, MachineName, Number of client licenses on the current instances, servername and many more property information about the current sql server instance.
Check a list of other properties over here.
SELECT @@VERSION as VERSION
returns 'Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 6.0 (Build 6000: ) ' on my machine
SERVERPROPERTY displays information about the server instance
We can use the SERVERPROPERTY with the 'propertyname' to get information about the SQL SERVER instance.
For eg:
To get the product version, use :
SELECT SERVERPROPERTY('productversion') as ProductVersion
returns '9.00.3042.00' on my machine
To get the Service Pack information or the level of version, use :
SELECT SERVERPROPERTY ('productlevel') as Level
returns 'SP2' on my machine
To get the product edition, use :
SELECT SERVERPROPERTY('edition') as EDITION
returns 'Developer Edition' on my machine
Similary you can retrieve the LicenseType, MachineName, Number of client licenses on the current instances, servername and many more property information about the current sql server instance.
Check a list of other properties over here.
How to find out if the logged in user is a sysadmin member
'sysadmin' is one of the fixed server roles. As stated in SQL Server 2005 Books Online, each member of a fixed server role can add other logins to that same role.
The following query returns the list of available fixed server roles
EXEC sp_helpsrvrole;
Members belonging to the sysadmin server role have the permissions to perform any activity in the server.
In order to find out if the logged in user is a sysadmin:
SELECT IS_SRVROLEMEMBER( 'sysadmin', '' );
Returns 1 if user is a sysadmin, 0 if user is not.
In order to find out the permissions associated with the sysadmin fixed server role:
EXEC sp_srvrolepermission 'sysadmin';
The following query returns the list of available fixed server roles
EXEC sp_helpsrvrole;
Members belonging to the sysadmin server role have the permissions to perform any activity in the server.
In order to find out if the logged in user is a sysadmin:
SELECT IS_SRVROLEMEMBER( 'sysadmin', '
Returns 1 if user is a sysadmin, 0 if user is not.
In order to find out the permissions associated with the sysadmin fixed server role:
EXEC sp_srvrolepermission 'sysadmin';
How to back up a database using script in SQL SERVER 2005
Use this script in order to backup a database :
/*
Author : Suprotim Agarwal
Creation Date: Jan 2, 2008
*/
CREATE PROCEDURE [dbo].[usersp_BackUpDB]
@DBNAME nvarchar(50),
@PATH nvarchar(200),
@BCKUPNAME nvarchar(50)
AS
BACKUP DATABASE @DBNAME
TO DISK = @PATH
WITH NOFORMAT, NOINIT,
NAME = @BCKUPNAME,
STATS = 10
You can call this script using :
DECLARE @return_value int
EXEC @return_value = [dbo].[usersp_BackUpDB]
@DBNAME = N'Northwind',
@PATH = N'C:\temp\Northwind.bak',
@BCKUPNAME = N'NW'
SELECT 'Return Value' = @return_value
GO
Read more about this topic over here :
How to: Back Up a Database (SQL Server Management Studio)
/*
Author : Suprotim Agarwal
Creation Date: Jan 2, 2008
*/
CREATE PROCEDURE [dbo].[usersp_BackUpDB]
@DBNAME nvarchar(50),
@PATH nvarchar(200),
@BCKUPNAME nvarchar(50)
AS
BACKUP DATABASE @DBNAME
TO DISK = @PATH
WITH NOFORMAT, NOINIT,
NAME = @BCKUPNAME,
STATS = 10
You can call this script using :
DECLARE @return_value int
EXEC @return_value = [dbo].[usersp_BackUpDB]
@DBNAME = N'Northwind',
@PATH = N'C:\temp\Northwind.bak',
@BCKUPNAME = N'NW'
SELECT 'Return Value' = @return_value
GO
Read more about this topic over here :
How to: Back Up a Database (SQL Server Management Studio)
Subscribe to:
Posts (Atom)