March 12, 2014 at 8:45 am
Hi,
We need to restore total 48 dbs on sql server 2012 for a migration task.
If we do manually restore all dbs it will take time so .
Can any one provide me any automation scrip to restore all dbs on a server.
many thanks,
March 12, 2014 at 9:18 am
I would consider doing it with Powershell here is an article that discusses doing just that: http://www.sqlservercentral.com/blogs/everyday-sql/2013/02/12/t-sql-tuesday-use-powershell-to-restore-a-database-on-a-different-server/
March 13, 2014 at 3:02 am
1.Write the backup script for each and every databases and name it as backupalldbs.sql
(use excel to get the databases list from sys.databases tables on one column to link with backup script in another column of sql.i.e by concat function in excel.Repeat the same for restore script also.)
2.Write the restore script for each and every databases and name it as restorealldbs.sql
3.Execute the step1.
4.copy the files to destination server path by robocopy.
5.Execute the step2.
6.Fix orphan users.
7.etc.
Regards,
Kumar
March 13, 2014 at 3:24 am
Refer following link:
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 14, 2014 at 10:20 am
This is what I have....
create a database called TOOLS or whatever you want.
Create the below SP in this database. Then execute it. It will build the restore commands based on the last backups in MSDB DB. It also applies the lastest log backups so if you don't want that you can simply delete them out of the output.
USE [TOOLS]
GO
/****** Object: StoredProcedure [dbo].[usp_build_restore_script] Script Date: 03/14/2014 12:18:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[usp_build_restore_script]
AS
--
-- This stored procedure was written by Greg Larsen for Washington State Department of Health.
-- Date: 12/16/2001
--
-- Description:
-- This stored procedure generates TSQL script that will restore all the databases
-- on the current SQL Server. This stored procedure takes into account when the last
-- full and differential backups WHERE taken, and how many transaction log backups
-- have been taken since the last database backup, based on the information in
-- the msdb database.
--
-- Modified:
-- 11/30/2005 - Modified code to support two different databases being backed up in less than one
-- second. Bug found by: Viktor Placek
--
-- DECLARE variables used in SP
DECLARE @cmd nvarchar (2000)
DECLARE @cmd1 nvarchar (2000)
DECLARE @db nvarchar(128)
DECLARE @filename nvarchar(512)
DECLARE @cnt int
DECLARE @num_processed int
DECLARE @name nvarchar(512)
DECLARE @physical_device_name nvarchar(512)
DECLARE @backup_start_date datetime
DECLARE @type CHAR(1)
-- Turn off the row number message
SET NOCOUNT ON
-- SECTION 1 ----------------------------------------------
-- Define cursor to hold all the different databases for the restore script will be built
DECLARE db CURSOR FOR
SELECT name FROM master..sysdatabases
WHERE name not in ('tempdb')
-- Create a global temporary table that will hold the name of the backup, the database name, and the type of database backup.
CREATE TABLE ##backupnames (
name nvarchar(512),
database_name nvarchar(128),
type CHAR(1) )
-- OPEN cursor containing list of database names.
OPEN db
FETCH NEXT FROM db into @db
-- Process until no more databases are left
WHILE @@FETCH_STATUS = 0
BEGIN
-- Subsection 1A --------------------------------------------
-- initialize the physical device name
SET @physical_device_name = ''
-- get the name of the last full database backup
SELECT @physical_device_name = physical_device_name , @backup_start_date = backup_start_date
FROM msdb..backupset a
INNER JOIN msdb..backupmediaset b
ON a.media_set_id = b.media_set_id
INNER JOIN msdb..backupmediafamily c
ON a.media_set_id = c.media_set_id
WHERE @db = database_name AND type='d'
AND backup_start_date =(SELECT TOP 1 backup_start_date FROM msdb..backupset WHERE @db = database_name AND type = 'd'
ORDER BY backup_start_date desc)
-- Did a full database backup name get found
IF @physical_device_name <> ''
BEGIN
-- Build command to place a record in table that holds backup names
SELECT @cmd = 'insert into ##backupnames values (' + CHAR(39) +
@physical_device_name + CHAR(39) + ',' + CHAR(39) + @db + CHAR(39) + ',' +
CHAR(39) + 'd' + CHAR(39)+ ')'
-- Execute command to place a record in table that holds backup names
EXEC sp_executesql @cmd
END
-- Subsection 1B --------------------------------------------
-- Reset the physical device name
SET @physical_device_name = ''
-- Find the last differential database backup
SELECT @physical_device_name = physical_device_name, @backup_start_date = backup_start_date
FROM msdb..backupset a INNER JOIN msdb..backupmediaset b ON a.media_set_id = b.media_set_id
INNER JOIN msdb..backupmediafamily c ON a.media_set_id = c.media_set_id
WHERE type = 'i'
AND backup_start_date = (SELECT top 1 backup_start_date FROM msdb..backupset WHERE @db = database_name AND type = 'I' AND backup_start_date > @backup_start_date
ORDER BY backup_start_date desc)
-- Did a differential backup name get found
IF @physical_device_name <> ''
BEGIN
-- Build command to place a record in table that holds backup names
SELECT @cmd = 'insert into ##backupnames values (' + CHAR(39) +
@physical_device_name + CHAR(39) + ',' + CHAR(39) + @db + CHAR(39) + ',' +
CHAR(39) + 'i' + CHAR(39)+ ')'
-- Execute command to place a record in table that holds backup names
exec sp_executesql @cmd
END
-- Subsection 1C --------------------------------------------
-- Build command to place records in table to hold backup names for all
-- transaction log backups FROM the last database backup
SET @CMD = 'insert into ##backupnames SELECT physical_device_name,' + CHAR(39) + @db + CHAR(39) +
',' + CHAR(39) + 'l' + CHAR(39) +
'FROM msdb..backupset a INNER JOIN msdb..backupmediaset b ON a.media_set_id = b.media_set_id INNER JOIN ' +
'msdb..backupmediafamily c ON a.media_set_id = c.media_set_id ' +
'WHERE type=' + CHAR(39) + 'l' + CHAR(39) + 'AND backup_start_date > @backup_start_dat AND' +
CHAR(39) + @db + CHAR(39) + ' = database_name ORDER BY backup_start_date'
-- Execute command to place records in table to hold backup names
-- for all transaction log backups FROM the last database backup
EXEC sp_executesql @cmd,@params=N'@backup_start_dat datetime', @backup_start_dat = @backup_start_date
-- get next database to process
FETCH NEXT FROM db into @db
END
-- CLOSE
CLOSE db
-- Section B ----------------------------------------------
OPEN db
-- Get first recod FROM database list cursor
FETCH NEXT FROM db into @db
-- Generate Heading in Restore script
PRINT '-- Restore All databases'
-- Process all databases
WHILE @@FETCH_STATUS = 0
BEGIN
-- define CURSOR FOR all database and log backups for specific database being processed
DECLARE backup_name CURSOR FOR
SELECT name,type FROM ##backupnames WHERE database_name = @DB
-- OPEN cursor containing list of database backups for specific database being processed
OPEN backup_name
-- Determine the number of different backups available for specific database being processed
SELECT @CNT = count(*) FROM ##backupnames WHERE database_name = @DB
-- Get first database backup for specific database being processed
FETCH NEXT FROM backup_name into @physical_device_name, @type
-- Set counter to track the number of backups processed
SET @NUM_PROCESSED = 0
-- Process until no more database backups exist for specific database being processed
WHILE @@FETCH_STATUS = 0
BEGIN
-- Increment the counter to track the number of backups processed
SET @NUM_PROCESSED = @NUM_PROCESSED + 1
-- Is the number of database backup processed the same as the number of different backups
-- available for specific database being processed?
IF @CNT = @NUM_PROCESSED
-- If so, is the type of backup currently being processed a transaction log backup?
IF @TYPE = 'l'
-- build restore command to restore the last transaction log
SELECT @cmd = 'restore log ' + RTRIM(@db) + CHAR(13) +
' FROM disk = ' + CHAR(39) +
RTRIM(SUBSTRING(@physical_device_name,1,len(@physical_device_name))) +
CHAR(39) + CHAR(13) + ' with replace'
ELSE
-- Last backup was not a transaction log backup
-- Build restore command to restore the last database backup
SELECT @cmd = 'restore database ' + RTRIM(@db) + CHAR(13) +
' FROM disk = ' + CHAR(39) +
RTRIM(SUBSTRING(@physical_device_name,1,len(@physical_device_name))) +
CHAR(39) + CHAR(13) + ' with replace'
ELSE
-- Current backup is not the last backup
-- Is the current backup being processed a transaction log backup?
if @TYPE = 'l'
-- Build restore command to restore the current transaction backup, with no recovery
SELECT @cmd = 'restore log ' + RTRIM(@db) + CHAR(13) +
' FROM disk = ' + CHAR(39) +
RTRIM(SUBSTRING(@physical_device_name,1,len(@physical_device_name))) +
CHAR(39) + CHAR(13) + ' with replace, norecovery'
ELSE
-- Current backup being processed is not a transaction log backup
-- Build restore command to restore the currrent database backup, with no recovery
SELECT @cmd = 'restore database ' + RTRIM(@db) + CHAR(13) +
' FROM disk = ' + CHAR(39) +
RTRIM(SUBSTRING(@physical_device_name,1,len(@physical_device_name))) +
CHAR(39) + CHAR(13) + ' with replace, norecovery'
-- if it is master comment line out
IF @db = 'master'
SET @cmd = '/* ' + CHAR(13) + @cmd + CHAR(13) + '*/'
-- Generate the restore command and other commands for restore script
PRINT @cmd
PRINT 'go'
PRINT ' '
-- Get next database backup to process
FETCH NEXT FROM backup_name into @physical_device_name, @type
END
-- CLOSE and DEALLOCATE database backup name CURSOR FOR current database being processed
CLOSE backup_name
DEALLOCATE backup_name
-- Get next database to process
FETCH NEXT FROM db into @db
END
-- CLOSE and DEALLOCATE cursor containing list of databases to process
CLOSE db
DEALLOCATE db
-- Drop global temporary table
DROP TABLE ##backupnames
GO
March 14, 2014 at 2:25 pm
This may be an irregular way of doing it, but here's my process for restoring all of our databases from backup in DR. This is assuming the Log and Data files are going into the default location, plus in this scenario I've already manually restored the System databases so SQL is seeing the User databases but they're not accessible until the restore.
But here's a paste from my DR notes in this regard. It's a very KISS method, but hopefully this helps.
Sam
Restore User Databases
One solution that seems to work great in testing is getting a directory listing of the Backup directory then using Excel to stage the backups and create the restore script. Below are instructions on how to do this when restoring only full Backups, but this can be modified easily enough to also import Transaction Logs if they exist.
-On server run ‘cmd’ as Administrator
-‘cd’ do the backup drive
-Run this: dir . /p /s /b /o:d /T:C > dr_dir.txt
-Open Excel and copy results of ‘dir’ command into A2 (Full Path) and create the following other columns:
oB2 (Extension): =RIGHT(A2,3)
oC2 (Filename):=TRIM(RIGHT(SUBSTITUTE(A2,"\",REPT(" ",99)),99))
oD2 (DBName):=LEFT(C2,LEN(C2)-37)
oE2 (Date):=LEFT(RIGHT(C2,29),17)
oF2 (Restore):="RESTORE DATABASE [" & D2 & "] FROM DISK = '" & A2 & "' WITH STATS = 10"
-Sort by B and remove rows where file extension not equal to BAK
-Sort by E and remove rows where backup date is older than 24 hours (depending on when copied to UNC)
-Sort by A and this should be the backup scripts in F for each instance. Remove any that aren’t part of DR or not needed.
-Copy column F into SSMS after Master is restored and execute. If all folders exist on the server as they do in Production all databases should restore with no problems. If a database generates an error saying
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply