How To Take Database Schema Backup in SQL Server 2005

How To Take Database Schema Backup in SQL Server 2005

• Go to OBJECT EXPLORER of the SQL Server 2005 and expand the Databases tree node, you can see the available databases in your server.

• Then Right Click on the specific Database you wish to make the schema backup. And go to TASKSà GENERATE SCRIPTS like shown in the figure below:

• After selecting the GENERATE SCRIPTS you will see a welcome screen like shown below.

• Click on NEXT button of the Wizard and you will be asked to select the database you wish to generate schema backup like below in figure(TestDB is the database in this case):

• If you want to take the full schema back up of the database then check the checkbox “Script all objects in the selected database” like shown here in the figure and click Next button of the wizard.

• In the next screen you can play with the different properties. Just need to change one of the property if you need to use this schema on SQL Server 2000 version, that is “Script for Server Version” and select the appropriate version you needed.

• After selecting the appropriate SQL Server version for which you are preparing the schema backup click Next Button and you will reach at the OUTPUT OPTION and then select the appropriate output option. In my case I am saving the script of schema in a File of Unicode Text like:

• Then Click Next and you will reach on the Script wizard Summary screen, Simply Click Finish to execute your requested job.

• After clicking the Finish button you will reach at the Generate Script progress Wizard. It will take a little time depending upon the number of objects in your selected database. After completing its job it will show you the success status.

• If you want to see the Report of the whole process, just select from the options of Report button just up from the Close and select the output option of the report. Otherwise click Close. That’s it and you are Done.