Copying/Transferring Tables

Is there a way to copy all tables (not the data, just the structure) and their relationship diagrams from one SQL server db to another?
What is the easiest way?
ThanksRight click on the Database,Generate Sql Script and set the condition on all the tabs.In order to transfer the permissions,roles,relationship you need to go under option in the same wizard.|||IMHO DTS is the best choice here as it will take care of all dependent objects and constraints. In this case you would elect NOT to transfer the data or probably user id and permissions. The only problem with this is it will not transfer the relationship diagram.|||I just want to transfer Tables and diagrams. I don't want other objects.
I know I can use a wizard to transfer the tables but it doesn't have an option to transfer the diagrams.

Originally posted by sqlserver2k
Right click on the Database,Generate Sql Script and set the condition on all the tabs.In order to transfer the permissions,roles,relationship you need to go under option in the same wizard.|||I am not aware of any tool or wizard that will transfer a relationship diagram, however the data is contained in a table called dtProperties and you could use the following to transfer a diagram:

set identity_insert dtproperties on
INSERT INTO <target db>.dbo.dtproperties(id, objectid, property, value, uvalue, lvalue, version)
select * from <source db>.dbo.dtproperties
set identity_insert dtproperties offsql

Copying/migrating SQL 7 Databases over to SQL 2005

Hi,
I've been trying to work out how to copy 2 small databases from an old
SQL7 server to a new 2005 server. I'll admit straight away that i'm
totally new to the world of SQL, so i'm stuggling a little.
I used the "backup database" function and saved to file on the old
server. I then copied them over to the test server and tried to
"restore" each of them.
It errors for both when I do this. It appears to be because on the
SQL7 server, each database sits on a F: partition, and i have setup
the test server using just a C: drive.
Thanks for reading!
nade
Take a look at WITH MOVE option of RESTORE command in the BOL
"nade" <nadeboy@.gmail.com> wrote in message
news:1172054201.292745.29510@.a75g2000cwd.googlegro ups.com...
> Hi,
> I've been trying to work out how to copy 2 small databases from an old
> SQL7 server to a new 2005 server. I'll admit straight away that i'm
> totally new to the world of SQL, so i'm stuggling a little.
> I used the "backup database" function and saved to file on the old
> server. I then copied them over to the test server and tried to
> "restore" each of them.
> It errors for both when I do this. It appears to be because on the
> SQL7 server, each database sits on a F: partition, and i have setup
> the test server using just a C: drive.
> Thanks for reading!
>

Copying/migrating SQL 7 Databases over to SQL 2005

Hi,
I've been trying to work out how to copy 2 small databases from an old
SQL7 server to a new 2005 server. I'll admit straight away that i'm
totally new to the world of SQL, so i'm stuggling a little.
I used the "backup database" function and saved to file on the old
server. I then copied them over to the test server and tried to
"restore" each of them.
It errors for both when I do this. It appears to be because on the
SQL7 server, each database sits on a F: partition, and i have setup
the test server using just a C: drive.
Thanks for reading!nade
Take a look at WITH MOVE option of RESTORE command in the BOL
"nade" <nadeboy@.gmail.com> wrote in message
news:1172054201.292745.29510@.a75g2000cwd.googlegroups.com...
> Hi,
> I've been trying to work out how to copy 2 small databases from an old
> SQL7 server to a new 2005 server. I'll admit straight away that i'm
> totally new to the world of SQL, so i'm stuggling a little.
> I used the "backup database" function and saved to file on the old
> server. I then copied them over to the test server and tried to
> "restore" each of them.
> It errors for both when I do this. It appears to be because on the
> SQL7 server, each database sits on a F: partition, and i have setup
> the test server using just a C: drive.
> Thanks for reading!
>

Copying/migrating SQL 7 Databases over to SQL 2005

Hi,
I've been trying to work out how to copy 2 small databases from an old
SQL7 server to a new 2005 server. I'll admit straight away that i'm
totally new to the world of SQL, so i'm stuggling a little.
I used the "backup database" function and saved to file on the old
server. I then copied them over to the test server and tried to
"restore" each of them.
It errors for both when I do this. It appears to be because on the
SQL7 server, each database sits on a F: partition, and i have setup
the test server using just a C: drive.
Thanks for reading!nade
Take a look at WITH MOVE option of RESTORE command in the BOL
"nade" <nadeboy@.gmail.com> wrote in message
news:1172054201.292745.29510@.a75g2000cwd.googlegroups.com...
> Hi,
> I've been trying to work out how to copy 2 small databases from an old
> SQL7 server to a new 2005 server. I'll admit straight away that i'm
> totally new to the world of SQL, so i'm stuggling a little.
> I used the "backup database" function and saved to file on the old
> server. I then copied them over to the test server and tried to
> "restore" each of them.
> It errors for both when I do this. It appears to be because on the
> SQL7 server, each database sits on a F: partition, and i have setup
> the test server using just a C: drive.
> Thanks for reading!
>

copying/ backing up a database

Hi. im shortly going to have to submit my project for uni which ive created using sql server. How can i copy everything that ive made so i can submit everything and it can be replicated if necessary. Do i use the backup database task in enterprise manager or do i have to do that and export data or..?

ive used tables and stored procedures and a diagram btw.

thanks for any advice

Look up sp_detach_db in Books Online. Detach the database, copyit, and submit it. Then they can re-attach it using sp_attach_db.

sql

copying website: database doesnt work?

Since VWD Express doesn't support Publish Website, I used 'Copy Website' to deploy my local site to the hosting server. The static page works fine, but when it gets to accessing database, I kept getting the following error:

An attempt to attach an auto-named database for file I:\Data\Web\dnh.sk\lingling_15726c71-b2bf-479f-bcc3-b7ae43318f3c\www\App_Data\Personal.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Anyone has the same experience? How to resolve this?

Thanks,

When you deploy your project, you have to upload your database separately.

You cannot use the db from the app_data folder. Execute the SQL against the db that you have in your hosting server and change the connectionstring for that.

Regards

|||

smiling4ever wrote:

When you deploy your project, you have to upload your database separately.

You cannot use the db from the app_data folder. Execute the SQL against the db that you have in your hosting server and change the connectionstring for that.

How do you do this when you only have VS.2005 and the basic SQL Express tools? Is it possible?

Copying Views from one db to other

Hi,
How to copy view from one db to other one...It should be
done via script...Is it possible to user SQL-DMO.
Regards
SridharYes, you can use SQL DMO t script objects. You can se DTS as well. Here is a
short examle of DMO:
Dim oSS As SQLDMO.SQLServer
Dim oDb As SQLDMO.Database
Dim oT As SQLDMO.Transfer
Dim sS As String
Sub Script()
Set oSS = New SQLDMO.SQLServer
Set oT = New SQLDMO.Transfer
oSS.LoginSecure = True
oSS.Connect
Set oDb = oSS.Databases("pubs")
oT.CopyAllTables = True
oDb.ScriptTransfer oT, SQLDMOXfrFile_SingleFile, "C:\pubs.sql"
End Sub
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:8a2a01c485af$9b128630$a501280a@.phx.gbl...
> Hi,
> How to copy view from one db to other one...It should be
> done via script...Is it possible to user SQL-DMO.
>
> Regards
> Sridhar

Copying Views from one db to other

Hi,
How to copy view from one db to other one...It should be
done via script...Is it possible to user SQL-DMO.
Regards
Sridhar
Yes, you can use SQL DMO t script objects. You can se DTS as well. Here is a
short examle of DMO:
Dim oSS As SQLDMO.SQLServer
Dim oDb As SQLDMO.Database
Dim oT As SQLDMO.Transfer
Dim sS As String
Sub Script()
Set oSS = New SQLDMO.SQLServer
Set oT = New SQLDMO.Transfer
oSS.LoginSecure = True
oSS.Connect
Set oDb = oSS.Databases("pubs")
oT.CopyAllTables = True
oDb.ScriptTransfer oT, SQLDMOXfrFile_SingleFile, "C:\pubs.sql"
End Sub
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:8a2a01c485af$9b128630$a501280a@.phx.gbl...
> Hi,
> How to copy view from one db to other one...It should be
> done via script...Is it possible to user SQL-DMO.
>
> Regards
> Sridhar

Copying Views from one db to other

Hi,
How to copy view from one db to other one...It should be
done via script...Is it possible to user SQL-DMO.
Regards
SridharYes, you can use SQL DMO t script objects. You can se DTS as well. Here is a
short examle of DMO:
Dim oSS As SQLDMO.SQLServer
Dim oDb As SQLDMO.Database
Dim oT As SQLDMO.Transfer
Dim sS As String
Sub Script()
Set oSS = New SQLDMO.SQLServer
Set oT = New SQLDMO.Transfer
oSS.LoginSecure = True
oSS.Connect
Set oDb = oSS.Databases("pubs")
oT.CopyAllTables = True
oDb.ScriptTransfer oT, SQLDMOXfrFile_SingleFile, "C:\pubs.sql"
End Sub
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
<anonymous@.discussions.microsoft.com> wrote in message
news:8a2a01c485af$9b128630$a501280a@.phx.gbl...
> Hi,
> How to copy view from one db to other one...It should be
> done via script...Is it possible to user SQL-DMO.
>
> Regards
> Sridharsql

Copying views

I have two databases A & B, I copied all of the tabels and data from A
to B. I can't figure out how to copy the Views and Stored procedures
from A to B. As you can see I'm new at this, can anyone point me in
the right direction? Oh... forgot running SQL 2000.

Thanks[posted and mailed, please reply in news]

Jim Davidson (raccoon@.icubed.com) writes:
> I have two databases A & B, I copied all of the tabels and data from A
> to B. I can't figure out how to copy the Views and Stored procedures
> from A to B. As you can see I'm new at this, can anyone point me in
> the right direction? Oh... forgot running SQL 2000.

If you want to make a complete copy of a database, there are certainly
easier ways to go. The below assumes that you run Query Analyzer:

exec sp_helpdb yourdb
-- Make notice of the values in the name and filename columns. (Cut
-- and paste to query window.)

BACKUP DATABASE yourdb TO DISK = 'C:\temp\yourbackup.bak'
RESTORE DATABASE yourdbcopy FROM DISK = 'C:\temp\yourbackup.bak'
WITH MOVE 'yourdb' TO 'C:\MSSQL\Data\copyofyourdb.mdf',
MOVE 'yourdblog' TO 'C:\MSSQL\Data\copyofyourdb.ldf',
REPLACE
EXEC master..xp_cmdshell 'DEL C:\temp\yourbackup.bak'

In the RESTORE command replace yourdb and yourdblog with the values
from the name column in the sp_helpdb output, and in paths, replace
the directory paths with the value from the filename columns. You
must change the file name.

Note that RESTORE DATABASE creates the database if it does not exist.

You can also do the backup and restore stuff from Enterprise Manager,
but I am more confident with the T-SQL commands, so I cannot describe
those dialogs.

An alternative, is to use sp_detach_db, copy the database files and
then use sp_attach_db on the copy and the original.

As for the original question, the answer is that you should maintain
all your SQL objects under version control and reload from the source
there. You can also use the scripting facilities in Enterprise
Manager. Right-click the database, and select All Tasks and then
Generate SQL Scripts.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Copying Views

Is there an easy way to copy all views from one database to another? Using
the import/export wizard tries to copy the data in a View to a Table in the
destination table. I don't want to create new tables just copy the view
object over (the databases are equivalent, one production, one for testing).
I use Views for pre-defined queries often used in the production
applications to give the end-user views of the data that makes more sense.
Am I using Views wrong?
By the way I have found a work around by right clicking on the view and go
to Script As -> CREATE TO -> New Query. Then change the first line from:
USE [SourceDatabaseName]
to
USE [DestinationDatabaseName]
This process is unyieldy though because I have to repeat the multi-step
process for every view I want to copy.
Thanks,
RyanRyan
You can use SQLDMO object library to script out all views and then run it on
destination server
Sub ScriptDB(strLogin As String, strPwd As String, _
strDataBase As String, StrFilePath As String)
Dim sql As Object
Dim db As Object
Dim objTrigger As Object
Dim intOptions As Long
Dim genObj
Set sql = CreateObject("SQLDMO.SQLServer")
Set db = CreateObject("SQLDMO.Database")
Set objTrigger = CreateObject("SQLDMO.Trigger")
Const sDrops As Integer = 1
Const sIncludeHeaders As Long = 131072
Const sDefault As Integer = 4
Const sAppendToFile As Integer = 256
Const sBindings As Integer = 128
Const SQLDMOScript2_NoCollation As Long = 8388608
' Set scripting options. Because you need to specify multiple behaviors
' for the ScriptType argument, you use "Or" to combine these.
intOptions = sDrops Or sIncludeHeaders Or _
sDefault Or sAppendToFile Or sBindings Or SQLDMOScript2_NoCollation
' Connect to local server
sql.Connect "(local)", strLogin, strPwd
Set db = sql.Databases(strDataBase, "dbo")
' Script Views, ignoring system views and informational schemas
For Each genObj In db.Views
If genObj.SystemObject = False Then
genObj.Script intOptions, StrFilePath
End If
Next
MsgBox "Finished generating SQL scripts."
End Sub
--usage
Call ScriptDB("UserName","Password","DatabaseName","C:\MyResults.SQL")
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:uulTfxXOHHA.4100@.TK2MSFTNGP04.phx.gbl...
> Is there an easy way to copy all views from one database to another?
> Using the import/export wizard tries to copy the data in a View to a Table
> in the destination table. I don't want to create new tables just copy the
> view object over (the databases are equivalent, one production, one for
> testing). I use Views for pre-defined queries often used in the production
> applications to give the end-user views of the data that makes more sense.
> Am I using Views wrong?
> By the way I have found a work around by right clicking on the view and go
> to Script As -> CREATE TO -> New Query. Then change the first line from:
> USE [SourceDatabaseName]
> to
> USE [DestinationDatabaseName]
> This process is unyieldy though because I have to repeat the multi-step
> process for every view I want to copy.
> Thanks,
> Ryan
>|||Ryan wrote:
> Is there an easy way to copy all views from one database to another? Usin
g
> the import/export wizard tries to copy the data in a View to a Table in th
e
> destination table. I don't want to create new tables just copy the view
> object over (the databases are equivalent, one production, one for testing
).
> I use Views for pre-defined queries often used in the production
> applications to give the end-user views of the data that makes more sense.
> Am I using Views wrong?
> By the way I have found a work around by right clicking on the view and go
> to Script As -> CREATE TO -> New Query. Then change the first line from:
> USE [SourceDatabaseName]
> to
> USE [DestinationDatabaseName]
> This process is unyieldy though because I have to repeat the multi-step
> process for every view I want to copy.
> Thanks,
> Ryan
>
I wouldn't say you're "using" them wrong, I'd say you're "creating" them
wrong. The scripting method that you discovered is not a workaround,
it's the accepted method of working with database objects. You should
be creating your views with a script, and archiving that script in a
version control system, like Visual SourceSafe or Perforce. This allows
you to track the revision history of your objects, gives you a
"rollback" method for undo-ing changes, AND it makes your objects more
portable. To "copy" and object to a new location, you simply run the
CREATE script again, in the new location.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zxv31XOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Ryan
> You can use SQLDMO object library to script out all views and then run it
> on destination server
>
> Sub ScriptDB(strLogin As String, strPwd As String, _
> strDataBase As String, StrFilePath As String)
> Dim sql As Object
> Dim db As Object
> Dim objTrigger As Object
> Dim intOptions As Long
> Dim genObj
> Set sql = CreateObject("SQLDMO.SQLServer")
> Set db = CreateObject("SQLDMO.Database")
> Set objTrigger = CreateObject("SQLDMO.Trigger")
> Const sDrops As Integer = 1
> Const sIncludeHeaders As Long = 131072
> Const sDefault As Integer = 4
> Const sAppendToFile As Integer = 256
> Const sBindings As Integer = 128
> Const SQLDMOScript2_NoCollation As Long = 8388608
> ' Set scripting options. Because you need to specify multiple behaviors
> ' for the ScriptType argument, you use "Or" to combine these.
> intOptions = sDrops Or sIncludeHeaders Or _
> sDefault Or sAppendToFile Or sBindings Or SQLDMOScript2_NoCollation
> ' Connect to local server
> sql.Connect "(local)", strLogin, strPwd
> Set db = sql.Databases(strDataBase, "dbo")
>
> ' Script Views, ignoring system views and informational schemas
> For Each genObj In db.Views
> If genObj.SystemObject = False Then
> genObj.Script intOptions, StrFilePath
> End If
> Next
> MsgBox "Finished generating SQL scripts."
> End Sub
> --usage
> Call ScriptDB("UserName","Password","DatabaseName","C:\MyResults.SQL")
>
> "Ryan" <Tyveil@.newsgroups.nospam> wrote in message
> news:uulTfxXOHHA.4100@.TK2MSFTNGP04.phx.gbl...
>|||Sure, that makes sense. The thing is this isn't a usual process. I am
needing to create a non-production (test) database off of the production
database so I thought there would be a way to simply copy the entire
database (including objects such as views, stored procedures, etc) in one
fell swoop. I guess another option would be to detach the production
database and make a copy of the db files before reattaching it. The only
downside to this is downtime on the production side. Thanks for the info.
Ryan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45ACE89E.2020400@.realsqlguy.com...
> Ryan wrote:
> I wouldn't say you're "using" them wrong, I'd say you're "creating" them
> wrong. The scripting method that you discovered is not a workaround, it's
> the accepted method of working with database objects. You should be
> creating your views with a script, and archiving that script in a version
> control system, like Visual SourceSafe or Perforce. This allows you to
> track the revision history of your objects, gives you a "rollback" method
> for undo-ing changes, AND it makes your objects more portable. To "copy"
> and object to a new location, you simply run the CREATE script again, in
> the new location.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Ryan wrote:
> Sure, that makes sense. The thing is this isn't a usual process. I am
> needing to create a non-production (test) database off of the production
> database so I thought there would be a way to simply copy the entire
> database (including objects such as views, stored procedures, etc) in one
> fell swoop. I guess another option would be to detach the production
> database and make a copy of the db files before reattaching it. The only
> downside to this is downtime on the production side. Thanks for the info.
>
Why not just do a backup of the production DB, and restore that onto
your Dev server?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Because the database structure doesn't exist on the Dev server. To do a
restore doesn't the database structure have to already exist? At least I
couldn't find a way to do it otherwise. I just did a detach, copy file,
reattach to both the dev and production server. This seems to be the
easiest solution.
Ryan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AD3AEC.3080907@.realsqlguy.com...
> Ryan wrote:
> Why not just do a backup of the production DB, and restore that onto your
> Dev server?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Ryan wrote:
> Because the database structure doesn't exist on the Dev server. To do a
> restore doesn't the database structure have to already exist? At least I
> couldn't find a way to do it otherwise. I just did a detach, copy file,
> reattach to both the dev and production server. This seems to be the
> easiest solution.
>
Nope, definately not true. You can restore a backup, give it a new
database name, and it will create the database "on the fly".
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Ah.. ok thanks for the info. I will definitely use this for future
solutions.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE2D47.8030504@.realsqlguy.com...
> Ryan wrote:
> Nope, definately not true. You can restore a backup, give it a new
> database name, and it will create the database "on the fly".
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Copying Views

Is there an easy way to copy all views from one database to another? Using
the import/export wizard tries to copy the data in a View to a Table in the
destination table. I don't want to create new tables just copy the view
object over (the databases are equivalent, one production, one for testing).
I use Views for pre-defined queries often used in the production
applications to give the end-user views of the data that makes more sense.
Am I using Views wrong?
By the way I have found a work around by right clicking on the view and go
to Script As -> CREATE TO -> New Query. Then change the first line from:
USE [SourceDatabaseName]
to
USE [DestinationDatabaseName]
This process is unyieldy though because I have to repeat the multi-step
process for every view I want to copy.
Thanks,
RyanRyan
You can use SQLDMO object library to script out all views and then run it on
destination server
Sub ScriptDB(strLogin As String, strPwd As String, _
strDataBase As String, StrFilePath As String)
Dim sql As Object
Dim db As Object
Dim objTrigger As Object
Dim intOptions As Long
Dim genObj
Set sql = CreateObject("SQLDMO.SQLServer")
Set db = CreateObject("SQLDMO.Database")
Set objTrigger = CreateObject("SQLDMO.Trigger")
Const sDrops As Integer = 1
Const sIncludeHeaders As Long = 131072
Const sDefault As Integer = 4
Const sAppendToFile As Integer = 256
Const sBindings As Integer = 128
Const SQLDMOScript2_NoCollation As Long = 8388608
' Set scripting options. Because you need to specify multiple behaviors
' for the ScriptType argument, you use "Or" to combine these.
intOptions = sDrops Or sIncludeHeaders Or _
sDefault Or sAppendToFile Or sBindings Or SQLDMOScript2_NoCollation
' Connect to local server
sql.Connect "(local)", strLogin, strPwd
Set db = sql.Databases(strDataBase, "dbo")
' Script Views, ignoring system views and informational schemas
For Each genObj In db.Views
If genObj.SystemObject = False Then
genObj.Script intOptions, StrFilePath
End If
Next
MsgBox "Finished generating SQL scripts."
End Sub
--usage
Call ScriptDB("UserName","Password","DatabaseName","C:\MyResults.SQL")
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:uulTfxXOHHA.4100@.TK2MSFTNGP04.phx.gbl...
> Is there an easy way to copy all views from one database to another?
> Using the import/export wizard tries to copy the data in a View to a Table
> in the destination table. I don't want to create new tables just copy the
> view object over (the databases are equivalent, one production, one for
> testing). I use Views for pre-defined queries often used in the production
> applications to give the end-user views of the data that makes more sense.
> Am I using Views wrong?
> By the way I have found a work around by right clicking on the view and go
> to Script As -> CREATE TO -> New Query. Then change the first line from:
> USE [SourceDatabaseName]
> to
> USE [DestinationDatabaseName]
> This process is unyieldy though because I have to repeat the multi-step
> process for every view I want to copy.
> Thanks,
> Ryan
>|||Ryan wrote:
> Is there an easy way to copy all views from one database to another? Using
> the import/export wizard tries to copy the data in a View to a Table in the
> destination table. I don't want to create new tables just copy the view
> object over (the databases are equivalent, one production, one for testing).
> I use Views for pre-defined queries often used in the production
> applications to give the end-user views of the data that makes more sense.
> Am I using Views wrong?
> By the way I have found a work around by right clicking on the view and go
> to Script As -> CREATE TO -> New Query. Then change the first line from:
> USE [SourceDatabaseName]
> to
> USE [DestinationDatabaseName]
> This process is unyieldy though because I have to repeat the multi-step
> process for every view I want to copy.
> Thanks,
> Ryan
>
I wouldn't say you're "using" them wrong, I'd say you're "creating" them
wrong. The scripting method that you discovered is not a workaround,
it's the accepted method of working with database objects. You should
be creating your views with a script, and archiving that script in a
version control system, like Visual SourceSafe or Perforce. This allows
you to track the revision history of your objects, gives you a
"rollback" method for undo-ing changes, AND it makes your objects more
portable. To "copy" and object to a new location, you simply run the
CREATE script again, in the new location.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zxv31XOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Ryan
> You can use SQLDMO object library to script out all views and then run it
> on destination server
>
> Sub ScriptDB(strLogin As String, strPwd As String, _
> strDataBase As String, StrFilePath As String)
> Dim sql As Object
> Dim db As Object
> Dim objTrigger As Object
> Dim intOptions As Long
> Dim genObj
> Set sql = CreateObject("SQLDMO.SQLServer")
> Set db = CreateObject("SQLDMO.Database")
> Set objTrigger = CreateObject("SQLDMO.Trigger")
> Const sDrops As Integer = 1
> Const sIncludeHeaders As Long = 131072
> Const sDefault As Integer = 4
> Const sAppendToFile As Integer = 256
> Const sBindings As Integer = 128
> Const SQLDMOScript2_NoCollation As Long = 8388608
> ' Set scripting options. Because you need to specify multiple behaviors
> ' for the ScriptType argument, you use "Or" to combine these.
> intOptions = sDrops Or sIncludeHeaders Or _
> sDefault Or sAppendToFile Or sBindings Or SQLDMOScript2_NoCollation
> ' Connect to local server
> sql.Connect "(local)", strLogin, strPwd
> Set db = sql.Databases(strDataBase, "dbo")
>
> ' Script Views, ignoring system views and informational schemas
> For Each genObj In db.Views
> If genObj.SystemObject = False Then
> genObj.Script intOptions, StrFilePath
> End If
> Next
> MsgBox "Finished generating SQL scripts."
> End Sub
> --usage
> Call ScriptDB("UserName","Password","DatabaseName","C:\MyResults.SQL")
>
> "Ryan" <Tyveil@.newsgroups.nospam> wrote in message
> news:uulTfxXOHHA.4100@.TK2MSFTNGP04.phx.gbl...
>> Is there an easy way to copy all views from one database to another?
>> Using the import/export wizard tries to copy the data in a View to a
>> Table in the destination table. I don't want to create new tables just
>> copy the view object over (the databases are equivalent, one production,
>> one for testing). I use Views for pre-defined queries often used in the
>> production applications to give the end-user views of the data that makes
>> more sense. Am I using Views wrong?
>> By the way I have found a work around by right clicking on the view and
>> go to Script As -> CREATE TO -> New Query. Then change the first line
>> from:
>> USE [SourceDatabaseName]
>> to
>> USE [DestinationDatabaseName]
>> This process is unyieldy though because I have to repeat the multi-step
>> process for every view I want to copy.
>> Thanks,
>> Ryan
>|||Sure, that makes sense. The thing is this isn't a usual process. I am
needing to create a non-production (test) database off of the production
database so I thought there would be a way to simply copy the entire
database (including objects such as views, stored procedures, etc) in one
fell swoop. I guess another option would be to detach the production
database and make a copy of the db files before reattaching it. The only
downside to this is downtime on the production side. Thanks for the info.
Ryan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45ACE89E.2020400@.realsqlguy.com...
> Ryan wrote:
>> Is there an easy way to copy all views from one database to another?
>> Using the import/export wizard tries to copy the data in a View to a
>> Table in the destination table. I don't want to create new tables just
>> copy the view object over (the databases are equivalent, one production,
>> one for testing). I use Views for pre-defined queries often used in the
>> production applications to give the end-user views of the data that makes
>> more sense. Am I using Views wrong?
>> By the way I have found a work around by right clicking on the view and
>> go to Script As -> CREATE TO -> New Query. Then change the first line
>> from:
>> USE [SourceDatabaseName]
>> to
>> USE [DestinationDatabaseName]
>> This process is unyieldy though because I have to repeat the multi-step
>> process for every view I want to copy.
>> Thanks,
>> Ryan
> I wouldn't say you're "using" them wrong, I'd say you're "creating" them
> wrong. The scripting method that you discovered is not a workaround, it's
> the accepted method of working with database objects. You should be
> creating your views with a script, and archiving that script in a version
> control system, like Visual SourceSafe or Perforce. This allows you to
> track the revision history of your objects, gives you a "rollback" method
> for undo-ing changes, AND it makes your objects more portable. To "copy"
> and object to a new location, you simply run the CREATE script again, in
> the new location.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Ryan wrote:
> Sure, that makes sense. The thing is this isn't a usual process. I am
> needing to create a non-production (test) database off of the production
> database so I thought there would be a way to simply copy the entire
> database (including objects such as views, stored procedures, etc) in one
> fell swoop. I guess another option would be to detach the production
> database and make a copy of the db files before reattaching it. The only
> downside to this is downtime on the production side. Thanks for the info.
>
Why not just do a backup of the production DB, and restore that onto
your Dev server?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Because the database structure doesn't exist on the Dev server. To do a
restore doesn't the database structure have to already exist? At least I
couldn't find a way to do it otherwise. I just did a detach, copy file,
reattach to both the dev and production server. This seems to be the
easiest solution.
Ryan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AD3AEC.3080907@.realsqlguy.com...
> Ryan wrote:
>> Sure, that makes sense. The thing is this isn't a usual process. I am
>> needing to create a non-production (test) database off of the production
>> database so I thought there would be a way to simply copy the entire
>> database (including objects such as views, stored procedures, etc) in one
>> fell swoop. I guess another option would be to detach the production
>> database and make a copy of the db files before reattaching it. The only
>> downside to this is downtime on the production side. Thanks for the
>> info.
> Why not just do a backup of the production DB, and restore that onto your
> Dev server?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Ryan wrote:
> Because the database structure doesn't exist on the Dev server. To do a
> restore doesn't the database structure have to already exist? At least I
> couldn't find a way to do it otherwise. I just did a detach, copy file,
> reattach to both the dev and production server. This seems to be the
> easiest solution.
>
Nope, definately not true. You can restore a backup, give it a new
database name, and it will create the database "on the fly".
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Ah.. ok thanks for the info. I will definitely use this for future
solutions.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE2D47.8030504@.realsqlguy.com...
> Ryan wrote:
>> Because the database structure doesn't exist on the Dev server. To do a
>> restore doesn't the database structure have to already exist? At least I
>> couldn't find a way to do it otherwise. I just did a detach, copy file,
>> reattach to both the dev and production server. This seems to be the
>> easiest solution.
> Nope, definately not true. You can restore a backup, give it a new
> database name, and it will create the database "on the fly".
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Copying users from one database to another

how do i copy all the users, roles, permissions etc from on database to
another database?JT
Not easy task. If you have the same structure of the database the best thing
will be restoring the database with a different name
Otherwise, you will have to generate a script for adding users to be mapped
with the logins
sp_grantdbaccess @.login
sp_addrolemember 'db_owner',@.login
"JT" <col@.newsgroup.nospam> wrote in message
news:ec9iL$pNGHA.2884@.TK2MSFTNGP12.phx.gbl...
> how do i copy all the users, roles, permissions etc from on database to
> another database?

copying users between roles

I would like to copy all users currently from RoleA into RoleB. I know there
must be a nice way to script this. Any ideas?Robert
There is no a "nice" script to move users from one Role to another at least
I am not aware.
You will have to deal with system tables that hold the info about users but
it is not recommended.
"Robert Kinesta" <RobertKinesta@.discussions.microsoft.com> wrote in message
news:66EA482B-0FD9-49A9-B049-6CADCA5B5BD1@.microsoft.com...
>I would like to copy all users currently from RoleA into RoleB. I know
>there
> must be a nice way to script this. Any ideas?|||Hi Robert,
This may help u......a nice script to move all users from roleA to roleB
create table #users (
UserName sysname,
GroupName sysname,
LoginName sysname,
DefDBName sysname,
UserID smallint,
SID smallint
)
insert #users
exec('sp_helpuser')
select identity(int,1,1) as idn, * into #usersInRoleA from #users where
GroupName like 'roleA'
declare @.i as int,
@.maxusers as int,
@.username as sysname
select @.maxusers = max(idn) from #usersInRoleA
set @.i = 1
while (@.i <= @.maxusers)
begin
select @.username = username from #usersInRoleA where idn = @.i
exec sp_addrolemember 'roleB',@.username
exec sp_droprolemember 'roleA',@.username
set @.i = @.i + 1
end
drop table #usersInRoleA
drop table #users
enjoy and keep going...
"Robert Kinesta" wrote:

> I would like to copy all users currently from RoleA into RoleB. I know the
re
> must be a nice way to script this. Any ideas?|||Hi Robert
Maybe something like:
CREATE TABLE #Rolemembers ( DbRole sysname,
MemberName sysname,
MemberSID varbinary(85) )
INSERT INTO #Rolemembers ( DbRole, MemberName, MemberSID )
EXEC sp_helprolemember 'ISDLOAD_ROLE'
DECLARE @.member sysname
DECLARE Member_Cursor CURSOR FOR SELECT MemberName FROM #Rolemembers
OPEN Member_Cursor
FETCH NEXT FROM Member_Cursor INTO @.member
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC sp_executesql N'EXEC sp_addrolemember @.rolename= ''ROLEB'' ,
@.membername = @.membernm' ,
N'@.membernm sysname',
@.membernm = @.member
FETCH NEXT FROM Member_Cursor INTO @.member
END
CLOSE Member_Cursor
DEALLOCATE Member_Cursor
DROP TABLE #Rolemembers
John
"Robert Kinesta" wrote:

> I would like to copy all users currently from RoleA into RoleB. I know the
re
> must be a nice way to script this. Any ideas?

copying the reportserver database..

Is it possible to copy a reportserver database from one sql server to another without problems?

Has anyone done this?

Copying the reports is easy. You can just export the rdl files and then import them into the new ReportServer database.|||

won't work for me, I need to copy the whole database.

I found an article here.

http://msdn2.microsoft.com/en-us/library/ms156421.aspx

sql

Copying the database

Hi,

I am changing my hosting from one company to another company. How can I copy my full database along with views and stored procedures. I have only access to query analyzer and enterprise manager from where I am not able to backup the database on my local computer. As it is very urgent please suggest me a way to do this.

Thanks in advance,
UdayIf you have Enterprise Manager, right-click on the database, All Tasks -> Export Data.

The source should already be selected, Click next.

Set up the destination to a SQL Server that you have control over and create a <new> database. Type in a name, click OK, then click Next.

Select Copy objects and data, click Next.

Select run immediately, click Next. Then you'll need to click OK somewhere in there.|||Hi,

I did that but I am not able to copy the stored procedures and views, when I try to generate query it gives me

/****** Encrypted object is not transferable, and script can not be generated. ******/

How can I copy this stored procedures and views.

Thanks in advance,
Uday.

Copying tables, MSDE

Hi!

I've got a very simple problem I can't find an answere to.

I've got an MSDE database and I want to copy a table.

I've tried something like:

create table2 as select * from table1

with and without the "as", but I can't get it to work and I can't find a good answere on the internet.

very thankful for an answere!

/Jon

hello..

use SELECT INTO statement:
from MSDN:

The SELECT INTO statement creates a new table and populates it with the result set of the SELECT statement. SELECT INTO can be used to combine data from several tables or views into one table. It can also be used to create a new table that contains data selected from a linked server.
sample code:
SELECT * INTO table2
FROM table1

|||ok, almost there...
it works, except for the keys.
how do I make the primary keys be primary keys in the copied table aswell?
Thanx!
/J|||

(sorry if this reply got posted twice)

thanx,

is it possible to get the old primary keys to be primary keys in the new table aswell?

The newly created table does not contain any kesy at this moment.

/jon

copying tables with SMO between different instances

Hi,

I want to be able to with SMO copy one table (with a schema other than dbo) to another instance.

I can successfully do this on eth same machine (same instance) but when I try the following script to copy accross different instances. it doesn't work as the Transfer methode in SMO doesn't have a property for instance.

would be able to help

Thanks

Public Sub Main()

Dim svr_connection As ServerConnection = New ServerConnection()

'svr_connection.ServerInstance = "(local)"

svr_connection.ConnectionString = "Data Source=TECH-2L56H2J\sql2k5;Initial Catalog=creditcard;Trusted_Connection=true"

Dim dest_svr_connection As ServerConnection = New ServerConnection()

'svr_connection.ServerInstance = "(local)"

dest_svr_connection.ConnectionString = "Data Source=TECH-2L56H2J\sql2k5_2;Initial Catalog=master;Trusted_Connection=true"

Dim srv As Server = New Server(svr_connection)

Dim dest_srv As Server = New Server(dest_svr_connection)

'Reference the source database

Dim db As Database

db = srv.Databases("creditcard")

'Create a new database that is to be destination database.

Dim dbCopy As Database

dbCopy = New Database(dest_srv, "creditcardcopy")

dbCopy.Create()

'Define a Transfer object and set the required options and properties.

Dim xfr As Transfer

'Point the transfer object to the source database (to be copied from)

xfr = New Transfer(db)

'Turn off the copying of all objects - seems to be on by default

xfr.CopyAllObjects = False

'Add the schema, role and table we want to copy (as the tables isn't in dbo, the associated schema and role needs to be moved)

'xfr.ObjectList.Add(db.Roles("test"))

xfr.ObjectList.Add(db.Schemas("test"))

xfr.ObjectList.Add(db.Tables("credittest", "test"))

'We want to copy the data as well

xfr.CopyData = True

'Set other transfer options

xfr.Options.WithDependencies = False

'Set where we are copying to

xfr.DestinationDatabase = "creditcardcopy"

xfr.DestinationServer = "TECH-2L56H2J\sql2k5_2" 'dest_srv.Name

'Do the transfer

xfr.TransferData()

'return success

' Dts.TaskResult = Dts.Results.Success

End Sub

Could you confirm the error you're getting? If you have multiple instances on the same box, you may want to specify port numbers in your connection string (get this from SQL Configuration Manager)

eg Data Source=TECH-2L56H2J\sql2k5_2, 1434

HTH!|||Thanks but tried that already, you see that part works fine and it can connect to both instances and creates the database on the target machine.it fails in the process of transfering objects(tables and schemas). therefore I think it should be down to xfr.DestinationServer ( the transfer object properties )

Thanks

sql

copying tables with SMO between different instances

Hi,

I want to be able to with SMO copy one table (with a schema other than dbo) to another instance.

I can successfully do this on eth same machine (same instance) but when I try the following script to copy accross different instances. it doesn't work as the Transfer methode in SMO doesn't have a property for instance.

would be able to help

Thanks

Public Sub Main()

Dim svr_connection As ServerConnection = New ServerConnection()

'svr_connection.ServerInstance = "(local)"

svr_connection.ConnectionString = "Data Source=TECH-2L56H2J\sql2k5;Initial Catalog=creditcard;Trusted_Connection=true"

Dim dest_svr_connection As ServerConnection = New ServerConnection()

'svr_connection.ServerInstance = "(local)"

dest_svr_connection.ConnectionString = "Data Source=TECH-2L56H2J\sql2k5_2;Initial Catalog=master;Trusted_Connection=true"

Dim srv As Server = New Server(svr_connection)

Dim dest_srv As Server = New Server(dest_svr_connection)

'Reference the source database

Dim db As Database

db = srv.Databases("creditcard")

'Create a new database that is to be destination database.

Dim dbCopy As Database

dbCopy = New Database(dest_srv, "creditcardcopy")

dbCopy.Create()

'Define a Transfer object and set the required options and properties.

Dim xfr As Transfer

'Point the transfer object to the source database (to be copied from)

xfr = New Transfer(db)

'Turn off the copying of all objects - seems to be on by default

xfr.CopyAllObjects = False

'Add the schema, role and table we want to copy (as the tables isn't in dbo, the associated schema and role needs to be moved)

'xfr.ObjectList.Add(db.Roles("test"))

xfr.ObjectList.Add(db.Schemas("test"))

xfr.ObjectList.Add(db.Tables("credittest", "test"))

'We want to copy the data as well

xfr.CopyData = True

'Set other transfer options

xfr.Options.WithDependencies = False

'Set where we are copying to

xfr.DestinationDatabase = "creditcardcopy"

xfr.DestinationServer = "TECH-2L56H2J\sql2k5_2" 'dest_srv.Name

'Do the transfer

xfr.TransferData()

'return success

' Dts.TaskResult = Dts.Results.Success

End Sub

Could you confirm the error you're getting? If you have multiple instances on the same box, you may want to specify port numbers in your connection string (get this from SQL Configuration Manager)

eg Data Source=TECH-2L56H2J\sql2k5_2, 1434

HTH!|||Thanks but tried that already, you see that part works fine and it can connect to both instances and creates the database on the target machine.it fails in the process of transfering objects(tables and schemas). therefore I think it should be down to xfr.DestinationServer ( the transfer object properties )

Thanks

Copying tables using SSIS package

I need to create a fairly simple package. And almost because of the simplicity, I'm stumped.

I need to copy all non-system tables from server1.database1 to server2.database2. Additionally, four of the 30+ tables need to be renamed on the fly -- i.e. their name will reflect the year and month that the copy takes place.

I've tried using the Transfer SQL Server Object Task to simply copy the tables, but I get flaky results at best with it. Sometimes it tells me the source table doesn't exist, when I can clearly see it (and I've selected it from the list). And even though I have turned on the Include Indexes option, they don't always come through.

I'm wondering if I need to do a For Each loop looking at an ADO object?

Any suggestions?

Stephanie

SBowe wrote:

I need to create a fairly simple package. And almost because of the simplicity, I'm stumped.

I need to copy all non-system tables from server1.database1 to server2.database2. Additionally, four of the 30+ tables need to be renamed on the fly -- i.e. their name will reflect the year and month that the copy takes place.

I've tried using the Transfer SQL Server Object Task to simply copy the tables, but I get flaky results at best with it. Sometimes it tells me the source table doesn't exist, when I can clearly see it (and I've selected it from the list). And even though I have turned on the Include Indexes option, they don't always come through.

I'm wondering if I need to do a For Each loop looking at an ADO object?

Any suggestions?

Stephanie

I suggest you use the Import Wizard to do this for you. I think you can configure it to build the tables for you if they are not already there.

-Jamie

|||

Jamie,

Thanks for the suggestion. However, that won't work for my environment. Specifically, my company requires that I create a scheduled job. So I then must utilize a package.

Here's where I get the flaky results using the SQL transfer object: I only want the non-system tables. So I set the All Tables property to False and then select the tables I want from the Tables Collection property. However, the package then fails when I run it and tells me it cannot find the tables from the source. This is mind-boggling since it allowed me to pick the tables from a list of tables.

I'll keep digging.

Thanks again,

Stephanie

|||

Stephanie,

If you use the import/export wizard in SSMS todo this; you can choose to save it as an SSIS package; then you can open that package and make the specific changes you need (eg renaming the 4 tables).

Rafael Salas

|||Stephanie,

I would advocate NOT using the transfer objects task.

This has some "features" (apparently to preserve sql 2000 compatability) that means the tables will not be transferred over accurately.

Specifically, you may find your transferred tables lose default values or identities

see my thread on this here|||

Rafael Salas wrote:

Stephanie,

If you use the import/export wizard in SSMS todo this; you can choose to save it as an SSIS package; then you can open that package and make the specific changes you need (eg renaming the 4 tables).

Rafael Salas

The import / export wizard will also not setup the tables correctly - see the post I linked to in my post above