Last active
November 5, 2015 22:38
-
-
Save pallid/39aae0b09cf4f88b2cd9 to your computer and use it in GitHub Desktop.
Создание и востановление из бекапа N количество баз
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
declare @tmp table(db varchar(100) ) | |
declare @paths varchar(255) | |
declare @pathsLog varchar(255) | |
declare @backupFolder varchar(255) | |
set @paths = 'D:\SQL_BASE\MSSQL11.MSSQLSERVER\MSSQL\DATA\' --Куда создаем базы | |
set @pathsLog = 'D:\SQL_BASE\MSSQL11.MSSQLSERVER\MSSQL\DATA\' -- Куда создаем логи | |
set @backupFolder= 'E:\SQL_BACKUP\' -- Откуда берем бекапы | |
insert into @tmp | |
--Ниже пишем список баз | |
select 'namedb1' union | |
select 'namedb2' union | |
select 'namedb3' union | |
select 'namedb4' union | |
select 'namedbN' | |
--select * from @tmp | |
declare @name varchar(100) | |
declare @createDB varchar(max) | |
declare @restoreDB varchar(max) | |
declare mycursor cursor for | |
select db from @tmp | |
open mycursor | |
fetch next from mycursor into @name | |
WHILE @@FETCH_STATUS = 0 | |
BEGIN | |
set @createDB='CREATE DATABASE '+@name | |
+' ON | |
PRIMARY (NAME='+@name+'Data, | |
FILENAME='''+@paths+@name+'.mdf'', | |
SIZE=200, | |
FILEGROWTH=200 ) | |
LOG ON ( | |
NAME='+@name+'Log, | |
FILENAME='''+@paths+@name+'.ldf'', | |
SIZE=1, | |
MAXSIZE=4096, | |
FILEGROWTH=50 | |
) ' | |
set @restoreDB='RESTORE DATABASE ['+@name+'] | |
FROM DISK = N'''+@backupFolder+@name+'.bak'' WITH FILE = 1, | |
MOVE N'''+@name+''' TO N'''+@paths+@name+'.mdf'', | |
MOVE N'''+@name+'_log'' TO N'''+@pathsLog+@name+'.ldf'', | |
NOUNLOAD, REPLACE, STATS = 5 ' | |
-- Select - для проверки | |
-- Exec - для выполнения | |
select @createDB | |
select @restoreDB | |
--exec(@createDB) | |
--exec(@restoreDB) | |
FETCH NEXT FROM mycursor | |
INTO @name | |
END | |
close mycursor | |
deallocate mycursor |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment