June 27, 2006 at 7:30 am
hi..
After successfully restoring a sqlserver2000 database from a backup set, some of the tables are shown as read only, but I was allowed to update, insert or delete the data in those tables (database does not show as read-only in "Enterprise Manager" as that shown if any database was inbetween restore operation)
I got the following message after restore operation.
49 percent restored.
99 percent restored.
100 percent restored.
Processed 16 pages for database 'database_name', file 'database_name' on file 1.
Processed 1 pages for database 'database_name', file 'database_name_log' on file 1.
RESTORE DATABASE successfully processed 17 pages in 0.155 seconds
(0.852 MB/sec).
Instead of 100% successful restore why does the tables shown as read only - I got the read only appended with the tablename when i open that respective table thro' query analyser.
tankx in advance
regards deena
June 27, 2006 at 9:52 am
Thats really interesting...
1. From where you have taken the backup? same server?
2. Was the backup taken by SQL Query Analyzer / Enterprise Manager earlier?
3. Can you restore the database on some other server and check the data?
4. Do you have any such table in model database?
5. You said "readonly" is appended to table name?
SQL can never make such changes by its own.
June 27, 2006 at 10:43 am
>> 1. From where you have taken the backup? same server?
same server having only one instance
>> 2. Was the backup taken by SQL Query Analyzer / Enterprise >>Manager earlier?
I have taken the backup using VDI API given by Microsoft.
>> 3. Can you restore the database on some other server and check the data?
I will try and post the results later.
I have successfully restored the backup set to a new database in a different server. But still got read-only while opening the table using query analyser in that server also.
>> 4. Do you have any such table in model database?
no, but I have a table named dbo.dtproperties in all the databases in my sqlserver including the restored database and model database.
>> 5. You said "readonly" is appended to table name?
If I try to open to view the data's in a tables thro' query analyser. Just RightClick on the particular table -> select open table. A new window will be available with data in that table where header of the table have appended read-only with table name
like test.dbo.products(read-only)
>> SQL can never make such changes by its own.
thanks for ur reply
regards deena
June 27, 2006 at 10:59 am
What were your restore commands? Is this database part of a publisher/subscriber replication?
-SQLBill
June 28, 2006 at 12:03 am
My restore command were
osql -S (local) -U user_name -P password -o output_file_name -b -Q "RESTORE DATABASE database_name FROM VIRTUAL_DEVICE=device_name WITH BLOCKSIZE=65556, MAXTRANSFERSIZE=4194304, NORECOVERY, REPLACE, STATS=10"
I have a full and differential backup, while restoring differential backup I have used recovery option.
And no replication involved.
regards deena
June 28, 2006 at 6:44 pm
You say "I have a full and differential backup, while restoring differential backup I have used recovery option." But the only command you show is the one above and that says "NORECOVERY".
Try running just this:
RESTORE DATABASE database_name WITH RECOVERY
-SQLBill
June 28, 2006 at 11:27 pm
Query used for full backup restore is
osql -S (local) -U user_name -P password -o output_file_name -b -Q "RESTORE DATABASE database_name FROM VIRTUAL_DEVICE=device_name WITH BLOCKSIZE=65556, MAXTRANSFERSIZE=4194304, NORECOVERY, REPLACE, STATS=10"
Query used for differential backup restore is
osql -S (local) -U user_name -P password -o output_file_name -b -Q "RESTORE DATABASE database_name FROM VIRTUAL_DEVICE=device_name WITH BLOCKSIZE=65556, MAXTRANSFERSIZE=4194304, RECOVERY, REPLACE, STATS=10"
regards deena
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply