Solved

SQL Server backup?

Posted on 2016-10-27
22
45 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 11

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 18

Expert Comment

by:Pawan Kumar Khowal
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 45

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 18

Expert Comment

by:Pawan Kumar Khowal
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 45

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 18

Expert Comment

by:Pawan Kumar Khowal
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 45

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 18

Expert Comment

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

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 18

Expert Comment

by:Pawan Kumar Khowal
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 45

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 11

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 45

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 45

Expert Comment

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

Expert Comment

by:Pawan Kumar Khowal
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 45

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 18

Expert Comment

by:Pawan Kumar Khowal
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 45

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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

760 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

22 Experts available now in Live!

Get 1:1 Help Now