DBA – Move master Database to another drive – in simple steps

SQL with Manoj


Well, there are times when you want to move your master database from the default location to some other drive. Now this activity cannot be done with the normal ALTER DATABASE statement with MODIFY FILE option. And you need a spacial handling for this case of master DB.

–> Let’s first check the location of master DB:

Move Master 01

–> Now leave SSMS, and open SSCM i.e. SQL Server Configuration Manager. Here select “SQL Server Service”, and Rigth Click on the instance of SQL Server, and choose Properties. Now select the Startup Parameters tab.

Move Master 02

Here you will see 3 line items:

1. -d is the path of the master data file.

2. -e is the path of the SQL error log file.

3. -l is the path of the master log file.

So, you need to update the 1st and 3rd ones. As I want to move my files to E:SystemDatabasesMaster

View original post 75 more words

Advertisements

SQL Server 2016 full and final version available – Download it now !!!

SQL with Manoj

Its 1st June 2016 and finally Microsoft has released SQL Server 2016 full and final version, and is available for download!

Exactly a year back the first CTP version of SQL Server 2016 was released and we got a chance to get a glimpse of the new features coming in. Then after every month or two Microsoft kept rolling out the CTP versions with more new features, enhancements, and bug fixes on the previous CTP builds.

–> Download SQL Server:

To download SQL Server 2016 you can Register and Download the SQL Server 2016 Full or free Evaluation version (180 days) here.

Or you can Direct download the DVD ISO file image (~2.1 GB) SQLServer2016-x64-ENU.iso

–> Free Developer Version:

Microsoft on March 2016 announced that going forward the Developer version of SQL Server any release will be free for Developers and Learning purpose. Register and Download the Developer version.

Or…

View original post 204 more words

Creating Encryption Key Backup for SSRS 2008R2

Creating Encryption Key Backup for SSRS 2008R2

– r for taking backup of key

-f for file location

-i is the instance name of the Reporting Services

C:\Windows\system32>rskeymgmt -e -f m:\rsdbkey.snk -p Abc1234567 -i PAYAPPSGRP02

 
Are you sure you want to extract the key from the report server? Yes (Y)/ No (N)
: Y

SECURE THE FILE IN A SAFE LOCATION

The command completed successfully

Copy one column to another sqlserver

UPDATE table SET columnB = columnA

–This will update every row.

UPDATE table_name SET
destination_column_name=orig_column_name
WHERE condition_if_necessary

–This will update rows which satisfy the WHERE clause

UPDATE table1
SET column1 = (
SELECT column2
FROM table2
WHERE table2.id = table1.id
);
Whereby:

table1 = table that has the column that needs to be updated
table2 = table that has the column with the data
column1 = blank column that needs the data from column2 (this is in table1)
column2 = column that has the data (that is in table2)

 

–This will update one column based on value of another table column