Create a gist now

Instantly share code, notes, and snippets.

"restoredb.rb" recipe needs templated T-SQL script to restore database
--
-- Author:: <%= @author_name %> / <%= @author_email %>
-- Cookbook Name:: smashrun
-- Templates:: <%= @basesql_template %>
--
-- Copyright 2010, Smashrun, Inc.
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
-- http://msdn.microsoft.com/en-us/library/ms175199(v=SQL.90).aspx
-- http://msdn.microsoft.com/en-us/library/ms191455(v=SQL.90).aspx
-- http://msdn.microsoft.com/en-us/library/ms174269(v=SQL.90).aspx
-- the logic for the retrieve script needs to be:
-- retrieve the most recent full backup
-- discover the hour of the day and subtract one
-- restore the most recent full backup
-- retrieve the most recent diff, depending on the hour of the day (N/A)
-- restore each translog backup younger than the current hour
-- restore the database from backup
-- begin with the most recent full backup and restore the full
-- database. Specify the original full database backup and NORECOVERY.
-- NORECOVERY allows subsequent restore operations to proceed.
--
USE master
GO
-- time for single user mode - All incomplete transactions will be rolled back
-- any other connections to the database will be immediately disconnected.
-- application downtime begin here
ALTER DATABASE <%= @dbname %>
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
RESTORE DATABASE <%= @dbname %>
FROM DISK = '<%= @restoredrive %><%= @restoredir %>\full-<%= @dbname %>-0.bak'
<% if @restoretype == "refresh" %>
WITH REPLACE, RECOVERY;
GO
<% else %>
WITH REPLACE, NORECOVERY;
GO
-- restore each transaction log backup created after the full
<% 0.upto(@hour.to_i) do |log| -%>
RESTORE LOG <%= @dbname %>
FROM DISK = '<%= @restoredrive %><%= @restoredir %>\trans-<%= @dbname %>-<%= log %>.bak'
WITH NORECOVERY;
GO
<% end -%>
RESTORE DATABASE <%= @dbname %>
WITH RECOVERY;
GO
<% end -%>
-- done with single user mode - let my users connect
-- application downtime ends here
ALTER DATABASE <%= @dbname %>
SET MULTI_USER
WITH NO_WAIT;
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment