SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup/restore full + differential


Backup/restore full + differential

Author
Message
niklasrene
niklasrene
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 63
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218825 Visits: 46279
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 recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


niklasrene
niklasrene
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 63
ok thanks, but i can restore them as one operation? just choose both backup files?
Niklas
george sibbald
george sibbald
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24028 Visits: 13698
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.

---------------------------------------------------------------------
niklasrene
niklasrene
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 63
the db is set to read only about an hour after i take the first backup, so in that hour, changes have happened Smile
Richard Fryar
Richard Fryar
SSC Eights!
SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)SSC Eights! (911 reputation)

Group: General Forum Members
Points: 911 Visits: 1172
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.


Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com
dan-572483
dan-572483
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3487 Visits: 2000
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.
durai nagarajan
durai nagarajan
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3401 Visits: 2784
Will DB in norecovery mode accepts "Set to read only" command?

Regards
Durai Nagarajan
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218825 Visits: 46279
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 recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


durai nagarajan
durai nagarajan
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3401 Visits: 2784
thanks gail, misunderstood.

Regards
Durai Nagarajan
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search