Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server backup?

Posted on 2016-10-27
22
Medium Priority
?
63 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 3
  • +3
22 Comments
 
LVL 14

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 30

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 52

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

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 52

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 30

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 52

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 30

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 52

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 30

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 52

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
 
LVL 13

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
 
LVL 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 52

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 27

Accepted Solution

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

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

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

Expert Comment

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

Expert Comment

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

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 52

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 30

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 27

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 52

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 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