Sql-server – SQL Server 2005 Restore from Full and Differential Backup

backuprestoresql server

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.

Best Answer



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

Related Question