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
I have one SQL 2005 Server.
I want to make an exact copy of my database + content on the same server with the only difference being the database name. How can I achieve this.
I have a copy of the database but without the content. How do I get all the content of the original database into the new one?? backup/restore does not work. SQL says the target database is not the same database as the database that was backed up…
Can you help me out on this?
I think with this we need to attach drop and create database also I am having some problem in that
1.IF EXISTS (SELECT name FROM sys.databases WHERE name = N’Items’)
some where sys.databases works but some places we need to change it to sysdatabases.
how can we avoid that.
2.CREATE DATABASE [SVIDB] ON PRIMARY
( NAME = N’Items’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\SVIDB.mdf’ , SIZE = 12288KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’ITEMS_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\SVIDB_log.ldf’ , SIZE = 57664KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname=N’SVIDB’, @new_cmptlevel=90
GO
problem with path and user authentications.
like some systems stores at MSSQL.2\MSSQL\DATA\
some stores at MSSQL.1\MSSQL\DATA\
and some systems don’t have rights to apend folder.
How to create a duplicate database from another database?
Example we have a db with 4 tables named (‘A’, ‘B’, ‘C’, ‘D’) with some data. wa want to create a new duplicate db from first one with tables(‘A’, ‘D’) with there data. Need immediate reply on mail. Thanks
hello sir,
how can i create a database in sql 2005?
Hi Penal
Thanks for such an informative website, am a dummy in SQL 2005.
Can you please tell me how to copy the data extracted through sql query into another sql table.
Thanks and regards
Harish B K
Insert into target_table(column list)
your_query
Hi PD
will u plz help me on how to add the sql script that u generate above to another Sql Server 2005 so that the same database is created on that Sql Server 2005
plz help me
I have two different servers, each with sql server 2005. I wanted to copy a database from one to the other. After struggling with generating scripts and such, I just created a database on on the destination server with the same name and security settings. Then I copied the DB_NAME.mdf and DB_NAME.ldf from the source server to the destination server replacing the files on the destination. (I turned both servers services off before copying the files). When I restarted the destination server, everything came up fine. With this method, the schema, data, views, and stored procedures transfered perfectly.
Do you foresee a problem with this method?
This blog was helpful in generating the scripts, thank you.
Nick
Hi,
I m using asp.net with sql server 2005. Most of the time the server on which my database is kept faces some problem. I have taken one other database server also. What I want is that whatever record (tables,views,procedures and functions) I have, should automatically be transfered in the new database along with data. What should I do for it? Please suggest.
Pinal, I was just trying to script out a full database in Visual Studio 2008 but it wouldn’t work because of an XML assembly object. The workflow was through the Database Schema Compare method. VS 2008 let me choose to not create the assembly object but then choked on the assembly anyway.
I’m going to try the method above see if this will work but I suspect I’ll still have a problem with the assembly.
I’ll let you know. Maybe my only choice is to detach the assembly, script out the database, then go through the steps to attach the assembly to the new database.
Your blog is very helpful, by the way. Thanks.
hello,
i want to make a user an owner of my database,am using sql server 2005,please can you help me to create a
1.login
2.user
3.make the user the owner of my database
thx
Hello Pinal,
Presently I am working on dotnet. We are using sql server 2005. So, my requirement is when ever I click the button I have to delete the particular table’s data from database. But, before deleting I have to take backup and insert into another database.
Please give me advice in 2 to 3 ways. We are using sql select statements for transferring data. Is there any other way to transfer?
regards
Ramesh
Hello —
This was a great help. But is there a way to generate seperate scripts for the objects like we can do it SQL 2000 instead of getting one script in one go.
Thanks
Best Regards
Hi All,
I am following the above steps to take the stored procedures scripts to individual sql or text files. but to my surprize in the 5th step: Select Output Options, I couldn’t find the option to take the individual stored procedure scripts to defferent sql files. Is it my local sql server software problem or need I do any setting for this option?
Awaiting for your earlier reponse.
Thanks & regards,
Srinu
Hi sir,
I want how to create runtime sqlservser database in asp.net with c#.
Awaiting for your earlier reponse.
Thanks & regards,
Swapnil
I want to add something saying, there is an option called
Script Behavior: you can select two options for this,
a) Generate CREATE Statement only
b) Generate DROP Statement only.
Make sure you select option a) if you are trying to script all objects of database so that you can create it later on.
Before running the script in the PROD, Make sure you check if it is DROP or if It is CREATE.
Second thing I am concerned about is, Whether to use : Script USE DATABSE:
SCRIPT USE DATABASE:
a) TRUE
b) FALSE
By default it would be false. I also suggest to keep it false, because, when you generate the script and you execute (RUN) the script in prod, and you haven’t checked that it was a DROP Statement script. Trust me you are done…. ( Your Job is finished…) It will DROP all your database objects.
if you dont mention Database name, still you have one more chance to check…
hope this helps, please correct me if I am wrong.
Hi,
I have to give ‘create Proc’ permission to developers team but restrict them from creating or altering table designs. I tried giving Connect, Create Procedure’,Delete, Insert, Select and Update. But it doesn’t work for them. It only works if I also give ‘Alter any schema’ but on giving this, they get permission to change table designs. How can I restrict table design changes for deleopers while giving them create/alter proc permission. This is all for sql server 2005. Please help.
Thanks,
Sunil
Hello Sunil,
This might be a temporary solution,
Open SQL Server – expand database- expand security – expand roles – database roles – right click ( new database role)
1. Give the name of the role,
2. select the owner of the role ( dbo or any other user)
3. on the left hand side of the box, click securables, and then click add
a) after you click add, click specific objects – click ok
b) click objects types, check stored procedure- click ok
c) Click browse, all stored procedures will be displayed, you select which stored procedures you want to select, and then click ok.
d) Then you should be able to see which types of permissions you can give to those stored procedures. Remember, you have to give permissions to every single stored procedure, dont think if you give it to one, it will apply for all stored procedure, you have to select one stored proc at one time and then give permisisons ( alter/create/control) and after you complete all the permissions for all the stored procedures, then click ok.
4. Add developers userid to this database role, to do this,
Open SQL Server – database – security – roles- database roles- select newly created role – right click properties ( double click name) and then click on add and then click browse, select the userid of developers and click ok, and click ok again, Thats it.
Hope this helps.
Thanks for quick reply, Imran.
I tried this, but this way developers won’t be able to create new proc. So I give ‘Create Procedure’ permission to the role. On giving this, user is still not able to create proc as he is getting error
Msg 2760, Level 16, State 1, Procedure try, Line 2
The specified schema name “dbo” either does not exist or you do not have permission to use it.
I think, I might need to give ‘Alter Schema’ also because its addition of object to the schema. And if I give this, then user can change table design also.
Regards,
Sunil
Thanks
what is main differeence between sql server 2000 and 2005?