Friday, August 30, 2019

To change SQL Server Collation


When the master, model, msdb, and tempdb system databases are rebuilt, the databases are dropped and re-created in their original location. If a new collation is specified in the rebuild statement, the system databases are created using that collation setting. Any user modifications to these databases are lost. For example, you may have user-defined objects in the master database, scheduled jobs in msdb, or changes to the default database settings in the model database.

Perform the following tasks before you rebuild the system databases to ensure that you can restore the system databases to their current settings.

1.        Record all server-wide configuration values.

SELECT * FROM sys.configurations;

2.        Record all service packs and hotfixes applied to the instance of SQL Server and the current collation. You must reapply these updates after rebuilding the system databases.

SELECT

SERVERPROPERTY('ProductVersion ') AS ProductVersion,

SERVERPROPERTY('ProductLevel') AS ProductLevel,

SERVERPROPERTY('ResourceVersion') AS ResourceVersion,

SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,

SERVERPROPERTY('Collation') AS Collation;

3.        Record the current location of all data and log files for the system databases. Rebuilding the system databases installs all system databases to their original location. If you have moved system database data or log files to a different location, you must move the files again.

SELECT name, physical_name AS current_file_location

FROM sys.master_files

WHERE database_id IN (DB_ID('master'), DB_ID('model'), DB_ID('msdb'), DB_ID('tempdb'));

4.        Locate the current backup of the master, model, and msdb databases.

5.        If the instance of SQL Server is configured as a replication Distributor, locate the current backup of the distribution database.

6.        Ensure you have appropriate permissions to rebuild the system databases. To perform this operation, you must be a member of the sysadmin fixed server role. For more information, see Server-Level Roles.

7.        Verify that copies of the master, model, msdb data and log template files exist on the local server. The default location for the template files is C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\Templates. These files are used during the rebuild process and must be present for Setup to succeed. If they are missing, run the Repair feature of Setup, or manually copy the files from your installation media. To locate the files on the installation media, navigate to the appropriate platform directory (x86, x64, or ia64) and then navigate to setup\sql_engine_core_inst_msi\Pfiles\SqlServr\MSSQL.X\MSSQL\Binn\Templates.

8.        Script out all the jobs on the server.

9.        Check the database maintenance plan and make sure that you have noted down the schedules for each step in the maintenance plan.

10.    Export all the SSIS packages to the file system.

11.    Check whether any mail profiles are present and copy the details of each profile to a table on one of the user databases.

12.    Script out the logins on server and prepare a list that contains the permissions provided for each login.

13.    Stop the replication agent if you have replication set up. Use the table sysarticles to get the list of objects that are being replicated only if you chose to use GUI to create replication.

14.    Detach all user defined databases using below script.

 

Rebuilding the System Databases

The following procedure rebuilds the master, model, msdb, and tempdb system databases. You cannot specify the system databases to be rebuilt. For clustered instances, this procedure must be performed on the active node and the SQL Server resource in the corresponding cluster application group must be taken offline before performing the procedure.

This procedure does not rebuild the resource database.

To rebuild system databases for an instance of SQL Server:

1.        Insert the SQL Server 2008 R2 installation media into the disk drive.

2.        From a command prompt window, enter the following command. Square brackets are used to indicate optional parameters. Do not enter the brackets. When using the Windows Vista operating system with User Account Control (UAC) enabled, running Setup requires elevated privileges. The command prompt must be run as Administrator.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS="" /SQLCOLLATION=Latin1_General_CI_AS_KS_WS

 

3.        When Setup has completed rebuilding the system databases, it returns to the command prompt with no messages. Examine the Summary.txt log file to verify that the process completed successfully. This file is located at C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Logs.

Post Rebuild Task

After rebuilding the database you may need to perform the following additional tasks:

  • Restore your most recent full backups of the master, model, and msdb databases.
  • Attach all the user databases
  • Recreate the linked servers and logins. Check for orphaned users using the command
  • Create all the mail profiles (tailor the script given in step6 and use it).
  •  Import the SSIS packages to the SQL Server from the file system.
  •  Recreate all the jobs.
  •  Create the maintenance plan using schedules.
  • Setup replication.
  • Create logins.

Thursday, July 3, 2014

Packt’s celebrates 10 years with a special $10 offer

This month marks 10 years since we embarked on our mission to deliver effective learning and information services to IT professionals. To celebrate this huge milestone, we are offering ALL of our eBooks and Videos at just $10 each for 10 days – this promotion covers every title and you can stock up on as many copies as you like until July 5th. More information about the deal can be found below in a press release from Packt. To skip right to the deals, click here : http://bit.ly/ashjswl10






Some of the categories you can choose the books from:
  • Application Development
  • Big Data and Business Intelligence
  • Business Skills
  • CMS and eCommerce
  • Enterprise Products and Platforms
  • Game Development
  • Instant
  • Mobile Application Development
  • Networking and Servers
  • Video
  • Virtualization and Cloud
  • Web Development

Wednesday, July 2, 2014

Review for Managing Windows Servers with Chef

I was recommended ManagingWindows Servers with Chef by a marketing professional at Packt. Would like to share my thoughts on this.

This book has practical examples that will help you to deploy software with ease. After going through the examples you can develop and test recipes for multiple platforms and will also let you manage Windows hosts. This book treated me with great introduction of how to use Chef which helped me understand how to take advantage of it when I am managing my infrastructure.

I recommend it to all system administrators with some exposure to Chef and who are familiar with programming or scripting languages. It will be helpful if you are interested in managing Windows-based systems using Chef. 

Tuesday, May 20, 2014

Microsoft Exchange Server 2013 High Availability: Review

A very well structured good. It shows readers the importance of high availability and resilience. The book is packed with diagrams and scenarios that will help readers use the monitoring features of Exchange 2013.

http://www.packtpub.com/microsoft-exchange-2013-high-availability/book

The book covers basic aspects such as roles, components, and features, as well as advanced aspects such as load balancing, site resilience, and public folders.

The book explains core concepts of MS Exchange in a very intuitive way. Great book to have if you want to understand basic administrative and configuring tasks involving MS Exchange server earn to successfully design, configure, and maintain a highly available Exchange 2013 environment by going through different examples and real-world scenarios.

I would highly recommend this book to everyone.

Thursday, May 15, 2014

Review for Getting Started with CreateJS

This is a definitive guide for anyone who is planning to use the prolific CreateJS suite to develop compelling web applications.



It is a for one planning to use the CreateJS to develop web applications. The book contains many screenshots that are highly useful for a web developer. Really helpful for how to create your first working web application which visually impressive. Afshin has a great way to explain how to think about CreateJS.

The book creates a fictitious application that helps its readers implement the features. I'm one of those guys who likes work with the book, so that boosted my understanding big time.

Worth Buying it: http://www.packtpub.com/getting-started-with-createjs/book

Tuesday, May 6, 2014

Packt celebrates International Day Against DRM

Packt Publishing firmly believes that you should be able to read and interact with your content when you want, where you want, and how you want – to that end they have been advocates of DRM-free content since their very first eBook was published back in 2004.



To show their continuing support for Day AgainstDRM, Packt Publishing is offering all its DRM-free content at $10 for 24 hours only on May 6th – that’s all 2000+ eBooks and Videos at www.packtpub.com

“Our top priority at Packt has always been to meet the evolving needs of developers in the most practical way possible, while at the same time protecting the hard work of our authors. DRM-free content continues to be instrumental in making that happen, providing the flexibility and freedom that is essential for an efficient and enhanced learning experience. That’s why we’ve been DRM-free from the beginning – we’ll never put limits on the innovation of our users.”  

– Dave Maclean, Managing Director

Advocates of Day Against DRM are invited to spread the word and celebrate on May 6th by exploring the full range of DRM-free content at www.packtpub.com, where all eBooks and Videos will be $10 for 24 hours.






Thursday, May 1, 2014

Review for ''Learning Geospatial Analysis with Python''

I recommend this book to all those interested in getting a startup with background in understanding Geospatial Analysis.There are few simple Python examples also that says how Python can be used.I think this is a tutorial-style book that helps you how to use Python in geospatial analysis, from a basic start to a quite advanced and complicated level.




read more here : http://www.packtpub.com/learning-geospatial-analysis-with-python/book