Copy backup to not destroy backupchain

Question

Copy backup to not destroy backupchain

Landola

SSC Enthusiast

Points: 115
More actions
November 27, 2022 at 7:21 pm

#4118661

Hi,
A bit unsure about how to make a copy only backup and the right way och doing this. I dont want to tamper with the backupchain and do something wrong. In the general tab\Destination\Back up to: , where i can choose destination i alredy have a path where the backups are stored. Can i temporary add a new direction and point the backup to that direction and then remove that when finished? Will the old direction be preserved there?
/L
November 29, 2022 at 6:02 pm

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

Yes... you can backup to anywhere you can reach or the server can reach. There are a couple of ways to do it and, rather than me forgetting something, have a look at the following Microsoft Documentation that teaches how to do it by GUI and by T-SQL. It also teaches how to do it using PowerShell but you probably don't need to go there.

https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server

You like do not need to make a copy-only backup of the transaction log.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Landola SSC Enthusiast Points: 115 More actions · Answer 2

Hi,

Ok, thank you for your explanation. Then i can do it as i thought without taking any risks.

BR

Landola

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

The real risk is you forgetting to use the "Copy Only" functionality. 😀 You can tell I've "never had that problem before", right? 😀 I've made that mistake a couple of times.

Shifting gears a bit, why do you need to make a "Copy Only" backup to begin with? That seems like it could be a bit of a logistics issue.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

For clarity - unless you are also performing differential backups then a regular full backup is the same as a copy only backup. The reason you want to take a copy only backup is to not interfere with the differential chain, since the next differential backup will be based on the last non-copy full backup.

To recover to a point in time the minimum you need is a full backup (regular or copy_only) and all transaction log backups from the full to the point in time to be recovered. Differential backups are a way to reduce the number of files needed to restore to that point in time - but are not required as long as you have the full log backup chain.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

The only time I use the COPY ONLY option is if I need to send the actual file someone. I don't want a file listed in MSDB for my backups for a potential restore if the file doesn't actually exist for me to restore from.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Landola SSC Enthusiast Points: 115 More actions · Answer 6

Hi,

The reason that i want to use the copy only is that i am sending the .bak file to our consultants to troubleshoot when we have got some problems with the database. But just to be sure also to not destroy the backup chain in some way. Otherwise i will get in trouble with my backup operator 😀

br

Landola

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 7

On that note, do you actually have permission to do that from the company and is there any PII in that database where it shouldn't be sent even if you do have "permission" to do so? And, no... I'm not trying to chastise you... I'm trying to warn you of the possibly server ramifications, especially if you read about your database in the morning spam.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Landola SSC Enthusiast Points: 115 More actions · Answer 8

Yes i have the permission to do that, but only if that database do not contain information that can violate GDPR. So not all databases are sent, and if they contain that then we do anonymization of that tables

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 9

K. I was just checking. To paraphrase "Red Green", "We're all in this together and I'm pullin' for ya."

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)