pallid

#Generate restore command for each backup file in $backupFolder
#Author: Evgeny Khabarov <ekhabarov@dev.ms>
#Date: 08.06.2015


#Import module for use SMO objects
Import-Module "SQLPS" -DisableNameChecking;

$sqlserver = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server('.');
$restore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore;

pallid

CREATE DATABASE SQLtoDT1
ON
	PRIMARY (NAME=SQLtoDT1Data, 
	FILENAME='D:\SQL_BASE\MSSQL11.MSSQLSERVER\MSSQL\DATA\SQLtoDT1_data.mdf', 
	SIZE=200,
	FILEGROWTH=200 )
LOG ON (
	NAME=SQLtoDTLog1,
	FILENAME='D:\SQL_BASE\MSSQL11.MSSQLSERVER\MSSQL\DATA\SQLtoDT1_log.ldf', 
	SIZE=1, 

pallid

declare @tmp table(db varchar(100) ) 

declare @paths varchar(100)

set @paths = 'D:\SQL_BASE\MSSQL11.MSSQLSERVER\MSSQL\DATA\'

insert into @tmp
select 'db1' union
select 'db2' union
select 'db3' union

pallid

--Здесь ничего не трогаем
declare @SQLString nvarchar(4000), @TableName nvarchar(16)
declare @t table (fname NVARCHAR(50))
DECLARE @counter INT, @backupfile NVARCHAR(50) 
SET @counter = 0

---------------------------------------------------------------------------
-- Здесь изменяем имя базы
set @TableName = N'Ins_3_3'
-- Здесь вставляем необходимое количество бекапов.

pallid

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'E:\SQL_BACKUP\_test\' -- Куда выгружать...обязатель указать закрывающию "\"
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name

pallid

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\' -- Откуда берем бекапы

pallid

<?xml version="1.0" encoding="UTF-8"?>
<config xmlns="http://v8.1c.ru/v8/tech-log">
<dump create="true" location="C:\_DUMPS" type="0"/>
    <log location="C:\_LOGS" history="168">
	<property name="all"/>
        <event>
            <eq property="name" value="excp"/>
        </event>
    </log>
</config>

pallid

USE [HRM]
ALTER DATABASE [HRM]
SET RECOVERY SIMPLE;
GO

DBCC SHRINKFILE (N'HRM_log', 4100) -- 100 кол-во мб
GO

ALTER DATABASE [HRM]
SET RECOVERY FULL;

pallid

set nocount on
declare @res varchar(max)
declare @st table ([Database Name] varchar(100), [Log Size (MB)] real, [Log Space Used (%)] real, [Status] real)
insert into @st ([Database Name], [Log Size (MB)], [Log Space Used (%)], [Status])
exec('DBCC SQLPERF(LOGSPACE)')
declare @l int 
select @l=max(len([Database Name])) from @st
set @res='[Database Name]'+Replicate(' ',@l-len('[Database Name]'))+' [Log Size (MB)] [Log Space Used (%)] [Status]'+char(13)
select @res=@res+[Database Name]+Replicate(' ',@l-len([Database Name]))+' '	+str([Log Size (MB)],15,2)+' '+str([Log Space Used (%)],18,1)+' '+str([Status],8)+char(13) from @st
select @res

pallid

BACKUP DATABASE [OldBase] TO  DISK = N'D:\SQL_Base\MSSQL11.TEST_SQL\MSSQL\Backup\OldBase_backup_ПередПереходомВПростуюМодель.bak' WITH NOFORMAT,
NOINIT,  NAME = N'OldBase-ПередПереходомВПростуюМодель', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

USE [OldBase]
ALTER DATABASE [OldBase]
SET RECOVERY SIMPLE;
GO