|
|
You're in the
sql server dts
group:Copy Database Wizard
sql server dts:
Users yes (see Andrew's post) Jobs - these are held in MSDB but you can script the jobs out by server. [quoted text, click to view] "Ryan" <Ryan@discussions.microsoft.com> wrote in message
news:Ryan@discussions.microsoft.com: > Thanks Allan. Does the Backup and Restore take all of the logins, jobs, etc > with it? > > "Allan Mitchell" wrote: > > > Even though you have a few dbs to back up I would still be using BACKUP RESTORE > > > > You can generate the syntax to do this > > > > SELECT 'BACKUP DATABASE ' + name + ' TO DISK = '<location>' > > FROM master.dbo.Sysdatabases > > > > You can then generate a script to do the restores as well. > > > > I dislike the CDW as it has some quirks as you are experiencing > > > > "Ryan" wrote: > > > > > I am in the process of consolidating 17 SQL servers into 3 (yes they > > > extremely under-utilized). I am trying to use the Copy Database Wizard to do > > > this, mostly because there are a couple hundred databases that need to be > > > moved and it would be very tedious to do each one via backup and restore or > > > detach and attach. I am having some issues with moving the logins but I can > > > deal with that. My biggest problem is one of my target servers doesn't seem > > > to want to accept anything from the CDW. When attempting to copy from Server > > > A to Server B, it just locks up with no error message, no status screen, > > > nothing. I can copy from A to C, or even B to C, but B just can't be the > > > target for some reason. Is there a setting or configuration that I'm > > > missing? Any help would be greatly appreciated. Thanks in advance. > > > > > > Ryan
I am in the process of consolidating 17 SQL servers into 3 (yes they
extremely under-utilized). I am trying to use the Copy Database Wizard to do this, mostly because there are a couple hundred databases that need to be moved and it would be very tedious to do each one via backup and restore or detach and attach. I am having some issues with moving the logins but I can deal with that. My biggest problem is one of my target servers doesn't seem to want to accept anything from the CDW. When attempting to copy from Server A to Server B, it just locks up with no error message, no status screen, nothing. I can copy from A to C, or even B to C, but B just can't be the target for some reason. Is there a setting or configuration that I'm missing? Any help would be greatly appreciated. Thanks in advance.
Even though you have a few dbs to back up I would still be using BACKUP RESTORE
You can generate the syntax to do this SELECT 'BACKUP DATABASE ' + name + ' TO DISK = '<location>' FROM master.dbo.Sysdatabases You can then generate a script to do the restores as well. I dislike the CDW as it has some quirks as you are experiencing [quoted text, click to view] "Ryan" wrote:
> I am in the process of consolidating 17 SQL servers into 3 (yes they > extremely under-utilized). I am trying to use the Copy Database Wizard to do > this, mostly because there are a couple hundred databases that need to be > moved and it would be very tedious to do each one via backup and restore or > detach and attach. I am having some issues with moving the logins but I can > deal with that. My biggest problem is one of my target servers doesn't seem > to want to accept anything from the CDW. When attempting to copy from Server > A to Server B, it just locks up with no error message, no status screen, > nothing. I can copy from A to C, or even B to C, but B just can't be the > target for some reason. Is there a setting or configuration that I'm > missing? Any help would be greatly appreciated. Thanks in advance. >
Thanks Allan. Does the Backup and Restore take all of the logins, jobs, etc
with it? [quoted text, click to view] "Allan Mitchell" wrote:
> Even though you have a few dbs to back up I would still be using BACKUP RESTORE > > You can generate the syntax to do this > > SELECT 'BACKUP DATABASE ' + name + ' TO DISK = '<location>' > FROM master.dbo.Sysdatabases > > You can then generate a script to do the restores as well. > > I dislike the CDW as it has some quirks as you are experiencing > > "Ryan" wrote: > > > I am in the process of consolidating 17 SQL servers into 3 (yes they > > extremely under-utilized). I am trying to use the Copy Database Wizard to do > > this, mostly because there are a couple hundred databases that need to be > > moved and it would be very tedious to do each one via backup and restore or > > detach and attach. I am having some issues with moving the logins but I can > > deal with that. My biggest problem is one of my target servers doesn't seem > > to want to accept anything from the CDW. When attempting to copy from Server > > A to Server B, it just locks up with no error message, no status screen, > > nothing. I can copy from A to C, or even B to C, but B just can't be the > > target for some reason. Is there a setting or configuration that I'm > > missing? Any help would be greatly appreciated. Thanks in advance. > >
Thanks Andrew. I've seen you post this response before and have read through
all of the links, but none say anything about it just locking up. I can certainly use the backup and restore or detach and attach methods, but I'd still like to use DTS to move the logins, jobs, etc. I'm thinking it may be a permission issue on my target server but I can't pin point it. It looks like I have the exact same permissions on all of my servers. [quoted text, click to view] "Andrew J. Kelly" wrote:
> Here is an article that list common "Copy DB Wizard" errors. I don't know > if yours is listed or not. > http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues > > Here are some other associated links that you may find useful: > > http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs > http://www.databasejournal.com/features/mssql/article.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/scripts/scriptdetails.asp?scriptid=599 > Restoring a .mdf > http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles > for SQL Server > > > Personally I have never had anything but trouble with the wizard and found > that scripting the backup & restore or attach was much easier, faster and > cleaner. > > -- > Andrew J. Kelly SQL MVP > > > "Ryan" <Ryan@discussions.microsoft.com> wrote in message > news:C1EFCDC6-B26C-4F53-B2C5-6BCC4972E4C5@microsoft.com... > >I am in the process of consolidating 17 SQL servers into 3 (yes they > > extremely under-utilized). I am trying to use the Copy Database Wizard to > > do > > this, mostly because there are a couple hundred databases that need to be > > moved and it would be very tedious to do each one via backup and restore > > or > > detach and attach. I am having some issues with moving the logins but I > > can > > deal with that. My biggest problem is one of my target servers doesn't > > seem > > to want to accept anything from the CDW. When attempting to copy from > > Server > > A to Server B, it just locks up with no error message, no status screen, > > nothing. I can copy from A to C, or even B to C, but B just can't be the > > target for some reason. Is there a setting or configuration that I'm > > missing? Any help would be greatly appreciated. Thanks in advance. > > > > Ryan > >
Here is an article that list common "Copy DB Wizard" errors. I don't know
if yours is listed or not. http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues Here are some other associated links that you may find useful: http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs http://www.databasejournal.com/features/mssql/article.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/scripts/scriptdetails.asp?scriptid=599 Restoring a .mdf http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles for SQL Server Personally I have never had anything but trouble with the wizard and found that scripting the backup & restore or attach was much easier, faster and cleaner. -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Ryan" <Ryan@discussions.microsoft.com> wrote in message news:C1EFCDC6-B26C-4F53-B2C5-6BCC4972E4C5@microsoft.com... >I am in the process of consolidating 17 SQL servers into 3 (yes they > extremely under-utilized). I am trying to use the Copy Database Wizard to > do > this, mostly because there are a couple hundred databases that need to be > moved and it would be very tedious to do each one via backup and restore > or > detach and attach. I am having some issues with moving the logins but I > can > deal with that. My biggest problem is one of my target servers doesn't > seem > to want to accept anything from the CDW. When attempting to copy from > Server > A to Server B, it just locks up with no error message, no status screen, > nothing. I can copy from A to C, or even B to C, but B just can't be the > target for some reason. Is there a setting or configuration that I'm > missing? Any help would be greatly appreciated. Thanks in advance. > > Ryan
Is the account you are running DTS under a domain account and does it have
sa rights to the target server? Beyond that I don't know what to tell you as the CDW has not been reliable enough for me to use so I just plain don't<g>. Have you tried the other method in this KB? http://support.microsoft.com/default.aspx?scid=kb;en-us;246133 -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Ryan" <Ryan@discussions.microsoft.com> wrote in message news:435EDD93-C63C-4829-9495-F956233C4FB5@microsoft.com... > Thanks Andrew. I've seen you post this response before and have read > through > all of the links, but none say anything about it just locking up. I can > certainly use the backup and restore or detach and attach methods, but I'd > still like to use DTS to move the logins, jobs, etc. I'm thinking it may > be > a permission issue on my target server but I can't pin point it. It looks > like I have the exact same permissions on all of my servers. > > "Andrew J. Kelly" wrote: > >> Here is an article that list common "Copy DB Wizard" errors. I don't >> know >> if yours is listed or not. >> http://www.support.microsoft.com/?id=274463 Copy DB Wizard >> issues >> >> Here are some other associated links that you may find useful: >> >> http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs >> http://www.databasejournal.com/features/mssql/article.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/scripts/scriptdetails.asp?scriptid=599 >> Restoring a .mdf >> http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles >> for SQL Server >> >> >> Personally I have never had anything but trouble with the wizard and >> found >> that scripting the backup & restore or attach was much easier, faster and >> cleaner. >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "Ryan" <Ryan@discussions.microsoft.com> wrote in message >> news:C1EFCDC6-B26C-4F53-B2C5-6BCC4972E4C5@microsoft.com... >> >I am in the process of consolidating 17 SQL servers into 3 (yes they >> > extremely under-utilized). I am trying to use the Copy Database Wizard >> > to >> > do >> > this, mostly because there are a couple hundred databases that need to >> > be >> > moved and it would be very tedious to do each one via backup and >> > restore >> > or >> > detach and attach. I am having some issues with moving the logins but >> > I >> > can >> > deal with that. My biggest problem is one of my target servers doesn't >> > seem >> > to want to accept anything from the CDW. When attempting to copy from >> > Server >> > A to Server B, it just locks up with no error message, no status >> > screen, >> > nothing. I can copy from A to C, or even B to C, but B just can't be >> > the >> > target for some reason. Is there a setting or configuration that I'm >> > missing? Any help would be greatly appreciated. Thanks in advance. >> > >> > Ryan >> >> >>
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |