SELECT*

Below points are disscussing about point in time recovery of data in the Database 1)Create a database in the SQL Server Management Studio. Here i created a database named as New. 2)Create a table and Insert the data into that table. You can run the below query for creating table and insert the data into it. Use New GO CREATE TABLE TestForBackupNew (Sno INT IDENTITY(1,1),Valuess INT) GO DECLARE @I INT DECLARE @Count INT SET @I=1001 SET @Count=2000 WHILE(@I BEGIN INSERT INTO TestForBackupNew (Valuess) SELECT @I SELECT @I=@I+1 END SELECT * FROM TestForBackupNew 3)After Creating table and Inserting the data into it take FULL Backup and Transaction Log Backups. 4)Now delete the data from table. DELETE FROM TestForBackupNew WHERE SNO BETWEEN 1 AND 68---68 Rows affected 5)Change Database to Master 6)Restore the Database by choosing overwrite existing database. 7)Write Select Query and you can find the deleted data.

DECLARE @@TABNAME varchar(30) DECLARE @@USERNAME varchar(30) DECLARE @@MESSAGE varchar(255) SET @@TABNAME = 'customers' SET @@USERNAME = USER_NAME() SELECT @@MESSAGE = 'The table ' + @@TABNAME + ' is not owned by the user ' + @@USERNAME + '.' PRINT @@MESSAGE USE master EXEC xp_logevent 610000, @@MESSAGE, ERROR(You can also give Information in the place of ERROR.) We can find this error in EventViewer like Controlpanel->AdministrativeTools->EventViewer.

CREATE TABLE #testing ([Name] VARCHAR(50)) INSERT #testing VALUES('Raemsh') INSERT #testing VALUES('Sateesh') INSERT #testing VALUES('Madhu') GO CREATE TABLE #HoldDeleteData ([Name] VARCHAR(50)) DELETE FROM #testing OUTPUT DELETED.* INTO #HoldDeleteData WHERE [Name]='Madhu' SELECT *FROM #HoldDeleteData

While sending mail from sqlserver 2000 i encountered with the below error.Solution is: First stop the mail by running the below query. exec master.dbo.xp_stopmail Then send try send the mail. The below link is describing about this error. http://support.microsoft.com/default.aspx?scid=kb;en-us;555180

To know about the orphaned users in the database we can run below query and find out. Use DatabaseName go sp_change_users_login 'Report'---This procedure used to give the details of orphaned users in the database. After you find the orphaned users take the users type two times the sameway mentioned below EXEC sp_change_users_login 'Update_One','ramesh','ramesh' EXEC sp_change_users_login 'Update_One','kiran','kiran' EXEC sp_change_users_login 'Update_One','RequestDB','RequestDB'

exec sp_update_jobschedule @job_id='E9814833-7715-4BEE-B646-E286EBC33DE8', @name = 'Centrailised Database Backup Job - From 41.48 to 41.41 E Drive',---This name comes from sysjobschedules table @new_name='We can give any name here instead of Daily,Weekly'--As per our intention. @enabled =1, @freq_type = 8, @freq_interval =64, @freq_recurrence_factor=1

SELECT Job_ID, CONVERT(DATETIME, RTRIM(run_date)) + ((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) AS Start_DateTime , CONVERT(DATETIME, RTRIM(run_date)) + ((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100) / (86399.9964 /* Start Date Time */) + ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100 /*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime , ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100 /*run_duration_elapsed_seconds*/), GETDATE(), USER_NAME() FROM msdb.dbo.sysjobhistory SELECT * FROM master.dbo.JobsExecutionLog

The below example will explain about the How Delete works when we join two tables and delete the values. declare @Table table (sno INT,[Name] varchar(25)) insert @Table values(1,'Ramesh') insert @Table values(2,'Suresh') select * from @Table declare @table2 table (OrderID int,ItemName varchar(30),Sno INT) INSERT @table2 VALUES(1,'Idli',1) INSERT @table2 VALUES(2,'Chapathi',4) SELECT * FROM @table2 DELETE FROM @table2 FROM @table2 as T2 INNER JOIN @Table AS T ON T2.Sno=T.Sno SELECT *from @table2

DECLARE @SecondsToConvert int SET @SecondsToConvert = 3600 -- Declare variables DECLARE @Hours int DECLARE @Minutes int DECLARE @Seconds int DECLARE @Time datetime -- Set the calculations for hour, minute and second SET @Hours = @SecondsToConvert/3600 SET @Minutes = (@SecondsToConvert % 3600) / 60 SET @Seconds = @SecondsToConvert % 60 -- Store the datetime information retrieved in the @Time variable SET @Time = (SELECT RTRIM(CONVERT(char(8), @Hours) ) + ':' + CONVERT(char(2), @Minutes) + ':' + CONVERT(char(2), @Seconds)); -- Display the @Time variable in the format of HH:MMS SELECT CONVERT(varchar(8),CONVERT(datetime,@Time),108)

CREATE FUNCTION [DBO].[SPLITDELIMITED] ( @LIST NVARCHAR(2000), @SPLITON NVARCHAR(1) ) RETURNS @RTNVALUE TABLE ( ID INT IDENTITY(1,1), VALUE NVARCHAR(100) ) AS BEGIN WHILE (CHARINDEX(@SPLITON,@LIST)>0) BEGIN INSERT INTO @RTNVALUE (VALUE) SELECT VALUE = LTRIM(RTRIM(SUBSTRING(@LIST,1,CHARINDEX(@SPLITON,@LIST)-1))) SET @LIST = SUBSTRING(@LIST,CHARINDEX(@SPLITON,@LIST)+LEN(@SPLITON),LEN(@LIST)) END INSERT INTO @RTNVALUE (VALUE) SELECT VALUE = LTRIM(RTRIM(@LIST)) RETURN END

You might get this doubt "How do I move the default log files placed in: C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG to a different location?" This may not be required all the times but say on the partition where these log files are stored need few more disk space to freeup, also if you have enbaled to keepup more than 6 error log files online. Simple, for the error log opn SQL Server Configuration Manager, choose the relevant SQL Server services and on the right hand pane go to the advanced tab. Goto startup parameters, you will see the path after -e then change it to required directory, ensure to restart SQL Server services once this change has been affected.

USE TempDB GO EXEC sp_helpfile--By this we can find where the .mdf and .ldf files are placed defaultly. GO USE master GO ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = 'D:\30-04-2009BAK\Tempdb\datatempdb.mdf') GO ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'E:\TempdbLog\datatemplog.ldf') GO After running the above query Stop and Restart the services. USE master GO ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = 'D:\DATA\tempdb.mdf' ) ALTER DATABASE TempDB MODIFY FILE (NAME = temp2, FILENAME = 'D:\DATA\tempdb_mssql_2.ndf' ) ALTER DATABASE TempDB MODIFY FILE (NAME = temp3, FILENAME = 'D:\DATA\tempdb_mssql_3.ndf' ) ALTER DATABASE TempDB MODIFY FILE (NAME = temp4, FILENAME = 'D:\DATA\tempdb_mssql_4.ndf' ) GO ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'D:\DATA\templog.ldf' ) GO After this change stop and start the services and you can see...

The below trigger discuss about INSERT and UPDATE Events on Table table will effect on same table. Below example discussed about the same scenario. CREATE TABLE Table1 (ID INT IDENTITY(1,1),[Name] VARCHAR(50),AGE INT,Marks INT) GO CREATE TABLE Table2 (ID INT IDENTITY(1,1),[Name] VARCHAR(50),AGE INT,Marks INT) GO CREATE TRIGGER UpTodateTable2 ON Table1 FOR INSERT,UPDATE AS BEGIN IF EXISTS(SELECT ID FROM Table1 WHERE ID NOT IN (SELECT ID FROM Table2 )) BEGIN INSERT INTO Table2 SELECT [Name],Age,Marks FROM Inserted END IF EXISTS(SELECT T2.ID FROM Table2 AS T2 INNER JOIN Table1 as T1 ON T1.ID=T2.ID) BEGIN UPDATE TABLE2 SET [NAME]=I.[NAME], AGE=I.AGE, MARKS=I.MARKS FROM TABLE1 AS I INNER JOIN TABLE2 AS T2 ON T2.ID=I.ID END END GO INSERT INTO Table1 SELECT 'Mohan',30,20 GO update Table1 SET [Name]='ReddyChinna' WHERE id=2 GO SELECT * FROM Table1 SELECT * FROM Table2

This is one example for coverting Multiple Rows into Single Column. DECLARE @TABLE TABLE (SNO INT) DECLARE @STRING CHAR(10) INSERT @TABLE SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 SELECT * FROM @TABLE SET @STRING='' SELECT @STRING=CONVERT(VARCHAR(50),SNO)+';'+@STRING FROM @TABLE SELECT LEFT(@STRING,LEN(@STRING)-1)

use master go sp_configure 'show advanced options' , 1 go reconfigure with override go sp_configure 'Database Mail XPs' , 1 --go --sp_configure 'SQL Mail XPs',0 go reconfigure go ------------------------- EXECUTE msdb.dbo.sysmail_add_account_sp @ account_name = 'DatabaseBackupStatus' , @ description = 'To know about the database backup status.' , @ email_address = 'a.rameshk@yahoo.com' , @ display_name = 'From DatabaseBackupStatus' , @ username = 'sa' , @ password = 'sa' , @ mailserver_name = '192.168.41.11' ----- EXECUTE msdb.dbo.sysmail_add_profile_sp @ profile_name = 'Profile for Database Backup' , @ description = 'Profile for Database Backup' --------- EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @ profile_name = 'Profile for Database Backup' , @ account_name = 'DatabaseBackupStatus' , @ sequen...

After installing SQL Server 2005. I am unable to see the SQL Server Management Studio by clicking sqlwb from Run. Normally we can find this in the below default path C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE But in my installation i did not find sqlwb.exe file.So what i did was I copied IDE folder from other Server.And copied into my local. It is wroking perfectly for me. Thanks Ramesh.M

This command will stop users to view the Databases which are all existing in that particular server. REVOKE VIEW ANY DATABASE TO Public And want to give permission to the users(Other Logins) to see the database run this below command. GRANT VIEW ANY DATABASE TO Public Thanks Ramesh.M

Deny table Permission at Table Level: 1)Connect SQL Server through windows authentication at Sysadmin Level 2)Under Security Find Logins Eg: Under my Security there are logins like Prasad,Rameh,Siva 3)Now i decided I want to give Deny Table permission to Siva named Login 4)Now Click on Siva Login Properties->Go to Server Roles->Give Public->Don’t give any other Server roles. And especially Don’t give sysadmin server role and at the same time he should not be the owner of that database(db_owner) 5)Next open Query Analyzer run the below Script. use MCTS GO GRANT INSERT TO Siva GO use MCTS GO GRANT SELECT TO Siva GO use MCTS GO GRANT UPDATE TO Siva GO use MCTS GO DENY DELETE TO Siva--This Stops Dropping and Deleting 6) After running the query If siva next time login by using his credentials he can not run delete script. ----CREATING A ROLE AND ASSIGNING MEMBER TO IT. Here i am creating a database role which will not allow users to take a specific datab...

In Adventure Works Sample database which is in SQL Server 2005 contains tables like HumanResources.Employee HumanResouces.Address Person.Address If we run the These tables without giving schema name we can not get the result. For Eg:SELECT * FROM Employee--Here we wont get results because we are not giving schema name here. If we write query then only we will get the result like SELECT * FROM HumanResources.Employee--We can get result here. Now i want to change the schema from HumanResources to dbo so that i can get the result without mentioning the Schema name before the table ALTER SCHEMA dbo TRANSFER HumanResources.Employee This statement changes Table from HumanResources.Employee to Employee. And we can get the result.

You can add or enable the constraint without checking existing rows,but the constraint will be marked as not trusted. If you try to add or enable a constraint checking existing rows, and there is a row breaking the constraint,then you will not be able to add or enable the Constraint The below Example gives the Scenario: CREATE TABLE Books (Bookid INT IDENTITY(1,1) PRIMARY KEY,[Name] VARCHAR(25)) GO CREATE TABLE Authors (Sno INT IDENTITY(100,1),AuthorName VARCHAR(25),BookID INT) ---At this sage there no foreign key relation specified. INSERT Books ([Name]) VALUES('Ten Commandments') GO INSERT Authors (AuthorName,BookID) VALUES('Sydney',10)---Data is inserted in both the Table.No relations defined so far. ----Now i want to define the relation based on Existing Tables and Data First i ran this query to Create Foreign Key Relation. ALTER TABLE Authors ADD CONSTRAINT FK_Books_Bookid FOREIGN KEY(BookID) REFERENCES Books(BookID)--This is Giving me Error because alreay Data is ...

By running the below system stored procedure we can get the tables in all the database in the server sp_msforeachdb @command1='USE ?;SELECT * FROM sys.Tables'

By running the Below query we can Enable Agent XP Enable: sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Agent XPs', 1; GO RECONFIGURE

Run this query ALTER DATABASE DataBaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE We can run the query without rollback statement. But it takes so much time. And we can find out the result by running this query.We can come to know that whether services have been restored or not. SELECT [name], is_broker_enabled FROM sys.databases WHERE [Name]='DatabaseName'

I am trying to Connect to the Configurationg Manager and getting Error as: Can not Connect to WMI Provider Soluting for this is :Go to Command Prompt(cmd) Copy the below path and paste mofcomp "C:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmproviderxpsp2up.mof" and Press Enter get the message like this. Microsoft (R) 32-bit MOF Compiler Version 5.1.2600.2180 Copyright (c) Microsoft Corp. 1997-2001. All rights reserved. Parsing MOF file: C:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmprovider xpsp2up.mof MOF file has been successfully parsed Storing data in the repository... Done!

---This link will be useful for Common DBA automated Pracitces. http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=AutomatedDBA