sql server db: February 2012

Saturday, February 25, 2012

Copying a Datbase between two different servers

Hello Everyone,

I am new to SQl Server 2005. I have created a database on one machine and I tried copying it on to another machine where SQL Server 2005 is installed. Both machines have SQL Server 2005 installed. When I tried copying using copy database wizard its asking for the destination server. I have given the name of the destination server but its giving me error msg saying tht it cant connect. How to chk whether my destination server is online? If it is not how to make it online? Any help would be appreciated.

Srividya.

Remote connections are disabled by default for SQL Server. YOu will have to enable them first. For more information see my screencast on my site which covers the "Enable of remote connections for SQL Server 2005".

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de

Copying a database...

I've used the backup/restore method of copying a database. The only problem
is that all the tables aren't getting copied! There are supposed to be 74
tables but only 60 are coming across--I haven't checked other kinds of
objects.
I've done it twice with the same result. I've been very careful to select
full backup when generating the *.bak file. Any ideas? Please?
Mike...
"...after all He's not a tame lion..."
mporter (mporter@.discussions.microsoft.com) writes:
> I've used the backup/restore method of copying a database. The only
> problem is that all the tables aren't getting copied! There are supposed
> to be 74 tables but only 60 are coming across--I haven't checked other
> kinds of objects.
> I've done it twice with the same result. I've been very careful to select
> full backup when generating the *.bak file. Any ideas? Please?
If you did:
BACKUP DATABASE db TO DISK = 'C:\mybackup.bak'
and there already was a a backup in that file, you did not overwrite it,
but you appended.
Do a RESTORE HEADERONLY on the backup file.
In the future, if you don't wish to keep the old backup, add WITH INIT
to the BACKUP command.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Hi,
Donot overwrite the media file or disk file.
from
killer

Copying a database...

I've used the backup/restore method of copying a database. The only problem
is that all the tables aren't getting copied! There are supposed to be 74
tables but only 60 are coming across--I haven't checked other kinds of
objects.
I've done it twice with the same result. I've been very careful to select
full backup when generating the *.bak file. Any ideas? Please?
Mike...
"...after all He's not a tame lion..."mporter (mporter@.discussions.microsoft.com) writes:
> I've used the backup/restore method of copying a database. The only
> problem is that all the tables aren't getting copied! There are supposed
> to be 74 tables but only 60 are coming across--I haven't checked other
> kinds of objects.
> I've done it twice with the same result. I've been very careful to select
> full backup when generating the *.bak file. Any ideas? Please?
If you did:
BACKUP DATABASE db TO DISK = 'C:\mybackup.bak'
and there already was a a backup in that file, you did not overwrite it,
but you appended.
Do a RESTORE HEADERONLY on the backup file.
In the future, if you don't wish to keep the old backup, add WITH INIT
to the BACKUP command.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi,
Donot overwrite the media file or disk file.
from
killer

Copying a database...

I've used the backup/restore method of copying a database. The only problem
is that all the tables aren't getting copied! There are supposed to be 74
tables but only 60 are coming across--I haven't checked other kinds of
objects.
I've done it twice with the same result. I've been very careful to select
full backup when generating the *.bak file. Any ideas? Please?
Mike...
"...after all He's not a tame lion..."mporter (mporter@.discussions.microsoft.com) writes:
> I've used the backup/restore method of copying a database. The only
> problem is that all the tables aren't getting copied! There are supposed
> to be 74 tables but only 60 are coming across--I haven't checked other
> kinds of objects.
> I've done it twice with the same result. I've been very careful to select
> full backup when generating the *.bak file. Any ideas? Please?
If you did:
BACKUP DATABASE db TO DISK = 'C:\mybackup.bak'
and there already was a a backup in that file, you did not overwrite it,
but you appended.
Do a RESTORE HEADERONLY on the backup file.
In the future, if you don't wish to keep the old backup, add WITH INIT
to the BACKUP command.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Hi,
Donot overwrite the media file or disk file.
from
killer

Copying a database to another server

Im running SQL Server 2005 Express with SQL Server Management studio installed on my laptop. After updating my database, how do I copy the entire contents to the main server in the office, which is running windows server 2003 and SQL Server 2000 Developer Edition.
And is it possible to do it all from my laptop?

right click the database an you will see a context menu. on one of the sub menus here you will see generate script. In the wizard, one of the options is to specify sql 200 compatible

After running the script on the office server, right click on the database, select export, follow the steps in the wizard to export the data.

Copying a database to a different SQL Server

Hi,
I have a SQL Server 2000 database. I want to copy the database to a
different SQL server 2000. The destination sever does not at all have that
database. How can I copy the database schema and the all the data stored in
the database.
Any help regarding this will be highly appreciated.
Thank,
Vinita"Vinita Sharma" <sharmavi@.mail.armstrong.edu> wrote in message
news:#paERIR4DHA.2304@.TK2MSFTNGP10.phx.gbl...

quote:

> I have a SQL Server 2000 database. I want to copy the database to a
> different SQL server 2000. The destination sever does not at all have

that

quote:

> database. How can I copy the database schema and the all the data stored

quote:

> the database.

The best way is to use a current BACKUP from your source server and do a
RESTORE to your target server. See BOL for syntax.
You could experiment with the copy database wizard from Enterprise manager
on your target server, right click databases> all tasks > copy database
wizard. I have to caution you I have not used it -- I prefer to do things
the old fashioned way.
Steve|||Watch out if you need to move logins and users, see this page -
http://support.microsoft.com/defaul...;EN-US;Q246133&
Ray Higdon MCSE, MCDBA, CCNA
--
"Vinita Sharma" <sharmavi@.mail.armstrong.edu> wrote in message
news:%23paERIR4DHA.2304@.TK2MSFTNGP10.phx.gbl...

quote:

> Hi,
> I have a SQL Server 2000 database. I want to copy the database to a
> different SQL server 2000. The destination sever does not at all have

that

quote:

> database. How can I copy the database schema and the all the data stored

quote:

> the database.
> Any help regarding this will be highly appreciated.
> Thank,
> Vinita
>

|||I also prefer the old fashioned way. I like the ability to control things.
Make sure you run the sp_help_revlogin to bring over your logins properly.
Lately I been experimenting with DTS and I am fascinated by its power.
Steven S. Warren
MCSA, MCSE, MCDBA, CCA, CIW-SA, CIW-MA, NETWORK+, i-NET+
http:\\www.swtechworks.com
This posting is provided AS IS with no warranties, and confers no rights.
"Vinita Sharma" <sharmavi@.mail.armstrong.edu> wrote in message
news:%23paERIR4DHA.2304@.TK2MSFTNGP10.phx.gbl...

quote:

> Hi,
> I have a SQL Server 2000 database. I want to copy the database to a
> different SQL server 2000. The destination sever does not at all have

that

quote:

> database. How can I copy the database schema and the all the data stored

quote:

> the database.
> Any help regarding this will be highly appreciated.
> Thank,
> Vinita
>

|||Hey Vinita,
In the Enteprise manager, right click on the database and go to "All Tasks".
Then click on Detach Database... and click Ok. The database will disappear
from the list. Now simply go to the drive where the database is stored (In m
y case it's C:\Program File
s\Microsoft SQL Server\MSSQL\Data). Now select the database MDF and LDF File
s and copy them to the destination machine. Then do the same steps, right cl
ick on the database and go to "All Tasks", and now click on Attach Database.
It will make you select th
e database file and point it to the DatabaseName_Data.MDF file that you just
copied. Thats it. It should then appear on the list. If you got any questio
ns email.
Danny Cabrera
dcabrera@.medstarholdings.com
System Programmer|||Thanks a lot ...
It worked.
Vinita
"Danny" <dcabrera@.medstarholdings.com> wrote in message
news:E3310419-761C-4EE9-9696-2D54EB283D1B@.microsoft.com...

quote:

> Hey Vinita,
> In the Enteprise manager, right click on the database and go to "All

Tasks". Then click on Detach Database... and click Ok. The database will
disappear from the list. Now simply go to the drive where the database is
stored (In my case it's C:\Program Files\Microsoft SQL Server\MSSQL\Data).
Now select the database MDF and LDF Files and copy them to the destination
machine. Then do the same steps, right click on the database and go to "All
Tasks", and now click on Attach Database. It will make you select the
database file and point it to the DatabaseName_Data.MDF file that you just
copied. Thats it. It should then appear on the list. If you got any
questions email.

quote:

> Danny Cabrera
> dcabrera@.medstarholdings.com
> System Programmer

copying a database in the same SQLserver instance

Im using SQL server express and Management studio express, and i have a database attached called database1.

Now i would like to have an exact copy of this database, named database2.

I would only need the tables, and not the data in them, is there somehow i can do this?

There is no copy button in managment studio...

Create a new database called Database2, make Script of the database1 and open a query analyser window, change the database in the dropdown combo to datbase 2 (or run Use database2) and run the script

Steps to script database

(a) Right Click on DB

(b) Tasks Generate SQL Script and follow the instruction. You will get script without data.

Madhu

|||

Thank you for that..

I have no idea what it did with those scripts and such, but it made a perfect copy like i wanted..

copying a database diagram

Hi,

I have a database diagram on my sql server 2005, how can i copy that diagram to my pen drive so that i can build the database using that diagram on another computer...well the bottom line is i want to copy the datbase from one computer to another.

thanks

Vishal

If you want to copy the database from one computer to the other, you'd best make a backup of the database and restore it on the other computer.

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

|||

Checkout these two links:

http://www.codeproject.com/dotnet/ScriptDiagram2005.asp

http://www.dotnetforce.com/Content.aspx?t=a&n=208

Good luck.

|||

how do i go about it??

thanks

Visam

|||

thanks let me check it

Copying a database between servers

Is backup and restore the best way to simply copy a database from one
SQL Server 7.0 database with 'select' access to another SQL Server 7.0
database on another machine with 'all' access ? Or is there another
easier way with the SQL Server 7.0 tools ?
Edward Diener wrote:
> Is backup and restore the best way to simply copy a database from one
> SQL Server 7.0 database with 'select' access to another SQL Server 7.0
> database on another machine with 'all' access ? Or is there another
> easier way with the SQL Server 7.0 tools ?
You could try detaching and reattaching the database using sp_detach_db
and sp_attach_db / sp_attach_single_file_db. You would need to stop the
server and copy the data and log files and attach the copy. You wouldn't
need to detach in this case. When you attach the copy, you'll likely get
an error related to the log file since the data file points to a log
file in use by the original database. SQL Server 2000 will create a new
log file and attach. I'm not sure if SQL 7 will do the same, but it
likely will.
David Gugick
Imceda Software
www.imceda.com
|||David Gugick wrote:
> Edward Diener wrote:
>
> You could try detaching and reattaching the database using sp_detach_db
> and sp_attach_db / sp_attach_single_file_db. You would need to stop the
> server and copy the data and log files and attach the copy. You wouldn't
> need to detach in this case. When you attach the copy, you'll likely get
> an error related to the log file since the data file points to a log
> file in use by the original database. SQL Server 2000 will create a new
> log file and attach. I'm not sure if SQL 7 will do the same, but it
> likely will.
Can this detach/attach be done with Enterprise Manager and, if not, how
do I do it ?
I tried to backup and restore but SQL Server 7 would only allow me to
backup on the machine where the server resides in which is the database
I want to backup, and would only allow me to restore from the machine
where is the server to which I wanted to restore the database. Now that
is what I call flexibility ! Why I can not backup and restore to and
from any machine to which I am connected and have directory rights I do
not know.
|||Edward Diener wrote:
> Can this detach/attach be done with Enterprise Manager and, if not,
> how do I do it ?
No. You have to run the commands I mentioned.
- Use the database you want to copy in query analyzer
- Run sp_helpfile and note the locations of all data and log files
- Stop the SQL Server
- Open Explorer and make a _copy_ of all data and log files from
sp_helpfile
- Start SQL Server
- Run either sp_attach_db or sp_attach_single_file_db with the new
database name and data file location. For example, for
sp_attach_single_file_db:
Exec sp_attach_single_file_db 'NewDBName', 'C:\Data\NewDataFile.mdf'
-- You'll likely see an error on the log file and a message indicating
the new log file name
-- you can then delete the copied log file since it won't be used any
longer
David Gugick
Imceda Software
www.imceda.com
|||I'm not sure about SQL7.0, but SQL2000 will without any problems backup and
restore databases from other servers. In 2000 you can backup to an UNC path
or a local drive and the same goes for the restore. Attaching and Detaching
the files as DAvid explains will work, but you have to remember that it's an
offline operation where your source database will be unavailable while you
are copying the files. Also there're more steps to be done than if you just
backup the database and then restore it on the new server.
I'm not an expert in doing this from EM, but maybe others can help you with
that. I'd suggest that you look up the Backup and Restore command in Books
On Line and then do it from Query Analyzer - that will give you more options
and flexibility.
Regards
Steen
Edward Diener wrote:
> David Gugick wrote:
> Can this detach/attach be done with Enterprise Manager and, if not,
> how do I do it ?
> I tried to backup and restore but SQL Server 7 would only allow me to
> backup on the machine where the server resides in which is the
> database I want to backup, and would only allow me to restore from
> the machine where is the server to which I wanted to restore the
> database. Now that is what I call flexibility ! Why I can not backup
> and restore to and from any machine to which I am connected and have
> directory rights I do not know.

copying a database across servers

I'm developing a web application locally using SQL Server 2000. When it's time for me to throw it up on my host, how do I copy the database from my computer to their server? Thanks.use the DTS tools provided with SQL Server (import/export data).

What I usually do is script the database structure (and objects), set that up and check it over, THEN transfer the data, but whether you want to do it that way is up to you

j|||Another way is to simply stop your sql server and copy the database and log files to the other location and then attach the database.

Regards|||There are multiple ways as in using DTS or other tools. BUt if the other server is far apart and you donot want to use the internet bandwidth then I feel the best bet is to backup the database and then transfer/FTP the DB backup to be restored into the destination server. Copying the data and the log files are fine. But not the cleanest of approaches AFAIK.|||All solutions provided here are good, I would personally go with Atrax' Scripting solution, it is probably the best way to do it if you don't need to transfer any data and bandwidth is an issue, but you will need the admin on the remote server to launch your .sql script. Here's what I do: Select all your user tables, right-click and copy. Paste them into the Query Analyzer and boom you've got the script to create your tables. Same with SPROC's and any other objects you have created. You can paste them all into one file.

Add this to the top of your SQL Script if the DB does not exist on the server:

CREATE DATABASE [YourDBName]--Skip these two lines if the DB does exist
Go
USE YourDBName -- Add this regardless

and Save it. The script can now be executed from the QA on any SQL server to create your DB.
Piece-o-cake.

If you did need to transfer any data, you could create a DTS package to export the data to the remote server. But of course you have to have access and it depends on the amount of data you have stored and bandwidth as to how effective a transfer would be.

But again, all of the solutions provided here by the other posters so far are viable. It's a matter of personal preference. All though, if you script it and something blows up locally, you can easily recreate your database without having to copy it back from the remote server. An unlikey scenario, but entirely possible... just a thought.

Good luck.

Copying a database - not on a domain

Hi;
I am trying to copy a database from one computer to another. Neither
computer is on a database.
Everything I have tried has failed. And everything I find under google says
that the databases have to be in the same domain.
How can I do this if neither computer is in a domain?
--
thanks - daveHi Dave,
Thanks for your post.
From your descriptions, I understood you would like to copy database via
two computer that are not in the same domain. If I have misunderstood your
concern, please feel free to point it out.
Here are two Knowledge Base articles describing how to move database
between computers. You could BACKUP the database and then use WITH MOVE
parameter to RESTORE it in the new computer.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/kb/314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/kb/221465
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi;
First off, no domains are involved - both Sql Server installations are
running on machines in a workgroup.
1) The backup/restore approach, if the drive letter changes (it does),
requires changing the system tables and has warnings that this can totally
hose up the Sql Server system.
2) Sp_detach/attach is for moving a DB and I want to copy it.
3) Export/Import seems to work ok - but it does not copy PK:FK constraints,
descriptions or default values across across.
So, is there a safe way to copy everything?
thanks - dave
--
thanks - dave
"Michael Cheng [MSFT]" wrote:
> Hi Dave,
> Thanks for your post.
> From your descriptions, I understood you would like to copy database via
> two computer that are not in the same domain. If I have misunderstood your
> concern, please feel free to point it out.
> Here are two Knowledge Base articles describing how to move database
> between computers. You could BACKUP the database and then use WITH MOVE
> parameter to RESTORE it in the new computer.
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/kb/314546
> INF: Using the WITH MOVE Option with the RESTORE Statement
> http://support.microsoft.com/kb/221465
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||David,
Have you tried exporting the schema, and then using bcp to export the
data? I used to do this all the time on databases that were shipped half way
around the world )and definatley not on the same domain). Basically you
would be doing what dts does under the covers.
Bob
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C93E09-8DB1-4121-AE32-061E2AFBA906@.microsoft.com...
> Hi;
> First off, no domains are involved - both Sql Server installations are
> running on machines in a workgroup.
> 1) The backup/restore approach, if the drive letter changes (it does),
> requires changing the system tables and has warnings that this can totally
> hose up the Sql Server system.
> 2) Sp_detach/attach is for moving a DB and I want to copy it.
> 3) Export/Import seems to work ok - but it does not copy PK:FK
> constraints,
> descriptions or default values across across.
> So, is there a safe way to copy everything?
> thanks - dave
> --
> thanks - dave
>
> "Michael Cheng [MSFT]" wrote:
>> Hi Dave,
>> Thanks for your post.
>> From your descriptions, I understood you would like to copy database via
>> two computer that are not in the same domain. If I have misunderstood
>> your
>> concern, please feel free to point it out.
>> Here are two Knowledge Base articles describing how to move database
>> between computers. You could BACKUP the database and then use WITH MOVE
>> parameter to RESTORE it in the new computer.
>> HOW TO: Move Databases Between Computers That Are Running SQL Server
>> http://support.microsoft.com/kb/314546
>> INF: Using the WITH MOVE Option with the RESTORE Statement
>> http://support.microsoft.com/kb/221465
>> Thank you for your patience and cooperation. If you have any questions or
>> concerns, don't hesitate to let me know. We are always here to be of
>> assistance!
>>
>> Sincerely yours,
>> Michael Cheng
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>|||Hi
See inline:
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C93E09-8DB1-4121-AE32-061E2AFBA906@.microsoft.com...
> Hi;
> First off, no domains are involved - both Sql Server installations are
> running on machines in a workgroup.
> 1) The backup/restore approach, if the drive letter changes (it does),
> requires changing the system tables and has warnings that this can totally
> hose up the Sql Server system.
What do you mean by this?
You can use the MOVE option on the restore command if the files are to be
located in a different drive/directory. This is not that different to
specifying the new locations when you use sp_attach.
> 2) Sp_detach/attach is for moving a DB and I want to copy it.
No. Coping the data file and the re-attaching the original does not move the
original database. Attaching the copied data files onto the second server
would accomplish what you require!
> 3) Export/Import seems to work ok - but it does not copy PK:FK
> constraints,
> descriptions or default values across across.
This is usually considerable longer as well!
> So, is there a safe way to copy everything?
All three are safe well established ways to move/copy a database. Re-read
the link Michael posted.
> thanks - dave
>
John
> --
> thanks - dave
>
> "Michael Cheng [MSFT]" wrote:
>> Hi Dave,
>> Thanks for your post.
>> From your descriptions, I understood you would like to copy database via
>> two computer that are not in the same domain. If I have misunderstood
>> your
>> concern, please feel free to point it out.
>> Here are two Knowledge Base articles describing how to move database
>> between computers. You could BACKUP the database and then use WITH MOVE
>> parameter to RESTORE it in the new computer.
>> HOW TO: Move Databases Between Computers That Are Running SQL Server
>> http://support.microsoft.com/kb/314546
>> INF: Using the WITH MOVE Option with the RESTORE Statement
>> http://support.microsoft.com/kb/221465
>> Thank you for your patience and cooperation. If you have any questions or
>> concerns, don't hesitate to let me know. We are always here to be of
>> assistance!
>>
>> Sincerely yours,
>> Michael Cheng
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>|||Hi Dave,
Thanks for your questions.
All three are safe way moving the database files. If you have any questions
about that KB articles, please feel free let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Copying a database - not on a domain

Hi;
I am trying to copy a database from one computer to another. Neither
computer is on a database.
Everything I have tried has failed. And everything I find under google says
that the databases have to be in the same domain.
How can I do this if neither computer is in a domain?
thanks - daveHi Dave,
Thanks for your post.
From your descriptions, I understood you would like to copy database via
two computer that are not in the same domain. If I have misunderstood your
concern, please feel free to point it out.
Here are two Knowledge Base articles describing how to move database
between computers. You could BACKUP the database and then use WITH MOVE
parameter to RESTORE it in the new computer.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/kb/314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/kb/221465
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi;
First off, no domains are involved - both Sql Server installations are
running on machines in a workgroup.
1) The backup/restore approach, if the drive letter changes (it does),
requires changing the system tables and has warnings that this can totally
hose up the Sql Server system.
2) Sp_detach/attach is for moving a DB and I want to copy it.
3) Export/Import seems to work ok - but it does not copy PK:FK constraints,
descriptions or default values across across.
So, is there a safe way to copy everything?
thanks - dave
thanks - dave
"Michael Cheng [MSFT]" wrote:

> Hi Dave,
> Thanks for your post.
> From your descriptions, I understood you would like to copy database via
> two computer that are not in the same domain. If I have misunderstood your
> concern, please feel free to point it out.
> Here are two Knowledge Base articles describing how to move database
> between computers. You could BACKUP the database and then use WITH MOVE
> parameter to RESTORE it in the new computer.
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/kb/314546
> INF: Using the WITH MOVE Option with the RESTORE Statement
> http://support.microsoft.com/kb/221465
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||David,
Have you tried exporting the schema, and then using bcp to export the
data? I used to do this all the time on databases that were shipped half way
around the world )and definatley not on the same domain). Basically you
would be doing what dts does under the covers.
Bob
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C93E09-8DB1-4121-AE32-061E2AFBA906@.microsoft.com...[vbcol=seagreen]
> Hi;
> First off, no domains are involved - both Sql Server installations are
> running on machines in a workgroup.
> 1) The backup/restore approach, if the drive letter changes (it does),
> requires changing the system tables and has warnings that this can totally
> hose up the Sql Server system.
> 2) Sp_detach/attach is for moving a DB and I want to copy it.
> 3) Export/Import seems to work ok - but it does not copy PK:FK
> constraints,
> descriptions or default values across across.
> So, is there a safe way to copy everything?
> thanks - dave
> --
> thanks - dave
>
> "Michael Cheng [MSFT]" wrote:
>|||Hi
See inline:
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C93E09-8DB1-4121-AE32-061E2AFBA906@.microsoft.com...
> Hi;
> First off, no domains are involved - both Sql Server installations are
> running on machines in a workgroup.
> 1) The backup/restore approach, if the drive letter changes (it does),
> requires changing the system tables and has warnings that this can totally
> hose up the Sql Server system.
What do you mean by this?
You can use the MOVE option on the restore command if the files are to be
located in a different drive/directory. This is not that different to
specifying the new locations when you use sp_attach.

> 2) Sp_detach/attach is for moving a DB and I want to copy it.
No. Coping the data file and the re-attaching the original does not move the
original database. Attaching the copied data files onto the second server
would accomplish what you require!

> 3) Export/Import seems to work ok - but it does not copy PK:FK
> constraints,
> descriptions or default values across across.
This is usually considerable longer as well!
> So, is there a safe way to copy everything?
All three are safe well established ways to move/copy a database. Re-read
the link Michael posted.

> thanks - dave
>
John
[vbcol=seagreen]
> --
> thanks - dave
>
> "Michael Cheng [MSFT]" wrote:
>|||Hi Dave,
Thanks for your questions.
All three are safe way moving the database files. If you have any questions
about that KB articles, please feel free let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Copying a database - not on a domain

Hi;
I am trying to copy a database from one computer to another. Neither
computer is on a database.
Everything I have tried has failed. And everything I find under google says
that the databases have to be in the same domain.
How can I do this if neither computer is in a domain?
thanks - dave
Hi Dave,
Thanks for your post.
From your descriptions, I understood you would like to copy database via
two computer that are not in the same domain. If I have misunderstood your
concern, please feel free to point it out.
Here are two Knowledge Base articles describing how to move database
between computers. You could BACKUP the database and then use WITH MOVE
parameter to RESTORE it in the new computer.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/kb/314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/kb/221465
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi;
First off, no domains are involved - both Sql Server installations are
running on machines in a workgroup.
1) The backup/restore approach, if the drive letter changes (it does),
requires changing the system tables and has warnings that this can totally
hose up the Sql Server system.
2) Sp_detach/attach is for moving a DB and I want to copy it.
3) Export/Import seems to work ok - but it does not copy PK:FK constraints,
descriptions or default values across across.
So, is there a safe way to copy everything?
thanks - dave
thanks - dave
"Michael Cheng [MSFT]" wrote:

> Hi Dave,
> Thanks for your post.
> From your descriptions, I understood you would like to copy database via
> two computer that are not in the same domain. If I have misunderstood your
> concern, please feel free to point it out.
> Here are two Knowledge Base articles describing how to move database
> between computers. You could BACKUP the database and then use WITH MOVE
> parameter to RESTORE it in the new computer.
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/kb/314546
> INF: Using the WITH MOVE Option with the RESTORE Statement
> http://support.microsoft.com/kb/221465
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||David,
Have you tried exporting the schema, and then using bcp to export the
data? I used to do this all the time on databases that were shipped half way
around the world )and definatley not on the same domain). Basically you
would be doing what dts does under the covers.
Bob
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C93E09-8DB1-4121-AE32-061E2AFBA906@.microsoft.com...[vbcol=seagreen]
> Hi;
> First off, no domains are involved - both Sql Server installations are
> running on machines in a workgroup.
> 1) The backup/restore approach, if the drive letter changes (it does),
> requires changing the system tables and has warnings that this can totally
> hose up the Sql Server system.
> 2) Sp_detach/attach is for moving a DB and I want to copy it.
> 3) Export/Import seems to work ok - but it does not copy PK:FK
> constraints,
> descriptions or default values across across.
> So, is there a safe way to copy everything?
> thanks - dave
> --
> thanks - dave
>
> "Michael Cheng [MSFT]" wrote:
|||Hi
See inline:
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:81C93E09-8DB1-4121-AE32-061E2AFBA906@.microsoft.com...
> Hi;
> First off, no domains are involved - both Sql Server installations are
> running on machines in a workgroup.
> 1) The backup/restore approach, if the drive letter changes (it does),
> requires changing the system tables and has warnings that this can totally
> hose up the Sql Server system.
What do you mean by this?
You can use the MOVE option on the restore command if the files are to be
located in a different drive/directory. This is not that different to
specifying the new locations when you use sp_attach.

> 2) Sp_detach/attach is for moving a DB and I want to copy it.
No. Coping the data file and the re-attaching the original does not move the
original database. Attaching the copied data files onto the second server
would accomplish what you require!

> 3) Export/Import seems to work ok - but it does not copy PK:FK
> constraints,
> descriptions or default values across across.
This is usually considerable longer as well!
> So, is there a safe way to copy everything?
All three are safe well established ways to move/copy a database. Re-read
the link Michael posted.

> thanks - dave
>
John
[vbcol=seagreen]
> --
> thanks - dave
>
> "Michael Cheng [MSFT]" wrote:
|||Hi Dave,
Thanks for your questions.
All three are safe way moving the database files. If you have any questions
about that KB articles, please feel free let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Copying a database

Hi all,
I want to copy a database from my SQL Server system and install it on another. Can anyone suggest me how I can copy the same along with log info, login info, permissions, etc. and install it on another system. Thanks!

Vik!Hi,

Basically, you can't. Not in one fell swoop. What you can do, however, is to detach the database from one server, copy it--along with the log file--to the new server, and attach it. But you'll need to recreate the logins, because the internal SIDs--security IDs--on the new server won't match those on the old.

If permissions are assigned to database roles, those will be intact.

Don|||If you have Enterprise Manager, you can just create a sql script, and script that database, and it's users.|||What donkiely says in true. However, I'd say the best techniques are...
1. Backup database on server 1, restore it on server 2, re-wire logins
2. Use SQLs Transformation Wizards from Enterprise Server...ok for small databases but network intensive. Best to sort out the security issue first for this one, that way the transform will do the bulk (ahem) of the work for you.
3. As previous posters says, script all the meta data, then copy the data. Basically the same issues as (2) but at least you've got some nice scripts to reapply if your db gets into trouble.|||KraGiE,

Just keep in mind that scripting the database won't copy the data. You'd then have to use DTS or some other means to copy it.

Don

Copying a database

I want to copy a database, say d:\aa.mdf and d:\aa.ldf, to
the same server but with a different name. Of course, I
can't use the Copy Database Wizard. Will the following
work?
1. Detach the database.
2. Go into Win Explorer and make copies of the files, say
d:\aa1.mdf and d:\aa1.ldf.
3. Re-attach the original database using d:\aa.mdf and
d:\aa.ldf.
4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
with a different database name.
I'm just wondering if the server or system tables will get
confused for some obscure reason. Thanks for the help."GoX" <anonymous@.discussions.microsoft.com> wrote in message
news:c76f01c48a09$d840a330$a401280a@.phx.gbl...
> I want to copy a database, say d:\aa.mdf and d:\aa.ldf, to
> the same server but with a different name. Of course, I
> can't use the Copy Database Wizard. Will the following
> work?
> 1. Detach the database.
> 2. Go into Win Explorer and make copies of the files, say
> d:\aa1.mdf and d:\aa1.ldf.
> 3. Re-attach the original database using d:\aa.mdf and
> d:\aa.ldf.
> 4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
> with a different database name.
> I'm just wondering if the server or system tables will get
> confused for some obscure reason. Thanks for the help.
As long as you use a different database name for step 4, that will work
fine.
Steve|||Should work. If the db isn't too big, I suggest doing a backup and restore. The GUI work fine for just this,
as when you do restore, you type in the desired database name, and the GUI will change physical filenames
automatically.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"GoX" <anonymous@.discussions.microsoft.com> wrote in message news:c76f01c48a09$d840a330$a401280a@.phx.gbl...
> I want to copy a database, say d:\aa.mdf and d:\aa.ldf, to
> the same server but with a different name. Of course, I
> can't use the Copy Database Wizard. Will the following
> work?
> 1. Detach the database.
> 2. Go into Win Explorer and make copies of the files, say
> d:\aa1.mdf and d:\aa1.ldf.
> 3. Re-attach the original database using d:\aa.mdf and
> d:\aa.ldf.
> 4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
> with a different database name.
> I'm just wondering if the server or system tables will get
> confused for some obscure reason. Thanks for the help.|||Sounds like you have a plan. This should work just fine.
DeeJay
>--Original Message--
>I want to copy a database, say d:\aa.mdf and d:\aa.ldf,
to
>the same server but with a different name. Of course, I
>can't use the Copy Database Wizard. Will the following
>work?
>1. Detach the database.
>2. Go into Win Explorer and make copies of the files,
say
>d:\aa1.mdf and d:\aa1.ldf.
>3. Re-attach the original database using d:\aa.mdf and
>d:\aa.ldf.
>4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
>with a different database name.
>I'm just wondering if the server or system tables will
get
>confused for some obscure reason. Thanks for the help.
>.
>|||Should have thought of that. And then I don't have to
take the database off line!
>--Original Message--
>Should work. If the db isn't too big, I suggest doing a
backup and restore. The GUI work fine for just this,
>as when you do restore, you type in the desired database
name, and the GUI will change physical filenames
>automatically.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"GoX" <anonymous@.discussions.microsoft.com> wrote in
message news:c76f01c48a09$d840a330$a401280a@.phx.gbl...
>> I want to copy a database, say d:\aa.mdf and d:\aa.ldf,
to
>> the same server but with a different name. Of course, I
>> can't use the Copy Database Wizard. Will the following
>> work?
>> 1. Detach the database.
>> 2. Go into Win Explorer and make copies of the files,
say
>> d:\aa1.mdf and d:\aa1.ldf.
>> 3. Re-attach the original database using d:\aa.mdf and
>> d:\aa.ldf.
>> 4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
>> with a different database name.
>> I'm just wondering if the server or system tables will
get
>> confused for some obscure reason. Thanks for the help.
>
>.
>|||Correct. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<anonymous@.discussions.microsoft.com> wrote in message news:557b01c48a1b$3f0615d0$a301280a@.phx.gbl...
> Should have thought of that. And then I don't have to
> take the database off line!
> >--Original Message--
> >Should work. If the db isn't too big, I suggest doing a
> backup and restore. The GUI work fine for just this,
> >as when you do restore, you type in the desired database
> name, and the GUI will change physical filenames
> >automatically.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"GoX" <anonymous@.discussions.microsoft.com> wrote in
> message news:c76f01c48a09$d840a330$a401280a@.phx.gbl...
> >> I want to copy a database, say d:\aa.mdf and d:\aa.ldf,
> to
> >> the same server but with a different name. Of course, I
> >> can't use the Copy Database Wizard. Will the following
> >> work?
> >>
> >> 1. Detach the database.
> >> 2. Go into Win Explorer and make copies of the files,
> say
> >> d:\aa1.mdf and d:\aa1.ldf.
> >> 3. Re-attach the original database using d:\aa.mdf and
> >> d:\aa.ldf.
> >> 4. Attach the copied files, d:\aa1.mdf and d:\aa1.ldf,
> >> with a different database name.
> >>
> >> I'm just wondering if the server or system tables will
> get
> >> confused for some obscure reason. Thanks for the help.
> >
> >
> >.
> >

Copying a cube but using a different data source

We have a cube that we've been using for a while. I need to make several more cubes with the exact same schema and roles and everything else...just with different data sources. The other data sources have the same schema as the database the original cube is using. I know that when you copy a cube and paste it, the data source is copied over too. And I've read that you can't change the data source of a cube.

My question is what is the easiest way to create the new cubes? Or do I have to create every new cube from scratch (pain in the butt)?

Thanks in advance for your advice.
After you made a copy of your project, create a new DataSource to point to your new relational DB. Open DSV in Xml Mode, change the tag <DataSourceID>...</DataSourceID> to point to your new data source ID.|||Thanks Ken. We're using AS2000. Is there a DSV equivalent?
|||Oh AS2000 is total different story then. There is no DSV concept in AS2000. You can try changing the connection string in the same data source rather than switching the data source on the cube.|||Yeah, I thought about that but we have another cube using the data source so that option is out. Looks like I will need to create the each new cube from scratch. : (
|||Go to every partition of this cube, edit it, and then change the data source. This should do a job.|||Thanks Sasha!

Copying a column to a SSIS package variable

I need to use a value retrieved in one data flow in the second data flow. What's the best way to do this?

How do I copy the column retrieved to a variable so I can use that variable in the second data flow?

Data Flow implies multiple rows, which doesn't naturally fit with a single variable.

Normally I would be using an Exec SQL Task to populate a variable from a table. You could use a Script Component in the data flow, or perhaps the Recordset Destination if there are several rows.

If reading just one value/line from a file for example, then I'd just use the Script Task.

Copying a column names from one Database to another Database

Hello to everyone,
I am trying to check scripts when copying column names from one Database to
another Database.
If the Trigger is the answer. I don't know how to write the script properly
calling out the Database name.
Please help.
Thanks so much,Use the threepart name:
Select * from Database.Owner.Objectname
HTH, Jens Suessmeyer.|||Jens wrote:
>Use the threepart name:
>Select * from Database.Owner.Objectname
>HTH, Jens Suessmeyer.
Hi Jens,
Thanks for the attention. However I am not sure what does the "owner" means.
In my example, I need to copy the standard cost column from DB01 to standard
cost column of DB02. The table is Item master.
In this the correct syntax:
Update DB02.dbo(just guessing).stdost
Set DB02.dbo.stdcost=db1.dbo.stdcost
where dbo2.item no. = dbo1.item no.
Please let me if the above is right, else would like to request if you can
correct the above, many thanks!|||Close:
Update DB02.dbo.<tableName>.stdost
Set DB02.dbo.<tableName>.stdcost=db1.dbo.<tableName>.stdcost
where DB02.dbo.<tableName>.item_no. = DB01.dbo.DB02.item_no.
Assuming that the owner is dbo (if not just leave it out -->
DB02..<tableName>.stdost) and the database is on the local server
otherwise you need a linked server entry.
HTH, jens Suessmeyer.|||Jens wrote:
>Close:
>Update DB02.dbo.<tableName>.stdost
>Set DB02.dbo.<tableName>.stdcost=db1.dbo.<tableName>.stdcost
>where DB02.dbo.<tableName>.item_no. = DB01.dbo.DB02.item_no.
>Assuming that the owner is dbo (if not just leave it out -->
>DB02..<tableName>.stdost) and the database is on the local server
>otherwise you need a linked server entry.
>HTH, jens Suessmeyer.
Hi Jens
Thanks so much for your support,
May I further ask if I still using triggers? or the below is the whole synta
x
already?
Also I have question on DB01 of the 3rd line, please see below
Update DB02.dbo.<tableName>.stdost
>Set DB02.dbo.<tableName>.stdcost=db1.dbo.<tableName>.stdcost
>where DB02.dbo.<tableName>.item_no. = DB01.dbo."Should this be <tablename> instead
of DB02".item_no.
At any rate, kindly correct me. I hope I have the last questions on this
matter.
What a great world is this, thanks so much Jens God Bless
>
Message posted via http://www.webservertalk.com|||Sure, just a copy&paste error, sorry for that ;-)
But at the end you learned the syntax that fast, that you could even
find the errors :-)
HTH, Jens Suessmeyer.|||Jens wrote:
>Sure, just a copy&paste error, sorry for that ;-)
>But at the end you learned the syntax that fast, that you could even
>find the errors :-)
>HTH, Jens Suessmeyer.
I have you as my good tutor that's why, :-) Thanks so much ^.^
Message posted via http://www.webservertalk.com

Copying a Backup Set Using xp_cmdshell Fails

Folks,
This is a strange one. I have one full backup which is done every night. I n
eed to copy this backup set over to a dev box for refresh every night. So af
ter the backup is done, i have another job which run to copy this backup set
. And the job fails !! the
weird part is that i can run it from query analyser ..works fine... if i run
that job a few times it will start copying(in one of the tries)...I am real
ly lost here .. Please help.
The command which run the copy is run under sa... the command is
exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.* \\servername
\sharename\refreshstage\LSPRD_DB_COPY.DAT';
GO
Xcopy and copy results are the same... AND
I am copying 2 backup sets... ONE works just fine is 5 GB.. every night.. th
e Other one fails is 15 gb.. Disk space in not the issue..
Any help would be great -- ThankYouIt would help if you gave some clue as to what the error messages were.
Andrew J. Kelly SQL MVP
"Girish" <Girish@.discussions.microsoft.com> wrote in message
news:CEE12F0B-DB0E-48B9-B822-B9BCADC2E350@.microsoft.com...
> Folks,
> This is a strange one. I have one full backup which is done every night.
I need to copy this backup set over to a dev box for refresh every night. So
after the backup is done, i have another job which run to copy this backup
set. And the job fails !! the weird part is that i can run it from query
analyser ..works fine... if i run that job a few times it will start
copying(in one of the tries)...I am really lost here .. Please help.
> The command which run the copy is run under sa... the command is
> exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.*
\\servername\sharename\refreshstage\LSPR
D_DB_COPY.DAT';
> GO
> Xcopy and copy results are the same... AND
> I am copying 2 backup sets... ONE works just fine is 5 GB.. every night..
the Other one fails is 15 gb.. Disk space in not the issue..
> Any help would be great -- ThankYou|||Hi,
Slow network can be also a issue. Check with your system admin for details.
Thanks
Hari
MCDBA
"Girish" <Girish@.discussions.microsoft.com> wrote in message
news:CEE12F0B-DB0E-48B9-B822-B9BCADC2E350@.microsoft.com...
> Folks,
> This is a strange one. I have one full backup which is done every night.
I need to copy this backup set over to a dev box for refresh every night. So
after the backup is done, i have another job which run to copy this backup
set. And the job fails !! the weird part is that i can run it from query
analyser ..works fine... if i run that job a few times it will start
copying(in one of the tries)...I am really lost here .. Please help.
> The command which run the copy is run under sa... the command is
> exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.*
\\servername\sharename\refreshstage\LSPR
D_DB_COPY.DAT';
> GO
> Xcopy and copy results are the same... AND
> I am copying 2 backup sets... ONE works just fine is 5 GB.. every night..
the Other one fails is 15 gb.. Disk space in not the issue..
> Any help would be great -- ThankYou|||The Job returns as Successfull. But the Copy is not in the traget location.
Infact it Delete's the last backup (copied) from the target location. But It
does not copy the fresh backup.
"Andrew J. Kelly" wrote:

> It would help if you gave some clue as to what the error messages were.
> --
> Andrew J. Kelly SQL MVP
>
> "Girish" <Girish@.discussions.microsoft.com> wrote in message
> news:CEE12F0B-DB0E-48B9-B822-B9BCADC2E350@.microsoft.com...
> I need to copy this backup set over to a dev box for refresh every night.
So
> after the backup is done, i have another job which run to copy this backup
> set. And the job fails !! the weird part is that i can run it from query
> analyser ..works fine... if i run that job a few times it will start
> copying(in one of the tries)...I am really lost here .. Please help.
> \\servername\sharename\refreshstage\LSPR
D_DB_COPY.DAT';
> the Other one fails is 15 gb.. Disk space in not the issue..
>
>|||See what is in this table after you attempt the copy:
CREATE TABLE #Errors (Results VARCHAR(1000))
INSERT INTO #Errors (Results)
exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.*
\\servername\sharename\refreshstage\LSPR
D_DB_COPY.DAT'
Andrew J. Kelly SQL MVP
"Girish" <Girish@.discussions.microsoft.com> wrote in message
news:8359FF59-4A37-46E8-9621-59258CE6AA62@.microsoft.com...
> The Job returns as Successfull. But the Copy is not in the traget
location. Infact it Delete's the last backup (copied) from the target
location. But It does not copy the fresh backup.[vbcol=seagreen]
> "Andrew J. Kelly" wrote:
>
night.[vbcol=seagreen]
night. So[vbcol=seagreen]
backup[vbcol=seagreen]
night..[vbcol=seagreen]

Copying a Backup Set Using xp_cmdshell Fails

Folks,
This is a strange one. I have one full backup which is done every night. I need to copy this backup set over to a dev box for refresh every night. So after the backup is done, i have another job which run to copy this backup set. And the job fails !! the weird part is that i can run it from query analyser ..works fine... if i run that job a few times it will start copying(in one of the tries)...I am really lost here .. Please help.
The command which run the copy is run under sa... the command is
exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.* \\servername\sharename\refreshstage\LSPRD_DB_COPY.DAT';
GO
Xcopy and copy results are the same... AND
I am copying 2 backup sets... ONE works just fine is 5 GB.. every night.. the Other one fails is 15 gb.. Disk space in not the issue..
Any help would be great -- ThankYouIt would help if you gave some clue as to what the error messages were.
--
Andrew J. Kelly SQL MVP
"Girish" <Girish@.discussions.microsoft.com> wrote in message
news:CEE12F0B-DB0E-48B9-B822-B9BCADC2E350@.microsoft.com...
> Folks,
> This is a strange one. I have one full backup which is done every night.
I need to copy this backup set over to a dev box for refresh every night. So
after the backup is done, i have another job which run to copy this backup
set. And the job fails !! the weird part is that i can run it from query
analyser ..works fine... if i run that job a few times it will start
copying(in one of the tries)...I am really lost here .. Please help.
> The command which run the copy is run under sa... the command is
> exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.*
\\servername\sharename\refreshstage\LSPRD_DB_COPY.DAT';
> GO
> Xcopy and copy results are the same... AND
> I am copying 2 backup sets... ONE works just fine is 5 GB.. every night..
the Other one fails is 15 gb.. Disk space in not the issue..
> Any help would be great -- ThankYou|||Hi,
Slow network can be also a issue. Check with your system admin for details.
Thanks
Hari
MCDBA
"Girish" <Girish@.discussions.microsoft.com> wrote in message
news:CEE12F0B-DB0E-48B9-B822-B9BCADC2E350@.microsoft.com...
> Folks,
> This is a strange one. I have one full backup which is done every night.
I need to copy this backup set over to a dev box for refresh every night. So
after the backup is done, i have another job which run to copy this backup
set. And the job fails !! the weird part is that i can run it from query
analyser ..works fine... if i run that job a few times it will start
copying(in one of the tries)...I am really lost here .. Please help.
> The command which run the copy is run under sa... the command is
> exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.*
\\servername\sharename\refreshstage\LSPRD_DB_COPY.DAT';
> GO
> Xcopy and copy results are the same... AND
> I am copying 2 backup sets... ONE works just fine is 5 GB.. every night..
the Other one fails is 15 gb.. Disk space in not the issue..
> Any help would be great -- ThankYou|||The Job returns as Successfull. But the Copy is not in the traget location. Infact it Delete's the last backup (copied) from the target location. But It does not copy the fresh backup.
"Andrew J. Kelly" wrote:
> It would help if you gave some clue as to what the error messages were.
> --
> Andrew J. Kelly SQL MVP
>
> "Girish" <Girish@.discussions.microsoft.com> wrote in message
> news:CEE12F0B-DB0E-48B9-B822-B9BCADC2E350@.microsoft.com...
> > Folks,
> > This is a strange one. I have one full backup which is done every night.
> I need to copy this backup set over to a dev box for refresh every night. So
> after the backup is done, i have another job which run to copy this backup
> set. And the job fails !! the weird part is that i can run it from query
> analyser ..works fine... if i run that job a few times it will start
> copying(in one of the tries)...I am really lost here .. Please help.
> >
> > The command which run the copy is run under sa... the command is
> >
> > exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.*
> \\servername\sharename\refreshstage\LSPRD_DB_COPY.DAT';
> > GO
> >
> > Xcopy and copy results are the same... AND
> >
> > I am copying 2 backup sets... ONE works just fine is 5 GB.. every night..
> the Other one fails is 15 gb.. Disk space in not the issue..
> >
> > Any help would be great -- ThankYou
>
>|||See what is in this table after you attempt the copy:
CREATE TABLE #Errors (Results VARCHAR(1000))
INSERT INTO #Errors (Results)
exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.*
\\servername\sharename\refreshstage\LSPRD_DB_COPY.DAT'
Andrew J. Kelly SQL MVP
"Girish" <Girish@.discussions.microsoft.com> wrote in message
news:8359FF59-4A37-46E8-9621-59258CE6AA62@.microsoft.com...
> The Job returns as Successfull. But the Copy is not in the traget
location. Infact it Delete's the last backup (copied) from the target
location. But It does not copy the fresh backup.
> "Andrew J. Kelly" wrote:
> > It would help if you gave some clue as to what the error messages were.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Girish" <Girish@.discussions.microsoft.com> wrote in message
> > news:CEE12F0B-DB0E-48B9-B822-B9BCADC2E350@.microsoft.com...
> > > Folks,
> > > This is a strange one. I have one full backup which is done every
night.
> > I need to copy this backup set over to a dev box for refresh every
night. So
> > after the backup is done, i have another job which run to copy this
backup
> > set. And the job fails !! the weird part is that i can run it from query
> > analyser ..works fine... if i run that job a few times it will start
> > copying(in one of the tries)...I am really lost here .. Please help.
> > >
> > > The command which run the copy is run under sa... the command is
> > >
> > > exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.*
> > \\servername\sharename\refreshstage\LSPRD_DB_COPY.DAT';
> > > GO
> > >
> > > Xcopy and copy results are the same... AND
> > >
> > > I am copying 2 backup sets... ONE works just fine is 5 GB.. every
night..
> > the Other one fails is 15 gb.. Disk space in not the issue..
> > >
> > > Any help would be great -- ThankYou
> >
> >
> >

Copying a Backup Set Using xp_cmdshell Fails

Folks,
This is a strange one. I have one full backup which is done every night. I need to copy this backup set over to a dev box for refresh every night. So after the backup is done, i have another job which run to copy this backup set. And the job fails !! the
weird part is that i can run it from query analyser ..works fine... if i run that job a few times it will start copying(in one of the tries)...I am really lost here .. Please help.
The command which run the copy is run under sa... the command is
exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.* \\servername\sharename\refreshstage\LSPRD_DB_COPY. DAT';
GO
Xcopy and copy results are the same... AND
I am copying 2 backup sets... ONE works just fine is 5 GB.. every night.. the Other one fails is 15 gb.. Disk space in not the issue..
Any help would be great -- ThankYou
It would help if you gave some clue as to what the error messages were.
Andrew J. Kelly SQL MVP
"Girish" <Girish@.discussions.microsoft.com> wrote in message
news:CEE12F0B-DB0E-48B9-B822-B9BCADC2E350@.microsoft.com...
> Folks,
> This is a strange one. I have one full backup which is done every night.
I need to copy this backup set over to a dev box for refresh every night. So
after the backup is done, i have another job which run to copy this backup
set. And the job fails !! the weird part is that i can run it from query
analyser ..works fine... if i run that job a few times it will start
copying(in one of the tries)...I am really lost here .. Please help.
> The command which run the copy is run under sa... the command is
> exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.*
\\servername\sharename\refreshstage\LSPRD_DB_COPY. DAT';
> GO
> Xcopy and copy results are the same... AND
> I am copying 2 backup sets... ONE works just fine is 5 GB.. every night..
the Other one fails is 15 gb.. Disk space in not the issue..
> Any help would be great -- ThankYou
|||Hi,
Slow network can be also a issue. Check with your system admin for details.
Thanks
Hari
MCDBA
"Girish" <Girish@.discussions.microsoft.com> wrote in message
news:CEE12F0B-DB0E-48B9-B822-B9BCADC2E350@.microsoft.com...
> Folks,
> This is a strange one. I have one full backup which is done every night.
I need to copy this backup set over to a dev box for refresh every night. So
after the backup is done, i have another job which run to copy this backup
set. And the job fails !! the weird part is that i can run it from query
analyser ..works fine... if i run that job a few times it will start
copying(in one of the tries)...I am really lost here .. Please help.
> The command which run the copy is run under sa... the command is
> exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.*
\\servername\sharename\refreshstage\LSPRD_DB_COPY. DAT';
> GO
> Xcopy and copy results are the same... AND
> I am copying 2 backup sets... ONE works just fine is 5 GB.. every night..
the Other one fails is 15 gb.. Disk space in not the issue..
> Any help would be great -- ThankYou
|||The Job returns as Successfull. But the Copy is not in the traget location. Infact it Delete's the last backup (copied) from the target location. But It does not copy the fresh backup.
"Andrew J. Kelly" wrote:

> It would help if you gave some clue as to what the error messages were.
> --
> Andrew J. Kelly SQL MVP
>
> "Girish" <Girish@.discussions.microsoft.com> wrote in message
> news:CEE12F0B-DB0E-48B9-B822-B9BCADC2E350@.microsoft.com...
> I need to copy this backup set over to a dev box for refresh every night. So
> after the backup is done, i have another job which run to copy this backup
> set. And the job fails !! the weird part is that i can run it from query
> analyser ..works fine... if i run that job a few times it will start
> copying(in one of the tries)...I am really lost here .. Please help.
> \\servername\sharename\refreshstage\LSPRD_DB_COPY. DAT';
> the Other one fails is 15 gb.. Disk space in not the issue..
>
>
|||See what is in this table after you attempt the copy:
CREATE TABLE #Errors (Results VARCHAR(1000))
INSERT INTO #Errors (Results)
exec xp_cmdshell 'xcopy /Y f:\mssql\sqlbkup\dbsbkup\LSPRD_db*.*
\\servername\sharename\refreshstage\LSPRD_DB_COPY. DAT'
Andrew J. Kelly SQL MVP
"Girish" <Girish@.discussions.microsoft.com> wrote in message
news:8359FF59-4A37-46E8-9621-59258CE6AA62@.microsoft.com...
> The Job returns as Successfull. But the Copy is not in the traget
location. Infact it Delete's the last backup (copied) from the target
location. But It does not copy the fresh backup.[vbcol=seagreen]
> "Andrew J. Kelly" wrote:
night.[vbcol=seagreen]
night. So[vbcol=seagreen]
backup[vbcol=seagreen]
night..[vbcol=seagreen]

Copying / Merging a Database

Hello
This seems very simple idea to do, but just dont know how.
I have to databases QLFL and QLFD (our naming convention is 3 letters to
symbolise application/db, and the last to symbol its use L=Live,
D=Development)
I want to import ALL records from live into development, ie copy the data,
but i tried the import wizard, and it fails as the tables all exist in
Development, i know that, i just need the data copying. would prefer a
completely automated way, as this database does have 1190 tables in it.
_________________________
Adam Simmonds
Systems Administrator
1) go through the import wizard, and save as ssis package but to not
execute.
2) script a drop statement for every table, save as file
3) edit the ssis package and add in a tsql execute step before the inserts
and paste in the drop script
4) save and execute
Note it could be just as easy to create a new database, execute the import
package into that, then script and transfer all the other objects in the
original database.
I would also recommend ApexSQL's Diff product, which can sync data as well
as other database objects, and can be automated as you require.
TheSQLGuru
President
Indicium Resources, Inc.
"Adam Simmonds (SimAda00)" <AdamSimmondsSimAda00@.discussions.microsoft.com>
wrote in message news:E6BCBA3C-2876-44C3-A890-BCEA81E27413@.microsoft.com...
> Hello
> This seems very simple idea to do, but just dont know how.
> I have to databases QLFL and QLFD (our naming convention is 3 letters to
> symbolise application/db, and the last to symbol its use L=Live,
> D=Development)
> I want to import ALL records from live into development, ie copy the data,
> but i tried the import wizard, and it fails as the tables all exist in
> Development, i know that, i just need the data copying. would prefer a
> completely automated way, as this database does have 1190 tables in it.
> --
> _________________________
> Adam Simmonds
> Systems Administrator

Copying / Merging a Database

Hello
This seems very simple idea to do, but just dont know how.
I have to databases QLFL and QLFD (our naming convention is 3 letters to
symbolise application/db, and the last to symbol its use L=Live,
D=Development)
I want to import ALL records from live into development, ie copy the data,
but i tried the import wizard, and it fails as the tables all exist in
Development, i know that, i just need the data copying. would prefer a
completely automated way, as this database does have 1190 tables in it.
--
_________________________
Adam Simmonds
Systems Administrator1) go through the import wizard, and save as ssis package but to not
execute.
2) script a drop statement for every table, save as file
3) edit the ssis package and add in a tsql execute step before the inserts
and paste in the drop script
4) save and execute
Note it could be just as easy to create a new database, execute the import
package into that, then script and transfer all the other objects in the
original database.
I would also recommend ApexSQL's Diff product, which can sync data as well
as other database objects, and can be automated as you require.
TheSQLGuru
President
Indicium Resources, Inc.
"Adam Simmonds (SimAda00)" <AdamSimmondsSimAda00@.discussions.microsoft.com>
wrote in message news:E6BCBA3C-2876-44C3-A890-BCEA81E27413@.microsoft.com...
> Hello
> This seems very simple idea to do, but just dont know how.
> I have to databases QLFL and QLFD (our naming convention is 3 letters to
> symbolise application/db, and the last to symbol its use L=Live,
> D=Development)
> I want to import ALL records from live into development, ie copy the data,
> but i tried the import wizard, and it fails as the tables all exist in
> Development, i know that, i just need the data copying. would prefer a
> completely automated way, as this database does have 1190 tables in it.
> --
> _________________________
> Adam Simmonds
> Systems Administrator

Copying / Merging a Database

Hello
This seems very simple idea to do, but just dont know how.
I have to databases QLFL and QLFD (our naming convention is 3 letters to
symbolise application/db, and the last to symbol its use L=Live,
D=Development)
I want to import ALL records from live into development, ie copy the data,
but i tried the import wizard, and it fails as the tables all exist in
Development, i know that, i just need the data copying. would prefer a
completely automated way, as this database does have 1190 tables in it.
--
_________________________
Adam Simmonds
Systems Administrator1) go through the import wizard, and save as ssis package but to not
execute.
2) script a drop statement for every table, save as file
3) edit the ssis package and add in a tsql execute step before the inserts
and paste in the drop script
4) save and execute
Note it could be just as easy to create a new database, execute the import
package into that, then script and transfer all the other objects in the
original database.
I would also recommend ApexSQL's Diff product, which can sync data as well
as other database objects, and can be automated as you require.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Adam Simmonds (SimAda00)" <AdamSimmondsSimAda00@.discussions.microsoft.com>
wrote in message news:E6BCBA3C-2876-44C3-A890-BCEA81E27413@.microsoft.com...
> Hello
> This seems very simple idea to do, but just dont know how.
> I have to databases QLFL and QLFD (our naming convention is 3 letters to
> symbolise application/db, and the last to symbol its use L=Live,
> D=Development)
> I want to import ALL records from live into development, ie copy the data,
> but i tried the import wizard, and it fails as the tables all exist in
> Development, i know that, i just need the data copying. would prefer a
> completely automated way, as this database does have 1190 tables in it.
> --
> _________________________
> Adam Simmonds
> Systems Administrator

Copying .mdf and .ldf files bewteen servers

We have a user at work at enjoys stopping SQL Server 7 and 2000 machines and
copying the .mdf and .ldf files from one server to another. He then simply
restarts the production server and re-attaches the database to the new
server.
I have asked this employee to either backp the DB to disk and copy this to
another server or use the sp_detach command and copy file then re-attach.
What are the ramifications of doing a simple .mdf and .ldf copy?
We have a number of MSDE databases as well. Is this acceptable for these?
Perhaps the reason I am getting the SQL-DMO the name 'dbo' was not found in
the users collection is because of these types of copies.
Thanks
--
...david
http://www.micro-mess.com
http://www.va-mustang.com
If you wish to reply to me personally, please remove
the "underline" from scandal_123@.cox.net. The is done to avoid SPAM!I agree with you that backup/restore is probably a better approach. This
eliminates the need to stop the SQL Server service on the source server.
Although sp_attach_db often works without sp_detach_db, the documentation
clearly states that it should only be used with database files detached with
sp_detach_db.
After attaching or restoring databases from another server, you can run
sp_changedbowner to correct the login mapping for the 'dbo' user. This will
correct the DMO 'dbo' user problem. You may also need to run
sp_change_users_login to correct the login/user mapping for other users.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"DavidM" <scandal_123@.cox.net> wrote in message
news:%23I8gmHdxDHA.1364@.tk2msftngp13.phx.gbl...
> We have a user at work at enjoys stopping SQL Server 7 and 2000 machines
and
> copying the .mdf and .ldf files from one server to another. He then simply
> restarts the production server and re-attaches the database to the new
> server.
> I have asked this employee to either backp the DB to disk and copy this to
> another server or use the sp_detach command and copy file then re-attach.
> What are the ramifications of doing a simple .mdf and .ldf copy?
> We have a number of MSDE databases as well. Is this acceptable for these?
> Perhaps the reason I am getting the SQL-DMO the name 'dbo' was not found
in
> the users collection is because of these types of copies.
> Thanks
>
> --
> ...david
> http://www.micro-mess.com
> http://www.va-mustang.com
> If you wish to reply to me personally, please remove
> the "underline" from scandal_123@.cox.net. The is done to avoid SPAM!
>

Copying .mdf and .ldf files bewteen servers

quote:

> We have a user at work at enjoys stopping SQL Server 7 and 2000 machines

and

quote:

> copying the .mdf and .ldf files from one server to another. He then simply
> restarts the production server and re-attaches the database to the new
> server.
> I have asked this employee to either backp the DB to disk and copy this to
> another server or use the sp_detach command and copy file then re-attach.
> What are the ramifications of doing a simple .mdf and .ldf copy?
> We have a number of MSDE databases as well. Is this acceptable for these?
> Perhaps the reason I am getting the SQL-DMO the name 'dbo' was not found

quote:

> the users collection is because of these types of copies.
> Thanks
>
> --
> ...david
> http://www.micro-mess.com
> http://www.va-mustang.com
> If you wish to reply to me personally, please remove
> the "underline" from scandal_123@.cox.net. The is done to avoid SPAM!
>

Copying .MDF & .LDF files for new server

Hello - I am trying to copy a database file from our existing server into an FTP site so I can download and then upload to a new server. When using the file explorer and copy command I recieve an error that the states

" Cannot copy ***data file: It is being used by another person or program, close any programs that might be using the file and try again"

I have stopped MS SQL, stopped IIS etc - ANY SUGGESTIONS?

VinceDetach the database before trying to copy it.|||

Quote:

Originally Posted by seniorinsight

" Cannot copy ***data file: It is being used by another person or program, close any programs that might be using the file and try again"

I have stopped MS SQL, stopped IIS etc - ANY SUGGESTIONS?

Vince

Use the Backup tool from Enterprise manager. Copy and load the backup on your new server. This is the proper way to transfer databases.

Copying "My Reports" between users.

Is there a way to copy a report saved in a users "My Reports" folder to a different users folder? If so what permissions and roles do I need to be in?Using the default settings no user has enough permissions to do this. You would need each user to give you permission or you need to go in as an admin (local RS box admin) and modify the security permissions to give yourself permission.

copying

how do I copy databases from mssql2000 to mssql2005? they're both on the
samr subnet.
thanks,
Raul Rego
NJPIES
detach database from 2000 server or stop 2000 server, copy to the new server
and attach to 2005.
You could also migrate the data, but it will take longer.
"Raul" wrote:

> how do I copy databases from mssql2000 to mssql2005? they're both on the
> samr subnet.
> thanks,
> Raul Rego
> NJPIES
>
>
|||detaching the database will be difficult as we run a medical hotline which
is all 24hr/day.
How can I migrate the data?
Thanks,
Raul Rego
"FrancoisViljoen" <francoisv at dev dot barloworldoptimus dot com> wrote in
message news:8D882740-10C5-474F-B42F-2CA3DBAAE116@.microsoft.com...[vbcol=seagreen]
> detach database from 2000 server or stop 2000 server, copy to the new
> server
> and attach to 2005.
> You could also migrate the data, but it will take longer.
> "Raul" wrote:
|||Right click on the 2005 database and select "Import"...
"Raul" wrote:

> detaching the database will be difficult as we run a medical hotline which
> is all 24hr/day.
> How can I migrate the data?
> Thanks,
> Raul Rego
> "FrancoisViljoen" <francoisv at dev dot barloworldoptimus dot com> wrote in
> message news:8D882740-10C5-474F-B42F-2CA3DBAAE116@.microsoft.com...
>
>
|||Hello,
DO the below steps:-
1. Backup the SQL 2000 database using (Backup Database)
2. Copy the Backup file to destination server
3. Restore the database using Restore database command.
This will automaticaklly upgrade the SQL 2000 database to SQL 2005.
Thanks
Hari
"Raul" <jjkgr@.hotmail.com> wrote in message
news:u36M5UMMHHA.2236@.TK2MSFTNGP02.phx.gbl...
> detaching the database will be difficult as we run a medical hotline which
> is all 24hr/day.
> How can I migrate the data?
> Thanks,
> Raul Rego
> "FrancoisViljoen" <francoisv at dev dot barloworldoptimus dot com> wrote
> in message news:8D882740-10C5-474F-B42F-2CA3DBAAE116@.microsoft.com...
>
)
|||Raul wrote:
> how do I copy databases from mssql2000 to mssql2005? they're both on the
> samr subnet.
> thanks,
> Raul Rego
> NJPIES
>
Fastest and simplest way is to BACKUP the database on the 2000 server,
then RESTORE it to the 2005 server. Note that this doesn't work in
reverse - you can't restore a 2005 database back to 2000.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||If you have real time data synchronization needs I would look at
replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Raul" <jjkgr@.hotmail.com> wrote in message
news:utSV75LMHHA.4244@.TK2MSFTNGP04.phx.gbl...
> how do I copy databases from mssql2000 to mssql2005? they're both on the
> samr subnet.
> thanks,
> Raul Rego
> NJPIES
>
|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E538F.9040408@.realsqlguy.com...
> Raul wrote:
> Fastest and simplest way is to BACKUP the database on the 2000 server,
> then RESTORE it to the 2005 server. Note that this doesn't work in
> reverse - you can't restore a 2005 database back to 2000.
To add to this, you may have to do something like the following:
Backup 2000 copy
Restore to 2005 box WITH NORECOVERY
Backup 2000 Transaction LOG
Restore to 2005 box WITH NORECOVERY
Depending on how long the full and log backup/restores take you may want to
repeat the cycle of log backup/restores until you're down to a minimal
amount of time.
Then finally kick everyone off of the 2000 box.
One last 2000 log backup
Stop 2000 Server
restore to 2005 box with NORECOVERY
Make sure you haven't missed anything or skipped anything
Restore 2005 box WITH RECOVERY.
You can do the "switch over" in under a minute in some cases, especially if
you script it all.

>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

copying

how do I copy databases from mssql2000 to mssql2005? they're both on the
samr subnet.
thanks,
Raul Rego
NJPIESdetach database from 2000 server or stop 2000 server, copy to the new server
and attach to 2005.
You could also migrate the data, but it will take longer.
"Raul" wrote:

> how do I copy databases from mssql2000 to mssql2005? they're both on the
> samr subnet.
> thanks,
> Raul Rego
> NJPIES
>
>|||detaching the database will be difficult as we run a medical hotline which
is all 24hr/day.
How can I migrate the data?
Thanks,
Raul Rego
"FrancoisViljoen" <francoisv at dev dot barloworldoptimus dot com> wrote in
message news:8D882740-10C5-474F-B42F-2CA3DBAAE116@.microsoft.com...[vbcol=seagreen]
> detach database from 2000 server or stop 2000 server, copy to the new
> server
> and attach to 2005.
> You could also migrate the data, but it will take longer.
> "Raul" wrote:
>|||Right click on the 2005 database and select "Import"...
"Raul" wrote:

> detaching the database will be difficult as we run a medical hotline which
> is all 24hr/day.
> How can I migrate the data?
> Thanks,
> Raul Rego
> "FrancoisViljoen" <francoisv at dev dot barloworldoptimus dot com> wrote i
n
> message news:8D882740-10C5-474F-B42F-2CA3DBAAE116@.microsoft.com...
>
>|||Hello,
DO the below steps:-
1. Backup the SQL 2000 database using (Backup Database)
2. Copy the Backup file to destination server
3. Restore the database using Restore database command.
This will automaticaklly upgrade the SQL 2000 database to SQL 2005.
Thanks
Hari
"Raul" <jjkgr@.hotmail.com> wrote in message
news:u36M5UMMHHA.2236@.TK2MSFTNGP02.phx.gbl...
> detaching the database will be difficult as we run a medical hotline which
> is all 24hr/day.
> How can I migrate the data?
> Thanks,
> Raul Rego
> "FrancoisViljoen" <francoisv at dev dot barloworldoptimus dot com> wrote
> in message news:8D882740-10C5-474F-B42F-2CA3DBAAE116@.microsoft.com...
>
)|||Raul wrote:
> how do I copy databases from mssql2000 to mssql2005? they're both on the
> samr subnet.
> thanks,
> Raul Rego
> NJPIES
>
Fastest and simplest way is to BACKUP the database on the 2000 server,
then RESTORE it to the 2005 server. Note that this doesn't work in
reverse - you can't restore a 2005 database back to 2000.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||If you have real time data synchronization needs I would look at
replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Raul" <jjkgr@.hotmail.com> wrote in message
news:utSV75LMHHA.4244@.TK2MSFTNGP04.phx.gbl...
> how do I copy databases from mssql2000 to mssql2005? they're both on the
> samr subnet.
> thanks,
> Raul Rego
> NJPIES
>|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459E538F.9040408@.realsqlguy.com...
> Raul wrote:
> Fastest and simplest way is to BACKUP the database on the 2000 server,
> then RESTORE it to the 2005 server. Note that this doesn't work in
> reverse - you can't restore a 2005 database back to 2000.
To add to this, you may have to do something like the following:
Backup 2000 copy
Restore to 2005 box WITH NORECOVERY
Backup 2000 Transaction LOG
Restore to 2005 box WITH NORECOVERY
Depending on how long the full and log backup/restores take you may want to
repeat the cycle of log backup/restores until you're down to a minimal
amount of time.
Then finally kick everyone off of the 2000 box.
One last 2000 log backup
Stop 2000 Server
restore to 2005 box with NORECOVERY
Make sure you haven't missed anything or skipped anything
Restore 2005 box WITH RECOVERY.
You can do the "switch over" in under a minute in some cases, especially if
you script it all.

>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Saturday, February 25, 2012

Copying a Datbase between two different servers

Copying a database...

Copying a database...

Copying a database...

Copying a database to another server

Copying a database to a different SQL Server

copying a database in the same SQLserver instance

copying a database diagram

Copying a database between servers

copying a database across servers

Copying a database - not on a domain

Copying a database - not on a domain

Copying a database - not on a domain

Copying a database

Copying a database

Copying a database

Copying a database

Copying a cube but using a different data source

Copying a column to a SSIS package variable

Copying a column names from one Database to another Database

Copying a Backup Set Using xp_cmdshell Fails

Copying a Backup Set Using xp_cmdshell Fails

Copying a Backup Set Using xp_cmdshell Fails

Copying / Merging a Database

Copying / Merging a Database

Copying / Merging a Database

Copying .mdf and .ldf files bewteen servers

Copying .mdf and .ldf files bewteen servers

Copying .MDF & .LDF files for new server

Copying "My Reports" between users.

copying

copying

sql server db

Blog Archive

About Me