Moving an entire server(sql 2005) to a new physical machine

I'm trying to finalize the details of moving an entire sql server to a new box.

Is there a simpler way to do this? Can I restore all the system and user databases to the new server?

This is what I've done in sql 2000.

Backup all the system and user databases from the old Server (ServerB)

Install the new default server on a new box (ServerA)

script all the server level security logins from the old Server (ServerB) and create them on the new Server (ServerA)

Restore the system databases to the new Server (Server A) msdb

,tempdb,model. Restore all the user databases to the new server (Server A)

Yes...you can restore all databases to the new server.

The following articles provide more information on moving databases to another server:

How to move databases between computers that are running SQL Server

http://support.microsoft.com/kb/314546

How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server

http://support.microsoft.com/kb/224071

-Sue

Move entire SQL Server instance

I have tried searching all over and have not been able to find what I need, but this is what I have pieced together.

Background:
I need to move an entire instance of SQL Server 2000 SP3 running on Windows Server 2000 to a new physical server running SQL Server 2000 SP4 running on Windows Server 2003. I guess my biggest question is how do I move system databases (master,msdb,tempdb,model), I don't particularly like the idea of using sp_detach/sp_attach on system databases?

This is what I had in mind.
-Install SP4 on the source server
-Backup source to tape
-Restore from tape to destination server (restore system dbs as re_master,re_msdb)
-Shut SQL services down on destination server.
-Replace the system dbs .mdf and .ldf with the re_master & re_msdb .mdf and .ldf
-Restart SQL services
-Restore User databases from tape.

Ultimately, I'm asking can you change the undlying .mdf & .ldf files for system databases?

Also, are there any issues going from Windows Server 2000 to Windows Server 2003?

Thanks in advance.gee, why would you want to move temp? most people would not need to move model either.

I would...

1. backup your source user databases.
2. restore them to the new server.
3. migrate what you need from master and msdb and you can do some of this with DTS I believe and you can have EM script some other things. It really depends on your needs.|||You can also backup and restore the msdb database, if you have large numbers of jobs and/or DTS packages (shudder) to move. You will have to go in and update the sysjobs table, so the SQL Agent on the new machine does not think it is hosting jobs from a master job server, though. There is no good way to go through jobs and DTS packages for all the hard-coded server names, though.|||I've migrated lots of instances (from non-clustered to clustered, from old h/w to new) and have never attempted to restore a backup of master, model or msdb.

- Most of master gets updated when databases are restored/attached.
- Jobs can be scripted (keep new db names the same as old)
- DTS packages can be backed up using a script (and moved using a variation of the same script)

I'm always leery of restoring master, model or msdb because you might get some ugliness in there as well.

Just my $.02.

Regards,

hmscott|||something else that has worked in the past ...
1. Build out your new server.
2. Stop sql server on both old and new servers.
3. Rename the master mdf and ldf on the new server
4. Copy the master mdf and ldf from the old server to the new server in the location specified for the new server
5. Fire up the new server.
6. Modify sysservers for the new server name (unless you will be using the same server name).
7. Use microsoft methods for moving temp and restoring msdb
8. Detach and reattach the user database files, or backup and restore the user database file, whichever you prefer.|||I'd take hmscott's advice:
- Install the new instance
- restore all user databases
- Script everything else you need from the old instance

Because:
- Master: only contains the logins needed, just script them. If you use the same sid's in the script you don't even have to use sp_change_users_login (when you only have Windows Authentication you don't even have to do that)
- Model: most people don't do anything special with model and leave it standard. If you have customized stuff in there do a compare and script it.
- msdb: script (jobs, DTS, etc)! If you really want to, this is the only one I'd restore (prepare for some problems though).
- tempdb: don't touch it, it's useless to try. Each restart will empty tempdb so there's nothing in there to re-use. If there is, you're doing something wrong.|||Thanks for all the insight, in case you couldn't tell this is my first migration and I'm just trying to make sure I don't miss anything. I'll let you know what I did and how it turned out.|||Am I understanding this correctly, that I don't have to install SP4 on the source server if I take Lexiflex & hmscott's advice?|||that's correct|||Am I understanding this correctly, that I don't have to install SP4 on the source server if I take Lexiflex & hmscott's advice?
Why wouldn't you want to install SP4?

Btw: if the old server has logshipping, replication or linked servers don't forget to script or rebuild them too. They're also in master...|||I probably will install SP4 on the source. I was just wondering because the source server is going to get wiped out and have a clean install of Windows Server 2003 and new installation of SQL anyways.

No logshipping, replication or linked servers to worry about.|||Sorry, I read your post too quickly. You said source-server not target :o

You do not need to install SP4 on the source.

Move entire DB, best practices?

Hello, we're in the process of moving our website to a new datacenter and was wondering if anyone had any do's/don'ts about moving an entire database?

Background, both boxes are running sql2000, the current db is 16GB and since it's used for our website, I'd prefer to minimize downtime as much as possible.

Last time we made a move like this, we used DTS and it literally took 8 hours. We also had referential integrity issues. This time, I'm thinking about detaching the database on the current box, copying both the mdf and ldf over to the new box and then reattaching. I'm unclear as to how that would affect users/logins and if there is anything else I'm not seeing.

BTW, I freely disclaim I'm not a SQL admin (I guess that statement didn't surprise anyone).

Appreciate any feedback!to be honest a sql newbie may not want to detatch and attatch a db. It is a bit proccess oreinted and you could kill your production box. The easiest thing that I have found is to take the most current full backup, move it local to the new machine and restore from it. Now if you dont have the same drive lettering it will be a bit tricky but it can be done. The trick is on the options tab, where you need to force the backup and it also has a window where you can change drive lettering. Just change the letter under the Restore As column and you are set. also u need to leave the database operational.
Hope this helps just let us know if not
Regards
Jim|||Yes, you might get in trouble with the SIDs. Provided you only use SQL Server logins, it can be fixed using sp_change_users_login - study this SP in BOL.

I guess you could script out names, SIDs and passwords.
After the copying, drop all database users and re-create them using the script. (I haven't done this myself.)

I think server collations need to be the same on both servers.|||Don't you still need to deal with the SID problem?|||Honetsly you may not have to do security seeings how it is part of the db and thus in the backup. try it without first and see
Regards JimOriginally posted by Coolberg
Don't you still need to deal with the SID problem?|||Originally posted by JDionne
Honetsly you may not have to do security seeings how it is part of the db and thus in the backup. try it without first and see
Regards Jim

Yes, but will the mapping between logins and users be retained, since it's stored in the master (I believe) database?|||Im prety shure that it will, if im not mistaken when you do a recovery operation the master db is updated. The great thing of having two databases is that if it doesnt you can always script the security from the other :). Id give it a try and see what happens. Just test before you go into production.
Regards
Jim

Originally posted by Coolberg
Yes, but will the mapping between logins and users be retained, since it's stored in the master (I believe) database?|||Interesting. I'll play with this on Monday!|||Let me know how it goes|||JDionne, I'm sure there are many things I'm in the dark about regarding detach/attach, but it does seem pretty straightforward. Am I missing something besides right-clicking on the DB, selecting detach (update stats), copying mdf/ldf to the new box and selecting attach?|||I don't recommend you to do detach/attach because it can be more downtime .
I think if you want to avoid downtime you have to running both of them(old production, new production) paralell ,You can backup/restore old to new and set replication (transact) both of them when you check , test and everything is OK you will switch production to new production it can make a little downtime but I thing It's safer way and less downtime than detach/attach to new box but it's take loger time|||RE: Hello, we're in the process of moving our website to a new datacenter and was wondering if anyone had any do's/don'ts about moving an entire database?
Background, both boxes are running sql2000, the current db is 16GB and since it's used for our website, I'd prefer to minimize downtime as much as possible.
Last time we made a move like this, we used DTS and it literally took 8 hours. We also had referential integrity issues. This time, I'm thinking about detaching the database on the current box, copying both the mdf and ldf over to the new box and then reattaching. I'm unclear as to how that would affect users/logins and if there is anything else I'm not seeing.
BTW, I freely disclaim I'm not a SQL admin (I guess that statement didn't surprise anyone). Appreciate any feedback!

Q1 [How may downtime be minnimized in moving a database?]

A1 What is 'best' really depends on the circumstances of your specific situation and what resources you have available. You should be dry run testing various techniques to see what will work given whatever your maximum downtime criteria and resource availability is. Since your DB is relativly small, copying your DB files and reattaching on the target server may meet whatever your downtime limit is (much depends on how much bandwidth you have available between the source and target). However, from your post it appears your system will be down / unavailable durring the entire copy and reattachment process. One technique I've used to minnimize downtime to well under one minute with much larger DBs (and a very small budget) involves the following:
1 Do a DB restore from a full backup dump, (delivering a several 100 GB full DB backup dump on tape can save lots of bandwidth expense); then sucessivly apply multiple small transaction log dumps (small TL backup dumps are cheap to transmit compared to huge Full DB backup dumps) to the target server (with norecovery option).
2 When the folks responsible for the application e.g., (your webserver folks) are ready to redirect their application to the target:
i) Dump and restore a final source server transaction log dump, (time it to make it as small as possible), to the target server and take the original DB off-line (immediatly following the final source server transaction log dump).
ii) Recover the restored DB on the target.
iii) Have the application staff redirect the application to the target. This could actually occur at step i or after step ii (much depends on how the application handles failed connections).

Move Database Store

We are replacing our drive arrays on our production SQL 2000 server. Can
anyone please tell me what I have to do to relocate the entire store? Let's
assume that I want to move everything from F:\SQLDATA to G:\SQLDATA. Can I
stop SQL server
xcopy f:\sqldata g:\sqldata /e /i
point SQL to G:\SQLDATA
start SQL server
Life is good
If so, how and where do I reset where SQL looks for the files? in step 3?
How to move SQL Server databases to a new location by using Detach and
Attach functions in SQL Server
http://support.microsoft.com/kb/224071/en-us
Note that the system databases each have special considerations.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"de Graff" <rjdegraff@.hydro.mb.ca> wrote in message
news:uLsVDeHSGHA.196@.TK2MSFTNGP10.phx.gbl...
> We are replacing our drive arrays on our production SQL 2000 server. Can
> anyone please tell me what I have to do to relocate the entire store?
> Let's assume that I want to move everything from F:\SQLDATA to G:\SQLDATA.
> Can I
> stop SQL server
> xcopy f:\sqldata g:\sqldata /e /i
> point SQL to G:\SQLDATA
> start SQL server
> Life is good
> If so, how and where do I reset where SQL looks for the files? in step 3?
>
|||Exactly what I was looking for. Thanks.
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:uikhynHSGHA.1844@.TK2MSFTNGP12.phx.gbl...
> How to move SQL Server databases to a new location by using Detach and
> Attach functions in SQL Server
> http://support.microsoft.com/kb/224071/en-us
> Note that the system databases each have special considerations.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "de Graff" <rjdegraff@.hydro.mb.ca> wrote in message
> news:uLsVDeHSGHA.196@.TK2MSFTNGP10.phx.gbl...
>
|||We use a vendor program called Misys. Recently we migrated to a larger
server with multiple RAID arrays. The application install puts the program
and database files on the same drive. We needed to move the log files and
databases to their own drives.
I see others proposing methods using SQL, but we found that the following
worked well without requiring any SQL programming.
1) Stopped the Misys Server service.
2) Using SQL Server Agent, backed up the database
3) Detached the database
4) Moved the MDF And LDF files to the new locations
5) Re-attached the database. Adjusted the MDF and LDF file locations to the
new ones
6) Re-started the service
7) Adjusted (as needed) any SQL Server Agent jobs that backed up and
compressed the database
Regards,
Hank Arnold
"de Graff" <rjdegraff@.hydro.mb.ca> wrote in message
news:uLsVDeHSGHA.196@.TK2MSFTNGP10.phx.gbl...
> We are replacing our drive arrays on our production SQL 2000 server. Can
> anyone please tell me what I have to do to relocate the entire store?
> Let's assume that I want to move everything from F:\SQLDATA to G:\SQLDATA.
> Can I
> stop SQL server
> xcopy f:\sqldata g:\sqldata /e /i
> point SQL to G:\SQLDATA
> start SQL server
> Life is good
> If so, how and where do I reset where SQL looks for the files? in step 3?
>