[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 70
  • Last Modified:

SQL Server backup?

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
Jess31
Asked:
Jess31
  • 9
  • 7
  • 3
  • +3
1 Solution
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Pawan KumarDatabase ExpertCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Pawan KumarDatabase ExpertCommented:
No no it generates the scripts in the proper manner, first master tables then the dependent ones. !!
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Máté FarkasDatabase Developer and AdministratorCommented:
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
 
Jess31Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
(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
 
ZberteocCommented:
Use this:

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

It will simplify your backup tasks a lot!
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
@Zberteoc: Ola's script already supports Azure?
0
 
Pawan KumarDatabase ExpertCommented:
I think yes they support azure.
0
 
ZberteocCommented:
Not only that but also Availability Groups. It will backup all the db under a group just giving its name.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
With AG I know since I'm working with that feature.
About Azure is only curiosity since I never worked with that.
Cheers
0
 
Pawan KumarDatabase ExpertCommented:
Azure is the future, It is definitely going to change lot of things specially for DBAs.!!
0
 
ZberteocCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 9
  • 7
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now