October 2012 | SansSQL

Wednesday, October 31, 2012

Backup job failed - "file manipulation operations"


On some occasions when scheduling maintenance jobs like, Full database backup, Shrink database, Differential backups, index maintenance jobs we oversee the schedules and end up with jobs running into each other.

This will definitely lead to bigger errors and failures at any point in time. One such error we come across in such situations is the following.
 
"Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed."
Note: This is just the part of job history.
 
Fixes:
  • All we need to do is be alert and make sure we have the correct schedules for all the jobs without allowing any of the jobs running into each other.
  • If error or failures, make sure to change the job schedules and confirm if it works fine.
Hope this helps!

Thursday, October 18, 2012

Un-Installing Service Pack for SQL Server

Unlike any other software, SQL Server also allows uninstalling of the Service packs. This is a feature that was introduced from SQL Server 2008 SP1 and above and here is how you do it.
Before you do the activity, it is recommended to take a full instance backup just in case to recover if anything goes wrong.

Check the Current Service pack level of the SQL Server Instance.
Go to "Programs and Features" options in Control Panel and click on "view installed updates"

Highlight the Service Pack you want to uninstall and click "Uninstall"

The uninstall Service Pack for SQL Server wizard will now come up and click "Next" here once the update rules have been verified.

Select the features for which you need to remove service pack and click "Next"

Once the file check is completed, Click "Next"

Verify the Summary and click "Remove"

Wait for the process to complete

Once this is completed, Restart the computer to finish the SQL Server Setup.

Now verify the changes


Wednesday, October 17, 2012

A failure was detected for a previous installation, patch, or repair during configuration for features...

You receive the following error when you try to apply an update or service pack for a SQL Server Instance.

TITLE: SQL Server Setup failure.
------------------------------
SQL Server Setup has encountered the following error:

A failure was detected for a previous installation, patch, or repair during configuration for features [SQL_Engine_Core_Shared,SQL_PowerShell_Engine_CNS,CommonFiles,CommonFiles_Loc,]. In order to apply this patch package (KB2528583), you must resolve any issues with the previous operation that failed. View the summary.txt log to determine why the previous operation failed.

Error code 0x84B20001.
------------------------------
BUTTONS:
OK
------------------------------

This happens when you try to install an update or service pack or do an edition upgrade and if that action does not complete successfully.
In my case, I recently tried to do an upgrade of SQL Server 2008 R2 Evaluation version to SQL Server 2012 Evaluation edition and since this upgrade path was not supported, the installation gave an exception which forced me to quit the installer.
After this I tried to install SP1 for SQL Server 2008 R2 and I got this error.

To Fix this issue, you have to "Repair" the existing SQL Server Instance. This option is available in "Add Or Remove Programs" feature of Control Panel.


Once the Repair is completed, you can restart the installation of Service Pack.

Sunday, October 7, 2012

How to change server collation

The Server Collation acts as the default collation for all the system databases on that instance of SQL Server and also for the newly created user databases.
The Collation for an instance is specified during the setup of SQL Server, whereas this can be changed at any point of time by rebuilding the master database and specifying the new collation.
This operation will overwrite the system databases and hence it is strongly recommended to have a complete system backup before proceeding with this activity.

Before you proceed,
  • Make sure you have backup of all user database, jobs, logins, maintenance plans, etc.. 
  • Drop / Detach all user databases
  • Rebuild Master database by specifying new collation
For SQL Server 2005,
Check the current Collation of the server by running the below script
SELECT SERVERPROPERTY('collation') AS [Server Collation]

Navigate to the setup path using command prompt and run the below query by changing the parameters
start /wait setup.exe /qb INSTANCENAME=SQL2005 REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=yourSApassword SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI


This will start the GUI for setup

Once the Installation of Prerequisites is completed, you will be presented with the below screen

Click "Yes"
Once this configuration is completed, it will automatically close the GUI.
Now you can verify the change of collation by executing the below commands
SELECT SERVERPROPERTY('collation') AS [Server Collation]

For SQL Server 2008, SQL Server 2008 R2, SQL 2012,
Check the current Collation of the server by running the below script
SELECT SERVERPROPERTY('collation') AS [Server Collation]

Navigate to the setup path using command prompt and run the below query by changing the parameters
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName
/SQLSYSADMINACCOUNTS=adminaccount /[ SAPWD= StrongPassword ]
/SQLCOLLATION=CollationName


Wait for the configuration to complete

Verify the change of collation by executing the below commands
SELECT SERVERPROPERTY('collation') AS [Server Collation]
Once the activity of changing the collation is completed,

  • Recreate / Attach the users databases
  • Make sure to verify / recreate the jobs, logins, maintenance plans, etc..