Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backup/restore full + differential Expand / Collapse
Author
Message
Posted Friday, November 29, 2013 5:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:16 AM
Points: 4, Visits: 39
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
Post #1518510
Posted Friday, November 29, 2013 5:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:43 AM
Points: 41,517, Visits: 34,434
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 2008, MVP
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

Post #1518520
Posted Friday, November 29, 2013 6:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:16 AM
Points: 4, Visits: 39
ok thanks, but i can restore them as one operation? just choose both backup files?
Niklas
Post #1518527
Posted Friday, November 29, 2013 6:16 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:53 PM
Points: 5,845, Visits: 12,576
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.


---------------------------------------------------------------------

Post #1518534
Posted Friday, November 29, 2013 9:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:16 AM
Points: 4, Visits: 39
the db is set to read only about an hour after i take the first backup, so in that hour, changes have happened :)
Post #1518569
Posted Wednesday, December 04, 2013 4:41 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 28, 2014 5:23 AM
Points: 283, Visits: 1,110
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
Post #1519837
Posted Wednesday, December 04, 2013 4:58 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 504, Visits: 1,462
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.
Post #1519840
Posted Wednesday, December 04, 2013 10:56 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 5:48 AM
Points: 1,048, Visits: 2,646
Will DB in norecovery mode accepts "Set to read only" command?


Regards
Durai Nagarajan
Post #1519870
Posted Wednesday, December 04, 2013 11:45 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:43 AM
Points: 41,517, Visits: 34,434
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 2008, MVP
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

Post #1519878
Posted Thursday, December 05, 2013 12:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 5:48 AM
Points: 1,048, Visits: 2,646
thanks gail, misunderstood.



Regards
Durai Nagarajan
Post #1519890
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse