sql server db: wizard

Showing posts with label wizard. Show all posts

Tuesday, March 27, 2012

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

Sunday, March 25, 2012

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

Monday, March 19, 2012

copying DBs w/out Copy Wizard

Is it a bad thing to do a manual copy of all my databases
from one server to another in Win Exp? Will it hurt
anything? I detached the DB, copied it over, and then
reattached on the new server. ?Detach/attach is the way the copy database wizard does it as well, so that
is fine.
The other option is to do a backup, copy the backup and then do a restore on
the new server.
--
Jacco Schalkwijk
SQL Server MVP
"kb" <anonymous@.discussions.microsoft.com> wrote in message
news:001b01c393f8$661599d0$a301280a@.phx.gbl...
> Is it a bad thing to do a manual copy of all my databases
> from one server to another in Win Exp? Will it hurt
> anything? I detached the DB, copied it over, and then
> reattached on the new server. ?|||If you're concerned run DBCC CHECKDB after you have successfully attached
the db. If the file is very large > 40Gb I generally use robocopy
--
HTH
Ryan Waight, MCDBA, MCSE
"kb" <anonymous@.discussions.microsoft.com> wrote in message
news:001b01c393f8$661599d0$a301280a@.phx.gbl...
> Is it a bad thing to do a manual copy of all my databases
> from one server to another in Win Exp? Will it hurt
> anything? I detached the DB, copied it over, and then
> reattached on the new server. ?|||See if these help:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB
Wizard issues
Andrew J. Kelly
SQL Server MVP
"kb" <anonymous@.discussions.microsoft.com> wrote in message
news:001b01c393f8$661599d0$a301280a@.phx.gbl...
> Is it a bad thing to do a manual copy of all my databases
> from one server to another in Win Exp? Will it hurt
> anything? I detached the DB, copied it over, and then
> reattached on the new server. ?

Copying DB in SQL 2005

I used the Copy Database Wizard in SQL 2005 and I selected SQL Management
Object method to copy one DB onto a new one on the same server, it looks like
it worked but the Identity Seed and Identity increment properties are missing
on some of the columns on the new DB. Has anyone else experienced this
behavior?
Same here... It also occurs with the Transfer Object task in SSIS, the
Identity attribute is dropped from tables. Currently I am using Dump/Restore
to copy DBs. My preference is to use Transfer Object to transfer only tables
between DBs, but the droppped Identity prevents that solution.
"Garios" wrote:

> I used the Copy Database Wizard in SQL 2005 and I selected SQL Management
> Object method to copy one DB onto a new one on the same server, it looks like
> it worked but the Identity Seed and Identity increment properties are missing
> on some of the columns on the new DB. Has anyone else experienced this
> behavior?

Copying DB in SQL 2005

I used the Copy Database Wizard in SQL 2005 and I selected SQL Management
Object method to copy one DB onto a new one on the same server, it looks like
it worked but the Identity Seed and Identity increment properties are missing
on some of the columns on the new DB. Has anyone else experienced this
behavior?Same here... It also occurs with the Transfer Object task in SSIS, the
Identity attribute is dropped from tables. Currently I am using Dump/Restore
to copy DBs. My preference is to use Transfer Object to transfer only tables
between DBs, but the droppped Identity prevents that solution.
"Garios" wrote:
> I used the Copy Database Wizard in SQL 2005 and I selected SQL Management
> Object method to copy one DB onto a new one on the same server, it looks like
> it worked but the Identity Seed and Identity increment properties are missing
> on some of the columns on the new DB. Has anyone else experienced this
> behavior?

Copying DB in SQL 2005

I used the Copy Database Wizard in SQL 2005 and I selected SQL Management
Object method to copy one DB onto a new one on the same server, it looks lik
e
it worked but the Identity Seed and Identity increment properties are missin
g
on some of the columns on the new DB. Has anyone else experienced this
behavior?Same here... It also occurs with the Transfer Object task in SSIS, the
Identity attribute is dropped from tables. Currently I am using Dump/Restor
e
to copy DBs. My preference is to use Transfer Object to transfer only table
s
between DBs, but the droppped Identity prevents that solution.
"Garios" wrote:

> I used the Copy Database Wizard in SQL 2005 and I selected SQL Management
> Object method to copy one DB onto a new one on the same server, it looks l
ike
> it worked but the Identity Seed and Identity increment properties are miss
ing
> on some of the columns on the new DB. Has anyone else experienced this
> behavior?

Sunday, March 11, 2012

Copying Database without data

Hi,
Does anyone know, with either the Apr or Jun CTP, how to copy an entire database without the data? I know to use the SSIS wizard to copy tables with data, but there seem to be no option to not copy the data.

Thanks.In the June CTP you can use the Transfer Objects Task. It has an option for transferring data that you may set to false.|||BTW, There are some issues with this task in the June CTP that have recently been fixed and will not be available until the next CTP. This solution will probably not work for you until you can get the version with the fixes. Sad |||Hi Kirk,
Thanks for the update. I was actually hoping for a checkbox option inside the SSIS Import and Export wizard. :) I'll wait till it is fixed then.|||

you can

1. left click source database ->Script database ->create to

1.1. change database and filenames on the script created

1.2 execute script to create new database

2. left click source database -> tasks -> generate scripts

2.1 add at the to the following lines

use newdatabasename

2.2. run the script

you can save the scripts and use them at SSIS for automation

Copying Database without data

you can

1. left click source database ->Script database ->create to

1.1. change database and filenames on the script created

1.2 execute script to create new database

2. left click source database -> tasks -> generate scripts

2.1 add at the to the following lines

use newdatabasename

2.2. run the script

you can save the scripts and use them at SSIS for automation

Copying database using "Copy Database" - still failing after SP2

I am struggling with the "Copy Database Wizard" in SQL Server 2005. I am used to working with DTS to copy data from one server to another, but with the new tools, I am having serious problems (in fact, it's unusable)

1. When copying a database using the "SQL Management Object" method, I always get errors concerning logins. If I select not to copy logins, I get errors "sp_grantdbaccess failed". If I do select to copy logins, the logins can't be created because the machine names are different (one the source machine, the login is called MACHINE1/mylogin, but on the destination machine, such a login cannot be created)

2. For small databases, I ALWAYS get the error "CREATE DATABASE failed. Primary file must be at least 3 MB to accommodate a copy of the model database". There is no way to specify a larger size for the database in the wizard.

Note: copying databases using detach/attach is not an option (the servers cannot see eachother's shares)

I must say that in general I hate the new management tools. Enterprise Manager had its flaws too, but the quality of the "SQL Server Management Studio" seems very low IMHO.

Regards,
Philippe Leybaert

I can get the copy to work but it will not copy any of my stored procedures or functions.|||did you ever get a resolution on this. I am having the same issue.|||Yeah - this sucks. Has anyone seen where SP's can be copied? Sure we can script them and run in teh new DB - but it's a pain in the butt, especially if you can see shared drivespace.|||

Blake05 wrote:
I can get the copy to work but it will not copy any of my stored procedures or functions.

This is actually very simple, but the error messages are of no help. The problem is that there is a bug in at least one stored procedure or function. Go through them all until you find the ones that you can't save/update, and repair or delete (if obsolete) them. Once they are all functioning, the copy will work without a hitch.

|||I notice that no one from Microsoft has answered this problem.

It seems to me the Copy Database Wizard is completely ignoring the fact that I am always trying to deselect the logins. It just happily tries to copy them even though it's going to a development machine that will only see 4 of the 100 logins on the production server.

I struggled with this problem for days and was crossing my fingers that it would be solved in SP1, sadly it's not.

Detaching is not an option for production databases!!!

Fix this Microsoft!!!|||

We are aware of many issues in the Copy Database Wizard. We're doing a lot of work on the wizard in SP2 to address these.

If you have a specific scenario you would like to see addressed, please file a defect report here: http://lab.msdn.microsoft.com/productfeedback/Default.aspx. Defects reports entered at the feedback center are used to prioritize work for future versions and service packs.

Thanks,
Steve

|||

Hi Philippe,

You can now install the latest version of the Copy Database Wizard from the following location:

http://www.microsoft.com/sql/ctp.mspx

This CTP version includes many improvements and hopefully they should address most of your current issues.

Thanks,
Oscar.

|||

Hello Oscar

I have a problem with Copy Database Wizard to.

Have agent account as sysadmin in both source and target database

Target server has CTP Dec 2006 installed. Bat I can’t install it to the source “production” system

Managed to transfer five databases fixing the error found at the error log file

At the sixth database I have an error that I can not understand

The error appears when trying to create a view at the target system.

errorCode=-1073548784 description=Executing the query "create view [dbo].[VShift_Cost]

select sf.ShiftID, SFD.UnitID, sum(sfd.charge) as Shift_Charge

from shiftcostdetail sfd inner join shiftcost sf on sfd.shiftcostid = sf.shiftcostid

group by sf.shiftid, SFD.unitID

" failed with the following error: "Invalid object name 'shiftcostdetail'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The shiftcostdetail is an existing table and the view works fine at the source system.

Can you help?

Thanks

|||

Hi Panzin,

Could you please tell me where are you launching CDW from? You should launch CDW from the target machine is this machine is the one that has the latest version installed. From here, you can select the source the database and perform the copy. Could you please confirm that this is what you are doing? You could also select the "Save Transfer Logs" option before clicking "Finish" and send me the corresponding TransferDump*.sql files generated in :

C:\Documents and Settings\<user-name>\Local Settings\Application Data\Microsoft\SQL Server\Smo

It would be useful too to select "Write log to a file" before performing the copy and check for any other errors.

I will investigate the error that you described above, but it seems that it could be a dependency issue. I will get back to you once I find more information.

Thanks,
Oscar.

|||

Hi Oscar

Thank you for your reply

The CDW is running from the target system.

The target system is the one with the CTP installed

The posted error is from the log file

I can't find the "Save Transfer Logs" option

Thank's again

|||

Hi Panzin,

Are you using the SMO Transfer method to copy the database or Detach/Attach? I believe you are using the SMO Transfer method...

The "Save transfer logs?" checkbox is in the "Configure the Package" page of the wizard.

By the way, are you also copying any extra objects apart from the database? If so, which ones? Are you referencing any objects that are outside this view that might require the selection of some particular extra objects?

Cheers,
Oscar.

|||

Hello Oscar

Thank you for your help

I'm using the SMO transfer method and the related objects that i'm copying are the "logins"

At the configure the Pacage page I'm using

Logging options "Text File" and the posted error is from the text file ...

also show that

1. db created at target server is at compatibility level 90 (source level=80)

2. when I changed the view script and add "dbo." in front of table names the error move to next view creation.

!!! this does not work for all views

Cheers

Nick

|||

Hi Nick,

I would really need to see the full log files (i.e. TransferDump*.sql and SMO Tranfer log) in order to be able to help more. You could send them directly to me at oscar.naim@.microsoft.com.

By the way, are you defining extended properties for this particular database?

Thanks,
Oscar.

|||

Hello oscar

I do not have any extended properties at my database.

I realy can;t find any files named TransferDump*.* in my disk

I found a directory SMO but it is empty

the directory is created under the SQLAgent acount documents and settings...

I monitor the directory as the CDW is executed and a file was created

but when the execution stops with or with out an error the file is deleted

so I can;t postit to you

Thank you

Copying database using "Copy Database"

Note: copying databases using detach/attach is not an option (the servers cannot see eachother's shares)

I must say that in general I hate the new management tools. Enterprise Manager had its flaws too, but the quality of the "SQL Server Management Studio" seems very low IMHO.

Regards,
Philippe Leybaert

Blake05 wrote:
I can get the copy to work but it will not copy any of my stored procedures or functions.