Update: This article is re-written with SQL Server 2008 R2 instance over here: SQL SERVER – 2008 – 2008 R2 – Create Script to Copy Database Schema and All The Objects – Data, Schema, Stored Procedure, Functions, Triggers, Tables, Views, Constraints and All Other Database Objects
Following quick tutorial demonstrates how to create T-SQL script to copy complete database schema and all of its objects such as Stored Procedure, Functions, Triggers, Tables, Views, Constraints etc. You can review your schema, backup for reference or use it to compare with previous backup.
Step 1 : Start
Step 2 : Welcome Screen
Step 3 : Select One or Multiple Database
If Script all objects in the selected database checkbox is not selected it will give options to selected individual objects on respective screen. (e.g. Stored Procedure, Triggers and all other object will have their own screen where they can be selected)
Step 4 : Select database options
Step 5 : Select output option
Step 6 : Review Summary
Step 7 : Observe script generation process
Step 8 : Database object script generation completed in new query window
Reference : Pinal Dave (https://blog.sqlauthority.com), All images are protected by copyright owner SQLAuthority.com
673 Comments. Leave new
HOW TO GENERATE DATABASE SCRIPT AT SQL PROMPT ???
SHANKU
Hi..
as you told i got all views in table form… in sqlserver 2000
so how to get it back in view form …
and i can not create all views again …
they are around 250 so… Please
Thanx
@ Sandeep,
Follow Screenshots properly, it has everything starting point to ending point.
At the last step you will get a new WIndow which will have all views scripts, you need to run that script in the database you want to create all these objects,
Also, please clearly explain your question.
Regards,
IM.
There is a way of transferring databases between 2005 and 2000 via Generate Scripts. You will need to drop several 2005 features though like Extended properties,etc.
More importantly the Script for Server Version must be set to 2000. I’ve tried it. It works.
Hi PinalDave,
I am afraid I don’t have very clear the concept of scripting the database. All I want to do is to copy a database from a computer or server to another computer or server. I tried to script the database to a file thinking that I could run it on the other computer and that I would be fine but it would ask me for the logging information which is not going to work as I am in a different computer. So, I am confused in which method should I use if there is any possibility of copying a database to the other computer.
An another thing… in case there is a method, would I be able to copy all the Data too?
Forgive my lack of knowledge please…
Any help would be appreciated.
Thanks,
Al
hi
i wannt to asked any one if some time we are forgot password in sql server 2005 or 2008. how can chage password in both version with re-installing the software
if any one have this answer please give me reply
hi
i wannt to asked any one if some time we are forgot password in sql server 2005 or 2008. how can change password in both version without re-installing the software
Hi,
Can you tell me how to change the definition in a function call when moving to diferent DB’s please.
old_db replicated to new_db but the views contain definition calls to the old_db functions
i.e.
select data,
old_db.function(variable) as result
from data_table
Should be
select data,
new_db.function(variable) as result
from data_table
thank you Graham
@Graham
This is a serious issue.
I came across same issue recently. Earlier when I wrote scripts for an application I hardcoded database names in script. When we moved our database from one environment to other, name of the database changes accordingly to naming convention and I saw my script doesn’t work any more in new environment.
Best way to over come this issue would be
1. Don;t use FourPart name if not needed For example if you are running this script in database Db_Example, and your script is something like this,
select * from Db_Example..Table1.
This is just waste, as it is of no use.
2. Use a variable that select database name by itself,
something like this,
declare @dbname varchar(100)
select @dbname =DB_NAME()
declare @sqlcmd varchar(8000)
set @sqlcmd = ‘select * from ‘+@dbname+’..Table1)
Exec(@sqlcmd)
You can perform step1 or step2 while creating sps/views/functions.
At this time since you already have views created you can do this.
Use Replace Function, this function will find and replace a string in text field.
You can get the definition of views and functions from syscomments system table.
Let us know if you need more help on this.
Regards,
IM.
Wow, this is long…
Looks like there is a lot of interest indeed!
Good blog…
hi pinal sir,
sir i want to attach/deattach and backup/restore database of sqlserver 2000 with the vb.net 2005
how can i do it
can u give me the vb.net code to attach/deattach .mdf database file and backup and restore database.
actually i am devloping one application which uses multidatabse and database is created dynamically how can i do this with sqlserver 2000.
this is completly done with ms-acess but is slow and single user
please reply me as far as possible.
thanks in advance.
and anyone expert read this comment and want to help me or reply me please send your help
hello sir,
i have requirment that i want to complile all the procedurs
of db to another db.
both db are contains same list of procedure
.
but i want compile all the procedurs
is there any script to do like this work.
kuldip bhatt
Thanks for sharing the knowledge.
I was wonder if we could write a stored procedure that would delete all the unused stored procedures after a month or certain deadline.
Is it possible????
What did you mean by unused?
Did you mean the procedures that were never called by your application for certain period of time?
hi thanks for the post it is very help full
i whant to copy all the data from one dabase of sql 2005 to another databse of sql 2005 how can i do that
hi ,
can someone help me with audit triggers .
am using column_updated function but it does not work for text data type fileds and more than 8 columns
if i am using normal trigger script it is taking a image of all the rows even if there is a change in one row.
please suggest on this issue.
thanks.
Hi,
How to migrate DB Script from Sql server 2005 to Sql Express
I hope this help………
thanks
palani
@David,
For your first Issue,
You can only create an INSTEAD OF trigger if your column has text data type, you cannot create a FOR trigger.
For your second issue, issue with Column_Update() and more than 8 columns, please refer to Article below
https://support.microsoft.com/en-us/help/232195
Regards,
IM.
hi ,
can i do the same thing with some other way with stored procedure. Actuly I want the stored procedure which will automatically copy one store procedure from one database to other database …
thanks
hi,
In visual studio 2005 i want make a connection with sqlserver 2005,when i am using asp.net to make an connection sql data source connection is success but when i test query it will show an error msg that ‘there was an error executing the query.please check the syntax of the command and if present the type and values of the parameters and ensure they are correct……..
invalid object name.
help me how to rectify it……………
i want to copy all the data from one database of sql 2005 to another databse of sql 2005 how can i do that