TSQL to copy a most recent sql server backup to a new location

In this blog I would like to post the TSQL script that I have written and am using to move a most recent sql server backup from one location to another. I am using the script inside a sqlagent job that runs when the backup exec job is done backing up the bak file to tape.  Please note that the command I am using is the robocopy for copying the file and I am also using the /MOV switch which copies the backup file to the destination folder and then deletes it from the source location. I am using this script as a temporary fix for shortage of storage on one of our staging database servers. Once I have my LUN expanded I will disable this job.

The script requires the xp_cmdshell to be enabled so I am enabling the show advanced options to enable the xp_cmdshell and I am disabling it right after the script execution is done.


EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
declare @date datetime
declare @dbname nvarchar(40)
DECLARE @cmd nvarchar(4000)
DECLARE @srcbkpLocation nvarchar(4000)
DECLARE @destbkpLocation nvarchar(4000)
DECLARE @bkpFileName nvarchar(4000)
DECLARE @bkpFileName1 nvarchar(4000)
set @destbkpLocation='\\Destserver\FULL\Backups\DBNameFolder\'
SELECT top 1 @date=max(bs.backup_finish_date),@dbname=bs.database_name,
@bkpFileName=bms.physical_device_name
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bms
ON bs.media_set_id = bms.media_set_id
INNER JOIN master.dbo.sysdatabases s
ON bs.database_name = s.name
WHERE
s.dbid IN (6) AND
bs.type = 'D'
order by bs.backup_finish_date desc
set @srcbkpLocation=(select substring(@bkpFileName,1,LEN(@bkpFileName) - Charindex('\',Reverse(@bkpFileName))+1))
set @bkpFileName1=(Select Substring(@bkpFileName,LEN(@bkpFileName) - Charindex('\',Reverse(@bkpFileName))+2,LEN(@bkpFileName)))

SET @cmd = 'robocopy '+@srcbkpLocation+' '+@destbkpLocation+' '+@bkpFileName1+' '+'/MOV'
print @cmd
EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT

EXEC master.dbo.sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

 

Hope this helps!

Advertisements
Posted in SQL Server DBA Stuff | Tagged , , | Leave a comment

SQL 2012: System.Data.SqlClient.SqlError: Cannot use file ” for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it. (Microsoft.SqlServer.SmoExtended)

I presented a new lun to the sql server 2012 cluster and tried to restore a database to the new storage. I got the below error:

“System.Data.SqlClient.SqlError: Cannot use file ” for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it. (Microsoft.SqlServer.SmoExtended)”

To resolve this I had to go to the Failover cluster  manager –>services and applications–>SQLServer (MS SQLServer) (highlight it) and in the Other resources select the SQL Server services–> Right click –> Properties–>Dependencies–>Insert (select the drive that you needs to be added or the ones that you presented to the cluster).This fixed the error.

Hope this helps!

 

Posted in SQL Server DBA Stuff | Tagged , | 2 Comments

Sync’ing sql server authentication logins between mirrored or logshipped databases in SQLServer

To Sync SQLServer authentication logins between mirrored instances:

First create the login on the source database.

Execute the below code in ssms on source server master database

select name,sid from syslogins 

Copy the name and sid and use the same sid when you are creating the login in the mirrored instance using the below code

create login  with password = '*******',sid = paste the sid here with no quotes,default_database = yourdatabasename, default_language = English

The default_database = yourdatabasename is not necessary as sqlserver by default will choose master database

Once this is done you can assign permissions to the login to a specific database and the log shipping or the mirroring takes care of the user permissions in the mirrored or logshipped database.

Hope this helps.

 

Posted in SQL Server DBA Stuff | Tagged , , , | Leave a comment

MSSQL Error# 32023. An entry for primary server , primary database does not exist on this secondary. Register the primary first.

Hello Everyone,

Hope all is well. Today I would like post the resolution for an error that I came up with when I was modifying the logshipping configuration. Logshipping in my scenario is not being used for high availability but I am using the logshipped databases for the purpose of SSRS reports. We did a failover a couple of weeks back from Server A to Server B using a third party HA solution and after a couple of weeks I wanted to change the retention on the secondary after the files are copied.

I tried to do this thorough GUI on primary server which is Server B and then when I make changes I got the below error:

SQL Server Management Studio could not save the configuration of ReportServer as a Secondary.An entry for primary server , primary database  does not exist on this secondary. Register the primary first. (Microsoft SQL Server, Error: 32023)

I used the below script to fix the issue, but I still put the value for @primary_server=’Server A’ and not server B. If I put server B I would still receive the same error:

exec master.dbo.sp_change_log_shipping_secondary_primary
@primary_server = 'Server A',
@primary_database = 'Db Name',
@file_retention_period ='time in minutes'

Hope this helps!

Posted in SQL Server DBA Stuff | Tagged , , , , , | Leave a comment

TSql Script to output records with all possible column values

Hello Everyone,

In this post I would like to focus on a tsql script to select data from a table with unique account ID for accounts but different values for the other columns and the query should be able to select data only if certain column values exist. Lets take an example to give you a clear picture. I have a table whose DDL is:


CREATE TABLE [dbo].[AccountData]([AccName] [nvarchar](255) NULL,[AccID] [float] NULL,[Duration] [float] NULL,[Issue] [nvarchar](255) NULL) ON [PRIMARY]

and the resultset is as shown:

Now I want to select only those records where Issue column has values Phase1,Phase2,Phase3 and Phase4. So if I use the following query it returns me 0 results.

select * from accountdata where issue='Phase1' and issue='Phase2' and issue='Phase3' and issue='Phase4'

I want to get accounts test1 and test4 in the result with all the columns and records. Here is the script for that:


Select a.AccName, a.AccID, a.Duration, a.Issue From dbo.AccountData a Where a.AccName In (Select a1.AccName From dbo.AccountData a1 Where a1.Issue In ('Phase1', 'Phase2', 'Phase3', 'Phase4) Group By a1.AccName Having Count(Distinct a1.Issue) = 4)

Here is the expected result set.

testimage 

Hope this helps!

Posted in SQL Server DBA Stuff | Tagged , , , , , , | Leave a comment

Identify and fix orphan users in SQL Server

In this post I would like to focus on identifying orphan users and fix them in sql server. We have a need for this when we restore a database from a one instance to a different instance. In order to list the users along with their Security Identifiers aka SID in a database that is not associated with any login. We need to pass the parameter ‘Report’ to the SP_Change_Users_Login stored proc.


sp_change_users_login @Action='Report';

TO fix the orphan user issue we need to relink the  server login account specified by <login_name> with the database user specified by <database_user> and this can be achieve with the following sql.

sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>',
 @LoginName='<login_name>';

Once the above query is executed the user should be able to access the database.

Hope this helps.

Posted in SQL Server DBA Stuff | Tagged , , | Leave a comment

Setting up clustering in SQL Server 2012 step by step process

In this post I would like to discuss about setting up SQL Server 2012 Clustering on Windows Server 2008 R2. This includes setting up windows clustering as well.

1)      I first went to windows server manager and expanded features and selected .net framework 3.5.1 and then selected failover clustering service. At the time of installation the server manager prompted that the .net framework requires the iis role to be installed. Said OK and installed on both nodes.

2)      After the installation was completed tried to create a cluster. Added both nodes and at the time of validation it errored out with the message:

An error occurred while executing the test.
There was an error getting information about the running processes on the nodes.
There was an error retrieving information about the Processes from node ‘node name‘.
Not found

Checked to see what the issue was and found that we have a hot fix available for this issue. At the same time identified that both the nodes were not patched. Applying the patches fixed the issue.

3)      Below are the cluster name and IP Addresses that I have assigned

1

4)      Below is the confirmation

2

3

At this point of time on 192.168.31.4 has the storage attached and as shown in the below screenshot is how the Failover Cluster Manager appears:

4

5

6

7

8

9

10

11

12

13

5)      Now I will install sql server 2012. I am using Windows Server 2008 R2. When I tried to install sql server 2012 I got a message that my OS has to be upgraded to SP1. Below is the message:

14

6)      I have now installed SP1 on both nodes in the cluster.

7)      Installing SQL

8)      In setup.exe go to advanced tab and select Advanced Cluster Preparation

15

The installation begins like normal and then after the prereq verification is done it will prompt for a product key.

16

Now you need to accept the license terms and it prompts for product updates.

17

Click Next

18

The next step would be to identify problems that might occur with the setup support files. Fix any issues if noticed or identified.

19

The next step would be to select features and I need Database Engine and SSMS for now.

20

The next step is it will verify for any blocking situations while preparing for the failover cluster process.21

Click Next to configure the instance. I am choosing the named instance option and I am naming it as TestEnv.

22

23

The next step would be to verify if the disk space requirements are met. Click Next. This will take us to the Server configuration page prompting us for account name and password settings.

24

Ensure that the startup types are manual for the sqlserver agent. I chose manual for both services. Click Next and it will take us to the “Help MS improve sql server features and services” page.

25

Click Next

26

Click Next

27

Copy the configuration file path which is C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\20130807_170107\ConfigurationFile.ini and then click Install.

28

Installation on Node A is done.

29

click close

9)      Now lets go to Node B

10)   Go to setup.exe of sql 2012 and go to advanced tab and select ‘Install based on configuration file’

30

Click on the browse button and select the configuration.ini file that is copied from the Node A.31

Rest of the installation is similar to what has been performed on Node A.

11) Now on Node A launch setup.exe and click on advanced tab and then click on Advanced cluster completion and then in the setup support files window click OK as shown below

32

33

Click OK

34

35

In the cluster node configuration enter the SQL Server Network Name shown below:

36

Click Next

37

38

Click Next

In the Cluster Disk Selection window check the disks to be included in the sql server resource cluster group as shown below:

39

Click Next

40

Enter IP Addresses for the Cluster Network 1 and 2 as shown above in the cluster network configuration window and then click Next

41

Click Next

In the database engine configuration window choose the authentication mode. For Mixed Mode enter the password and add the user you want to be sysadmin as shown

42

Enter the paths you want to choose for data, logs, backups and Tempdb database files in the data directories tab as shown

43

Click Next

44

Click Next

45

Click Install

46

 

47

Windows and SQL Clustering is now setup successfully.

12) This is how my failover cluster manager looks now

48

49

50

51

Hope this step by step installation process helps!

 

 

 

 

 

 

 

 

 

Posted in SQL Server DBA Stuff | Tagged , , | Leave a comment