Category: SQl server Administration

Today i will explain how to Check all Data file and log file for all your database we will check the Extensions for (MDF , NDF , LDF) by using policy management .

1- Create Conditions

at the first we will create 2 condition one for Data file and the other for Log file

I- Data File Extensions Condition

Open SQL Server Management studio >>> Mangment >>> Policy Mangment >>> Conditions >>>> Right Click on it >>> New Condition

Name : Data File Extensions .

Facet  : Data File .

Field : @File name

II – Log File Extensions Conditions :

Open SQL Server Management studio >>> Mangment >>> Policy Mangment >>> Conditions >>>> Right Click on it >>> New Condition

Name : Log File Extensions .

Facet  : Data File .

Field : @File name

2- Create policy :

Now we will Create 2 Policy one “Check Data File” and the Other is “Check log File”

I – Policy – Check Data File :

Open SQL Server Management studio >>> Mangment >>> Policy Mangment >>> Policies>>>> Right Click on it >>> New Policy

Name : Check Data File  .

Check Condition : Data File Extensions

II – Policy – Check Log File :

Open SQL Server Management studio >>> Mangment >>> Policy Mangment >>> Policies>>>> Right Click on it >>> New Policy

Name : Check Log File  .

Check Condition : Log File Extensions

Now after we create the policy if you go to Policies and right click on Policy name ” Check Data File ” >>> Evaluate

After the Evaluate Finish you will find the Data file for your all databases in your SQl Server Instance if you clicked on View you will See the Full path to the MDF and NDF

in my example now i have MDF only no File Extensions  With NDF so i have More Cross

I Wish Success To Everyone

Eng. Mostafa Elmasry

Database Administrator

Question :

How i can transfer SQL Server jobs Task from Source Server to Distention Server without T-SQL Script ?

Answer :

We can do that by SSIS ” SQL SERVER INTEGRATION SERVICES ”  by creating new package on it to transfer the jobs task from one server to another server.

Note : also by SSIS i can trnsfer

1- Database Task

2- Error Massage Task

3- Login Task

4- Master Stored procedure Task

5- SQL Server objects Task

 

Demo to how to transfer SQL Server jobs task :

1- open Start menu >>> SQL Server >>>  SQL Server Business Intelligence Development Studio >>> File Menu >>> New >>> project >>> Select project type is Business Intelligence Projects from the left bar >>> select from the Right template Integration Services Project >>>write name for this Project EX (Transfer Jobs) >>> Choose your path to save the project >>> OK

2- from the left Select from the Toolbox Menu under the Control flow items >>> Transfer job Task  >>> Drag and Drop this task on Control flow page >>> right click on the task >>> Select Edit .

 

 

3- After the Edit we will view New Screen ” Transfer Jobs Task Editor ” >>> Select jobs from the left bar .

4- We have 3 Group :

A- Connection :

Source Connection           : The SQL Server Instance the Jobs Crated on it .

Destination Connection : The SQL Server you need to referrer the jobs to it .

B- Jobs :

Transfer All Jobs : False if you don’t need to transfer All jobs and this is Default Value , True  if you need to transfer All jobs .

Job List : if you select The transfer All jobs is FALSE Select from here the Jobs list you need to transfer it to another Server .

C- Option :

If Object Exist : Meaning if the one Job from the List job you need to transfer it to another Server it’s Created already on this Server you will Select from this 3 Value

• FailTask : If the job Found Fail The task.
• Overwrite : if the job Found Overwrite on it .
• Skip : if the job Found Skip this job

EnablejobsAtDistention : False = Disable , True = Enable .

After you finish your Configuration Click OK

4- now we need to run the task by F5 or Click on play icon to execute the package.

5- Now Check your Distinction SQL Server Agent Jobs you will found the New Jobs you Selected it to Transfer .

I Wish Success To Everyone

Eng. Mostafa Elmasry

Database Administrator