SQL Server 2005 Restore from Full and Differential Backup

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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.