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.

I'm not a DBA, however I have been a developer for 10yrs and am competent.

I wish to move our corporate application to a hosted service and I need to copy the data. The data centre is too far from our office to deliver a disk and ftp is too slow. Our current database uses SQL Server 2005; the target is SQL Server 2008.

My plan is to courier a disk with a full backup on a Friday morning and do a restore at the data centre over the weekend. I then plan to FTP a differential backup with deltas from Friday's backup and restore the differential to make the data current.

To test the process, I plan to restore a full backup then create a differential and restore that. My only environment for testing this is our production server. I believe that I can restore the backup to a new database name using the following T-SQL:

-- make sure have good backup
RESTORE VERIFYONLY FROM DISK = 'x:\mypath\data.bak'

-- get logical names
RESTORE FILELISTONLY FROM DISK = 'x:\mypath\data.bak'

-- restore to new db
RESTORE DATABASE MyNewDb
FROM DISK = 'x:\mypath\data.bak'
WITH
    RECOVERY,   
    STATS = 10,
MOVE 'olddb_Data' TO 'x:\mynewpath\MyNewDb.mdf', 
MOVE 'olddb_Log' TO 'x:\mynewpath\MyNewDb.ldf',
MOVE 'sysft_ft_resume' TO 'x:\mynewpath\ft_resume'
GO

By running something similar with VERIFYONLY I have observed that this only works with a full backup if no subsequent differential has been run.

My questions are:

  1. Is this safe to run on a production database (I am paranoid about overwriting files, etc)?
  2. Since a differential appears to alter the full backup, will my plan to ftp the differential work given that the full backup will have no knowledge of the differential?

I don't think this is a complex task although I will pay for a professional, if that's necessary.

Any other suggestions would be gratefully received.

share|improve this question

3 Answers 3

up vote 1 down vote accepted



Hopefully, my previous research can be helpful for you. Here is a step-by-step guide I created when I had to move several very large databases to a data center. It uses log shipping.

Regards,
John

share|improve this answer

To answer your questions:

1) Yes, you can safely run that. Be 100% sure that you're going to a new path, and specify a new DB name (which it appears you have). If you want to practice restoring the Differential then the RESTORE command needs to use NORECOVERY:

RESTORE DATABASE MyNewDb
   FROM DISK = 'x:\mypath\data.bak'
   MOVE 'olddb_Data' TO 'x:\mynewpath\MyNewDb.mdf', 
   MOVE 'olddb_Log' TO 'x:\mynewpath\MyNewDb.ldf',
   MOVE 'sysft_ft_resume' TO 'x:\mynewpath\ft_resume'
   WITH
      NORECOVERY,   
      STATS = 10,
GO

2) I'm not sure what you mean by "alter the full backup". Differential backups are deltas of the last full backup. So if the backup you courier to your data center is the last full backup your new differential will contain your differences.

So assuming the backup you send is the last full backup, the new differential will contain all changes since the full backup and your plan will work. Since you have a slow connection do you know how big your differential might be? If it will take a while you may want to plan downtime, additional backups, etc.

Also when restoring the differential be sure to use RECOVERY on it, to make the new DB available.

share|improve this answer
    
It has taken some time to try this out, but the step-by-step guide was very useful. Thanks –  Romski Feb 23 '12 at 22:05

If you want to restore a differential backup on top of a full backup you will need to change the options for the full backup restore so that it does not recover (WITH NORECOVERY) the database. This will leave the database in a restoring state and then you should be able to apply the differential backup and use the WITH RECOVERY option to recover it afterwards.

share|improve this answer

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.