| Name |
SQL_Generate_TSQL_ToCreate_DWStaging_Snapshot |
| Value |
 Expression: "
EXEC('
SET NOCOUNT ON
IF 1=2
BEGIN
SELECT CAST('''' AS varchar(8000)) AS CreateSnapshot
END
DECLARE @SourceDBName SYSNAME
DECLARE @SourceDBSnapShotName SYSNAME
DECLARE @FileName SYSNAME
DECLARE @SQL VARCHAR(max)
DECLARE @SQL_FILE VARCHAR(4000)
DECLARE @DBFileFolder SYSNAME
DECLARE @DataSpaceId INT
SET @DBFileFolder = ''" + @[User::DWStagingDBFileFolder] + "''
SET @SourceDBSnapShotName = ''" + @[User::DWStagingSnapShotName] + "''
SET @SourceDBName = ''" + @[User::DWStagingDBName] + "''
SET @SQL = ''CREATE DATABASE ['' + @SourceDBSnapShotName + ''] ON''
DECLARE curFileName CURSOR FAST_FORWARD
FOR
SELECT f.data_space_id, f.name AS DBFileName
FROM sys.database_files f
INNER JOIN sys.filegroups fg
ON (f.data_space_id = fg.data_space_id)
WHERE fg.type = ''FG''
ORDER BY f.data_space_id
OPEN curFileName
FETCH NEXT FROM curFileName INTO @DataSpaceId, @FileName
WHILE @@fetch_status = 0
BEGIN
IF @SQL_FILE IS NULL
BEGIN
SET @SQL_FILE = '' ''
END
ELSE
EXEC('
SET NOCOUNT ON
IF 1=2
BEGIN
SELECT CAST('''' AS varchar(8000)) AS CreateSnapshot
END
DECLARE @SourceDBName SYSNAME
DECLARE @SourceDBSnapShotName SYSNAME
DECLARE @FileName SYSNAME
DECLARE @SQL VARCHAR(max)
DECLARE @SQL_FILE VARCHAR(4000)
DECLARE @DBFileFolder SYSNAME
DECLARE @DataSpaceId INT
SET @DBFileFolder = ''C:\DW Local Stuff\SQL Data\''
SET @SourceDBSnapShotName = ''DWStaging_ETL_20170309_172829''
SET @SourceDBName = ''DWStaging''
SET @SQL = ''CREATE DATABASE ['' + @SourceDBSnapShotName + ''] ON''
DECLARE curFileName CURSOR FAST_FORWARD
FOR
SELECT f.data_space_id, f.name AS DBFileName
FROM sys.database_files f
INNER JOIN sys.filegroups fg
ON (f.data_space_id = fg.data_space_id)
WHERE fg.type = ''FG''
ORDER BY f.data_space_id
OPEN curFileName
FETCH NEXT FROM curFileName INTO @DataSpaceId, @FileName
WHILE @@fetch_status = 0
BEGIN
IF @SQL_FILE IS NULL
BEGIN
SET @SQL_FILE = '' ''
END
ELSE
BEGIN
SET @SQL_FILE = '', ''
END
SET @SQL_FILE = @SQL_FILE + ''
(
NAME = '''''' + @FileName + '''''',
FILENAME = '''''' + @DBFileFolder + @SourceDBSnapShotName + ''_'' + @FileName + CASE WHEN @DataSpaceId = 1 THEN ''.mdf'' ELSE ''.ndf'' END + ''''''
)
''
SET @SQL = @SQL + @SQL_FILE
FETCH NEXT FROM curFileName INTO @DataSpaceId,@FileName
END
SET @SQL = @SQL + '' AS SNAPSHOT OF ['' + @SourceDBName + ''];''
CLOSE curFileName
DEALLOCATE curFileName
SELECT CAST(@SQL AS varchar(8000)) AS CreateSnapshot
')
|
| Value Type |
String |
| Description |
|
| System Variable |
False |
| Namespace |
User |
| Qualified Name |
User::SQL_Generate_TSQL_ToCreate_DWStaging_Snapshot |
| Evaluate As Expression |
True |
| Expression |
"
EXEC('
SET NOCOUNT ON
IF 1=2
BEGIN
SELECT CAST('''' AS varchar(8000)) AS CreateSnapshot
END
DECLARE @SourceDBName SYSNAME
DECLARE @SourceDBSnapShotName SYSNAME
DECLARE @FileName SYSNAME
DECLARE @SQL VARCHAR(max)
DECLARE @SQL_FILE VARCHAR(4000)
DECLARE @DBFileFolder SYSNAME
DECLARE @DataSpaceId INT
SET @DBFileFolder = ''" + @[User::DWStagingDBFileFolder] + "''
SET @SourceDBSnapShotName = ''" + @[User::DWStagingSnapShotName] + "''
SET @SourceDBName = ''" + @[User::DWStagingDBName] + "''
SET @SQL = ''CREATE DATABASE ['' + @SourceDBSnapShotName + ''] ON''
DECLARE curFileName CURSOR FAST_FORWARD
FOR
SELECT f.data_space_id, f.name AS DBFileName
FROM sys.database_files f
INNER JOIN sys.filegroups fg
ON (f.data_space_id = fg.data_space_id)
WHERE fg.type = ''FG''
ORDER BY f.data_space_id
OPEN curFileName
FETCH NEXT FROM curFileName INTO @DataSpaceId, @FileName
WHILE @@fetch_status = 0
BEGIN
IF @SQL_FILE IS NULL
BEGIN
SET @SQL_FILE = '' ''
END
ELSE
|
| ID |
bf2065b9-f82a-462c-b7a8-ee85957e7ee0 |
| Raise Changed Event |
False |
Properties
