Backup/restore full + differential
-
Hi all, we are upgrading an application, and I need to backup two quite big databases, and restore them to another sql server.
Because the dbs must be set to read-only, and we have short time, I want to take a full backup before read-only, and just a differential backup after.
And the restore the backup set to the new server.
The dbs are simple rec mode.
Is this the right procedure, or can some one describe the correct way:
1. full backup, new backup set name "example" file name db.bak
2. set to read-only
3. differential backup, use same backup set name "example", same file name db.bak
4. move db.bak to another server
5. restore db.bak to new server
6. set to read/write
-
Pretty much, just use different backup file names. Otherwise you're liable to make some mistake somewhere.
db_full.bak and db_diff.bak for example.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
ok thanks, but i can restore them as one operation? just choose both backup files?
Niklas
-
if the database is being set to read only there would be no need for another backup, nothing will change.
if you want to save time:
full backup to file
restore to new location ahead of migration
stop application
set database read only
take differential backup
restore that to new location
you will need logins in place via sp_help_rev_login and whatever other SQL related components the app uses.
---------------------------------------------------------------------
-
the db is set to read only about an hour after i take the first backup, so in that hour, changes have happened 🙂
-
niklasrene (11/29/2013)
ok thanks, but i can restore them as one operation? just choose both backup files?Niklas
RESTORE DATABASE blah FROM DISK = 'BACKUPPATH\DB_FULL.BAK'WITH STATS, NORECOVERY;
RESTORE DATABASE blah FROM DISK = 'BACKUPPATH\DB_DIFF.BAK'
WITH STATS, RECOVERY;
Of course you'll also need WITH MOVE clauses if the new database has a different path.
And the database will be restored readonly, as you set it readonly before the diff backup.
-
To minimize read-only time, I'd change the order a little.
1. Full Backup
2. Copy backup to new server & restore with NORECOVERY
3. Set to read only
4. Differential Backup
5. Copy differential to new server, restore WITH RECOVERY
6. Set to read/write.
It doesn't make sense to restore the full & differential in one batch. You might as well just do a full backup & restore.
-
Will DB in norecovery mode accepts "Set to read only" command?
Regards
Durai Nagarajan -
durai nagarajan (12/4/2013)
Will DB in norecovery mode accepts "Set to read only" command?No, because it's inaccessible (can't be read or written). However in the list above, 'set to read only' means set the source database read only, the one that the backup was made from, not the new DB that's been restored norecovery.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
thanks gail, misunderstood.
Regards
Durai Nagarajan
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply