Backup plan of sql server database

Category: sql server dbengine

Question

RamTopen on Thu, 26 Dec 2019 14:18:18


Hi,

I have few databases and i want to backup them on daily basis.

I want to make it schedule everyday at midnight 12 AM.

I am planning to take a differential backup as it takes only updated changes.

But i want first time as full backup and from the next time it only takes changes in everyday basis.

How can I setup this.Please let me know

Does differential backup first time takes full backup or not ?


Ram

Replies

Stan210 on Thu, 26 Dec 2019 14:31:26


It does not do that by itself. You will have to write logic to do that. Or you can use ola hallengren backup scripts. They have all the logic you need to make this happen. These scripts pretty famous and used by many in production. Google for ola hallengren backup scripts refer : https://ola.hallengren.com/sql-server-backup.html

Hope it Helps!!



RamTopen on Thu, 26 Dec 2019 14:40:37


Hi Stan,

I think we can do that even through GUI,

We can set the maintainance plan in ssms.

MaintanancePlan>Newbackup>select the database which we want to take backup>schedule the time.

But,

My question is when we set the differential backup will it takes the full backup first time ?

Erland Sommarskog on Thu, 26 Dec 2019 15:14:00


My question is when we set the differential backup will it takes the full backup first time ?

No, if you take a differential backup of the database before you have taken a differential backup, you will get an error message:

CREATE DATABASE somedb
go
USE somedb
go
CREATE TABLE sometable (a int NOT NULL PRIMARY KEY, name sysname NOT NULL)
go
INSERT sometable(a, name)
   SELECT object_id, name FROM sys.objects
go
BACKUP DATABASE somedb TO DISK = 'C:\temp\somedb.bak' WITH DIFFERENTIAL
go
USE tempdb
go
DROP DATABASE somedb

Also note that if you start with taking a differential backup, and you actually took a full backup of the database six months ago, you will not get an error message. But if you don't have that backup around any more, you will have no use of that differential backup.

I am planning to take a differential backup as it takes only updated changes.

I would advice you to take a full backup at least once a week, so that you know where to start the restore.

Also, unless you have very lax requirements about data loss in case of a disaster, you should also backup the transaction log. How often depends a little on your business, but I would say at least once an hour.

I think we can do that even through GUI,

We can set the maintainance plan in ssms.

Yes you can, but the maintenance plans are not very good. Ola's solution is generally considered to be the preferred solution for backup and database maintenance.

Jefferson Silva DBA on Thu, 26 Dec 2019 16:26:16


Hello Friend,

* Does differential backup first time takes full backup or not ?*

Your answer is yes.
You need a FULL Backup before a DiFF Backup.

And to restore will also need this Backup FULL and DIFF.

Then analyze the backup routine well, because if you do a FULL for example, once a month (day 1) and the other days DIFF backups.
These backups accumulate data between FULL and DIFF. This way the 25th DIFF will probably be huge.

Do at least 1 FULL per week and daily DIFF. It seems to be sufficient for your situation.

If this answer has helped you, mark it helpful so that someone else with a question or similar problem can find an answer or help more easily. Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Database Analyst - Sql Server and Oracle]


Dawn Yang on Fri, 27 Dec 2019 02:35:38



My question is when we set the differential backup will it takes the full backup first time ?


Ram

Answer is No.

A differential backup is based on the most recent, previous full data backup. The full backup upon which a differential backup is based is known as the base of the differential.  If you perform differential backup directly without previous full backup, you will occur below error:

More description about differential backup, you can refer to Microsoft article: Differential Backups (SQL Server) 

In additional, we recommend that you could perform full backup weekly and differential backup daily to reduce the time when you restore backups. And please make sure that perform transaction log backup hourly if you want to restore database without data loss with stopping at any time point you want .

Hope it will be helpful!

BR 

Dawn Yang


RamTopen on Fri, 27 Dec 2019 15:18:53



My question is when we set the differential backup will it takes the full backup first time ?


Ram

Answer is No.

A differential backup is based on the most recent, previous full data backup. The full backup upon which a differential backup is based is known as the base of the differential.  If you perform differential backup directly without previous full backup, you will occur below error:

More description about differential backup, you can refer to Microsoft article: Differential Backups (SQL Server) 

In additional, we recommend that you could perform full backup weekly and differential backup daily to reduce the time when you restore backups. And please make sure that perform transaction log backup hourly if you want to restore database without data loss with stopping at any time point you want .

Hope it will be helpful!

BR 

Dawn Yang



MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Hi Dawn,

Thanks for your response. really appropriate your suggestions

I want this backup to remote server which is 10.10.10.10

BACKUP DATABASE IT_DATABASE TO DISK ='\\10.10.10.10\backup\BACKUP SQL\IT_DATABASE.bak'

I run this script it will backup manually to remote server but i want to schedule this automatically.

Can anyone please let me know how can I schedule this as daily backup at night 12 AM daily ?

I want to setup differential backup daily

and full backup once a week.

Dawn Yang on Mon, 30 Dec 2019 09:21:17


Hi Ram,

For make backup automatically, here are some methods can achieve this:

  1. Using SQL Server Agent jobs
  2. Using SQL Server Maintenance Plans

You can refer to this article which introduce step by step of making backup automatically.

Hope it will help.

BR 

Dawn Yang


MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.