Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup/restore full + differential


Backup/restore full + differential

Author
Message
niklasrene
niklasrene
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47213 Visits: 44369
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
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

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

Group: General Forum Members
Points: 6324 Visits: 13687
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
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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 Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 1171
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
SSChasing Mays
SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)

Group: General Forum Members
Points: 641 Visits: 1958
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

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

Regards
Durai Nagarajan
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47213 Visits: 44369
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1107 Visits: 2771
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