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
Hello Dave, I need to use sql 2005 sentences for copy a database to another, example copy db_x to db_y with data and all objects. Thank you.
Take a backup of a database and use restore command to restore it as a different name
Thank you, helped me..
I upgraded from SQL 2000 to 2005. I had a dts package that would determine the last backup of my production database. It would then restore to another DB called reportdb. How can I accomplish this in SQL 2005. This job ran nightly so that the report database was a copy of production as of the midnight before. All users would then only access the report DB and never touch production DB.
Hello again Dave, well my requeriment is “Take the previous database named p2010 and create a database named p2011 from p2010”, my idea is first backuped p2010 and restore this as p2011. I don’t know if this solution is the best because I don’t have idea if exists another option to do this.
For the moment my steps are restore and backup:
1. Backup
BACKUP DATABASE p2010
TO DISK = ‘C:Archivos de programaMicrosoft SQL ServerMSSQL.4MSSQLBackupsome_name.Bak’
1.1 Add to device
EXEC
sp_addumpdevice ‘disk’,
p2010,
‘C:Archivos de programaMicrosoft SQL ServerMSSQL.4MSSQLBackupsome_name.bak’
(1.1.1 To correct for tests)
exec sp_dropdevice p2010
2. Restore
(option 1)
RESTORE DATABASE p2011_newName
FROM DISK = ‘C:Archivos de programaMicrosoft SQL ServerMSSQL.4MSSQLBackupsome_name.bak’
WITH REPLACE,
MOVE ‘filename_Data’ TO ‘C:tempfilename_Data.MDF’,
MOVE ‘filename_Log’ TO ‘C:tempfilename_Log.LDF’
/* Move to change drive:path */
(option 2)
RESTORE FILELISTONLY FROM p2010
RESTORE DATABASE p2011_newName
FROM DISK = ‘C:Archivos de programaMicrosoft SQL ServerMSSQL.4MSSQLBackupp2010.bak’
WITH RECOVERY,
MOVE ‘filename_Data’ TO ‘C:tempfilename_Data.MDF’,
MOVE ‘filename_Log’ TO ‘C:tempfilename_Log.LDF’
can pls. send a complete tutorials of sql server database 2005 in any file format
Hi
is it possaible to Script only the Constaints in the entire database
Thanks Sir,
my problem is solved with the help of you
Hi friends,
I need a help for converting sql script to oracle script
i generate script from sql developer , now i want convert this script to oracle script.
i am using ubuntu os 10.4, then i am installed oracle10g edition
any one pls help me out for converting and how import script to oracle 10g
pls pls urgent
hi pls help out for the above problem
hi dev ,
again vinot, pls help me out for the solution for
sql script to oracle script
i am having sql script, using ubuntu 10 os.
and install oracle10g
pls help me out, and how to run script in oracle 10g
This site is for SQL Server. Post your question at http://www.orafaq.com
hi madhi,
i cant find above site
could u pls help for covert or tell me is there any tools(s/w) available for converting
That site is working. Did you get any error?
yes i find error could u try pls
What was the error you got?
Hi, I need to validate the proccess of a backup and restore, all the issues that will occur, handling errors. It’s possible use a tray & catch sentence in windows server 2000?
Thanks in advance
You cannot use Try/catch in version 2000. They are supported from version 2005 onwards
Hi, I need to validate the proccess of a backup and restore, all the issues that will occur, handling errors. It’s possible use a tray & catch sentence in SQL server 2000?
Thanks Madhivanan,
Ok, how can I do, to handling errors on SQL Server 2k?
Regards.
You need to make use of @@ERROR
–statement
If @@ERROR0
–Write catch block here
hi i need another help,
i want to create a schema in oracle, could u pls tell me the format with example.
thanks.
This site is for SQL Server. Try posting at ORACLE forums such as http://www.orafaq.com
how to install oracle10g standard edition on ubuntu
I have already told you that this site is for SQL Server. Try posting at ORACLE forums such as
Dear Experts,
I’m facing a very critical situation…and no one is here to help me…So my humble request to guide me….
Actually we have a server running a Intranet Site having os 2000 server and SQL 2000 .data also uploading to sql server i.e. at Denmark office…now I have to upgrade server in to a new machine…at first step i have transferred all data to new server .
now ii want to shift completely sql 2000 DB to SQL 2005 server including all security / logins store process SQL server agent jobs…everything..how will i do??I have not much idea on SQL except backup restore…and all simple activities
i have already took database backups from SQL 2000 and restore in SQL 2005…but how can I thasfer other Login and server agent jobs or any other process that is running in SQL 2000.
Thanking
You
Prabhusap
Hi,
This is great, but it just create a script with create table structure. How can i get all data in database to the scrips also.
Thank you alot.
Xinglee
It is possible only in version starting from 2008
I want single table backup in particular database , if its is possible means please give the commands
There is no direct way. You need to script out the table and bcp the data to a text file
How can I writte a select statement to get the information on the index’s define for a table ?
Try this
exec sp_helpindex ‘table_name’
I am looking for a script i can run that will restore 6 DB’s from a local drive on the SQL machine to 6 DB’s on the local machine. They are all named different but i am not very versed in the SQL world. I already have a script to stop and start all the DB’s now i just need to complete the script so i dont have to manually go in and restore each single database. I have to do this to them everyday and its quite a time consuming task and was hoping for some help. I will explain in some detail the names of the DB’s and hopefully that will help in the creation of such a script.
Train01 (Full DB name) has a backup located at d:backupTrain01.bak, I want that file to replace whats currently in the Train01 DB. I guess if i knew more SQL script code it would probably be very easy to do this but i dont. Any help or pointers would be very appreciated
Thanks
Joakim
I hope this example give to you some idea about how to do a backup/restore to specific drive:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
— =============================================
— Author: IDS – Gerardo Angeles Nava
— Create date: August 20, 2010
— Description: 1. Make a backup. 2. Make a restore of backup but with another name (year + 1)
— This stored procedure most be reside on the database server that contains the database to backup
—
— HELP to ME:
— If it’s possible that this script resides in external database server that’s no contains the database to backup,
—
— =============================================
Create PROCEDURE [dbo].[sp_Backup_RestoreWithNewName]
@dbNameSource VARCHAR(100),
@dbNameTarget VARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @dbNameToBackup AS VARCHAR(100)
DECLARE @pathBakFile AS VARCHAR(255)
—
DECLARE @dbNameToRestore AS VARCHAR(100)
DECLARE @pathRestore AS VARCHAR(255)
DECLARE @pathData AS VARCHAR(255)
DECLARE @pathLog AS VARCHAR(255)
—
DECLARE @data AS VARCHAR(100)
DECLARE @log AS VARCHAR(100)
/* Configuration path (drive): */
SET @dbNameToBackup = @dbNameSource
SET @pathBakFile = ‘E:dataMSSQLBACKUP’ + @dbNameToBackup + ‘.Bak’
—
SET @dbNameToRestore = @dbNameTarget
SET @pathData = ‘E:dataMSSQLBACKUP’ + @dbNameToRestore + ‘.mdf’
SET @pathLog = ‘E:dataMSSQLBACKUP’ + @dbNameToRestore + ‘.ldf’
SET @pathRestore = @pathBakFile
—
/* Remove device, if exists */
/*
IF EXISTS(SELECT * FROM sys.backup_devices WHERE name = @dbNameToBackup)
BEGIN
exec sp_dropdevice @dbNameToBackup
END
*/
/* Generate Backup */
BACKUP DATABASE @dbNameToBackup
TO DISK = @pathBakFile
/* Mount the sql-device, if exists */
EXEC
sp_addumpdevice ‘disk’,
@dbNameToBackup,
@pathBakFile
/* ¿Have a backup? */
IF EXISTS(
SELECT DISTINCT
S.[name]
,DATABASEPROPERTYEX(S.[name], ‘Status’) AS [Estatus]
,crdate
,[filename]
FROM master..sysdatabases S
JOIN msdb.dbo.backupset B
ON S.[name] = B.database_name
WHERE
S.[name] = @dbNameSource
and CONVERT(DATETIME, CAST(crdate AS VARCHAR(16)),120) = CONVERT(DATETIME, CAST(database_creation_date AS VARCHAR(16)),120)
)
BEGIN
/* Restore the Data Base with another name (year + 1) */
SELECT @data = [name] FROM master.dbo.sysaltfiles
WHERE [dbid] = (SELECT [dbid] FROM master.dbo.sysdatabases WHERE [name] = @dbNameSource and fileid = 1)
—
SELECT @log = [name] FROM master.dbo.sysaltfiles
WHERE [dbid] = (SELECT [dbid] FROM master.dbo.sysdatabases WHERE [name] = @dbNameSource and fileid = 2)
—
RESTORE DATABASE @dbNameToRestore
FROM DISK = @pathRestore
WITH REPLACE,NORECOVERY,
MOVE @data TO @pathData,
MOVE @log TO @pathLog
—
RESTORE DATABASE @dbNameToRestore WITH RECOVERY;
—
/* ¿Have a restore? */
IF EXISTS(SELECT name FROM master..sysdatabases WHERE [name] = @dbNameToRestore)
BEGIN
/* Yes we have it!*/
IF EXISTS(SELECT name FROM master..sysdatabases WHERE [name] = @dbNameToRestore AND DATABASEPROPERTYEX(name, ‘Status’) = ‘ONLINE’)
BEGIN
/* ¿The database is online? */
SELECT ‘Restore: yes. On line: yes.’
END
ELSE
BEGIN
SELECT ‘Restore: yes. On line: NO.’
RETURN
END
END
ELSE
BEGIN
SELECT ‘Restore: No’
RETURN
END
END
ELSE
BEGIN
SELECT ‘Backup: No’
RETURN
END
—
SELECT ‘Now you have a backup an restore with another name’
—
END