MOVING SYSTEM DATABASES FROM ONE LOCATION TO ANOTHER LOCATION

MOVING SYSTEM DATABASES FROM ONE LOCATION TO ANOTHER LOCATION

MOVING MASTER DATABASE:-

StartàAll ProgramsàMicrosoft SQL ServeràConfiguration toolsàSQL Server Configuration Manager

Here select SQL Server Services, right click on SQL Server instance and select Properties.

In Properties go to Advanced.

In Startup Parameters select the content and copy that to a notepad as shown below:

Here data file is in “C:\SQL Server\MSSQL.1\MSSQL\DATA” and the log file is in “C:\SQL Server\MSSQL.1\MSSQL\DATA”

Make the changes in notepad where we want to store the data and log file of master.

Copy the changed paths to the startup Parameters. Click Apply button.

Below shown message will be displayed. Click Ok on warning as shown below. Click Ok on Properties window.

Stop the SQL Server instance.

Change the location of the master.mdf and mastlog.ldf files to the specified locations and start the server.

Open Microsoft SQL Server Management Studio, open new Query and try the statements shown below:

USE master

go

select * from sys.sysfiles

go

 

Here you can see the file path, which is changed as shown in above screen.

MOVING MODEL DATABASE:

In the below shown screen shot we can see the logical name for the model and the file path where it is stored.

With the help of alter command shown below we can change the path of the model database.

use model

go

ALTER DATABASE model MODIFY FILE

(NAME='modeldev', FILENAME='C:\Data\Model_data\model.mdf')

go

ALTER DATABASE model MODIFY FILE

(NAME ='modellog', FILENAME='C:\Log\Model_log\modellog.ldf')

go

Note: “The file "modeldev" has been modified in the system catalog. The new path will be used the next time the database is started.” This message will be displayed after execution of the above mentioned statement.

Now we have to stop the SQL Server instance.

StartàAll ProgramsàMicrosoft SQL ServeràConfiguration toolsàSQL Server Configuration Manager

 

Change the location of the “model.mdf” and “modellog.ldf” files to the specified locations and start the server.

Open Microsoft SQL Server Management Studio, open new Query and try the statements shown below:

USE model

go

select * from sys.sysfiles

go

Here you can see the file path, which is changed as shown in above screen.

MOVING TEMPDB DATABASE:

In the below shown screen shot we can see the logical name of the tempdb and the file path where it is stored.

Use the below mentioned command to change the default path for the tempdb.mdf and templog.ldf files.

use tempdb

go

ALTER DATABASE tempdb MODIFY FILE

(NAME='tempdev', FILENAME='C:\Data\Tempdb_data\tempdb.mdf')

go

ALTER DATABASE tempdb MODIFY FILE

(NAME='templog', FILENAME='C:\Log\Tempdb_log\templog.ldf')

go

Note: “The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.” This message will be displayed after execution of the above mentioned statement.

We can restart the SQL Server either using “SQL Server Configuration Manager” or using commands in command prompt as shown below.

Use the command “ NET STOP MSSQL$<instance>” is the syntax to be used, to stop the server.

To start the server below shown command is used.

“NET START MSSQL$<instance>” is the syntax to be used, to start the server.

Open SQL Server Management Studio and open the “New Query” and check the paths of the tempdb data and log file information using below statements

USE TEMPDB

GO

SELECT * FROM SYS.SYSFILES

GO

Note: For Tempdb database there is no need of copying of “tempdb.mdf” and “templog.ldf” files from older location to newer location. “As the server starts every time the tempdb creates newly.”

 

MOVING MSDB DATABASE:

Here we can see the logical name and the file location of the msdb database.

Use the below mentioned command to change the default path for the MSDBData.mdf and MSDBLog.ldf files.

USE msdb

GO

ALTER DATABASE msdb MODIFY FILE

(NAME='MSDBData', FILENAME='C:\Data\MSDB_Data\MSDBData.mdf')

GO

ALTER DATABASE msdb MODIFY FILE

(NAME='MSDBLog', FILENAME='C:\Log\MSDB_log\MSDBLog.ldf')

Note: “The file "MSDBData" has been modified in the system catalog. The new path will be used the next time the database is started.” This message will be displayed after execution of the above mentioned statement.

 

Now we have to stop the SQL Server instance.

StartàAll ProgramsàMicrosoft SQL ServeràConfiguration toolsàSQL Server Configuration Manager

 

Change the location of the “MSDBData.mdf” and “MSDBLog.ldf” files to the specified locations and start the server.

Open Microsoft SQL Server Management Studio, open new Query and try the statements shown below:

USE MSDB

GO

SELECT * FROM SYS.SYSFILES

GO

Here you can see the file path, which is changed as shown in above screen.

 

MOVING TABLE FROM PRIMARY TO SECONDARY FILEGROUP

MOVING TABLE FROM PRIMARY TO SECONDARY FILEGROUP

CLUSTERED TABLE:

Create a database here in our discussion we consider the database name as ‘test1’

Create a secondary filegroup.

Create the file on the secondary filegroup.

Creating a table on primary.

Checking where the table is created.

With the help of “index_name” we can move the table from primary to secondary filegroup.

Checking where the change happened successfully or not.

MOVING NON CLUSTERED TABLE FROM PRIMARY TO SECONDARY FILEGROUP

Creating a database ‘data1’ on primary:

Creating a secondary filegroup with name fg1

Creating table:

Information about the table: we don’t have “index_name” information.

Creating a file on secondary file group:

Creating indexes for the table:

Information about the table: Here we have information about the “index_name”.

Creating index on table to move the table to secondary filegroup.

Information about the table which shows that, the table is moved to secondary filegroup.

 

moving of table from one instance to other instance

MOVING OF A TABLE FORM ONE INSTANCE TO OTHER

lets take 2 different instances NLR & BLR and 2 different database as one and two in each of the instance and table name STUDENT

lets see the student table has the data of about  196608 records as shown

we have to move it form NLR to BLR instance

go to one database in nlr instance ==> one database  right click==>task==>generate scripts

as shown

next follow below screen shorts

select the database in which table is present

in the next dialog box select the following to true

script data ==> true

script index==>true

 

next go to file location right click edit ==>copy all the script and paste in the blr instance and change the name database at the staring of the script and write the database name two and execute the script

thats all how to move the data between two different instances

 

INSTALLATION OF SQL SERVER 2005

INSTALLATION OF SQL SERVER 2005

To install sql server 2005

Insert media disk in cd drive or we can install from the media files from the hard disk.

Open the  media file we can find the below screen short

Click on setup.exe file we get a license agreement window accept and click on next.

A window will open installing  prerequisites  ie  .net frame work, sql server native clients , sql server support files . click to install to continue .

Else we can install them before the installation of sql server.

Click  next in the popup window.

Sql server installation preparation will be started .

It will ask for registration ( name and product key) enter the details and click next.

It will then asks for type of instance (default or named) select of your choice then click on next .

 

It will ask for the features to be installed select the features you required .

Click on advanced and choose the file path of “mdf” and “ldf” files.

Its recommended to keep them in different drives as a best practice

Click on next.

It will ask for type of account select of your choice .

Choose the services you want install .

then click on next.

 

 

 

 

It will ask for type of authentication (windows or mixed)

Windows authentication mode will allow the users who are authorized in windows level only to connect to the sql server (this is the highest level of security )

Mixed mode will allow both the users windows level and sql users to access the sql server

(as the best practice we will choose mixed authentication mode)

It will ask for “sa” account password choose the strongest password you can for you database safety

Click on next

Password is secret1#

Then it will ask for collation (language) by default it will Latin General (case insensitive)

Click on next

Then it will ask for error report to be sent to microsoft  it’s not necessary click on next 

 

Then it will show the features going to be installed check them once and click on install

 

Then it starts the actual installation process wait until it completes the installation after completion click on finish.

Restart the server as the best practice then try to login in sql server

 

 

 

 

That’s all installation is completed .