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

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

  • 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!

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

  • 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!

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

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

    MCSE SQL Server 2012\2014\2016

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

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

    MCSE SQL Server 2012\2014\2016

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply