How to backup server principals in an instance ?

There are 2 scripts that microsoft has come up with inorder to backup sql server principals.I suppose it has different scripts for various sql server versions.I shall soon upload those scripts here :-) with further detailed explanation.

So please wait ...................

Exporting Data from SQL Server to Excel ....

There are different ways with which we can achive this ...Few of them to mention are as follows :

1) BCP ( Bulk Copy Program )-This ships with sql server
2) DTS ( Data Transformation Services )-Comes with SQL server 2000
3) SSIS ( Sql Server Integration Services ) - Sql server 2005 and above !
4) SQLCMD-command line utility - sql server 2005 and above
5) osql-command line utility - sql server 2000
6) .Bat scripts using one or more combination of above
( example :- .bat and bcp in it )

How to make bcp utility to append to a file ? ( Scheduling Tasks )

1) Create path to hold the running scripts

here in our example C:\testlab\s2

2) Create a batch script by name memory_in_use.bat

Code is as follows :

bcp "select object_name,counter_name,cntr_value/1024,getdate() from sys.dm_os_performance_counters where object_name='MSSQL$CSFSPD3TEST2:Memory Manager' and counter_name in ('Target Server Memory (KB)','Total Server Memory (KB)')" queryout C:\testlab\s2\dup_mem_res.xls -T -c -S CSCINDAE744358\CSFSPD3TEST2
cd C:\testlab\s2
copy mem_res.xls+dup_mem_res.xls tempfile.xls
del mem_res.xls
ren tempfile.xls mem_res.xls


3) Create a mem_res.xls excel sheet.Open the sheet and in the first row manually enter the columns one by one
Here for our example :

Counter_Name Object_Name ValueinMB Date


4) Create a dup_mem_res.xls excel sheet.

5) Now create a scheduled tasks from os as shown in the below figure and configure it to run every x minutes or hours as per your requirement :