Solved

SQL Server backup?

Posted on 2016-10-27
22
49 Views
Last Modified: 2016-10-31
SQL Server 2016 and SQL Server Management Studio. Is Generate Scripts (change type to Schema + Data) a good way to backup a Database? Will I be able to move it to another Server? Are there any other setting that needs to be set in this wizard?
0
Comment
Question by:Jess31
  • 9
  • 7
  • 3
  • +3
22 Comments
 
LVL 12

Expert Comment

by:Nakul Vachhrajani
ID: 41863371
Why not use a conventional backup to a file (the BACKUP DATABASE command)?

Normally, I use scripts to go the other way around (create a new schema from a set of scripts). In my development environment, I simply take backups of the DB, ensure that server-level securables (logins, jobs, etc) are scripted and that's it.

Why do you want to script out an existing database?
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41863411
Generate Scripts is a good way to take scripts and data backup. Here you can also select objects that you want to backup. It gives you lot of flexibility in choosing objects with Advanced properties.

Settings you can choose are -



Code...
Hope it helps !
1
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41863456
Is Generate Scripts (change type to Schema + Data) a good way to backup a Database?
No. Scripts doesn't export data but database structure only. Is really that what you want?

As Nakul commented above the right way to backup a database is to run the BACKUP DATABASE command or use SSMS GUI interface to perform the backup.
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41863460
This is INCORRECT that generate script does not export data. Below is the generated script for a table. It has schema and data also.

User can run it on any DB and will get table created and data inserted in that.

--

USE [master]
GO
/****** Object:  Table [dbo].[Hobby]    Script Date: 10/28/2016 12:38:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Hobby](
	[Id] [int] NULL,
	[hobby] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Hobby] ([Id], [hobby]) VALUES (1, N'tennis')
GO
INSERT [dbo].[Hobby] ([Id], [hobby]) VALUES (1, N'baseball')
GO
INSERT [dbo].[Hobby] ([Id], [hobby]) VALUES (2, N'bowling')
GO
INSERT [dbo].[Hobby] ([Id], [hobby]) VALUES (2, N'tennis')
GO
INSERT [dbo].[Hobby] ([Id], [hobby]) VALUES (2, N'basketball')
GO
INSERT [dbo].[Hobby] ([Id], [hobby]) VALUES (3, N'racing')
GO
INSERT [dbo].[Hobby] ([Id], [hobby]) VALUES (3, N'baseball')
GO
INSERT [dbo].[Hobby] ([Id], [hobby]) VALUES (4, N'tennis')
GO
INSERT [dbo].[Hobby] ([Id], [hobby]) VALUES (4, N'baseball')
GO
INSERT [dbo].[Hobby] ([Id], [hobby]) VALUES (4, N'fishing')
GO
INSERT [dbo].[Hobby] ([Id], [hobby]) VALUES (5, NULL)
GO
INSERT [dbo].[Hobby] ([Id], [hobby]) VALUES (5, N'tennis')
GO
INSERT [dbo].[Hobby] ([Id], [hobby]) VALUES (5, N'baseball')
GO
INSERT [dbo].[Hobby] ([Id], [hobby]) VALUES (6, NULL)
GO
INSERT [dbo].[Hobby] ([Id], [hobby]) VALUES (7, N'tennis')
GO
INSERT [dbo].[Hobby] ([Id], [hobby]) VALUES (7, N'tennis')
GO
INSERT [dbo].[Hobby] ([Id], [hobby]) VALUES (8, N'tennis')
GO
INSERT [dbo].[Hobby] ([Id], [hobby]) VALUES (8, N'tennis')
GO
INSERT [dbo].[Hobby] ([Id], [hobby]) VALUES (8, N'baseball')
GO


--

Open in new window

1
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41863461
Pawan, which option did you choose to add the data?
Also, how can you assure that data is loaded in the correct order to prevent any data integrity to be broken?
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41863464
Advanced scripting option -> Type of Data to Script -> Schema and Data

Schema and Data
SQL Server handles data integrity internally. I have checked this many times, Full back and restore and this option works equally. In this we have lot of options to choose from.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41863467
Advanced scripting option -> Type of Data to Script -> Schema and Data
Thanks. I will check that. I'm not really in with the SSMS options since I usually write my own T-SQL commands.


SQL Server handles data integrity internally.
Not for scripts. It runs scripts by the order of the commands inside the script. You can perform this test easily by adding some child tables and FK and the check by yourself the returned errors.
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41863469
No no it generates the scripts in the proper manner, first master tables then the dependent ones. !!
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41863475
No no it generates the scripts in the proper manner, first master tables then the dependent ones. !!
Last time I saw it (years ago) it didn't but I'll give a try.

I just performed a test right now with AdventureWorks. A database with 140MB size became a 630MB file (scripted with data). Took almost 5 minutes to export when a backup is less than 5 seconds. And loaded the script file and almost got all my memory used. Imagine this for a 1GB database or more. Just insane!
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41863478
LOL. Well as always it depends on your requirement. If you have heavy DB then backup will do but it will not give you options to choose from. !! So It depends.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41863480
Last time I saw it (years ago) it didn't but I'll give a try.
Still the same. It scripts by alphabetic order and doesn't care about parent/child table order. Maybe a 3rd party application can do this but not SSMS.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 12

Expert Comment

by:Máté Farkas
ID: 41863605
Making a sciprt of schema and data to transfer database instead of a backup can be usefull when
  • You database is big but you want to transfer just some small tables
  • Traget server version is older (before 2016) or edition is lower then source server
  • Source database uses a special feature (like columnstore index) which is not applicable on target server
1
 

Author Comment

by:Jess31
ID: 41863678
Nakul Vachhrajani,
Why not use a conventional backup to a file (the BACKUP DATABASE command)?
Where do I find Backup in SSMS? When I go to Tasks there is no Backup listed. (I am using SSMS 2016 and the db in hosted in Azure SQL)
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41863680
(I am using SSMS 2016 and the db in hosted in Azure SQL)
Those are the kind of things you should told us in the first place.
Backup an Azure database isn't the same as backup a regular SQL Server database. Check this article to see how you can do it.
1
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 41863900
Use this:

https://ola.hallengren.com/sql-server-backup.html

It will simplify your backup tasks a lot!
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41864001
@Zberteoc: Ola's script already supports Azure?
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41864146
I think yes they support azure.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41864203
Not only that but also Availability Groups. It will backup all the db under a group just giving its name.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41866633
With AG I know since I'm working with that feature.
About Azure is only curiosity since I never worked with that.
Cheers
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41866794
Azure is the future, It is definitely going to change lot of things specially for DBAs.!!
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41866923
Azure is the future, It is definitely going to change lot of things specially for DBAs.!!
If that is the trend that you should simply say The Cloud is the future, not just narrowed to databases. It may be if the prices will come down, which they will eventually. However, I don't see everyone using this feature and all databases being taking care of by the cloud companies. It will be a tremendous amount of work and responsibility for them and a bit to much of lost control by the user companies and I am not sure everyone is willing to go that way.
1
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41866942
Private clouds is what the big companies are looking for.

Talking about banks, that is the business that I'm supporting for the last 14 years, they don't want to let their data in the hands of any other company. Also, there are law restrictions as for example the customer sensitive information can't be stored out of the country and in a public cloud you can't guarantee that unless the cloud solution has a data center is in the same country that the bank is.

Besides that I think isn't yet possible to store terabytes or petabytes of information in a cloud solution. Maybe in a near future but for now I don't think there's a cloud provider offering that.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now