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 process Expand / Collapse
Author
Message
Posted Sunday, November 18, 2012 9:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 1, 2014 2:47 AM
Points: 152, Visits: 197
Hi,
Hi,
Our business can afford to loose data up-to one hour.
So in-case of say if a table is dropped or a disaster, etc., I would like to be able to recover the data at lease up-to one hour ago.
Let's say the database failes at 11:55 pm
I am trying to set the scripts of backup and restore of the database as follows:

Are the sample scripts correct please? See below:

BACKUP
------
1-
Make sure the database recovery is set to full

--schedule this script to run every mid-night monday-friday
BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK'
GO

2-
schedule this transaction log backup to run every 15 minutes
BACKUP LOG AdventureWorks TO DISK = 'C:\AdventureWorks.TRN'
GO

Re-store
--------
1-
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH NORECOVERY
GO

2-
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
WITH RECOVERY,
STOPAT = 'Nov 12, 2012 11:45:00 PM'
GO
Post #1386052
Posted Sunday, November 18, 2012 10:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 39,898, Visits: 36,243
Don't append backups to one file. Backups should ideally each to their own file, preferably time-stamped (DatabaseName20121115211501.trn)

To restore to a point in time, you need a full backup taken before that point and all log backups since then (not just one of them)



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 #1386060
Posted Monday, November 19, 2012 7:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 1, 2014 2:47 AM
Points: 152, Visits: 197
GilaMonster (11/18/2012)
Don't append backups to one file. Backups should ideally each to their own file, preferably time-stamped (DatabaseName20121115211501.trn)

To restore to a point in time, you need a full backup taken before that point and all log backups since then (not just one of them)

1-Do you mean to overwrite?
2-I am thinking of having t he full backup of the database every mid-night and transaction log backups every 15 minutes.
Does this mean if there is a disaster at 11:55 pm, to restore to 11:45 pm I should a) get the previous midnight database backup, b) from that point I have to restore every singly transaction log in order until 11:45 pm?
Post #1386385
Posted Monday, November 19, 2012 7:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:46 AM
Points: 6,378, Visits: 13,719
arkiboys (11/19/2012)
1-Do you mean to overwrite?

No, write to a new file each time with a unique name


arkiboys (11/19/2012)
2-I am thinking of having t he full backup of the database every mid-night and transaction log backups every 15 minutes.
Does this mean if there is a disaster at 11:55 pm, to restore to 11:45 pm I should a) get the previous midnight database backup, b) from that point I have to restore every singly transaction log in order until 11:45 pm?

Yes, the last full backup and any log backups up to the point you wish to restore to.


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

"Ya can't make an omelette without breaking just a few eggs"
Post #1386397
Posted Monday, November 19, 2012 7:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 1, 2014 2:47 AM
Points: 152, Visits: 197
Perry Whittle (11/19/2012)
arkiboys (11/19/2012)
1-Do you mean to overwrite?

No, write to a new file each time with a unique name


arkiboys (11/19/2012)
2-I am thinking of having t he full backup of the database every mid-night and transaction log backups every 15 minutes.
Does this mean if there is a disaster at 11:55 pm, to restore to 11:45 pm I should a) get the previous midnight database backup, b) from that point I have to restore every singly transaction log in order until 11:45 pm?

Yes, the last full backup and any log backups up to the point you wish to restore to.

1- if the backup of transaction log is scheduled to run say every 15 minutes then how can I "write to a new file each time with a unique name" ?
2- restoring each transaction log sinice the previous midnight seems to be a long process. How about introducing a differential backup?
Post #1386402
Posted Monday, November 19, 2012 9:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 3:08 AM
Points: 127, Visits: 131
arkiboys (11/19/2012)
1- if the backup of transaction log is scheduled to run say every 15 minutes then how can I "write to a new file each time with a unique name" ?


Have a look at Ola Hallengren's SQL Server Maintenance Solution. It's very easy to setup and use. Read and download from:

http://ola.hallengren.com/

Many DBAs use it.
Post #1386446
Posted Monday, November 19, 2012 9:26 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 39,898, Visits: 36,243
arkiboys (11/19/2012)
2- restoring each transaction log sinice the previous midnight seems to be a long process. How about introducing a differential backup?


Then you would need to restore the full backup, the latest differential and all log backups from the differential to the point you're restoring to.



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 #1386456
Posted Monday, November 19, 2012 9:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:46 AM
Points: 6,378, Visits: 13,719
arkiboys (11/19/2012)
1- if the backup of transaction log is scheduled to run say every 15 minutes then how can I "write to a new file each time with a unique name" ?

Your backup script would use the timestamp to form part of the filename. It's common to append the database name, backup type and timestamp to form the backup filename.

E.g. a log backup run on Noddy database at 15:34pm would generate a file called

Noddy_TLog_20121119_153400.trn




arkiboys (11/19/2012)
2- restoring each transaction log sinice the previous midnight seems to be a long process. How about introducing a differential backup?

Now your learning, good idea


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

"Ya can't make an omelette without breaking just a few eggs"
Post #1386460
Posted Wednesday, November 21, 2012 11:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 1, 2014 2:47 AM
Points: 152, Visits: 197
Thank you
Post #1387540
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse