SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Copying Databases - Error - TempDB - Error: 823 Severity: 24 State: 7


Copying Databases - Error - TempDB - Error: 823 Severity: 24 State: 7

Author
Message
joshdbguy
joshdbguy
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1877 Visits: 725
I'm trying to copy a few databases within the same server. Four of the database copies worked while two will not. When I try to copy those databases, I receive the following errors in order,

1. Operating system error 'invalid protection option' resulted from attempt to read the following: sort run page (1:106884), in file 'F:\SQLTempDB\tempdb.mdf', in database with ID 2. Sort is retrying the read.

2. Error: 823, Severity: 24, State: 7.

3. The operating system returned error invalid protection option to SQL Server during a read at offset 0x00000034308000 in file 'F:\SQLTempDB\tempdb.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB).


So here's what I have done so far,

1. I restarted the SQL Server service to refresh TempDB and the same issue occurred
2. I did a CHECKDB on the TEMPDB as well as the databases I'm copying from and still see the same issue.
3. I ran a chkdsk which found errors on the drives. We replaced those drives with brand new ones and we are still seeing the same issue.

Any ideas where to go from here? Note that these are the only errors I can find within the SQL Server logs and windows logs.
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73289 Visits: 40962
you cannot copy the mdf/ldf data files themselves unless the sql server is actually stopped; otherwise you get corrupted files.

you cannot replace a server's existing databases if the server is running either...it's not like text files you can copy and paste.

also, tempdb is created automatically whenever the server is restarted;you would never ever copy or restore tempdb

the recommended way to move databases is with backup and restore; but you would basically never want to restore a different server's 4 main files (master/temp/model/msdb to the same or another server; you'd only want to restore regular databases, or resotre them to teh same server in case of disaster recovery.


can you explain what you are trying to do?

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
joshdbguy
joshdbguy
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1877 Visits: 725
Sorry for the confusion, I'm not copying them directly within the server. I am using the SQL Server UI, selecting a database and then running the copy database wizard.

Edit: I am copying non-system databases.
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73289 Visits: 40962
ok, in that case, it might be that the tempdb is running out of space or something for the import wizards process?
i'd still recommend backup and resotre over the copy database wizard.

Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
joshdbguy
joshdbguy
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1877 Visits: 725
Lowell (2/15/2013)
ok, in that case, it might be that the tempdb is running out of space or something for the import wizards process?
i'd still recommend backup and resotre over the copy database wizard.


The weird thing is that out of the six database I've attempted to copy, four worked. Those four range in size from a few gb to 300 gb. The two databases I am copying are about 40 gb and 200 gb but only these two fail.

TempDB has plenty of space.
lkennedy76
lkennedy76
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3472 Visits: 919
Josh,

Why are you copying them? Backup and restore is the most efficient ways to move over a DB? What is your goal in doing a copy? If 4 worked and 2 do not, backup and restore them. I have never in my 15 years of SQL ever copied, always backup and restore. ;-)

MCSA SQL Server 2012
joshdbguy
joshdbguy
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1877 Visits: 725
Copying was an easy way to make a replica of another test database. Because we don't backup every database, if I didn't do a copy, I would have to make a backup and then do a restore. I realize this isn't a big deal but I found the copy functionality (as I'm a DBA in training) and wanted to try it out. If it's not a good idea, I'll make that recommendation going forward but in the meantime I ran into these errors. I'm concerned that there may be some underlying problem with one of our drives (as this is a brand new server) but if the copy feature is unreliable, I can attribute it to that and move on.
lkennedy76
lkennedy76
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3472 Visits: 919
JoshDBGuy (2/18/2013)
Copying was an easy way to make a replica of another test database. Because we don't backup every database, if I didn't do a copy, I would have to make a backup and then do a restore. I realize this isn't a big deal but I found the copy functionality (as I'm a DBA in training) and wanted to try it out. If it's not a good idea, I'll make that recommendation going forward but in the meantime I ran into these errors. I'm concerned that there may be some underlying problem with one of our drives (as this is a brand new server) but if the copy feature is unreliable, I can attribute it to that and move on.


Copy is not a good idea, at least thats my opinion, if you want to send an point in time copy try replication or snapshots. Also if you go for the backup and restore create an SSIS that way you can schedule it.

MCSA SQL Server 2012
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search