Solved

SQL Server backup?

Posted on 2016-10-27
22
53 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 28

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 47

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
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.

 
LVL 28

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 47

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 28

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 47

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 28

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 47

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 28

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 47

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 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 47

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 47

Expert Comment

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

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 47

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 28

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 47

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

778 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