|
|
You're in the
sql server new users
group:copying databases
sql server new users:
we'll be migrating to and so what I'd like to do is get at least one database copied over to the new machine so I can do some testing. The problem is, the two machines have little or no network connectivity currently. I tried doing a backup, burning the backup to dvd and then putting that on the new machine and doing a restore, but I was trying to restore to the original location of the database, which was on a drive that doesn't exist on the new server. So how can I accomplish what I need to?
WIll this work with SQLServer 2000?
[quoted text, click to view] "Hugo Kornelis" wrote:
> On Fri, 27 Jan 2006 13:09:27 -0800, Les Stockton wrote: > > >I have a server with an existing set of databases. We have a new server that > >we'll be migrating to and so what I'd like to do is get at least one database > >copied over to the new machine so I can do some testing. The problem is, the > >two machines have little or no network connectivity currently. > >I tried doing a backup, burning the backup to dvd and then putting that on > >the new machine and doing a restore, but I was trying to restore to the > >original location of the database, which was on a drive that doesn't exist on > >the new server. > >So how can I accomplish what I need to? > > Hi Les, > > Since you already have the backup on DVD, I suggest using restore with > the WITH MOVE option. Check out Books Online for the details. > > An alternative way would have been to detach the databases on the > original servers, copy the database files, then attach the original DB > files to the original server and the copies to the new server. > > -- > Hugo Kornelis, SQL Server MVP
Is there a way to do a search in BOL. I cannot find with "With Move" option
you mentioned. [quoted text, click to view] "Hugo Kornelis" wrote:
> On Fri, 27 Jan 2006 13:09:27 -0800, Les Stockton wrote: > > >I have a server with an existing set of databases. We have a new server that > >we'll be migrating to and so what I'd like to do is get at least one database > >copied over to the new machine so I can do some testing. The problem is, the > >two machines have little or no network connectivity currently. > >I tried doing a backup, burning the backup to dvd and then putting that on > >the new machine and doing a restore, but I was trying to restore to the > >original location of the database, which was on a drive that doesn't exist on > >the new server. > >So how can I accomplish what I need to? > > Hi Les, > > Since you already have the backup on DVD, I suggest using restore with > the WITH MOVE option. Check out Books Online for the details. > > An alternative way would have been to detach the databases on the > original servers, copy the database files, then attach the original DB > files to the original server and the copies to the new server. > > -- > Hugo Kornelis, SQL Server MVP
I was hoping not to interrupt operation on the existing server, but I might
ahve to if I can't do it any other way. As for your suggestion of restoring to new location, I'd like to do that, but find no way to do so. I'm running on SQLServer 2000 (don't ask me why, but that's the way it is and I don't have any say in the matter). Is this option available in 2000? and if so, where because I can't find it. [quoted text, click to view] "Raymond D'Anjou" wrote:
> "Les Stockton" <LesStockton@discussions.microsoft.com> wrote in message > news:5837D702-628C-40F1-8834-D0F3EB6097B2@microsoft.com... > >I have a server with an existing set of databases. We have a new server > >that > > we'll be migrating to and so what I'd like to do is get at least one > > database > > copied over to the new machine so I can do some testing. The problem is, > > the > > two machines have little or no network connectivity currently. > > I tried doing a backup, burning the backup to dvd and then putting that on > > the new machine and doing a restore, but I was trying to restore to the > > original location of the database, which was on a drive that doesn't exist > > on > > the new server. > > So how can I accomplish what I need to? > > You just need to specify the new location for the files when you do the > restore. > Use the same location that SQL server stores the files by default on the new > server. > You have an option "move" when you use Restore Database". > You can also do this in EM. > You could also copy the physical files to the new server and "attach" them. > I believe that you have to "detach" them first on the old server, > effectively shuting it down. > >
I give up. BOL was a waste of time. You have to know how to do it, in order
to find the section that tells how to do it. Never mind. [quoted text, click to view] "Les Stockton" wrote:
> I have a server with an existing set of databases. We have a new server that > we'll be migrating to and so what I'd like to do is get at least one database > copied over to the new machine so I can do some testing. The problem is, the > two machines have little or no network connectivity currently. > I tried doing a backup, burning the backup to dvd and then putting that on > the new machine and doing a restore, but I was trying to restore to the > original location of the database, which was on a drive that doesn't exist on > the new server. > So how can I accomplish what I need to?
Okay. Trying again.
I'm using Enterprise Manager with SQL Server 2000. I pull up a table using the Query option so that I can enter the following: RESTORE DATABASE ABC FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\abc.bak' WITH MOVE 'ABC_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ABC_Data.mdf' No matter what variation I try, it says that it can't parse this command. It's practically the same (except for name changes) as examples in the BOL. Any idea? [quoted text, click to view] "Les Stockton" wrote:
> I have a server with an existing set of databases. We have a new server that > we'll be migrating to and so what I'd like to do is get at least one database > copied over to the new machine so I can do some testing. The problem is, the > two machines have little or no network connectivity currently. > I tried doing a backup, burning the backup to dvd and then putting that on > the new machine and doing a restore, but I was trying to restore to the > original location of the database, which was on a drive that doesn't exist on > the new server. > So how can I accomplish what I need to?
This is the web version? The index? Restore (Described)? Can't find it.
[quoted text, click to view] "Hugo Kornelis" wrote:
> On Fri, 27 Jan 2006 13:31:27 -0800, Les Stockton wrote: > > >WIll this work with SQLServer 2000? > > Hi Les, > > Yes, it will. > > > And on Fri, 27 Jan 2006 13:34:27 -0800, Les Stockton wrote: > > >Is there a way to do a search in BOL. I cannot find with "With Move" option > >you mentioned. > > In the index, go to "RESTORE (described)". Then scroll down until you > get to the description of the MOVE argument. > > Or, go to "Copying database, overview". Some excellent examples there. > Plus a description of how to do this very easily with Enterprisse > Manager. > > -- > Hugo Kornelis, SQL Server MVP
[quoted text, click to view]
"Les Stockton" <LesStockton@discussions.microsoft.com> wrote in message news:5837D702-628C-40F1-8834-D0F3EB6097B2@microsoft.com... >I have a server with an existing set of databases. We have a new server >that > we'll be migrating to and so what I'd like to do is get at least one > database > copied over to the new machine so I can do some testing. The problem is, > the > two machines have little or no network connectivity currently. > I tried doing a backup, burning the backup to dvd and then putting that on > the new machine and doing a restore, but I was trying to restore to the > original location of the database, which was on a drive that doesn't exist > on > the new server. > So how can I accomplish what I need to? You just need to specify the new location for the files when you do the restore. Use the same location that SQL server stores the files by default on the new server. You have an option "move" when you use Restore Database". You can also do this in EM. You could also copy the physical files to the new server and "attach" them. I believe that you have to "detach" them first on the old server, effectively shuting it down.
[quoted text, click to view]
On Fri, 27 Jan 2006 13:09:27 -0800, Les Stockton wrote: >I have a server with an existing set of databases. We have a new server that >we'll be migrating to and so what I'd like to do is get at least one database >copied over to the new machine so I can do some testing. The problem is, the >two machines have little or no network connectivity currently. >I tried doing a backup, burning the backup to dvd and then putting that on >the new machine and doing a restore, but I was trying to restore to the >original location of the database, which was on a drive that doesn't exist on >the new server. >So how can I accomplish what I need to? Hi Les, Since you already have the backup on DVD, I suggest using restore with the WITH MOVE option. Check out Books Online for the details. An alternative way would have been to detach the databases on the original servers, copy the database files, then attach the original DB files to the original server and the copies to the new server. --
[quoted text, click to view]
On Fri, 27 Jan 2006 13:31:27 -0800, Les Stockton wrote: >WIll this work with SQLServer 2000? Hi Les, Yes, it will. [quoted text, click to view] And on Fri, 27 Jan 2006 13:34:27 -0800, Les Stockton wrote: >Is there a way to do a search in BOL. I cannot find with "With Move" option >you mentioned. In the index, go to "RESTORE (described)". Then scroll down until you get to the description of the MOVE argument. Or, go to "Copying database, overview". Some excellent examples there. Plus a description of how to do this very easily with Enterprisse Manager. --
[quoted text, click to view]
On Fri, 27 Jan 2006 15:19:26 -0800, Les Stockton wrote: >Okay. Trying again. >I'm using Enterprise Manager with SQL Server 2000. >I pull up a table using the Query option so that I can enter the following: > >RESTORE DATABASE ABC > FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\abc.bak' > WITH MOVE 'ABC_Data' TO 'C:\Program Files\Microsoft SQL >Server\MSSQL\Data\ABC_Data.mdf' > >No matter what variation I try, it says that it can't parse this command. >It's practically the same (except for name changes) as examples in the BOL. >Any idea? Hi Les, Try it in Query Analyzer. If you then still get errors, copy and paste the error message for us to look at. Or use the complete guided dialog from Enterprise Manager: rightclick the Databases icon, select All tasks / Restore Database; Enter name of DB to restore and location of backup on the first tab, then go to the options tab and enter or change the locations mentioned in the "Restore As" column. --
[quoted text, click to view]
On Fri, 27 Jan 2006 15:23:27 -0800, Les Stockton wrote: >This is the web version? The index? Restore (Described)? Can't find it. Hi Les, I used the version installed on my hard disk. Here's a link to the RESTORE command in the web version: http://msdn.microsoft.com/library/en-us/tsqlref/ts_ra-rz_25rm.asp --
I tried your enterprise manager suggestion, but it refused to accept it.
Haven't tried the first idea yet. [quoted text, click to view] "Hugo Kornelis" wrote:
> On Fri, 27 Jan 2006 15:19:26 -0800, Les Stockton wrote: > > >Okay. Trying again. > >I'm using Enterprise Manager with SQL Server 2000. > >I pull up a table using the Query option so that I can enter the following: > > > >RESTORE DATABASE ABC > > FROM DISK='C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\abc.bak' > > WITH MOVE 'ABC_Data' TO 'C:\Program Files\Microsoft SQL > >Server\MSSQL\Data\ABC_Data.mdf' > > > >No matter what variation I try, it says that it can't parse this command. > >It's practically the same (except for name changes) as examples in the BOL. > >Any idea? > > Hi Les, > > Try it in Query Analyzer. If you then still get errors, copy and paste > the error message for us to look at. > > Or use the complete guided dialog from Enterprise Manager: rightclick > the Databases icon, select All tasks / Restore Database; Enter name of > DB to restore and location of backup on the first tab, then go to the > options tab and enter or change the locations mentioned in the "Restore > As" column. > > -- > Hugo Kornelis, SQL Server MVP
[quoted text, click to view]
On Sat, 28 Jan 2006 06:19:26 -0800, Les Stockton wrote: >I tried your enterprise manager suggestion, but it refused to accept it. Hi Les, Did you get an error message? If so, what was it? If not, then what does "refused to accept" mean? [quoted text, click to view] >Haven't tried the first idea yet. You might have more luck with that, if the EM route doesn't work for you. --
Don't see what you're looking for? Try a search.
|
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 |