When I want to restore a database I think that the best option is to create a backup of the database. However, I can create a script that saves schema and database, save primary keys, foreign keys, triggers, indexes...
In this case of script the result is the same as restore's ? I ask this because the script has a size about 1MB and the backup about 4MB.
I ask this because I would like to change the collation of all my columns of all my tables and I try some scripts but this does not work, so I am thinking in the possibility to create and script, so when I create the tables these are created with the collation of database. This collation I set in the script when I create the database.
Is it a good option to use a script for that or I can lost some type of restrictions or other design elements?
Thanks.
You can see Full Database Backup at msdn. The primary difference is that the transaction log is backed up. This provides you with many options such as differential backups that will eventually lead to less space needed in your drives to store your data.
In addition, using the backup schemes will provide you with easier ways to organize where,when and how(that is strategies) you backup.
There are ways to implement a full db backup by scripts look here, where you will lose nothing.