🪄 SQL Server Script Database With Data And All Objects

Here's how to generate scripts for any SQL Server database, including database objects and all of the data. It's a great way to backup your database into a human readable format, as well as allow you to copy a database's structure and data from one database to another.

00:00 - Introduction
00:20 - Scripting a Database
01:35 - Scripting Table Data
02:20 - Changing SQL Version
02:35 - Generating Scripts
04:05 - Restore Database from Script

If you found this video leave a LIKE and SUBSCRIBE for more coding videos.

To script a backup of a database in SQL Server Management Studio you need to right click on the database in the object explorer panel. Choose the menu option Tasks and then Generate Scripts.

The Generate Scripts Wizard is quite self-explanatory. Just bear in mind there's an Advanced button on the Set Scripting Options step which isn't always obvious. If you click on this button then there are a few options that affect the scripting output. One of the most useful options is 'Type of data to script'. The options here are schema only (the default), data only and finally schema and data.

I tend to script each database twice: once to generate the schema (i.e. the create SQL scripts for the tables, views, stored procedures and other database objects). I then run the wizard a second time and generate a script for just the data.

I would also recommend that if your database is large (i.e. the tables contain a lot of data) you choose the option to save the script file to disk rather than copy it to the clipboard or open it in a new SSMS query window.

Incidentally the Set Scripting Options Advanced button also allows you to change the SQL Server version the script is compatible with, so this is useful if you want to export your schema and/or data to another version of SQL Server or another relational database.

Once the script is generated I tend to create the new database manually, then tweak the schema files before running a database restore using the script. Be careful to ensure you run the schema creation script in the correct database, as it's a real hassle deleting tables if you create them in the wrong database! I usually remove the top part of the schema script, and just leave everything after the first CREATE TABLE statement.

Finally be aware that restoring a database using these scripts can be very slow, compared with using .bak backup files. The database's data is scripted as a sequence of individual insert statements, which take a long time to run, especially if you're not executing them on the server itself.

[hasanmougharbel]

Glad to meet again..
As a new sql learner i hold a simple enquiry.
SQL Server 10.50.4000
How these numbers (10.50.4000) reveal anything about the version of sql server?
Thanks a lot