none
Read only databases and backups for batch processing... RRS feed

  • Question

  • Scenario:

    Database A ~(250GB) is read only

    A full backup is taken and transferred elsewhere, this becomes Database B

    Database B is updated with ~10GB of change.

    I want to be able to transfer the transactions from B back to database A.

    Database B is then removed to make way for the same process to happen again (There are many databases like A)

    The whole process repeats once a day.

    Problem:-

    Database A is not in recovery mode, so I can't simply take a log backup from B and apply it to A.

    I could take a full backup of B and move it back, but want to avoid that if possible due to the time it takes. (I have many other databases just like A which need to follow the same process, and I want to get on with the next one as quickly as possible).

    I could restore the original full backup of A along side, to make a database C. I can then restore a transaction log backup from B into C. This seems very inefficient though, given A and C are in data terms identical.

    Is there a way around this I'm missing!?

    Not wild on the complexity of using replication in this process, but it might be one way. A problem I have is I want control of whether I make the update back to A depending on the outcome of B. There can also be multiple Bs created and only one will be chosen to update A.

    Bit of a puzzle this one.

    Saturday, January 18, 2014 3:11 PM

Answers

  • Are you saying that you want *one* database writeable, and the others only readable? And the one being writeable will vary over time?

    If so, I believe that log backups and STANDBY or NORECOVERY can be helpful. The idea is that when you want to move from the writeable server, you do a log backup using NORECOVERY (we call this server X). You then restore this log backup onto the next server (we call this Y). The restore on Y will be using RECOVERY. Since you did a backup on X with NORECOVERY, the database is read_only. To make X readable (if needed) you can restore the log backup you just produced using the STANDBY option. Now db is readable on X and writeable on Y. And to get it back to X, you just reverse the roles.

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by shaunlord Monday, January 20, 2014 7:40 PM
    Monday, January 20, 2014 1:19 PM
    Moderator

All replies

  • Hello Shaun,

    I am sorry but i cannot understand your problem.Can you please write in short about what you want to achieve


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Saturday, January 18, 2014 3:27 PM
    Moderator
  • It is a little unusual I admit.

    Its a batch updating process for a collection of large analytical databases. The problem is the updating happens on a specialised server which uses locally installed third party software and specialised hardware to accelerate the process of updating. Much like building an SSAS cube though.

    The sql database doesnt get much io during the day, the specialised 'cubes' handle most of the work once they have been created, but each morning a full reprocess is needed, which is very intensive and as I say happens on specialised hardware.

    So in brief the data needs to move from where it usually sits onto one of these boxes, and come back again afterwards, so that the same process can happen again for another database.

    Here is a script which shows what I'm on about above, but I'd like to avoid needing to create database C if possible.

    --normal state
    create database A;
    alter database A set recovery full with no_wait
    alter database A set read_only with no_wait

    --do a full to put elsewhere
    backup database A to disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\A.bak' with init;

    --make a copy
    restore database B from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\A.bak'
    with move 'A' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\B.mdf',
    move 'A_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\B_log.LDF'

    --make it editable
    alter database B set read_write with no_wait

    --make a table
    select id into B.dbo.NewTable from master..sysobjects;

    --back up the log
    backup log B to disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\B_Log.bak' with init

    --make database C
    restore database C from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\A.bak'
    with move 'A' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\C.mdf',
    move 'A_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\C_log.LDF',
    norecovery

    --restore Bs transaction log into C
    restore log C from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\B_Log.bak'

    --check we have the new table
    select count(*) from C.dbo.NewTable

    --tidy up
    drop database A
    drop database B
    drop database C

    Saturday, January 18, 2014 3:45 PM
  • >>>I want to be able to transfer the transactions from B back to database A.

    Is it one or more tables to be affected by the transactions? It is possible to move only those tables.

    Is that possible to create an empty database (database B) and then backup and restore to move the  data back to the database A?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, January 19, 2014 7:37 AM
  • Are you saying that you want *one* database writeable, and the others only readable? And the one being writeable will vary over time?

    If so, I believe that log backups and STANDBY or NORECOVERY can be helpful. The idea is that when you want to move from the writeable server, you do a log backup using NORECOVERY (we call this server X). You then restore this log backup onto the next server (we call this Y). The restore on Y will be using RECOVERY. Since you did a backup on X with NORECOVERY, the database is read_only. To make X readable (if needed) you can restore the log backup you just produced using the STANDBY option. Now db is readable on X and writeable on Y. And to get it back to X, you just reverse the roles.

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by shaunlord Monday, January 20, 2014 7:40 PM
    Monday, January 20, 2014 1:19 PM
    Moderator
  • Uri

    -The transactions affect multiple tables, but usually there are two or three tables which make up much of the size of the database, and unfortunately they are the ones most heavily affected.

    -Yes I expect so, but this doesn't really solve my issue as I will have to move all the data from A to B. What I want to do is only have to move the changed data. (10Gb rather than 250Gb)

    Tibor

    -Yes, the writeable copy is essentially volatile, I'll keep loosing it. Normally I just have a read-only copy available, and when it is time to do an update, a writeable clone needs to be created on the second server. Once the writeable copy is updated, the challenge is to get the transactions back to the read-only version and update it, without needing a full database backup/restore over the network. I can get sort of there with my script above, but it does mean making a local recovery mode copy of my read-only database in order to restore the log that comes back, which seems inefficient. 

    I did have an experiment with tail backups which ended at a dead end - I think the database I'd taken the log backup from with norecovery would not accept the next restore as I had not restored a full database into it. I'm not certain though, so I'll double check.

    Thanks for your input.


    Monday, January 20, 2014 3:46 PM
  • Success!

    The key is the tail log backup taken on the source (X) using norecovery. When I tested this previously I didnt try restoring that onto my editable copy (Y) before making changes to it. If I do then X is happy enough to get the log backup from (Y), otherwise it wont accept it.

    Script is as follows:-

    --start with a writable database in full recovery mode
    create database X;
    alter database X set recovery full with no_wait;
    
    --make a table
    select id into X.dbo.NewTable from master..sysobjects;
    
    --do a full to put elsewhere
    backup database X to disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\X.bak' with init;
    
    --back up the log norecovery
    backup log X to disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\X_Log.bak' 
    with init, norecovery
    --so now X is in restoring mode
    
    --make the initial copy of Y, but dont recover yet
    restore database Y from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\X.bak'
    with move 'X' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Y.mdf',
    	 move 'X_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Y_log.LDF'
    	 ,norecovery
    
    --now Y is in restoring mode too
    --now restore the log and recover it
    --need this log to to able to restore this data back to X
    restore log Y from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\X_Log.bak'
    	with recovery
    
    --make a change to the clone
    select * into Y.dbo.NewTable2 from Y.dbo.NewTable
    
    --backup the changes
    backup log Y to disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Y_Log.bak'
    	with init
    
    --and move back to the source
    restore log X from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Y_Log.bak'  
    
    --and leave it read only
    alter database X set read_only
    
    --check we got the changes
    select * from X.dbo.NewTable2
    
    --get rid of our replica
    drop database Y
    
    --so just to check do it again
    
    --take it out of read only mode
    alter database X set read_write
    
    
    --do a full to put elsewhere
    backup database X to disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\X.bak' with init;
    
    --back up the log norecovery
    backup log X to disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\X_Log.bak' 
    with init, norecovery
    --so now X is in restoring mode
    
    --make the initial copy of X, but dont recover yet
    restore database Y2 from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\X.bak'
    with move 'X' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Y2.mdf',
    	 move 'X_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Y2_log.LDF'
    	 ,norecovery
    
    --now Y is in restoring mode too
    --now restore the log and recover it
    --need this log to to able to restore this data back to X
    restore log Y2 from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\X_Log.bak'
    	with recovery
    
    --make a change to the clone
    select * into Y2.dbo.NewTable3 from Y2.dbo.NewTable2
    
    --backup the changes
    backup log Y2 to disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Y2_Log.bak'
    	with init
    
    --and move back to the source
    restore log X from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Y2_Log.bak'  
    
    --and leave it read only
    alter database X set read_only
    
    --check we got the changes
    select count(*) from X.dbo.NewTable
    select count(*) from X.dbo.NewTable2
    select count(*) from X.dbo.NewTable3
    
    --get rid of our replica
    drop database Y2
    

    Monday, January 20, 2014 4:30 PM
  • Yep. You need an unbroken chain of logrecords. So, wherever you go, take all log records with you, so to speak. Glad you got it working! :-)

    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, January 21, 2014 8:15 AM
    Moderator