Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We've just got a new server so I need to copy the existing SQL Server 2008 instance over from the old db box.

I normally do this by copying .mdf and log files over and attaching them but I'm not able to do take the dbs off line as they're used 24/7 so I've backed up the databases and restored them on the new machine. However I've ended up with a few issues one of them being related to orphaned users. So what I'm looking for is a fail-safe process to move everything across with minimal fuss/bother/headache. I'm a .Net dev with a fair amount of SQL under my belt but the inner workings of SQL Server are largely a mystery to me and I find the MS documentation painful to trawl.

Please help.

share|improve this question
1  
You can find some ideas in this previous question. But the previous answers are pretty explanatory. I think that the best idea would be to establish a mirroring and break it after, or a Log shipping (can do only from wizard) for all dbs and just restore further the necessary log backups, but if you say you're not a DBA, then probably the backups method is better. –  Marian Mar 24 '11 at 15:10
add comment

2 Answers

If you cannot take the databases offline, tne you need to do backup/restore. I would suggest the following:

  1. Install SQL 2008 on the new box, using the same file structure as the old box for MDF and LDF files.
  2. Take backups of all databases on the old box.
  3. Restore master from the old box to the new box having the SQL started in single user mode. Restore master method
  4. Restore each database from the old box to the new box with NORECOVERY option in order to be able to apply future Diff or T-log backups.
  5. Restore msdb from the old box to the new box.

If you build and configure the new box to look exactly like the old one, then you will have minimal fuss.

I have no idea how big your databases are, so this method could take a long time. The easiest thing to do would be to shut down the old box and copy the MDF and LDF files and then (after restoring master) you would only need to restart SQL to have your databases online. But you stated that is not an option, as the databases are not allowed to be offline.

share|improve this answer
3  
Don't forget copying any data changes made after taking the backups. You can take a diff backup or tlog backup and apply them to the new server after it's up, but before you switch over. –  Eric Humphrey - lotsahelp Mar 24 '11 at 13:16
    
good point, i forgot to mention that as well. perhaps i should include a db mirror configuration, which could be broken afterwards? –  SQLRockstar Mar 24 '11 at 13:27
1  
I would imagine someone already has an extensive article on the different ways to migrate servers. Also, here's an article on transferring logins to a new server: support.microsoft.com/kb/246133 –  Eric Humphrey - lotsahelp Mar 24 '11 at 13:39
add comment

You mention 24/7 operation, so the best way to do this is to create a database mirror on the new system, then you can simply switch over with minimum - perhaps zero, depending on how your application is structured - downtime using a client-side redirect. The basic steps are:

  1. Make sure your primary database is running in FULL recovery mode.
  2. Backup the primary and restore it on the mirror WITH NORECOVERY
  3. Create "endpoints" on both servers, and ensure connectivity (e.g. firewall rules), setting the port and IP address correctly) e.g:

    CREATE ENDPOINT endpoint1
    STATE=STARTED AS TCP(LISTENER_PORT = 5222, LISTENER_IP = 192.168.1.5) 
    FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)
    
  4. Setup the mirroring on the mirror, pointing at the primary:

    ALTER DATABASE GaiusMirrorDB SET PARTNER = 'TCP://192.168.1.5:5222'
    go
    EXEC sys.sp_dbmmonitoraddmonitoring
    go
    
  5. And on the primary, pointing at the mirror (just a different database name and IP address.

  6. Then, when the time comes, just switch over on the primary to the mirror:

    ALTER DATABASE GaiusDB SET PARTNER FAILOVER
    GO
    

Note: I am assuming that these two servers are on the same network, so it will be OK to operate in synchronous mode. If this is over a WAN link, use async mode.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.