How to schedule a SQL FULL backup WITH COPY_ONLY

Question

How to schedule a SQL FULL backup WITH COPY_ONLY

http://www.veloci-group.com/remotebackup.html

Old Hand

Points: 385
More actions
January 13, 2010 at 5:09 pm

#219244

Hi!
I would like to schedule a daily FULL with COPY_ONLY backup from SQL Server Management Studio.
But I don't know how to do it ?
Is it possible ?
As it is now I can schedule a full backup of the database. It also creates a backup file for each day on my NAS, and my job includes a maintenance cleanup task deleting backup files older than 7 days, so drive is not filling up.
It would be nice just to modify existing job/task and add WITH COPY_ONLY, but that seems not to be possible....
Anyone who can help me ?
Thanks in advance,
🙂 Jens

Viewing 15 posts - 1 through 15 (of 25 total)

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

homebrew01 SSC Guru Points: 55454 More actions · Answer 1

Why do you want scheduled COPY ONLY backups ? That function is usually used for one-time backups that will not affect the log chain.

Anyway, I found this on BOL. Maybe you can't do it from a maintenance plan ?

[font="Courier New"]SQL Server Management Studio does not support copy-only backups.

For a copy-only full backup, the required BACKUP syntax is:

BACKUP DATABASE database_name TO <backup_device> … WITH COPY_ONLY …[/font]

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

If you job step is a T-SQL Backup command, you can add COPY_ONLY to it. Is your job a Maintenance Plan?

Greg

Answer 3

Greg Charles (1/13/2010)
If you job step is a T-SQL Backup command, you can add COPY_ONLY to it. Is your job a Maintenance Plan?

Yes, it's a Maintenance Plan.....

If I edit the task I can only view T-SQL (There's a button: View T-SQL), I can't modify it....

Can I find the file somewhere and modify that ?

Thanks in advance,

🙂 Jens

Answer 4

homebrew01 (1/13/2010)
Why do you want scheduled COPY ONLY backups ? That function is usually used for one-time backups that will not affect the log chain.

I want to backup Microsoft Dynamics ERP database to local NAS each night. I have also a remotebackup solution running, doing Log/Differential backup and don't want to break log chain.[/quote]

homebrew01
Anyway, I found this on BOL:
[font="Courier New"]SQL Server Management Studio does not support copy-only backups.
For a copy-only full backup, the required BACKUP syntax is:
BACKUP DATABASE database_name TO <backup_device> … WITH COPY_ONLY …[/font]

I know that the syntax is WITH COPY_ONLY and I would really like to add that to existing Maintenance Task, which is running perfectly.....

Thanks in advance,

🙂 Jens

xxdbxx SSC Eights! Points: 812 More actions · Answer 5

You need to add a T-SQL step to your maint plan and insert the code for the copy only backup there.

you cannot do it with the backup task in the maint plan.

but actually what i would do as it is simpler is to just create a sql job and run the code there and just schedule it how you like-that way you do not have to work within the maint plan interface.

db

Dugi SSCoach Points: 17998 More actions · Answer 6

I think that with SSMS you cannot do it as schedule!

You should create PowerShell script that will stop the SQL Server services, copy files from folder to other folder, then start the Services again!

But how to do it, read more about PowerShell... http://msdn.microsoft.com/en-us/library/aa973757%28VS.85%29.aspx

http://msdn.microsoft.com/en-us/library/cc281962.aspx

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

xxdbxx SSC Eights! Points: 812 More actions · Answer 7

You can definitely create a job within SSMS to run a piece of SQL code which could be a Copy Only Backup.

And I really have no idea why you would stop sql services just to take a backup, a copy-only one at that.

Backups operate on another thread and for the most part do not interfere with other SQL activities.

db

Dugi SSCoach Points: 17998 More actions · Answer 8

xxdbxx (1/14/2010)
You can definitely create a job within SSMS to run a piece of SQL code which could be a Copy Only Backup.
And I really have no idea why you would stop sql services just to take a backup, a copy-only one at that.
Backups operate on another thread and for the most part do not interfere with other SQL activities.
db

We are discussing in the SQL Server 2005 forum, so the copy only backups with SSMS is not supported in SQL Server 2005!

http://msdn.microsoft.com/en-us/library/ms191495%28SQL.90%29.aspx

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Dugi SSCoach Points: 17998 More actions · Answer 9

Copy Only Backup with SSMS are supported just in SQL Server 2008!

http://msdn.microsoft.com/en-us/library/ms187510.aspx

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

xxdbxx SSC Eights! Points: 812 More actions · Answer 10

Correct. The thread-starter did not mention versions and since they seemed to know what copy - only backups were I assumed they were on SQL 2008.

db

Dugi SSCoach Points: 17998 More actions · Answer 11

xxdbxx (1/14/2010)
Correct. The thread-starter did not mention versions and since they seemed to know what copy - only backups were I assumed they were on SQL 2008.
db

I understand you that we every time checking for the new versions and new features, early I did some mistakes like you, later I always check the forum first during response!

Anyway you are welcome in SSC forum...n'joy!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 12

Dugi (1/14/2010)
I think that with SSMS you cannot do it as schedule!
You should create PowerShell script that will stop the SQL Server services, copy files from folder to other folder, then start the Services again!
But how to do it, read more about PowerShell... http://msdn.microsoft.com/en-us/library/aa973757%28VS.85%29.aspx
http://msdn.microsoft.com/en-us/library/cc281962.aspx

Wow - are you really recommending to the OP to take their system down every night so they can perform a COPY_ONLY backup?

Seems a bit extreme to me.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 13

Dugi (1/14/2010)
Copy Only Backup with SSMS are supported just in SQL Server 2008!
http://msdn.microsoft.com/en-us/library/ms187510.aspx

Wrong - COPY_ONLY is not available when using the GUI in SSMS 2005, however - you can still perform copy only backups using the BACKUP command directly.

Actually, it is just not supported when using the Backup Database task in the maintenance plans. You can always use an Execute SQL Task and build your backup command yourself, including the COPY_ONLY parameter.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

Dugi SSCoach Points: 17998 More actions · Answer 14

I really don't know why he or they want to do copy only backup with SSMS with schedule, for that reason if really they want to do it they can do something with what I suggest, something like windows scripting then coping the files from one location to another... finally yes I agree with you that could be bad thing if they stop - run services for each time when they want to do copy only backup - scheduling, it was little bit confusable to me because at the first time I mean for the copy only backup (mdf and ldf ) files!!!!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]