World Search

Friday, February 28, 2014

Retore T-Log With Move

Here in the syntax we can you with move.



RESTORE LOG [Student] FROM
DISK =  N'P:\student_after_secfile.trn' WITH  FILE = 1,NORECOVERY,

MOVE N'Student_new' TO N'P:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Student_new2.ndf',
NOUNLOAD, STATS = 10
GO



Another Scenario While Restoring Tlog and Space Crunch:-

RESTORE LOG [DB] FROM
DISK = N’D:\DB.trn’
WITH FILE = 1,
STANDBY = N’D:\ROLLBACK_UNDO_DB.BAK’,
MOVE N’DBFILENAME′ TO N’P:\Data\DB.ndf’,
NOUNLOAD, STATS = 10
GO

Monday, February 24, 2014

Collecting and Saving Configuration Information for a Number of Servers.


Help Link https://www.simple-talk.com/sql/sql-training/how-to-document-and-configure-sql-server-instance-settings/

PowerShell Script:-
=============

SET<#
This PowerShell routine creates an excel spreadsheet with the current configuration settings of all your servers. Each instance is on a different worksheet and the worksheets have the name of the instance. It works by using ODBC connections created locally on your workstation.
Once the spreadsheet is created, the PowerShell script is no longer needed since the data can be refreshed from within Excel. This means that you will have a live record of the configuration settings of your instances.
#>
#change these following settings to your environment
$DirectoryToSaveTo='E:\Scripts\'
$Filename='DatabaseConfiguration'
#
<#
Just make your list of servers here as an XML fragment
these instance names are actually User ODBC DSNs DSNs. Better for Excel.
We associate a version number with each just so you are sure
of a script that will run (You can't get this info from the DSN, and
Excel only allows one select statement in an Excel query
#>

$Servers=[xml] @'

       
version="2008" name="Angry" />
       
version="2008" name="Grumpy" />
        version="2005" name="Dozy" />
        version="2000" name="Sleepy" />

'@
 
# constants.
$xlCenter=-4108
$xlTop=-4160
$xlOpenXMLWorkbook=[int]51
# and we put the queries in here
$SQL2005=@"
SELECT  name, value, minimum, maximum, value_in_use as [Value in use],
        description, is_dynamic AS [Dynamic?], is_advanced AS [Advanced?]
FROM    sys.configurations ORDER BY name ;

"@

$SQL2008=@"
SELECT  name, value, minimum, maximum, value_in_use as [Value in use],
        description, is_dynamic AS [Dynamic?], is_advanced AS [Advanced?]
FROM    sys.configurations ORDER BY name ;
"@


$SQL2000=@"
SELECT  Name, c.Value, low AS [minimum], high AS [Maximum],
        master.dbo.syscurconfigs.value AS [Value In Use],
        c.comment AS [Description]
FROM    master.dbo.spt_values v
        INNER JOIN master.dbo.sysconfigures c ON number = c.config
        INNER JOIN master.dbo.syscurconfigs ON number = master.dbo.syscurconfigs.config
WHERE   type = 'C'
ORDER BY LOWER(name)

"@


# before we do anything else, are we likely to be able to save the file?
# if the directory doesn't exist, then create it
if (!(Test-Path -path "$DirectoryToSaveTo")) #create it if not existing
  {
  New-Item "$DirectoryToSaveTo" -type directory | out-null
  }
$excel = New-Object -Com Excel.Application #open a new instance of Excel
$excel.Visible = $True #make it visible (for debugging more than anything)
$wb = $Excel.Workbooks.Add() #create a workbook
$currentWorksheet=1 #there are three open worksheets you can fill up
foreach ($server in $servers.serverlist.server)
      { #only create the worksheet if necessary
      if ($currentWorksheet-lt 4) {$ws = $wb.Worksheets.Item($currentWorksheet)}
      else  {$ws = $wb.Worksheets.Add()} #add if it doesn't exist
      $currentWorksheet += 1 #keep a tally
      if  ($server.version -eq 2005) {$SQL=$SQL2005} #get the right SQL Script
      if  ($server.version -eq 2008) {$SQL=$SQL2008}
      if ($server.version -eq 2000) {$SQL=$SQL2000}
      $currentName=$server.name  # and name the worksheet
      $ws.name=$currentName # so it appears in the tab
      # note we create the query so that the user can run it to refresh it
      $qt = $ws.QueryTables.Add("ODBC;DSN=$currentName", $ws.Range("A1"), $SQL)
      # and execute it
      if ($qt.Refresh()) #if the routine works OK
            {
            $ws.Activate()
            $ws.Select()
            $excel.Rows.Item(1).HorizontalAlignment = $xlCenter
            $excel.Rows.Item(1).VerticalAlignment = $xlTop
            $excel.Rows.Item(1).Orientation = -90
            $excel.Columns.Item("G:H").NumberFormat = "[Red][=0]û;[Blue][=1]ü"
            $excel.Columns.Item("G:H").Font.Name = "Wingdings"
            $excel.Columns.Item("G:H").Font.Size = 12
              $excel.Rows.Item("1:1").Font.Name = "Calibri"
            $excel.Rows.Item("1:1").Font.Size = 11
            $excel.Rows.Item("1:1").Font.Bold = $true
            $Excel.Columns.Item(1).Font.Bold = $true
            }
      }
$filename=$filename -replace  '[\\\/\:\.]',' ' #remove characters that can cause problems
$filename = "$DirectoryToSaveTo$filename.xlsx" #save it according to its title
if (test-path $filename ) { rm $filename } #delete the file if it already exists
$wb.SaveAs($filename,  $xlOpenXMLWorkbook) #save as an XML Workbook (xslx)
$wb.Saved = $True #flag it as being saved
$wb.Close() #close the document
$Excel.Quit() #and the instance of Excel
$wb = $Null #set all variables that point to Excel objects to null
$ws = $Null #makes sure Excel deflates
$Excel=$Null #let the air out
# Hristo Deshev's Excel trick 'Pro Windows PowerShell' p380
[GC]::Collect()
==================================================================
SQL Required Info Querys :-
===================

SQL2005
----------
SELECT  name, value, minimum, maximum, value_in_use as [Value in use],
        description, is_dynamic AS [Dynamic?], is_advanced AS [Advanced?]
FROM    sys.configurations ORDER BY name ;
SQL2008
----------
SELECT  name, value, minimum, maximum, value_in_use as [Value in use],
        description, is_dynamic AS [Dynamic?], is_advanced AS [Advanced?]
FROM    sys.configurations ORDER BY name ;
 

SQL2000
----------
SELECT  Name, c.Value, low AS [minimum], high AS [Maximum],
        master.dbo.syscurconfigs.value AS [Value In Use],
        c.comment AS [Description]
FROM    master.dbo.spt_values v
        INNER JOIN master.dbo.sysconfigures c ON number = c.config
        INNER JOIN master.dbo.syscurconfigs ON number = master.dbo.syscurconfigs.config
WHERE   type = 'C'
ORDER BY LOWER(name)


==================================================================

Wednesday, February 19, 2014

Adding Secondary Data File To Mirrored Database Steps

Adding Secondary Data File To Mirrored Database Steps

Steps to add secondary data file to Mirrored Database
========================================

1. Make sure mirrored database is in synchronized state using below query - at principal server

SELECT db_name(database_id) as database_name, mirroring_state_desc,
mirroring_role_desc,  mirroring_partner_name,  mirroring_partner_instance
FROM sys.database_mirroring where mirroring_state_desc = 'SYNCHRONIZED'
2. Disable backup jobs at principal server if any

3. Run the below command to stop the mirroring -- at principal server
  Alter database MirrorNew set partner off

Now you can observe db mirroring will be removed and mirror server database state becomes restoring

4. Add secondary data file / filegroup to principal server database - at principal server
alter database mirrornew add file
 (name='second_datafile',
  filename='E:\Program Files\Microsoft SQL Server\DATA\MirrorNew_second.ndf',
  size = 50mb)
5. Make sure file got added to principal server database -- at principal server
select * from MirrorNew..sysfiles

6. Generate log backup of principal database  -- at principal server
backup log mirrornew to disk='e:\backups\mirrornewwithsecondary.trn' with init

7. You can verify whether log backup has newly added file or not using the below command
restore filelistonly from disk='e:\backups\mirrornewwithsecondary.trn'

8. Copy the log backup to mirror server

9. Run the below command to verify the file location of the database
select * from sysaltfiles where dbid = DB_ID('mirrornew')

10. Restore the log file on mirror server database using with move option -- at mirror server

restore log mirrornew from disk='e:\backups\mirrornewwithsecondary.trn'
with norecovery, move 'second_datafile' to 'E:\Program Files\Microsoft SQL Server\\DATA\MirrorNew_second.ndf'
11. Verify newly created file added to database or nore   -- at mirror server
select * from sysaltfiles where dbid = DB_ID('mirrornew')

12. Reinitiate the mirroring from mirror server -- at mirror server
alter database mirrornew set partner ='tcp://kalyanmirror:5022' (Verify properties of DB-Choose Mirroring) -- It moves database into Inrecovery mode

13. Finally initiate mirroring step from principal server -- at principal server
alter database mirrornew set partner='tcp://kalyanprimary:5023'


Thanks :- http://calyansql.blogspot.in/2013/08/adding-secondary-data-file-to-mirrored_9.html

Tuesday, February 11, 2014

Script To List The Server Roles For Logins

select P.Rolememberships from (select  'EXEC sp_addsrvrolemember @rolename =' + SPACE(1)
        + QUOTENAME(DB_Roles.Roles, '''') + ', @loginame =' + SPACE(1)
        + QUOTENAME(sl.name, '''') as 'Rolememberships'
,sl.sid
from master..syslogins sl
cross join
(
select 'sysadmin' as Roles
union all
select 'securityadmin'
union all
select 'serveradmin'
union all
select 'setupadmin'
union all
select 'processadmin'
union all
select 'diskadmin'
union all
select 'dbcreator'
union all
select 'bulkadmin'
) DB_Roles

where
Case When sl.sysadmin=1 and DB_Roles.Roles='sysadmin' then 'sysadmin'
  When sl.securityadmin=1 and DB_Roles.Roles='securityadmin' then 'securityadmin'
  When sl.serveradmin=1 and DB_Roles.Roles='serveradmin' then 'serveradmin'
  When sl.setupadmin=1 and DB_Roles.Roles='setupadmin' then 'setupadmin'
  When sl.processadmin=1 and DB_Roles.Roles='processadmin' then 'processadmin'
  When sl.diskadmin=1 and DB_Roles.Roles='diskadmin' then 'diskadmin'
  When sl.dbcreator=1 and DB_Roles.Roles='dbcreator' then 'dbcreator'
  When sl.bulkadmin=1 and DB_Roles.Roles='bulkadmin' then 'bulkadmin'
  else null end=DB_Roles.Roles
  ) as p join sysusers su on p.sid=su.sid

Script to list DB User Roles for Logins

  SELECT 'EXEC sp_addrolemember N''' + USER_NAME(groupuid) + ''', N''' + USER_NAME(memberuid) + ''''+';'
FROM sysmembers where USER_NAME(memberuid) not in ('dbo', 'sa', 'public')

Sunday, February 9, 2014

Alter failed for Database 'DBNAME'. (Microsoft.SqlServer.Smo), The server network address "TCP:/win.abc.com:5023" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

Solution :-
========

1. Change the SQL Server Service Accounts and Agent Accounts to domain accounts.



2. Once this is done, start Mirroring.

3. To check the endpoints.

SELECT name, protocol_desc, port, state_desc FROM sys.tcp_endpoints

WHERE type_desc = 'DATABASE_MIRRORING'