How to execute Sql Server Agent job from Trigger

if you want to trigger the job on database insert,update and delete operation then you want to write the trigger in the way below:


CREATE TRIGGER TriggerName
ON TableName
AFTER INSERT, UPDATE, DELETE
AS
EXEC msdb..sp_start_job
@job_name = 'JobName';
GO

you need to only replace 'JobName' with your create job.



Cheers...

Get all the month name between two date

Just you need to pass the startdate and enddate to get all the month between two date. here is the link:

Click Here


Cheers...

I installed it but I don't see where I can open it.

Click on the Windows Start button, click on the "Run" option and try to run the following command:

-> ssms.exe



Cheers....

Find all databases and physical path on SQL Server

Here's a very simple way to find all databases and their physical path on the server:

SELECT [name], [physical_name],[Size]FROM sys.master_files

Happy Coding...

Grant user role permission in SQL Server 2005 using script

Here is the script to create role in Sql Server 2005. you need to pass '$(username)' parameter in this script file.



CREATE LOGIN [$(username)] FROM WINDOWS;
GO

EXEC sp_addsrvrolemember [$(username)], 'securityadmin';
GO

EXEC sp_addsrvrolemember [$(username)], 'dbcreator';
GO


Happy Coding...

Attach database in SQL Server 2005 using script

Here is the script through which you can easily attach database in sql server 2005.


USE [master]
GO

--ConfigDb
CREATE DATABASE [$(dbname)] ON
( FILENAME = N'$(MDFFileLocationPath)' ),
( FILENAME = N'$(LDFFileLocationPath)' )
FOR ATTACH
GO
if exists (select name from master.sys.databases sd where name = N'$(dbname)' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) EXEC [$(dbname)].dbo.sp_changedbowner @loginame=N'$(username)', @map=false


Happy Coding...

Detach database in SQL Server 2005 using script

Here is the script from which you can easily do this. just pass $(dbname) with database name you want to detach.


USE [master]
GO
-- detach farm config DB
EXEC master.dbo.sp_detach_db @dbname = N'$(dbname)', @keepfulltextindexfile=N'true'
GO



Happy Coding...

Create Database in SQL Server 2005 using script

Here is the script, through which we can easily create database in sql server 2005.
Change the parameter with your appropriate value to create database. likewise,

(1) $(dbname) with Database name
(2) $(dbfilepath) with MDF file location path
(3) $(dbfilelogpath) with LDF file location path


CREATE DATABASE [$(dbname)] ON PRIMARY
( NAME = N'$(dbname)', FILENAME = N'$(dbfilepath)$(dbname).mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'$(dbname)_log', FILENAME = N'$(dbfilelogpath)$(dbname)_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
COLLATE LATIN1_General_CI_AS_KS_WS
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'$(dbname)', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [$(dbname)].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [$(dbname)] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [$(dbname)] SET ANSI_NULLS OFF
GO
ALTER DATABASE [$(dbname)] SET ANSI_PADDING OFF
GO
ALTER DATABASE [$(dbname)] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [$(dbname)] SET ARITHABORT OFF
GO
ALTER DATABASE [$(dbname)] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [$(dbname)] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [$(dbname)] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [$(dbname)] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [$(dbname)] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [$(dbname)] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [$(dbname)] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [$(dbname)] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [$(dbname)] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [$(dbname)] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [$(dbname)] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [$(dbname)] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [$(dbname)] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [$(dbname)] SET READ_WRITE
GO
ALTER DATABASE [$(dbname)] SET RECOVERY FULL
GO
ALTER DATABASE [$(dbname)] SET MULTI_USER
GO
ALTER DATABASE [$(dbname)] SET PAGE_VERIFY CHECKSUM
GO
USE [$(dbname)]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [$(dbname)] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO


Happy Coding...

Set SQL Server Database file location path

To workaround this, you need to just pass MDF and LDF file location as mentioned in the below script:


USE [master]
GO
--Data file
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'$(mdffilelocationpath)'
GO
-- Log file
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'$(ldffilelocationpath)'
GO

Happy Coding....

Take all Database backup using SQL Server 2005

Here is the script through which you can easily take all the database backup in selected folder location.


DECLARE @DBName varchar(255)

DECLARE @DATABASES_Fetch int

DECLARE DATABASES_CURSOR CURSOR FOR
select
DATABASE_NAME = db_name(s_mf.database_id)
from
sys.master_files s_mf
where
-- ONLINE
s_mf.state = 0

-- Only look at databases to which we have access
and has_dbaccess(db_name(s_mf.database_id)) = 1

-- Not master, tempdb or model
and db_name(s_mf.database_id) not in ('Master','tempdb','model')
group by s_mf.database_id
order by 1

OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
declare @DBFileName varchar(256)
set @DBFileName = @DBName+'.bak';

exec ('BACKUP DATABASE [' + @DBName + '] TO DISK = N''$(path)' +
@DBFileName + ''' WITH NOFORMAT, INIT, NAME = N''' +
@DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 100')

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
END

CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR


you need to just pass $(path) with your suitable folder location.


Happy Coding....

Restore DB in SQL Server 2005

I sepnt more hours in internet and finally i got the reliable script through which i easily restore the database.

Here is the script:



USE MASTER
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[$(database)]') AND type in (N'U'))
ALTER DATABASE $(database) SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

create table #backupInformation (LogicalName varchar(100),
PhysicalName varchar(100),
Type varchar(1),
FileGroupName varchar(50) ,
Size bigint ,
MaxSize bigint,
FileId int,
CreateLSN int,
DropLSN int,
UniqueId uniqueidentifier,
ReadOnlyLSN int,
ReadWriteLSN int,
BackupSizeInBytes int,
SourceBlockSize int,
FileGroupId int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN bigint,
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit, IsPresent bit )

insert into #backupInformation exec('restore filelistonly from disk = ''$(filepath)''')

DECLARE @logicalNameD varchar(255);
DECLARE @logicalNameL varchar(255);

select top 1 @logicalNameD = LogicalName from #backupInformation where Type = 'D';
select top 1 @logicalNameL = LogicalName from #backupInformation where Type = 'L';

DROP TABLE #backupInformation

RESTORE DATABASE $(database)
FROM DISK = '$(filepath)'
WITH REPLACE,
MOVE @logicalNameD TO '$(mdffilelocation)\$(database).mdf',
MOVE @logicalNameL TO '$(ldffilelocation)\$(database).ldf'
GO


you need to change $(database) with your database name, $(filepath) with your db file path where it is,$(mdffilelocation) with appropriate MDF file location,

$(ldffilelocation) with appropriate LDF file location.

Thats it.


Happy Coding....