Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Copying Databases - Error - TempDB - Error: 823 Severity: 24 State: 7 Expand / Collapse
Author
Message
Posted Friday, February 15, 2013 11:59 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 23, 2014 3:25 PM
Points: 144, Visits: 300
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.
Post #1420715
Posted Friday, February 15, 2013 12:04 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:16 AM
Points: 12,923, Visits: 32,282
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1420718
Posted Friday, February 15, 2013 12:05 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 23, 2014 3:25 PM
Points: 144, Visits: 300
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.
Post #1420721
Posted Friday, February 15, 2013 12:07 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:16 AM
Points: 12,923, Visits: 32,282
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1420723
Posted Friday, February 15, 2013 12:10 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 23, 2014 3:25 PM
Points: 144, Visits: 300
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.
Post #1420724
Posted Monday, February 18, 2013 1:19 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, November 14, 2014 8:28 AM
Points: 301, Visits: 597
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
Post #1421338
Posted Monday, February 18, 2013 2:52 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 23, 2014 3:25 PM
Points: 144, Visits: 300
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.
Post #1421355
Posted Tuesday, February 19, 2013 9:48 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, November 14, 2014 8:28 AM
Points: 301, Visits: 597
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
Post #1421728
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse