I have 180+ databases on a production SQL Server 2000 system that I would
like to copy over to a new SQL Server 2005 system for testing prior to
rolling out a new database server.
Unfortunately, the Copy Database wizard using SQL MO fails. The event log
shows that it could not create a view because of an "invalid object name".
This, I assume, is because the Dependencies are not set so the SQLISPackage
doesn't know which order to create the objects. That is, the databases use
views that did not specify SCHEMABINDING when they were created, so when
using View Dependencies, the view only shows itself, even if the view query
makes use of other views/tables.
Is there a way to stop this check from occuring, so that the view is created
regardless of whether a view dependency has been fulfilled?
Andrew
Have you tried to BACKUP all user databases and then RESTORE on SQL Server
2005?
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:F323D970-8FC8-4CBB-8418-3137CD980046@.microsoft.com...
>I have 180+ databases on a production SQL Server 2000 system that I would
> like to copy over to a new SQL Server 2005 system for testing prior to
> rolling out a new database server.
> Unfortunately, the Copy Database wizard using SQL MO fails. The event log
> shows that it could not create a view because of an "invalid object name".
> This, I assume, is because the Dependencies are not set so the
> SQLISPackage
> doesn't know which order to create the objects. That is, the databases use
> views that did not specify SCHEMABINDING when they were created, so when
> using View Dependencies, the view only shows itself, even if the view
> query
> makes use of other views/tables.
> Is there a way to stop this check from occuring, so that the view is
> created
> regardless of whether a view dependency has been fulfilled?
|||I was hoping to avoid that. Performing a backup and restore of 180+ databases
is going to take some time...
"Uri Dimant" wrote:
> Andrew
> Have you tried to BACKUP all user databases and then RESTORE on SQL Server
> 2005?
>
|||Andrew
Well , I don't think so , especially comparing it with copy wizard ,but try
detach\attach , for more details please refer to the BOL
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:152BB668-35A7-4698-8379-438DB8DA3157@.microsoft.com...
>I was hoping to avoid that. Performing a backup and restore of 180+
>databases
> is going to take some time...
> "Uri Dimant" wrote:
>
|||On Feb 27, 5:06 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> I was hoping to avoid that. Performing a backup and restore of 180+ databases
> is going to take some time...
>
Backup/restore will be MUCH faster than the Copy Database wizard.
It's trivial to write a script to loop through sysdatabases to run a
BACKUP command for each database.
|||Uri - Can't use Detach/Attach since it's a production server in use 24/7. Or
if I do, it means spending several weeks doing it so I can minimise the
impact on our customers.
Tracy - OK. So I write a script to backup every database from the source
server, copy each backup file over the WAN to the destination server and then
perform a restore, changing the file locations for each database MDF. That
would probably take several days.
In either case, it doesn't resolve the issue that the Copy Database Wizard
fails to create views because it's performing a logic/object check during the
copy. Why can't I just turn that off?
|||On Feb 27, 7:36 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> Uri - Can't use Detach/Attach since it's a production server in use 24/7. Or
> if I do, it means spending several weeks doing it so I can minimise the
> impact on our customers.
> Tracy - OK. So I write a script to backup every database from the source
> server, copy each backup file over the WAN to the destination server and then
> perform a restore, changing the file locations for each database MDF. That
> would probably take several days.
> In either case, it doesn't resolve the issue that the Copy Database Wizard
> fails to create views because it's performing a logic/object check during the
> copy. Why can't I just turn that off?
How long do you think it's going to take the copy wizard to complete?
The first thing that gizmo does is script all of the objects in your
source database, run those scripts on the destination database (to
create the objects), and then imports/exports the data from the source
to the destination. That is going to take twice as long as going the
backup/restore route. Trust me, backup/restore is the way to go, you
can AUTOMATE the whole thing, and you have FULL CONTROL over what's
happening.
If you insist on using the wizard, I'm afraid I can't help you out. I
don't use it, it's just too klunky to work with.
Showing posts with label system. Show all posts
Showing posts with label system. Show all posts
Sunday, March 11, 2012
Copy Database From 2000 To 2005 Failing To Create View
I have 180+ databases on a production SQL Server 2000 system that I would
like to copy over to a new SQL Server 2005 system for testing prior to
rolling out a new database server.
Unfortunately, the Copy Database wizard using SQL MO fails. The event log
shows that it could not create a view because of an "invalid object name".
This, I assume, is because the Dependencies are not set so the SQLISPackage
doesn't know which order to create the objects. That is, the databases use
views that did not specify SCHEMABINDING when they were created, so when
using View Dependencies, the view only shows itself, even if the view query
makes use of other views/tables.
Is there a way to stop this check from occuring, so that the view is created
regardless of whether a view dependency has been fulfilled?Andrew
Have you tried to BACKUP all user databases and then RESTORE on SQL Server
2005?
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:F323D970-8FC8-4CBB-8418-3137CD980046@.microsoft.com...
>I have 180+ databases on a production SQL Server 2000 system that I would
> like to copy over to a new SQL Server 2005 system for testing prior to
> rolling out a new database server.
> Unfortunately, the Copy Database wizard using SQL MO fails. The event log
> shows that it could not create a view because of an "invalid object name".
> This, I assume, is because the Dependencies are not set so the
> SQLISPackage
> doesn't know which order to create the objects. That is, the databases use
> views that did not specify SCHEMABINDING when they were created, so when
> using View Dependencies, the view only shows itself, even if the view
> query
> makes use of other views/tables.
> Is there a way to stop this check from occuring, so that the view is
> created
> regardless of whether a view dependency has been fulfilled?|||I was hoping to avoid that. Performing a backup and restore of 180+ database
s
is going to take some time...
"Uri Dimant" wrote:
> Andrew
> Have you tried to BACKUP all user databases and then RESTORE on SQL Serve
r
> 2005?
>|||Andrew
Well , I don't think so , especially comparing it with copy wizard ,but try
detach\attach , for more details please refer to the BOL
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:152BB668-35A7-4698-8379-438DB8DA3157@.microsoft.com...
>I was hoping to avoid that. Performing a backup and restore of 180+
>databases
> is going to take some time...
> "Uri Dimant" wrote:
>
>|||On Feb 27, 5:06 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> I was hoping to avoid that. Performing a backup and restore of 180+ databa
ses
> is going to take some time...
>
Backup/restore will be MUCH faster than the Copy Database wizard.
It's trivial to write a script to loop through sysdatabases to run a
BACKUP command for each database.|||> It's trivial to write a script to loop through sysdatabases to run a
> BACKUP command for each database.
And automating a restore is pretty trivial as well. The code in
http://www.karaszi.com/SQLServer/ut...all_in_file.asp can be a good
starting point.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1172582161.712054.88030@.8g2000cwh.googlegroups.com...
> On Feb 27, 5:06 am, Andrew Hayes
> <AndrewHa...@.discussions.microsoft.com> wrote:
> Backup/restore will be MUCH faster than the Copy Database wizard.
> It's trivial to write a script to loop through sysdatabases to run a
> BACKUP command for each database.
>|||Uri - Can't use Detach/Attach since it's a production server in use 24/7. Or
if I do, it means spending several weeks doing it so I can minimise the
impact on our customers.
Tracy - OK. So I write a script to backup every database from the source
server, copy each backup file over the WAN to the destination server and the
n
perform a restore, changing the file locations for each database MDF. That
would probably take several days.
In either case, it doesn't resolve the issue that the Copy Database Wizard
fails to create views because it's performing a logic/object check during th
e
copy. Why can't I just turn that off?|||On Feb 27, 7:36 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> Uri - Can't use Detach/Attach since it's a production server in use 24/7.
Or
> if I do, it means spending several weeks doing it so I can minimise the
> impact on our customers.
> Tracy - OK. So I write a script to backup every database from the source
> server, copy each backup file over the WAN to the destination server and t
hen
> perform a restore, changing the file locations for each database MDF. That
> would probably take several days.
> In either case, it doesn't resolve the issue that the Copy Database Wizard
> fails to create views because it's performing a logic/object check during
the
> copy. Why can't I just turn that off?
How long do you think it's going to take the copy wizard to complete?
The first thing that gizmo does is script all of the objects in your
source database, run those scripts on the destination database (to
create the objects), and then imports/exports the data from the source
to the destination. That is going to take twice as long as going the
backup/restore route. Trust me, backup/restore is the way to go, you
can AUTOMATE the whole thing, and you have FULL CONTROL over what's
happening.
If you insist on using the wizard, I'm afraid I can't help you out. I
don't use it, it's just too klunky to work with.
like to copy over to a new SQL Server 2005 system for testing prior to
rolling out a new database server.
Unfortunately, the Copy Database wizard using SQL MO fails. The event log
shows that it could not create a view because of an "invalid object name".
This, I assume, is because the Dependencies are not set so the SQLISPackage
doesn't know which order to create the objects. That is, the databases use
views that did not specify SCHEMABINDING when they were created, so when
using View Dependencies, the view only shows itself, even if the view query
makes use of other views/tables.
Is there a way to stop this check from occuring, so that the view is created
regardless of whether a view dependency has been fulfilled?Andrew
Have you tried to BACKUP all user databases and then RESTORE on SQL Server
2005?
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:F323D970-8FC8-4CBB-8418-3137CD980046@.microsoft.com...
>I have 180+ databases on a production SQL Server 2000 system that I would
> like to copy over to a new SQL Server 2005 system for testing prior to
> rolling out a new database server.
> Unfortunately, the Copy Database wizard using SQL MO fails. The event log
> shows that it could not create a view because of an "invalid object name".
> This, I assume, is because the Dependencies are not set so the
> SQLISPackage
> doesn't know which order to create the objects. That is, the databases use
> views that did not specify SCHEMABINDING when they were created, so when
> using View Dependencies, the view only shows itself, even if the view
> query
> makes use of other views/tables.
> Is there a way to stop this check from occuring, so that the view is
> created
> regardless of whether a view dependency has been fulfilled?|||I was hoping to avoid that. Performing a backup and restore of 180+ database
s
is going to take some time...
"Uri Dimant" wrote:
> Andrew
> Have you tried to BACKUP all user databases and then RESTORE on SQL Serve
r
> 2005?
>|||Andrew
Well , I don't think so , especially comparing it with copy wizard ,but try
detach\attach , for more details please refer to the BOL
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:152BB668-35A7-4698-8379-438DB8DA3157@.microsoft.com...
>I was hoping to avoid that. Performing a backup and restore of 180+
>databases
> is going to take some time...
> "Uri Dimant" wrote:
>
>|||On Feb 27, 5:06 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> I was hoping to avoid that. Performing a backup and restore of 180+ databa
ses
> is going to take some time...
>
Backup/restore will be MUCH faster than the Copy Database wizard.
It's trivial to write a script to loop through sysdatabases to run a
BACKUP command for each database.|||> It's trivial to write a script to loop through sysdatabases to run a
> BACKUP command for each database.
And automating a restore is pretty trivial as well. The code in
http://www.karaszi.com/SQLServer/ut...all_in_file.asp can be a good
starting point.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1172582161.712054.88030@.8g2000cwh.googlegroups.com...
> On Feb 27, 5:06 am, Andrew Hayes
> <AndrewHa...@.discussions.microsoft.com> wrote:
> Backup/restore will be MUCH faster than the Copy Database wizard.
> It's trivial to write a script to loop through sysdatabases to run a
> BACKUP command for each database.
>|||Uri - Can't use Detach/Attach since it's a production server in use 24/7. Or
if I do, it means spending several weeks doing it so I can minimise the
impact on our customers.
Tracy - OK. So I write a script to backup every database from the source
server, copy each backup file over the WAN to the destination server and the
n
perform a restore, changing the file locations for each database MDF. That
would probably take several days.
In either case, it doesn't resolve the issue that the Copy Database Wizard
fails to create views because it's performing a logic/object check during th
e
copy. Why can't I just turn that off?|||On Feb 27, 7:36 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> Uri - Can't use Detach/Attach since it's a production server in use 24/7.
Or
> if I do, it means spending several weeks doing it so I can minimise the
> impact on our customers.
> Tracy - OK. So I write a script to backup every database from the source
> server, copy each backup file over the WAN to the destination server and t
hen
> perform a restore, changing the file locations for each database MDF. That
> would probably take several days.
> In either case, it doesn't resolve the issue that the Copy Database Wizard
> fails to create views because it's performing a logic/object check during
the
> copy. Why can't I just turn that off?
How long do you think it's going to take the copy wizard to complete?
The first thing that gizmo does is script all of the objects in your
source database, run those scripts on the destination database (to
create the objects), and then imports/exports the data from the source
to the destination. That is going to take twice as long as going the
backup/restore route. Trust me, backup/restore is the way to go, you
can AUTOMATE the whole thing, and you have FULL CONTROL over what's
happening.
If you insist on using the wizard, I'm afraid I can't help you out. I
don't use it, it's just too klunky to work with.
Copy Database From 2000 To 2005 Failing To Create View
I have 180+ databases on a production SQL Server 2000 system that I would
like to copy over to a new SQL Server 2005 system for testing prior to
rolling out a new database server.
Unfortunately, the Copy Database wizard using SQL MO fails. The event log
shows that it could not create a view because of an "invalid object name".
This, I assume, is because the Dependencies are not set so the SQLISPackage
doesn't know which order to create the objects. That is, the databases use
views that did not specify SCHEMABINDING when they were created, so when
using View Dependencies, the view only shows itself, even if the view query
makes use of other views/tables.
Is there a way to stop this check from occuring, so that the view is created
regardless of whether a view dependency has been fulfilled?Andrew
Have you tried to BACKUP all user databases and then RESTORE on SQL Server
2005?
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:F323D970-8FC8-4CBB-8418-3137CD980046@.microsoft.com...
>I have 180+ databases on a production SQL Server 2000 system that I would
> like to copy over to a new SQL Server 2005 system for testing prior to
> rolling out a new database server.
> Unfortunately, the Copy Database wizard using SQL MO fails. The event log
> shows that it could not create a view because of an "invalid object name".
> This, I assume, is because the Dependencies are not set so the
> SQLISPackage
> doesn't know which order to create the objects. That is, the databases use
> views that did not specify SCHEMABINDING when they were created, so when
> using View Dependencies, the view only shows itself, even if the view
> query
> makes use of other views/tables.
> Is there a way to stop this check from occuring, so that the view is
> created
> regardless of whether a view dependency has been fulfilled?|||I was hoping to avoid that. Performing a backup and restore of 180+ databases
is going to take some time...
"Uri Dimant" wrote:
> Andrew
> Have you tried to BACKUP all user databases and then RESTORE on SQL Server
> 2005?
>|||Andrew
Well , I don't think so , especially comparing it with copy wizard ,but try
detach\attach , for more details please refer to the BOL
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:152BB668-35A7-4698-8379-438DB8DA3157@.microsoft.com...
>I was hoping to avoid that. Performing a backup and restore of 180+
>databases
> is going to take some time...
> "Uri Dimant" wrote:
>> Andrew
>> Have you tried to BACKUP all user databases and then RESTORE on SQL
>> Server
>> 2005?
>|||On Feb 27, 5:06 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> I was hoping to avoid that. Performing a backup and restore of 180+ databases
> is going to take some time...
>
Backup/restore will be MUCH faster than the Copy Database wizard.
It's trivial to write a script to loop through sysdatabases to run a
BACKUP command for each database.|||> It's trivial to write a script to loop through sysdatabases to run a
> BACKUP command for each database.
And automating a restore is pretty trivial as well. The code in
http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp can be a good starting point.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1172582161.712054.88030@.8g2000cwh.googlegroups.com...
> On Feb 27, 5:06 am, Andrew Hayes
> <AndrewHa...@.discussions.microsoft.com> wrote:
>> I was hoping to avoid that. Performing a backup and restore of 180+ databases
>> is going to take some time...
> Backup/restore will be MUCH faster than the Copy Database wizard.
> It's trivial to write a script to loop through sysdatabases to run a
> BACKUP command for each database.
>|||Uri - Can't use Detach/Attach since it's a production server in use 24/7. Or
if I do, it means spending several weeks doing it so I can minimise the
impact on our customers.
Tracy - OK. So I write a script to backup every database from the source
server, copy each backup file over the WAN to the destination server and then
perform a restore, changing the file locations for each database MDF. That
would probably take several days.
In either case, it doesn't resolve the issue that the Copy Database Wizard
fails to create views because it's performing a logic/object check during the
copy. Why can't I just turn that off?|||On Feb 27, 7:36 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> Uri - Can't use Detach/Attach since it's a production server in use 24/7. Or
> if I do, it means spending several weeks doing it so I can minimise the
> impact on our customers.
> Tracy - OK. So I write a script to backup every database from the source
> server, copy each backup file over the WAN to the destination server and then
> perform a restore, changing the file locations for each database MDF. That
> would probably take several days.
> In either case, it doesn't resolve the issue that the Copy Database Wizard
> fails to create views because it's performing a logic/object check during the
> copy. Why can't I just turn that off?
How long do you think it's going to take the copy wizard to complete?
The first thing that gizmo does is script all of the objects in your
source database, run those scripts on the destination database (to
create the objects), and then imports/exports the data from the source
to the destination. That is going to take twice as long as going the
backup/restore route. Trust me, backup/restore is the way to go, you
can AUTOMATE the whole thing, and you have FULL CONTROL over what's
happening.
If you insist on using the wizard, I'm afraid I can't help you out. I
don't use it, it's just too klunky to work with.
like to copy over to a new SQL Server 2005 system for testing prior to
rolling out a new database server.
Unfortunately, the Copy Database wizard using SQL MO fails. The event log
shows that it could not create a view because of an "invalid object name".
This, I assume, is because the Dependencies are not set so the SQLISPackage
doesn't know which order to create the objects. That is, the databases use
views that did not specify SCHEMABINDING when they were created, so when
using View Dependencies, the view only shows itself, even if the view query
makes use of other views/tables.
Is there a way to stop this check from occuring, so that the view is created
regardless of whether a view dependency has been fulfilled?Andrew
Have you tried to BACKUP all user databases and then RESTORE on SQL Server
2005?
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:F323D970-8FC8-4CBB-8418-3137CD980046@.microsoft.com...
>I have 180+ databases on a production SQL Server 2000 system that I would
> like to copy over to a new SQL Server 2005 system for testing prior to
> rolling out a new database server.
> Unfortunately, the Copy Database wizard using SQL MO fails. The event log
> shows that it could not create a view because of an "invalid object name".
> This, I assume, is because the Dependencies are not set so the
> SQLISPackage
> doesn't know which order to create the objects. That is, the databases use
> views that did not specify SCHEMABINDING when they were created, so when
> using View Dependencies, the view only shows itself, even if the view
> query
> makes use of other views/tables.
> Is there a way to stop this check from occuring, so that the view is
> created
> regardless of whether a view dependency has been fulfilled?|||I was hoping to avoid that. Performing a backup and restore of 180+ databases
is going to take some time...
"Uri Dimant" wrote:
> Andrew
> Have you tried to BACKUP all user databases and then RESTORE on SQL Server
> 2005?
>|||Andrew
Well , I don't think so , especially comparing it with copy wizard ,but try
detach\attach , for more details please refer to the BOL
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:152BB668-35A7-4698-8379-438DB8DA3157@.microsoft.com...
>I was hoping to avoid that. Performing a backup and restore of 180+
>databases
> is going to take some time...
> "Uri Dimant" wrote:
>> Andrew
>> Have you tried to BACKUP all user databases and then RESTORE on SQL
>> Server
>> 2005?
>|||On Feb 27, 5:06 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> I was hoping to avoid that. Performing a backup and restore of 180+ databases
> is going to take some time...
>
Backup/restore will be MUCH faster than the Copy Database wizard.
It's trivial to write a script to loop through sysdatabases to run a
BACKUP command for each database.|||> It's trivial to write a script to loop through sysdatabases to run a
> BACKUP command for each database.
And automating a restore is pretty trivial as well. The code in
http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp can be a good starting point.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1172582161.712054.88030@.8g2000cwh.googlegroups.com...
> On Feb 27, 5:06 am, Andrew Hayes
> <AndrewHa...@.discussions.microsoft.com> wrote:
>> I was hoping to avoid that. Performing a backup and restore of 180+ databases
>> is going to take some time...
> Backup/restore will be MUCH faster than the Copy Database wizard.
> It's trivial to write a script to loop through sysdatabases to run a
> BACKUP command for each database.
>|||Uri - Can't use Detach/Attach since it's a production server in use 24/7. Or
if I do, it means spending several weeks doing it so I can minimise the
impact on our customers.
Tracy - OK. So I write a script to backup every database from the source
server, copy each backup file over the WAN to the destination server and then
perform a restore, changing the file locations for each database MDF. That
would probably take several days.
In either case, it doesn't resolve the issue that the Copy Database Wizard
fails to create views because it's performing a logic/object check during the
copy. Why can't I just turn that off?|||On Feb 27, 7:36 am, Andrew Hayes
<AndrewHa...@.discussions.microsoft.com> wrote:
> Uri - Can't use Detach/Attach since it's a production server in use 24/7. Or
> if I do, it means spending several weeks doing it so I can minimise the
> impact on our customers.
> Tracy - OK. So I write a script to backup every database from the source
> server, copy each backup file over the WAN to the destination server and then
> perform a restore, changing the file locations for each database MDF. That
> would probably take several days.
> In either case, it doesn't resolve the issue that the Copy Database Wizard
> fails to create views because it's performing a logic/object check during the
> copy. Why can't I just turn that off?
How long do you think it's going to take the copy wizard to complete?
The first thing that gizmo does is script all of the objects in your
source database, run those scripts on the destination database (to
create the objects), and then imports/exports the data from the source
to the destination. That is going to take twice as long as going the
backup/restore route. Trust me, backup/restore is the way to go, you
can AUTOMATE the whole thing, and you have FULL CONTROL over what's
happening.
If you insist on using the wizard, I'm afraid I can't help you out. I
don't use it, it's just too klunky to work with.
Copy Database fails
Hi,
I am getting the error below when I try to copy a database.
OnError,SQLSERVER2005,AUTORIDADE NT\SYSTEM,XX_XX_XX_XX_SQLSERVER2005_Transfer Objects Task,{E2404B46-E96F-47DA-91F7-ACBB914BD89D},{6D380D9F-9A9E-42F6-AE14-B8852DF6B8E9},16/6/2007 22:24:02,16/6/2007 22:24:02,0,0x,ERROR : errorCode=0 description=CREATE DATABASE failed. Some file names listed could not be created. Check related errors. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}
StackTrace: em Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer()
em Microsoft.SqlServer.Management.Smo.Transfer.TransferData()
em Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
I am using SQL Server 2005 SP2 (9.0.3042) with Windows XP SP2 inside a virtual machine (Virtual PC 2007 version 6.0.156.0).
I am copying the database to a local drive (C:\) and the error occurs after 45 minutes.
Using SQL Server 2000 takes 40 minutes to copy the same database without problem.
Do you have enough disk space on the virtual hard drive and your physical hard drive?|||Yes, there is enough space both on the virtual and physical hard drive.
Thursday, March 8, 2012
Copy Database
Hi all,
In our system, were using quite a primative method of copying one database
to another, but renamed (like a live version & a play version).
At the moment, we do a simple backup to a physcial file, and then restore it
using a different name.
The problem is that at some clients, they have a locked down server and the
SQL server doesn't have access to write any files anywhere, so this process
failes miserably :(
Is there any way (SQL Command) that I can make an identical copy of a
database, with a different name, without going via the file system?
Thanks :DData Transformation Sservices.
AMB
"-Ldwater" wrote:
> Hi all,
> In our system, were using quite a primative method of copying one database
> to another, but renamed (like a live version & a play version).
> At the moment, we do a simple backup to a physcial file, and then restore
it
> using a different name.
> The problem is that at some clients, they have a locked down server and th
e
> SQL server doesn't have access to write any files anywhere, so this proces
s
> failes miserably :(
> Is there any way (SQL Command) that I can make an identical copy of a
> database, with a different name, without going via the file system?
> Thanks :D|||Yes, but I need an SQL command that I can pass so that it doesn't involve th
e
user needing to go into enterprise manager :P
"Alejandro Mesa" wrote:
> Data Transformation Sservices.
>
> AMB
> "-Ldwater" wrote:
>|||Copying the production database to a reporting database is typically a
planned maintenace operation. Allowing the users to fire this off whenever
they please could be a significant performance hit on the system.
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:30F4D3D8-6A99-41BA-B9B2-BF7D7AB88994@.microsoft.com...
> Hi all,
> In our system, were using quite a primative method of copying one database
> to another, but renamed (like a live version & a play version).
> At the moment, we do a simple backup to a physcial file, and then restore
it
> using a different name.
> The problem is that at some clients, they have a locked down server and
the
> SQL server doesn't have access to write any files anywhere, so this
process
> failes miserably :(
> Is there any way (SQL Command) that I can make an identical copy of a
> database, with a different name, without going via the file system?
> Thanks :D|||Well its not really a reporting database as such, just a plain old copy of
the existing one.
The system is in place so that the user has the abiliy to have 2 systems.
The live system and then a play one. This process is simply to copy over th
e
current database situation of the live, and overwrite / create the play
version.
The performance side of things is something that we can live with, if the
users are aware of it.
"JT" wrote:
> Copying the production database to a reporting database is typically a
> planned maintenace operation. Allowing the users to fire this off whenever
> they please could be a significant performance hit on the system.
> "-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
> news:30F4D3D8-6A99-41BA-B9B2-BF7D7AB88994@.microsoft.com...
> it
> the
> process
>
>|||Restoring a full backup is probably the easiest. See these for more
details:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's
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/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:467ECB41-20C5-4249-AB18-6E35BB6B4968@.microsoft.com...
> Well its not really a reporting database as such, just a plain old copy of
> the existing one.
> The system is in place so that the user has the abiliy to have 2 systems.
> The live system and then a play one. This process is simply to copy over
> the
> current database situation of the live, and overwrite / create the play
> version.
> The performance side of things is something that we can live with, if the
> users are aware of it.
> "JT" wrote:
>|||Without using the usual backup/restore or detach/attch methods of copying a
database, the other method would be to create empty tables structures and
query/insert from the production to the copy database. Also scripting over
SPs, views, etc. The easiest way to do this would be to create a DTS package
and save it for re-use. There is DOS based executable called DTSRUN for
something like that which can take a package name as a parameter. This could
be a shortcut on the user's desktop. However, they will need sufficient
permissions to do this.
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:467ECB41-20C5-4249-AB18-6E35BB6B4968@.microsoft.com...
> Well its not really a reporting database as such, just a plain old copy of
> the existing one.
> The system is in place so that the user has the abiliy to have 2 systems.
> The live system and then a play one. This process is simply to copy over
the
> current database situation of the live, and overwrite / create the play
> version.
> The performance side of things is something that we can live with, if the
> users are aware of it.
> "JT" wrote:
>
whenever
database
restore
and|||Let's hope the users don't get the live version and "play" version
.
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:30F4D3D8-6A99-41BA-B9B2-BF7D7AB88994@.microsoft.com...
> Hi all,
> In our system, were using quite a primative method of copying one database
> to another, but renamed (like a live version & a play version).
> At the moment, we do a simple backup to a physcial file, and then restore
it
> using a different name.
> The problem is that at some clients, they have a locked down server and
the
> SQL server doesn't have access to write any files anywhere, so this
process
> failes miserably :(
> Is there any way (SQL Command) that I can make an identical copy of a
> database, with a different name, without going via the file system?
> Thanks :D
In our system, were using quite a primative method of copying one database
to another, but renamed (like a live version & a play version).
At the moment, we do a simple backup to a physcial file, and then restore it
using a different name.
The problem is that at some clients, they have a locked down server and the
SQL server doesn't have access to write any files anywhere, so this process
failes miserably :(
Is there any way (SQL Command) that I can make an identical copy of a
database, with a different name, without going via the file system?
Thanks :DData Transformation Sservices.
AMB
"-Ldwater" wrote:
> Hi all,
> In our system, were using quite a primative method of copying one database
> to another, but renamed (like a live version & a play version).
> At the moment, we do a simple backup to a physcial file, and then restore
it
> using a different name.
> The problem is that at some clients, they have a locked down server and th
e
> SQL server doesn't have access to write any files anywhere, so this proces
s
> failes miserably :(
> Is there any way (SQL Command) that I can make an identical copy of a
> database, with a different name, without going via the file system?
> Thanks :D|||Yes, but I need an SQL command that I can pass so that it doesn't involve th
e
user needing to go into enterprise manager :P
"Alejandro Mesa" wrote:
> Data Transformation Sservices.
>
> AMB
> "-Ldwater" wrote:
>|||Copying the production database to a reporting database is typically a
planned maintenace operation. Allowing the users to fire this off whenever
they please could be a significant performance hit on the system.
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:30F4D3D8-6A99-41BA-B9B2-BF7D7AB88994@.microsoft.com...
> Hi all,
> In our system, were using quite a primative method of copying one database
> to another, but renamed (like a live version & a play version).
> At the moment, we do a simple backup to a physcial file, and then restore
it
> using a different name.
> The problem is that at some clients, they have a locked down server and
the
> SQL server doesn't have access to write any files anywhere, so this
process
> failes miserably :(
> Is there any way (SQL Command) that I can make an identical copy of a
> database, with a different name, without going via the file system?
> Thanks :D|||Well its not really a reporting database as such, just a plain old copy of
the existing one.
The system is in place so that the user has the abiliy to have 2 systems.
The live system and then a play one. This process is simply to copy over th
e
current database situation of the live, and overwrite / create the play
version.
The performance side of things is something that we can live with, if the
users are aware of it.
"JT" wrote:
> Copying the production database to a reporting database is typically a
> planned maintenace operation. Allowing the users to fire this off whenever
> they please could be a significant performance hit on the system.
> "-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
> news:30F4D3D8-6A99-41BA-B9B2-BF7D7AB88994@.microsoft.com...
> it
> the
> process
>
>|||Restoring a full backup is probably the easiest. See these for more
details:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's
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/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:467ECB41-20C5-4249-AB18-6E35BB6B4968@.microsoft.com...
> Well its not really a reporting database as such, just a plain old copy of
> the existing one.
> The system is in place so that the user has the abiliy to have 2 systems.
> The live system and then a play one. This process is simply to copy over
> the
> current database situation of the live, and overwrite / create the play
> version.
> The performance side of things is something that we can live with, if the
> users are aware of it.
> "JT" wrote:
>|||Without using the usual backup/restore or detach/attch methods of copying a
database, the other method would be to create empty tables structures and
query/insert from the production to the copy database. Also scripting over
SPs, views, etc. The easiest way to do this would be to create a DTS package
and save it for re-use. There is DOS based executable called DTSRUN for
something like that which can take a package name as a parameter. This could
be a shortcut on the user's desktop. However, they will need sufficient
permissions to do this.
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:467ECB41-20C5-4249-AB18-6E35BB6B4968@.microsoft.com...
> Well its not really a reporting database as such, just a plain old copy of
> the existing one.
> The system is in place so that the user has the abiliy to have 2 systems.
> The live system and then a play one. This process is simply to copy over
the
> current database situation of the live, and overwrite / create the play
> version.
> The performance side of things is something that we can live with, if the
> users are aware of it.
> "JT" wrote:
>
whenever
database
restore
and|||Let's hope the users don't get the live version and "play" version
."-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:30F4D3D8-6A99-41BA-B9B2-BF7D7AB88994@.microsoft.com...
> Hi all,
> In our system, were using quite a primative method of copying one database
> to another, but renamed (like a live version & a play version).
> At the moment, we do a simple backup to a physcial file, and then restore
it
> using a different name.
> The problem is that at some clients, they have a locked down server and
the
> SQL server doesn't have access to write any files anywhere, so this
process
> failes miserably :(
> Is there any way (SQL Command) that I can make an identical copy of a
> database, with a different name, without going via the file system?
> Thanks :D
Subscribe to:
Posts (Atom)