Copying SQL 6.5 Database to SQL 2000

What is the easiest way to copy a 6.5 database to a SQL 2000 server? I've
tried to backup and restore with NetVault but it does not seem to work,
thought I'd done this in the past with Backup Exec but might be wrong. Is
there another way I can do this? Export and Import?
thanks
GavSeems that following postings can help you in resolving this issue:
http://www.microsoft.com/technet/co...r />
8BAFD2-1A
81-4558-B1DE-B6EB49F31B7E&dglist=&ptlist=&exp=&sloc=en-us
http://www.microsoft.com/technet/co...r />
96A551&ca
tlist=328BAFD2-1A81-4558-B1DE-B6EB49F31B7E&dglist=&ptlist=&exp=&sloc=en-us
"Gav" wrote:

> What is the easiest way to copy a 6.5 database to a SQL 2000 server? I've
> tried to backup and restore with NetVault but it does not seem to work,
> thought I'd done this in the past with Backup Exec but might be wrong. Is
> there another way I can do this? Export and Import?
> thanks
> Gav
>
>sql

Copying SQL 6.5 Database to SQL 2000

What is the easiest way to copy a 6.5 database to a SQL 2000 server? I've
tried to backup and restore with NetVault but it does not seem to work,
thought I'd done this in the past with Backup Exec but might be wrong. Is
there another way I can do this? Export and Import?
thanks
GavSeems that following postings can help you in resolving this issue:
http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?query=Restoring+SQL+6.5+to+SQL+2000&dg=microsoft.public.sqlserver.server&cat=en-us-technet-sqlserv&lang=en&cr=US&pt=261BA873-F3AB-420E-96D6-E3004596A551&catlist=328BAFD2-1A81-4558-B1DE-B6EB49F31B7E&dglist=&ptlist=&exp=&sloc=en-us
http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?query=Restoring+SQL+Server+6.5+.DAT+file+to+SQL+2000&dg=microsoft.public.sqlserver.server&cat=en-us-technet-sqlserv&lang=en&cr=US&pt=261BA873-F3AB-420E-96D6-E3004596A551&catlist=328BAFD2-1A81-4558-B1DE-B6EB49F31B7E&dglist=&ptlist=&exp=&sloc=en-us
"Gav" wrote:
> What is the easiest way to copy a 6.5 database to a SQL 2000 server? I've
> tried to backup and restore with NetVault but it does not seem to work,
> thought I'd done this in the past with Backup Exec but might be wrong. Is
> there another way I can do this? Export and Import?
> thanks
> Gav
>
>

Copying SQL 6.5 Database to SQL 2000

What is the easiest way to copy a 6.5 database to a SQL 2000 server? I've
tried to backup and restore with NetVault but it does not seem to work,
thought I'd done this in the past with Backup Exec but might be wrong. Is
there another way I can do this? Export and Import?
thanks
Gav
Seems that following postings can help you in resolving this issue:
http://www.microsoft.com/technet/com...st=328BAFD2-1A
81-4558-B1DE-B6EB49F31B7E&dglist=&ptlist=&exp=&sloc=en-us
http://www.microsoft.com/technet/com...3004596A551&ca
tlist=328BAFD2-1A81-4558-B1DE-B6EB49F31B7E&dglist=&ptlist=&exp=&sloc=en-us
"Gav" wrote:

> What is the easiest way to copy a 6.5 database to a SQL 2000 server? I've
> tried to backup and restore with NetVault but it does not seem to work,
> thought I'd done this in the past with Backup Exec but might be wrong. Is
> there another way I can do this? Export and Import?
> thanks
> Gav
>
>

Copying databases to other servers (Backup and Restore - Detach/Attach)

I want to copy a database from one server to another so that they are
identical and I have a few questions. Also I am running the 'Simple
Recovery Model' and therefore my transaction log is minimal.
BACKUP AND RESTORE: If I perform a backup and restore will user access
rights be backed up? What if the user doesn't exist on the new server?
Should I create it manually? Lastly, in the simple recovery model do
I need to backup the transaction log?
MANUALLY COPY THE *.MDF AND *.LDF FILES: What would be the
implications of stopping SQL server and manually copying the data and
log file? I could then attach it to the new server? Would this work?
DATACHING AND REATTACHING: I could detach the database, copy it to the
new server and then attach it to the original and new servers? Would
this work?
EXPORTING DB TO NEW SERVER: I could do an export of the db 500+
tables, but this procedure would take the longest.
All your questions are answered in the following Knowledge Base article:
314546 - HOW TO Move Databases Between Computers That Are Running SQL
Server:
http://support.microsoft.com/default...;en-us;Q314546
Jacco Schalkwijk
SQL Server MVP
<war_wheelan@.yahoo.com> wrote in message
news:1114097392.448483.201600@.f14g2000cwb.googlegr oups.com...
>I want to copy a database from one server to another so that they are
> identical and I have a few questions. Also I am running the 'Simple
> Recovery Model' and therefore my transaction log is minimal.
> BACKUP AND RESTORE: If I perform a backup and restore will user access
> rights be backed up? What if the user doesn't exist on the new server?
> Should I create it manually? Lastly, in the simple recovery model do
> I need to backup the transaction log?
> MANUALLY COPY THE *.MDF AND *.LDF FILES: What would be the
> implications of stopping SQL server and manually copying the data and
> log file? I could then attach it to the new server? Would this work?
> DATACHING AND REATTACHING: I could detach the database, copy it to the
> new server and then attach it to the original and new servers? Would
> this work?
> EXPORTING DB TO NEW SERVER: I could do an export of the db 500+
> tables, but this procedure would take the longest.
>

Copying databases to other servers (Backup and Restore - Detach/Attach)

I want to copy a database from one server to another so that they are
identical and I have a few questions. Also I am running the 'Simple
Recovery Model' and therefore my transaction log is minimal.
BACKUP AND RESTORE: If I perform a backup and restore will user access
rights be backed up? What if the user doesn't exist on the new server?
Should I create it manually? Lastly, in the simple recovery model do
I need to backup the transaction log?
MANUALLY COPY THE *.MDF AND *.LDF FILES: What would be the
implications of stopping SQL server and manually copying the data and
log file? I could then attach it to the new server' Would this work?
DATACHING AND REATTACHING: I could detach the database, copy it to the
new server and then attach it to the original and new servers? Would
this work?
EXPORTING DB TO NEW SERVER: I could do an export of the db 500+
tables, but this procedure would take the longest.All your questions are answered in the following Knowledge Base article:
314546 - HOW TO Move Databases Between Computers That Are Running SQL
Server:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
--
Jacco Schalkwijk
SQL Server MVP
<war_wheelan@.yahoo.com> wrote in message
news:1114097392.448483.201600@.f14g2000cwb.googlegroups.com...
>I want to copy a database from one server to another so that they are
> identical and I have a few questions. Also I am running the 'Simple
> Recovery Model' and therefore my transaction log is minimal.
> BACKUP AND RESTORE: If I perform a backup and restore will user access
> rights be backed up? What if the user doesn't exist on the new server?
> Should I create it manually? Lastly, in the simple recovery model do
> I need to backup the transaction log?
> MANUALLY COPY THE *.MDF AND *.LDF FILES: What would be the
> implications of stopping SQL server and manually copying the data and
> log file? I could then attach it to the new server' Would this work?
> DATACHING AND REATTACHING: I could detach the database, copy it to the
> new server and then attach it to the original and new servers? Would
> this work?
> EXPORTING DB TO NEW SERVER: I could do an export of the db 500+
> tables, but this procedure would take the longest.
>|||Hi,
Using all the below approches you can copy the database to second server.
But the 3rd approch may fail ( MANUALLY COPY THE *.MDF AND *.LDF FILES) if
you have not detached the files. So it is always recomended to detach the
file and copy to destination.
If the first server is production you could use BACK DATABASE, Copy the
Backup file to second server and Restore it (RESTORE DATABASE). All the Login
user chains can be created/established using the the system stored proc
sp_change_users_login
(See books online for usage and various parameters).
If the system is not production then you can detach the database , copy the
MDF and LDF to second server , Attach the database and use system stored proc
sp_change_users_login to syncronize the logins and users.
Third approach (Export and import) may not be a solution if you have miore
tables and data. This is really time consuming.
Thanks
Hari
SQL Server MVP
"war_wheelan@.yahoo.com" wrote:
> I want to copy a database from one server to another so that they are
> identical and I have a few questions. Also I am running the 'Simple
> Recovery Model' and therefore my transaction log is minimal.
> BACKUP AND RESTORE: If I perform a backup and restore will user access
> rights be backed up? What if the user doesn't exist on the new server?
> Should I create it manually? Lastly, in the simple recovery model do
> I need to backup the transaction log?
> MANUALLY COPY THE *.MDF AND *.LDF FILES: What would be the
> implications of stopping SQL server and manually copying the data and
> log file? I could then attach it to the new server' Would this work?
> DATACHING AND REATTACHING: I could detach the database, copy it to the
> new server and then attach it to the original and new servers? Would
> this work?
> EXPORTING DB TO NEW SERVER: I could do an export of the db 500+
> tables, but this procedure would take the longest.
>|||HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
AMB
"war_wheelan@.yahoo.com" wrote:
> I want to copy a database from one server to another so that they are
> identical and I have a few questions. Also I am running the 'Simple
> Recovery Model' and therefore my transaction log is minimal.
> BACKUP AND RESTORE: If I perform a backup and restore will user access
> rights be backed up? What if the user doesn't exist on the new server?
> Should I create it manually? Lastly, in the simple recovery model do
> I need to backup the transaction log?
> MANUALLY COPY THE *.MDF AND *.LDF FILES: What would be the
> implications of stopping SQL server and manually copying the data and
> log file? I could then attach it to the new server' Would this work?
> DATACHING AND REATTACHING: I could detach the database, copy it to the
> new server and then attach it to the original and new servers? Would
> this work?
> EXPORTING DB TO NEW SERVER: I could do an export of the db 500+
> tables, but this procedure would take the longest.
>

Copying databases to other servers (Backup and Restore - Detach/Attach)

I want to copy a database from one server to another so that they are
identical and I have a few questions. Also I am running the 'Simple
Recovery Model' and therefore my transaction log is minimal.
BACKUP AND RESTORE: If I perform a backup and restore will user access
rights be backed up? What if the user doesn't exist on the new server?
Should I create it manually? Lastly, in the simple recovery model do
I need to backup the transaction log?
MANUALLY COPY THE *.MDF AND *.LDF FILES: What would be the
implications of stopping SQL server and manually copying the data and
log file? I could then attach it to the new server' Would this work?
DATACHING AND REATTACHING: I could detach the database, copy it to the
new server and then attach it to the original and new servers? Would
this work?
EXPORTING DB TO NEW SERVER: I could do an export of the db 500+
tables, but this procedure would take the longest.All your questions are answered in the following Knowledge Base article:
314546 - HOW TO Move Databases Between Computers That Are Running SQL
Server:
http://support.microsoft.com/defaul...b;en-us;Q314546
Jacco Schalkwijk
SQL Server MVP
<war_wheelan@.yahoo.com> wrote in message
news:1114097392.448483.201600@.f14g2000cwb.googlegroups.com...
>I want to copy a database from one server to another so that they are
> identical and I have a few questions. Also I am running the 'Simple
> Recovery Model' and therefore my transaction log is minimal.
> BACKUP AND RESTORE: If I perform a backup and restore will user access
> rights be backed up? What if the user doesn't exist on the new server?
> Should I create it manually? Lastly, in the simple recovery model do
> I need to backup the transaction log?
> MANUALLY COPY THE *.MDF AND *.LDF FILES: What would be the
> implications of stopping SQL server and manually copying the data and
> log file? I could then attach it to the new server' Would this work?
> DATACHING AND REATTACHING: I could detach the database, copy it to the
> new server and then attach it to the original and new servers? Would
> this work?
> EXPORTING DB TO NEW SERVER: I could do an export of the db 500+
> tables, but this procedure would take the longest.
>

copying Databases

I backed a MSSQL2000 database into a BAK file. Now when I create a
MSSQL2005 system and I goto RESTORE - after creating a database with the
same name- I get
"ERROR the backup set holds a database other than the existing "Toxinet'
database. RESTORE DATABASE is terminating abnormally"
Any ideas? This process has always worked in MSSQL97 & 2000
Thanks,
Raul Rego
NJPIES
rrego@.njpies.org
Hi Raul
From your limited information, it sounds like the name in the backup is not
what you think it is.
You don't have to create a database before doing a restore. Why don't you
just try restoring and letting the restore process create the database.
Perhaps there is a spelling issue or you have a case sensitive collation.
BTW, there was no MSSQL97. The version before SQL Server 2000 was SQL Server
7.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Raul" <rrego@.njpies.org> wrote in message
news:BCE20BE9-3480-43A4-9B44-C6A0F628B38A@.microsoft.com...
>I backed a MSSQL2000 database into a BAK file. Now when I create a
>MSSQL2005 system and I goto RESTORE - after creating a database with the
>same name- I get
> "ERROR the backup set holds a database other than the existing "Toxinet'
> database. RESTORE DATABASE is terminating abnormally"
> Any ideas? This process has always worked in MSSQL97 & 2000
> Thanks,
> Raul Rego
> NJPIES
> rrego@.njpies.org
>
|||Use the "with replace" clause.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Raul" <rrego@.njpies.org> wrote in message
news:BCE20BE9-3480-43A4-9B44-C6A0F628B38A@.microsoft.com...
>I backed a MSSQL2000 database into a BAK file. Now when I create a
>MSSQL2005 system and I goto RESTORE - after creating a database with the
>same name- I get
> "ERROR the backup set holds a database other than the existing "Toxinet'
> database. RESTORE DATABASE is terminating abnormally"
> Any ideas? This process has always worked in MSSQL97 & 2000
> Thanks,
> Raul Rego
> NJPIES
> rrego@.njpies.org
>

copying Databases

I backed a MSSQL2000 database into a BAK file. Now when I create a
MSSQL2005 system and I goto RESTORE - after creating a database with the
same name- I get
"ERROR the backup set holds a database other than the existing "Toxinet'
database. RESTORE DATABASE is terminating abnormally"
Any ideas? This process has always worked in MSSQL97 & 2000
Thanks,
Raul Rego
NJPIES
rrego@.njpies.orgHi Raul
From your limited information, it sounds like the name in the backup is not
what you think it is.
You don't have to create a database before doing a restore. Why don't you
just try restoring and letting the restore process create the database.
Perhaps there is a spelling issue or you have a case sensitive collation.
BTW, there was no MSSQL97. The version before SQL Server 2000 was SQL Server
7.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Raul" <rrego@.njpies.org> wrote in message
news:BCE20BE9-3480-43A4-9B44-C6A0F628B38A@.microsoft.com...
>I backed a MSSQL2000 database into a BAK file. Now when I create a
>MSSQL2005 system and I goto RESTORE - after creating a database with the
>same name- I get
> "ERROR the backup set holds a database other than the existing "Toxinet'
> database. RESTORE DATABASE is terminating abnormally"
> Any ideas? This process has always worked in MSSQL97 & 2000
> Thanks,
> Raul Rego
> NJPIES
> rrego@.njpies.org
>|||Use the "with replace" clause.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Raul" <rrego@.njpies.org> wrote in message
news:BCE20BE9-3480-43A4-9B44-C6A0F628B38A@.microsoft.com...
>I backed a MSSQL2000 database into a BAK file. Now when I create a
>MSSQL2005 system and I goto RESTORE - after creating a database with the
>same name- I get
> "ERROR the backup set holds a database other than the existing "Toxinet'
> database. RESTORE DATABASE is terminating abnormally"
> Any ideas? This process has always worked in MSSQL97 & 2000
> Thanks,
> Raul Rego
> NJPIES
> rrego@.njpies.org
>

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 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.

COPY_ONLY Restore Problems

I did a COPY_ONLY backup of my production database (SQL Server 2005), moved
it to my development machine (only 225mb). I then tried to restore the
database (to a newly named database) from a device (disk), located the
backup file, checked contents in the Specify Backup window (it all seemed to
be there), and then added the file. When I get back to the Restore Database
window, there is NOTHING in the "Select the backup sets to restore:"
window!! So, I can't go any further, cannot go to the Options tab ("You must
select a restore source") and cannot restore the database.
Does anyone know what is going on?
Thanks for any help.1) Manually craft a RESTORE statement and try that from SSMS directly. See
if it works. Also try RESTORE VERIFYONLY, HEADERONLY or FILELISTONLY to see
what's up.
2) Try to restore a FULL, non-copyonly backup using the GUI to see if it
makes a difference.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Don Miller" <nospam@.nospam.com> wrote in message
news:Oh14Hmh9HHA.1208@.TK2MSFTNGP05.phx.gbl...
>I did a COPY_ONLY backup of my production database (SQL Server 2005), moved
>it to my development machine (only 225mb). I then tried to restore the
>database (to a newly named database) from a device (disk), located the
>backup file, checked contents in the Specify Backup window (it all seemed
>to be there), and then added the file. When I get back to the Restore
>Database window, there is NOTHING in the "Select the backup sets to
>restore:" window!! So, I can't go any further, cannot go to the Options tab
>("You must select a restore source") and cannot restore the database.
> Does anyone know what is going on?
> Thanks for any help.
>|||Hmmm. I knew SSMS GUI could not perform COPY_ONLY backups (it's in the docs)
and now I know that it cannot do COPY_ONLY restores (UNdocumented).
I ran a RESTORE script on the COPY_ONLY backup and it worked fine (the GUI
also worked for a non-COPY_ONLY backup).
Thanks for pointing me in the right direction (away from the GUI ;)
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:ufl2b$h9HHA.5980@.TK2MSFTNGP04.phx.gbl...
> 1) Manually craft a RESTORE statement and try that from SSMS directly.
> See if it works. Also try RESTORE VERIFYONLY, HEADERONLY or FILELISTONLY
> to see what's up.
> 2) Try to restore a FULL, non-copyonly backup using the GUI to see if it
> makes a difference.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:Oh14Hmh9HHA.1208@.TK2MSFTNGP05.phx.gbl...
>>I did a COPY_ONLY backup of my production database (SQL Server 2005),
>>moved it to my development machine (only 225mb). I then tried to restore
>>the database (to a newly named database) from a device (disk), located the
>>backup file, checked contents in the Specify Backup window (it all seemed
>>to be there), and then added the file. When I get back to the Restore
>>Database window, there is NOTHING in the "Select the backup sets to
>>restore:" window!! So, I can't go any further, cannot go to the Options
>>tab ("You must select a restore source") and cannot restore the database.
>> Does anyone know what is going on?
>> Thanks for any help.
>|||> Hmmm. I knew SSMS GUI could not perform COPY_ONLY backups (it's in the docs) and now I know that
> it cannot do COPY_ONLY restores (UNdocumented).
The RESTORE command doesn't differentiate between COPY_ONLY backups and regular backups. In fact,
there's nothing in the backup which indicates it is taken using COPY_ONLY. COPY_ONLY only affects
the database of which you do the backup (not resetting the BCM page if db backup or not emptying the
log if log backup). So my guess is that you didn't specify the correct options in the GUI (like MOVE
and/or REPLACE).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don Miller" <nospam@.nospam.com> wrote in message news:euirpti9HHA.5316@.TK2MSFTNGP04.phx.gbl...
> Hmmm. I knew SSMS GUI could not perform COPY_ONLY backups (it's in the docs) and now I know that
> it cannot do COPY_ONLY restores (UNdocumented).
> I ran a RESTORE script on the COPY_ONLY backup and it worked fine (the GUI also worked for a
> non-COPY_ONLY backup).
> Thanks for pointing me in the right direction (away from the GUI ;)
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:ufl2b$h9HHA.5980@.TK2MSFTNGP04.phx.gbl...
>> 1) Manually craft a RESTORE statement and try that from SSMS directly. See if it works. Also try
>> RESTORE VERIFYONLY, HEADERONLY or FILELISTONLY to see what's up.
>> 2) Try to restore a FULL, non-copyonly backup using the GUI to see if it makes a difference.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "Don Miller" <nospam@.nospam.com> wrote in message news:Oh14Hmh9HHA.1208@.TK2MSFTNGP05.phx.gbl...
>>I did a COPY_ONLY backup of my production database (SQL Server 2005), moved it to my development
>>machine (only 225mb). I then tried to restore the database (to a newly named database) from a
>>device (disk), located the backup file, checked contents in the Specify Backup window (it all
>>seemed to be there), and then added the file. When I get back to the Restore Database window,
>>there is NOTHING in the "Select the backup sets to restore:" window!! So, I can't go any further,
>>cannot go to the Options tab ("You must select a restore source") and cannot restore the
>>database.
>> Does anyone know what is going on?
>> Thanks for any help.
>>
>|||> So my guess is that you didn't specify the correct options in the GUI
> (like MOVE and/or REPLACE).
I didn't have a chance to specify options because every time I went to the
Option tab I got "Select the backup sets to restore:" because there was
nothing to select once I added the device(file).
Here is my backup SQL. Would the INIT have anything to do with this problem?
BACKUP DATABASE MyDatabase
TO DISK = N'E:\Backup\COPY_ONLY_MyDatabase_DAILY.BAK'
WITH INIT, COPY_ONLY
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:4CA88493-634A-4BF7-8BBA-0CE0B9F83A8F@.microsoft.com...
>> Hmmm. I knew SSMS GUI could not perform COPY_ONLY backups (it's in the
>> docs) and now I know that it cannot do COPY_ONLY restores (UNdocumented).
> The RESTORE command doesn't differentiate between COPY_ONLY backups and
> regular backups. In fact, there's nothing in the backup which indicates it
> is taken using COPY_ONLY. COPY_ONLY only affects the database of which you
> do the backup (not resetting the BCM page if db backup or not emptying the
> log if log backup). So my guess is that you didn't specify the correct
> options in the GUI (like MOVE and/or REPLACE).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:euirpti9HHA.5316@.TK2MSFTNGP04.phx.gbl...
>> Hmmm. I knew SSMS GUI could not perform COPY_ONLY backups (it's in the
>> docs) and now I know that it cannot do COPY_ONLY restores (UNdocumented).
>> I ran a RESTORE script on the COPY_ONLY backup and it worked fine (the
>> GUI also worked for a non-COPY_ONLY backup).
>> Thanks for pointing me in the right direction (away from the GUI ;)
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:ufl2b$h9HHA.5980@.TK2MSFTNGP04.phx.gbl...
>> 1) Manually craft a RESTORE statement and try that from SSMS directly.
>> See if it works. Also try RESTORE VERIFYONLY, HEADERONLY or
>> FILELISTONLY to see what's up.
>> 2) Try to restore a FULL, non-copyonly backup using the GUI to see if it
>> makes a difference.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "Don Miller" <nospam@.nospam.com> wrote in message
>> news:Oh14Hmh9HHA.1208@.TK2MSFTNGP05.phx.gbl...
>>I did a COPY_ONLY backup of my production database (SQL Server 2005),
>>moved it to my development machine (only 225mb). I then tried to restore
>>the database (to a newly named database) from a device (disk), located
>>the backup file, checked contents in the Specify Backup window (it all
>>seemed to be there), and then added the file. When I get back to the
>>Restore Database window, there is NOTHING in the "Select the backup sets
>>to restore:" window!! So, I can't go any further, cannot go to the
>>Options tab ("You must select a restore source") and cannot restore the
>>database.
>> Does anyone know what is going on?
>> Thanks for any help.
>>
>>
>|||NOINIT is not the problem. Let me try it and see if I get the same behaviour in the GUI as you
describe:
BACKUP DATABASE pubs
TO DISK = N'C:\pubs.bak'
WITH INIT, COPY_ONLY
Right-click Databases folder, Restore Database, Type in "pubs" for database name, select "from
device", press "..."
Backup media: File
File name: C:\pubs.bak, OK
OK
... and indeed, there is nothing listed!
OK, lets do the same except I don't specify COPY_ONLY... And now the backup is listed! So, my
apologies. I was incorrect. I'm surprised that the backup somehow indicated it was done using
COPY_ONLY. Let me try something else:
BACKUP DATABASE pubs
TO DISK = N'C:\pubs.bak'
WITH INIT
BACKUP DATABASE pubs
TO DISK = N'C:\pubs.bak'
WITH NOINIT, COPY_ONLY
RESTORE HEADERONLY FROM DISK = N'C:\pubs.bak'
Yes, RESTORE HEADERONLY does indicate whether the backup was done using COPY_ONLY. I see a
difference in the "flags" column as well as the "IsCopyOnly" column. And the restore dialog only
show the first backup. Let me now try the other way around:
BACKUP DATABASE pubs
TO DISK = N'C:\pubs.bak'
WITH INIT, COPY_ONLY
BACKUP DATABASE pubs
TO DISK = N'C:\pubs.bak'
WITH NOINIT
Now the restore dialog only show the second backup in the backup file (position 2). I get the same
result if I type in some other database name to restore into (a non-existing database).
So, the restore dialog does indeed refuse to list backups done using COPY_ONLY. So here's another
reason to type the RESTORE command instead of relying on how the GUI developer believe the restore
should be done... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don Miller" <nospam@.nospam.com> wrote in message news:OgO3CSy9HHA.5160@.TK2MSFTNGP05.phx.gbl...
>> So my guess is that you didn't specify the correct options in the GUI (like MOVE and/or REPLACE).
> I didn't have a chance to specify options because every time I went to the Option tab I got
> "Select the backup sets to restore:" because there was nothing to select once I added the
> device(file).
> Here is my backup SQL. Would the INIT have anything to do with this problem?
> BACKUP DATABASE MyDatabase
> TO DISK = N'E:\Backup\COPY_ONLY_MyDatabase_DAILY.BAK'
> WITH INIT, COPY_ONLY
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:4CA88493-634A-4BF7-8BBA-0CE0B9F83A8F@.microsoft.com...
>> Hmmm. I knew SSMS GUI could not perform COPY_ONLY backups (it's in the docs) and now I know that
>> it cannot do COPY_ONLY restores (UNdocumented).
>> The RESTORE command doesn't differentiate between COPY_ONLY backups and regular backups. In fact,
>> there's nothing in the backup which indicates it is taken using COPY_ONLY. COPY_ONLY only affects
>> the database of which you do the backup (not resetting the BCM page if db backup or not emptying
>> the log if log backup). So my guess is that you didn't specify the correct options in the GUI
>> (like MOVE and/or REPLACE).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Don Miller" <nospam@.nospam.com> wrote in message news:euirpti9HHA.5316@.TK2MSFTNGP04.phx.gbl...
>> Hmmm. I knew SSMS GUI could not perform COPY_ONLY backups (it's in the docs) and now I know that
>> it cannot do COPY_ONLY restores (UNdocumented).
>> I ran a RESTORE script on the COPY_ONLY backup and it worked fine (the GUI also worked for a
>> non-COPY_ONLY backup).
>> Thanks for pointing me in the right direction (away from the GUI ;)
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:ufl2b$h9HHA.5980@.TK2MSFTNGP04.phx.gbl...
>> 1) Manually craft a RESTORE statement and try that from SSMS directly. See if it works. Also
>> try RESTORE VERIFYONLY, HEADERONLY or FILELISTONLY to see what's up.
>> 2) Try to restore a FULL, non-copyonly backup using the GUI to see if it makes a difference.
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> "Don Miller" <nospam@.nospam.com> wrote in message news:Oh14Hmh9HHA.1208@.TK2MSFTNGP05.phx.gbl...
>>I did a COPY_ONLY backup of my production database (SQL Server 2005), moved it to my
>>development machine (only 225mb). I then tried to restore the database (to a newly named
>>database) from a device (disk), located the backup file, checked contents in the Specify Backup
>>window (it all seemed to be there), and then added the file. When I get back to the Restore
>>Database window, there is NOTHING in the "Select the backup sets to restore:" window!! So, I
>>can't go any further, cannot go to the Options tab ("You must select a restore source") and
>>cannot restore the database.
>> Does anyone know what is going on?
>> Thanks for any help.
>>
>>
>>
>

Copy timestamp data between columns

We have a table that was corrupted during a hardware outage and there
are no backups for the data !!
We are able to restore all the data, but there are torn pages in a
table called "WorkItem".
I wanted to copy what I could out of the workitem table and copy it to
workitem2. Noticed the timestamp column in the workitem table would
not allow me to execute:
insert into workitem2 select * from workitem where x = y
Select * into workitem2 from workitem where x= y is not good because
there are multiple runs that need to be executed to pull in workitem2
data. This statement creates the table.
FYI
To work around this - exported the data to a text file and re-
imported.
On Nov 13, 11:28 am, Justindawg <kfw...@.hotmail.com> wrote:
> We have a table that was corrupted during a hardware outage and there
> are no backups for the data !!
> We are able to restore all the data, but there are torn pages in a
> table called "WorkItem".
> I wanted to copy what I could out of the workitem table and copy it to
> workitem2. Noticed the timestamp column in the workitem table would
> not allow me to execute:
> insert into workitem2 select * from workitem where x = y
> Select * into workitem2 from workitem where x= y is not good because
> there are multiple runs that need to be executed to pull in workitem2
> data. This statement creates the table.

Copy timestamp data between columns

We have a table that was corrupted during a hardware outage and there
are no backups for the data !!
We are able to restore all the data, but there are torn pages in a
table called "WorkItem".
I wanted to copy what I could out of the workitem table and copy it to
workitem2. Noticed the timestamp column in the workitem table would
not allow me to execute:
insert into workitem2 select * from workitem where x = y
Select * into workitem2 from workitem where x= y is not good because
there are multiple runs that need to be executed to pull in workitem2
data. This statement creates the table.FYI
To work around this - exported the data to a text file and re-
imported.
On Nov 13, 11:28 am, Justindawg <kfw...@.hotmail.com> wrote:
> We have a table that was corrupted during a hardware outage and there
> are no backups for the data !!
> We are able to restore all the data, but there are torn pages in a
> table called "WorkItem".
> I wanted to copy what I could out of the workitem table and copy it to
> workitem2. Noticed the timestamp column in the workitem table would
> not allow me to execute:
> insert into workitem2 select * from workitem where x = y
> Select * into workitem2 from workitem where x= y is not good because
> there are multiple runs that need to be executed to pull in workitem2
> data. This statement creates the table.

Copy timestamp data between columns

We have a table that was corrupted during a hardware outage and there
are no backups for the data !!
We are able to restore all the data, but there are torn pages in a
table called "WorkItem".
I wanted to copy what I could out of the workitem table and copy it to
workitem2. Noticed the timestamp column in the workitem table would
not allow me to execute:
insert into workitem2 select * from workitem where x = y
Select * into workitem2 from workitem where x= y is not good because
there are multiple runs that need to be executed to pull in workitem2
data. This statement creates the table.FYI
To work around this - exported the data to a text file and re-
imported.
On Nov 13, 11:28 am, Justindawg <kfw...@.hotmail.com> wrote:
> We have a table that was corrupted during a hardware outage and there
> are no backups for the data !!
> We are able to restore all the data, but there are torn pages in a
> table called "WorkItem".
> I wanted to copy what I could out of the workitem table and copy it to
> workitem2. Noticed the timestamp column in the workitem table would
> not allow me to execute:
> insert into workitem2 select * from workitem where x = y
> Select * into workitem2 from workitem where x= y is not good because
> there are multiple runs that need to be executed to pull in workitem2
> data. This statement creates the table.

Copy SQL 7.0 db to SQL 2k

Hi all;
Is there a procedure to copy a database from 7.0 to 2K or can in fact a 7.0
db be copied into 2K?
Do I have to perform a backup/restore situation for this db or are there
possibly some CLI commands for a db copy?
Thanks in advance.
Steve
There is no "Copy" command per say but you can either do a Restore or a
sp_attach_db. Both operations will take a 7.0 db and upgrade it to a 2000
db and leave the data and objects etc intact.
Andrew J. Kelly SQL MVP
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:F4B16A4D-126A-4BDA-A16D-8BB57CD7A301@.microsoft.com...
> Hi all;
> Is there a procedure to copy a database from 7.0 to 2K or can in fact a
> 7.0
> db be copied into 2K?
> Do I have to perform a backup/restore situation for this db or are there
> possibly some CLI commands for a db copy?
> Thanks in advance.
> Steve
|||Hi Steve,
One of the easiest method is using backup/restore.
Thanks
Yogish
|||Keep in mind the collation type...
--
Sasan Saidi
MSc in CS, MCSE4, IBM Certified MQ 5.3 Administrator
Senior DBA
"Yogish" wrote:

> Hi Steve,
> One of the easiest method is using backup/restore.
> --
> Thanks
> Yogish

Copy SQL 7.0 db to SQL 2k

Hi all;
Is there a procedure to copy a database from 7.0 to 2K or can in fact a 7.0
db be copied into 2K?
Do I have to perform a backup/restore situation for this db or are there
possibly some CLI commands for a db copy?
Thanks in advance.
SteveThere is no "Copy" command per say but you can either do a Restore or a
sp_attach_db. Both operations will take a 7.0 db and upgrade it to a 2000
db and leave the data and objects etc intact.
--
Andrew J. Kelly SQL MVP
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:F4B16A4D-126A-4BDA-A16D-8BB57CD7A301@.microsoft.com...
> Hi all;
> Is there a procedure to copy a database from 7.0 to 2K or can in fact a
> 7.0
> db be copied into 2K?
> Do I have to perform a backup/restore situation for this db or are there
> possibly some CLI commands for a db copy?
> Thanks in advance.
> Steve|||Hi Steve,
One of the easiest method is using backup/restore.
--
Thanks
Yogish|||Keep in mind the collation type...
--
--
Sasan Saidi
MSc in CS, MCSE4, IBM Certified MQ 5.3 Administrator
Senior DBA
"Yogish" wrote:
> Hi Steve,
> One of the easiest method is using backup/restore.
> --
> Thanks
> Yogish

Copy SQL 7.0 db to SQL 2k

Hi all;
Is there a procedure to copy a database from 7.0 to 2K or can in fact a 7.0
db be copied into 2K?
Do I have to perform a backup/restore situation for this db or are there
possibly some CLI commands for a db copy?
Thanks in advance.
SteveThere is no "Copy" command per say but you can either do a Restore or a
sp_attach_db. Both operations will take a 7.0 db and upgrade it to a 2000
db and leave the data and objects etc intact.
Andrew J. Kelly SQL MVP
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:F4B16A4D-126A-4BDA-A16D-8BB57CD7A301@.microsoft.com...
> Hi all;
> Is there a procedure to copy a database from 7.0 to 2K or can in fact a
> 7.0
> db be copied into 2K?
> Do I have to perform a backup/restore situation for this db or are there
> possibly some CLI commands for a db copy?
> Thanks in advance.
> Steve|||Hi Steve,
One of the easiest method is using backup/restore.
Thanks
Yogish|||Keep in mind the collation type...
--
Sasan Saidi
MSc in CS, MCSE4, IBM Certified MQ 5.3 Administrator
Senior DBA
"Yogish" wrote:

> Hi Steve,
> One of the easiest method is using backup/restore.
> --
> Thanks
> Yogish