Moving SqlExpress Data from one location to another

Question

Moving SqlExpress Data from one location to another

ssudry

SSC-Addicted

Points: 422
More actions
December 14, 2010 at 2:02 pm

#228486

Hi there,
I have installed SQL Server 2008 R2 as well as SQL Express as two instances on default paths.
I need to change the location of SQL Express to a different drive. I did the following:
1) Stopped SQL Express instance (Server & Agent)
2) Exported SQLExpress registry
3) Renamed SQLExpress registry
4) Modified file from the exported registry with the new path
5) Imported the file to registry
6) When attempting to start SQLExpress, I receive an error 'Z:\MSSQL10\MSSQL10.EXPRESS\MSSQL\LOG\ERRORLOG' OS Error 3 (Can't find path)
Any help is appreciated.
Thanks,
Sol

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281344 More actions · Answer 1

Do the SQLExpress instances house any data currently?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ssudry SSC-Addicted Points: 422 More actions · Answer 2

Hi,

They do. The house Version Control data.

Thanks,

Sol

SQLRNNR SSC Guru Points: 281344 More actions · Answer 3

Which files did you move?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ssudry SSC-Addicted Points: 422 More actions · Answer 4

Hi,

The following directories: Binn\DllTmp32, Binn\DllTmp64, DATA, Log, Backup, Jobs, repldata, Template Data, upgrade

Sol

SQLRNNR SSC Guru Points: 281344 More actions · Answer 5

I recommend putting them back. Then look up these articles.

Move system databases:

http://msdn.microsoft.com/en-us/library/ms345408.aspx

Move user databases:

http://msdn.microsoft.com/en-us/library/ms345483.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ssudry SSC-Addicted Points: 422 More actions · Answer 6

Hi,

The following directories and files

Backup, DATA, Log, JOBS, repldata, Template Data, Binn/DllTmp32, Binn/DllTmp64, upgrade

Thanks,

Sol

SQLRNNR SSC Guru Points: 281344 More actions · Answer 7

Anything that you did - undo it.

Then go back and read those articles that I listed.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ssudry SSC-Addicted Points: 422 More actions · Answer 8

Hi,

Thanks for the articles.

I have two instance of MSSQL, MSSQL10.MSSQL1 and MSSQL10.SQLEXPRESS

When I run the query: select * FROM sys.master_files, I see entries for MSSQL10.MSSQL1 , however I don't see any entries for MSSQL10.SQLEXPRESS.

Are they treated differently?

Any help is appreciated.

Thanks,

Sol

SQLRNNR SSC Guru Points: 281344 More actions · Answer 9

Yes. Each instance is handled differently. It might help you to picture the two instances as being on separate servers (even though they are not). You would have to run that query on each instance.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ssudry SSC-Addicted Points: 422 More actions · Answer 10

Hi Jason,

The articles are helpful. I'm still running into the following issue, when I run the following query

use master;

ALTER DATABASE master MODIFY FILE (NAME = master, FILENAME = 'Z:\MSSQL10\MSSQL10.SQLEXPRESS\MSSQL\DATA\master.mdf');

ALTER DATABASE master MODIFY FILE (NAME = mastlog, FILENAME = 'Z:\MSSQL10\MSSQL10.SQLEXPRESS\MSSQL\DATA\mastlog.ldf');

--------------------I get -----------------------------

Msg 5121, Level 16, State 1, Line 2

The path specified by "Z:\MSSQL10\MSSQL10.SQLEXPRESS\MSSQL\DATA\master.mdf" is not in a valid directory.

Msg 5121, Level 16, State 1, Line 3

The path specified by "Z:\MSSQL10\MSSQL10.SQLEXPRESS\MSSQL\DATA\mastlog.ldf" is not in a valid directory.

-----------------------------------------------------------------------------------------------------------

The path is valid and files are present. I mapped drive Z: with different user. Can this cause the issue?

Thanks,

Sol

SQLRNNR SSC Guru Points: 281344 More actions · Answer 11

Is drive Z a network drive?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ssudry SSC-Addicted Points: 422 More actions · Answer 12

ssudry

SSC-Addicted

Points: 422

December 15, 2010 at 1:03 pm

#1263107

Yes.

Sol

SQLRNNR SSC Guru Points: 281344 More actions · Answer 13

You should place those on local disks or san attached disks - not network shares.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ssudry SSC-Addicted Points: 422 More actions · Answer 14

Thanks! I will look into it. We wanted to use on Network drive as it is backed up by the IT group.

Best,

Sol