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:
- Is this safe to run on a production database (I am paranoid about overwriting files, etc)?
- 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.