Link to home
Start Free TrialLog in
Avatar of prlit
prlit

asked on

How to move SQL Express server

I'm running an SQL express server as the backend of my database but need to move it to a new server. I cannot find steps on the internet to do so. Any help would be much appeciated.
Avatar of aissim
aissim
Flag of United States of America image

I haven't personally had to do this with SQL Express....but my best guess would be to install SQL Express on the new machine and then replicate the DB from old to new.
Avatar of prlit
prlit

ASKER

Is there somewhere I can find steps to to do the replication?
As you probably know there's no SQL agent with express...so there's only two alternate options.

Here's an article that lists your various options, as well as links to 'How-To's' for each option: http://msdn2.microsoft.com/en-us/library/ms165686.aspx


Avatar of prlit

ASKER

If my origional server died and I had to pull it off a backup tape, how would I do so?
You could put it on disk from tape, and then the following from the sql command window:

RESTORE DATABASE dbname
FROM DISK = c:\foldername\backupname.bak
GO
Avatar of prlit

ASKER

That easy?? I just want to copy the olf c:\program files\sql express\ to an external hard drive and then hook that drive to the new server. There's a very little data on the server. What files should I copy?
any mdf, ldf or ndf files. Assuming the new server has the same name and IP address. It is a matter of installing the SQL Express as the same name and all. Then shut down the SQL Services, replace the ?df files and restarting the services.
You'll probably have to install SQL Express on the new server...then copy the .bak file over (or put it on your external drive), then use the Restore command. It won't work to just copy the program files folder over to the new server - it needs to actually be installed (for registry entries, etc.).
Avatar of prlit

ASKER

The server doesn't have the same/ip name. I did a search of the SQL server express directory and there were no .bak files. I just want to copy all of the "data files" to a external hard drive and move them to the new server. I know I can't copy the program. If I go through and copy the .mdf, .ndf, and .ldf files, what command would I use below?

These commands:
RESTORE DATABASE dbname
FROM DISK = c:\foldername\backupname.bak  "what would go here since there's no .bak"
GO

Thanks for all the help!!!
ASKER CERTIFIED SOLUTION
Avatar of Jim P.
Jim P.
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of prlit

ASKER

MY command: sqlcmd sp_attach_db 'MyDBName', 'C:\Data\PRL.mdf', 'C:\Data\PRL_log.ldf'

Gives me: Sqlcmd: 'sqlcmd sp_attach_db' : Unkown Option. Enter -? for help
Avatar of prlit

ASKER

Or am I actually copying the master.mdf or the dbname.mdf
If you copy the master.mdf from the Oldserver and replace it the newserver, and have the same paths to on your newserver for all the other ?df files they will be there.

If you are using the the new master from the new install then you can attach the pre-existing ?df files using the sp_attach_db command. The syntax I have is for SQL2K/MSDE. It may have changed. And wouldn't it be:

sqlcmd sp_attach_db 'PRL', 'C:\Data\PRL.mdf', 'C:\Data\PRL_log.ldf'

Note that you will have orphaned user ids between the master database and the PRL DB.
Avatar of prlit

ASKER

Gives me: Sqlcmd: 'sqlcmd sp_attach_db' : Unkown Option. Enter -? for help

I used: sqlcmd sp_attach_db 'PRL', 'C:\Data\PRL.mdf', 'C:\Data\PRL_log.ldf' Everything in that line is right for my details. Is there a typo in the command?
Avatar of prlit

ASKER

I was able to attach the server using the GUI of SQL Express. Very easy, did not require any commands.
I'm used to MSDE which did not really have any native GUI. Glad it worked out for you.

Glad to be of assistance. May all your days get brighter and brighter.