一个人如何迁移 40TB SQL 服务器数据库？

CREATE DATABASE [TrafficLogs] CONTAINMENT = NONEON PRIMARY(   NAME = N'TrafficLogs_Current',   FILENAME = N'F:\Data\TrafficLogs_Current.mdf',   SIZE = 102400000KB,   FILEGROWTH = 5120000KB),FILEGROUP [TrafficLogs_Archive](   NAME = N'TrafficLogs_Archive1',   FILENAME = N'E:\Data\TrafficLogs_Archive1.ndf' ,   SIZE = 102400000KB,   FILEGROWTH = 5120000KB),(   NAME = N'TrafficLogs_Archive2',   FILENAME = N'E:\Data\TrafficLogs_Archive2.ndf',   SIZE = 102400000KB , FILEGROWTH = 5120000KB),(   NAME = N'TrafficLogs_Archive3',   FILENAME = N'E:\Data\TrafficLogs_Archive3.ndf',   SIZE = 102400000KB,   FILEGROWTH = 5120000KB)LOG ON(   NAME = N'TrafficLogs_log',   FILENAME = N'F:\Data\TrafficLogs_log.ldf',   SIZE = 5120000KB,   MAXSIZE = 2048GB,   FILEGROWTH = 102400000KB);

-- written by Nick CraverDeclare @month datetime = '2015-08-01';Declare @endmonth datetime = '2021-01-01'WHILE @month < @endmonthBEGINSet NoCount On;Declare @prevMonth datetime = DateAdd(Month, -1, @month);Declare @nextMonth datetime = DateAdd(Month, 1, @month);Declare @monthTable sysname     = 'Logs_' + Cast(DatePart(Year, @month) as varchar) 	  + '_' + Right('0' + Cast(DatePart(Month, @month) as varchar), 2);Begin Try  If Object_Id(@monthTable, 'U') Is Not Null  Begin    Declare @error nvarchar(400) 	  = 'Month ' + Convert(varchar(10), @month, 120) 	    + ' has already been moved to ' + @monthTable + ', aborting.';	Throw 501337, @error, 1;	Return;  End  -- Table Creation  Declare @tableTemplate nvarchar(4000) = '    Create Table {Name} (	  [CreationDate] datetime Not Null,	  <insert all the columns>,	  Constraint CK_{Name}_Low Check (CreationDate >= ''{LowerDate}''),	  Constraint CK_{Name}_High Check (CreationDate < ''{UpperDate}'')  ) On {Filegroup};		    Create Clustered Columnstore Index CCI_{Name} 	  On {Name} With (Data_Compression = {Compression}) On {Filegroup};';  -- Constraints exist for metadata swap  Declare @table nvarchar(4000) = @tableTemplate;  Set @table = Replace(@table, '{Name}', @monthTable);  Set @table = Replace(@table, '{Filegroup}', 'Logs_Archive');  Set @table = Replace(@table, '{LowerDate}', Convert(varchar(20), @month, 120));  Set @table = Replace(@table, '{UpperDate}', Convert(varchar(20), @nextMonth, 120));  Set @table = Replace(@table, '{Compression}', 'ColumnStore_Archive');  Print @table;  Exec sp_executesql @table;		  Declare @moveSql nvarchar(4000)     = 'Create Clustered Columnstore Index CCI_{Name} 	  On {Name} With (Drop_Existing = On, Data_Compression = Columnstore_Archive) On Logs_Archive;';  Set @moveSql = Replace(@moveSql, '{Name}', @monthTable);  Print @moveSql;  Exec sp_executesql @moveSql;End TryBegin Catch	Select Error_Number() ErrorNumber,	    Error_Severity() ErrorSeverity,	    Error_State() ErrorState,	    Error_Procedure() ErrorProcedure,	    Error_Line() ErrorLine,	    Error_Message() ErrorMessage;	Throw;End Catchset @month = dateadd(month, 1, @month)ENDGO

-- written by Nick Craver<Query Kind="Program"><NuGetReference>Dapper</NuGetReference><Namespace>Dapper</Namespace></Query>void Main(){  MoveDate(new DateTime(2015, 08, 1));  DateTime date = new DateTime(2015, 08, 1);  while (date < DateTime.UtcNow)  {    MoveDate(date);    date = date.AddDays(1);  }}static readonly List<string> cols = new List<string> { "<col list>" };public void MoveDate(DateTime date){  var tableName = GetTableName(date);  var destTable = GetDestTableName(date);  $"Attempting to migrate {date:yyyy-MM-dd} f    from {tableName} to {destTable}".Dump($"{date:yyyy-MM-dd}");  using (var conn = GetConn())  {    int rowCount;	try	{	  rowCount = conn.QuerySingle<int>($"Select Count(*) 						From HAProxyLogs.dbo.{tableName};");	}	catch (SqlException e)	{	  ("  Error migrating: " + e.Message).Dump();	  return;	}	$"  Summary for {date:yyyy-MM-dd}".Dump();	$"    {rowCount:n0} row(s) in {tableName}".Dump();	var pb = new Util.ProgressBar($"{tableName} (0/{rowCount})");	pb.Dump(tableName + " copy");	Func<int> GetDestRowCount = () 	  => conn.QuerySingle<int>($"Select Count(*) 					From {destTable} 					Where CreationDate >= @date 					  And CreationDate < @date + 1;", new { date });	  Action<long, int> UpdatePB = (copied, total) =>	  {		pb.Fraction = (double)copied / total;		pb.Caption = $"{tableName} ({copied}/{total})";	  };	  var destRowCount = GetDestRowCount();	  $"  {destRowCount:n0} row(s) in {destTable}".Dump();	  if (destRowCount > 0)	  {		$"Rows found in destiation table - aborting!".Dump();		return;	  }	var reader = conn.ExecuteReader($"Select {string.Join(",", cols)} 					From HAProxyLogs.dbo.{tableName};");	using (SqlConnection dest = GetConn())	{	  dest.Open();				  using (SqlBulkCopy bc = new SqlBulkCopy(dest))	  {	    bc.BulkCopyTimeout = 5*60;		bc.BatchSize = 1048576;		bc.NotifyAfter = 100000;		bc.DestinationTableName = GetDestTableName(date);		bc.SqlRowsCopied += (e, s) => UpdatePB(s.RowsCopied, rowCount);		foreach (var c in cols)		{		  bc.ColumnMappings.Add(c, c);		}		try		{ 		  bc.WriteToServer(reader);		}		catch (Exception ex)		{		  Console.WriteLine(ex.Message);		}		finally		{		  reader.Close();		}	  }	  var destFinalCount = GetDestRowCount();	}  }}public SqlConnection GetConn() =>  new SqlConnection(new SqlConnectionStringBuilder  {	DataSource = "servername",	InitialCatalog = "TrafficLogs",	IntegratedSecurity = true}.ToString());public string GetTableName(DateTime dt) => $"Log_{dt:yyyy_MM_dd}";public string GetDestTableName(DateTime dt) => $"HAProxyLogs_{dt:yyyy_MM}";

if object_id('tempdb..#NewTableDetails') is not null    drop table #NewTableDetails create table #NewTableDetails (	TrafficLogOrigTable varchar(50), 	TrafficLogTotalRows bigint, 	OriginalLogDate date) if object_id('tempdb..#LogDetails') is not null    drop table #LogDetails create table #LogDetails (LogDate date, OriginalTotalRows bigint) declare @sql nvarchar(max) = ''declare @startdate datetime = '2019-10-01'  declare @enddate datetime = '2019-11-01' if object_id('tempdb..#dates') is not null    drop table #dates; CREATE TABLE #dates(  [Date] DATE PRIMARY KEY,  FirstOfMonth AS CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, [Date]), 0)),  OldTableName as concat('Log_', year([date]), '_', right('0' + rtrim(month([date])), 2), '_', right('0' + rtrim(day([date])), 2)),  NewTableName as concat('HAProxyLogs_', year([date]), '_', right('0' + rtrim(month([date])), 2))); INSERT #dates([Date]) SELECT dFROM(  SELECT d = DATEADD(DAY, rn - 1, @StartDate)  FROM   (    SELECT TOP (DATEDIFF(DAY, @StartDate, @enddate))       rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])    FROM sys.all_objects AS s1    CROSS JOIN sys.all_objects AS s2    ORDER BY s1.[object_id]  ) AS x) AS y; -- for each new table name get the count of rows, get list of OldTables -- Imported and then get the counts for each onedeclare @newtablename nvarchar(100)declare @date datetimedeclare newtable_cursor cursor for    select distinct NewTableName    from #dates    where [date] < @enddateopen newtable_cursor fetch next from newtable_cursor into @newtablename while @@FETCH_STATUS = 0begin    set @sql = 'insert into #NewTableDetails (TrafficLogOrigTable, TrafficLogTotalRows)                 select                     TrafficLogOrigTable = OriginalLogTable,                     TrafficLogTotalRows = count(*)                from TrafficLogs.dbo.[' + @newtablename +']                 group by OriginalLogTable ';     exec sp_executesql @sql     FETCH NEXT FROM newtable_cursor INTO @newtablenameendCLOSE newtable_cursor  DEALLOCATE newtable_cursor   update #NewTableDetailsset OriginalLogDate     = Cast(Replace(Replace(TrafficLogOrigTable, 'Log_', ''), '_', '-') as date) declare @oldTableName nvarchar(100)declare @oldtabledate datedeclare table_cursor cursor for    select OriginalLogDate, TrafficLogOrigTable    from #NewTableDetailsopen table_cursor fetch next from table_cursor into @oldtabledate, @oldTableName while @@FETCH_STATUS = 0begin    set @sql = 'insert into #LogDetails (LogDate, OriginalTotalRows)                 select                     LogDate = '''+ convert(varchar(10), @oldtabledate, 23) +''',                     OriginalTotalRows = count(*)                 from HAProxyLogs.dbo.[' + @oldTableName +']';    exec sp_executesql @sql      FETCH NEXT FROM table_cursor INTO @oldtabledate, @oldTableNameendCLOSE table_cursor  DEALLOCATE table_cursor   select     nt.OriginalLogDate,     nt.TrafficLogOrigTable,     nt.TrafficLogTotalRows,     ot.OriginalTotalRows,     ot.LogDate,     IsMatch = case when nt.TrafficLogTotalRows = ot.OriginalTotalRows then 'Y' else 'N' endfrom #NewTableDetails ntfull join #LogDetails ot    on nt.OriginalLogDate = ot.LogDateorder by nt.OriginalLogDate

<Query Kind="Program"> <NuGetReference>Dapper</NuGetReference> <Namespace>Dapper</Namespace></Query> void Main(){   DateTime date = new DateTime(2016, 07, 26);   while (date < DateTime.UtcNow)   {       MoveDate(date);       date = date.AddDays(1);   }} static readonly List<string> cols = new List<string> { "<col list>" }; public void MoveDate(DateTime date){   var tableName = GetTableName(date);   var destTable = GetDestTableName(date);   $"Attempting to migrate {date:yyyy-MM-dd} from {tableName}    		to {destTable}".Dump($"{date:yyyy-MM-dd}");    if (date >= new DateTime(2017, 01, 12))   {       cols.Add("new col1");       cols.Add("new col2");   }    using (var conn = GetConn())   {       int rowCount;       try       {           rowCount = conn.QuerySingle<int>($"Select Count(*) 		   				From HAProxyLogs.dbo.{tableName};");       }       catch (SqlException e)       {           ("  Error migrating: " + e.Message).Dump();           return;       }       $"  Summary for {date:yyyy-MM-dd}".Dump();       $"    {rowCount:n0} row(s) in {tableName}".Dump();       var pb = new Util.ProgressBar($"{tableName} (0/{rowCount})");       pb.Dump(tableName + " copy");        Func<int> GetDestRowCount = () 	    => conn.QuerySingle<int>($"Select Count(*) 			   		From {destTable} 					Where CreationDate >= @date 					  And OriginalLogTable = '{tableName}';", new { date });       Action<long, int> UpdatePB = (copied, total) =>       {           pb.Fraction = (double)copied / total;           pb.Caption = $"{tableName} ({copied}/{total})";       };        var destRowCount = GetDestRowCount();       $"There are  {destRowCount:n0} row(s) for table: {destTable}".Dump();        if (destRowCount > 0)       {           $"Rows found in destination table - aborting!".Dump();           return;       }        var sqlString = $"Select {string.Join(",", cols)}, OriginalLogTable = '{tableName}' 	   			From HAProxyLogs.dbo.{tableName};";       var reader = conn.ExecuteReader(sqlString);        using (SqlConnection dest = GetConn())       {           dest.Open();                 using (SqlBulkCopy bc = new SqlBulkCopy(dest))           {                             bc.BulkCopyTimeout = 15*60;               bc.BatchSize = 1048576;               bc.NotifyAfter = 100000;               bc.DestinationTableName = GetDestTableName(date);               bc.SqlRowsCopied += (e, s) => UpdatePB(s.RowsCopied, rowCount);                foreach (var c in cols)               {                   bc.ColumnMappings.Add(c, c);               }                try               {                   bc.ColumnMappings.Add("OriginalLogTable", "OriginalLogTable");                   bc.WriteToServer(reader);               }               catch (Exception ex)               {                   Console.WriteLine(ex.Message);               }               finally               {                   reader.Close();               }           }            // try updating stats after full dump to see if that stops time-outs           var statsString 		= $"waitfor delay '00:00:05'; 		    update statistics dbo.[{destTable}] [{destTable}_OriginalLogTable];";           conn.Query(statsString);            var destFinalCount = GetDestRowCount();           $"After migration there are {destFinalCount:n0} row(s) in {destTable}".Dump();       }   }}  public SqlConnection GetConn() =>   new SqlConnection("Data Source=servername;Initial Catalog=TrafficLogs;Trusted_Connection=True;Connection Timeout = 1500;"); public string GetTableName(DateTime dt) => $"Log_{dt:yyyy_MM_dd}";public string GetDestTableName(DateTime dt) => $"HAProxyLogs_{dt:yyyy_MM}";

declare @startDate datetime = '2015-11-01'declare @endDate datetime = '2015-12-01'
declare @OldTableName varchar(100)declare @NewTableName varchar(100)
declare @cols varchar(max) = 'comma, separated, col, list'declare @originalrowcount intdeclare @newrowcount intdeclare @finalrowcount intdeclare @rowcountsql nvarchar(max)
declare @insertsql nvarchar(max)
declare @statssql nvarchar(max)
while @startDate < @endDatebegin
set @OldTableName   = concat('Log_', year(@startDate), '_'	  , right('0' + rtrim(month(@startDate)), 2), '_'	  , right('0' + rtrim(day(@startDate)), 2))set @NewTableName   = concat('HAProxyLogs_', year(@startDate), '_'	  , right('0' + rtrim(month(@startDate)), 2))
print concat('Attempting to migrate ', convert(varchar(10), @startDate, 120)	  , ' from ', @OldTableName, ' to ', @NewTableName)print ' '
if @startDate >= '2017-01-12'  begin    if (charindex('new col1', @cols) = 0)  	  begin	    set @cols = concat(@cols, ', new col1')	  end
	if (charindex('new col2', @cols) = 0)	  begin	    set @cols = concat(@cols, ', new col2')	  end  end
set @rowcountsql = concat('select @cnt = count(*) from HAProxyLogs.dbo.[', @OldTableName, ']');exec sp_executesql @rowcountsql, N'@cnt int output', @cnt = @originalrowcount output;
/*print concat('Summary for ', convert(varchar(10), @startDate, 120)	  , ' ', @originalrowcount, ' rows in ', @OldTableName);print ' 'print concat('Starting copy of ', @OldTableName);print ' '*/
-- check if the new table has any rows in itset @rowcountsql   = concat('select @cnt = count(*) from ', @NewTableName  	  , ' where CreationDate >= ''', convert(varchar(10), @startDate, 120)	  , ''' and OriginalLogTable = ''', @OldTableName, ''';')--print @rowcountsqlexec sp_executesql @rowcountsql, N'@cnt int output', @cnt = @newrowcount output;
print concat('There are ', @newrowcount, ' row(s) for table: ', @NewTableName);print ' '
If @newrowcount > 0	begin		print 'Rows found in the destination table - aborting'		print ' '		break;	end
set @insertsql   = concat('insert into TrafficLogs.dbo.', @NewTableName, '(', @cols, ', OriginalLogTable) 	  select ', @cols, ', OriginalLogTable = ''', @OldTableName, '''  	  from HAProxyLogs.dbo.', @OldTableName);print @insertsqlprint ' 'exec sp_executesql @insertsql;
set @statssql   = concat('update statistics TrafficLogs.dbo.', @NewTableName  	  , ' ', @NewTableName, '_OriginalLogTable');print @statssqlprint ' 'exec sp_executesql @statssql
set @rowcountsql   = concat('select @cnt = count(*) from ', @NewTableName    	, ' where CreationDate >= ''', convert(varchar(10), @startDate, 120)		, ''' and OriginalLogTable = ''', @OldTableName, ''';')exec sp_executesql @rowcountsql, N'@cnt int output', @cnt = @finalrowcount output;
print concat('After migration there are ', @finalrowcount, ' row(s) in the ', @NewTableName);
If @originalrowcount <> @finalrowcount  begin    print 'final row does not match original stopping operation';	print ' '	break;  end
set @startDate = dateadd(day, 1, @startDate)
END

<#  .SYNOPSIS  Traffic Log Data Migration  .EXAMPLE  .\DataMigration.ps1 -ServerName name -StartDate 2019-01-01 -EndDate 2019-02-01#>[CmdletBinding()] #See http://technet.microsoft.com/en-us/library/hh847884(v=wps.620).aspx for CmdletBinding common parametersparam(  [parameter(Mandatory = $true)]  [string]$ServerName,  [parameter(Mandatory = $true)]  [DateTime]$StartDate,  [parameter(Mandatory = $true)]  [DateTime]$EndDate)# taken from https://blog.netnerds.net/2015/05/getting-total-number-of-rows-copied-in-sqlbulkcopy-using-powershell/$source = 'namespace System.Data.SqlClient{     using Reflection;  public static class SqlBulkCopyExtension  {    const String _rowsCopiedFieldName = "_rowsCopied";    static FieldInfo _rowsCopiedField = null;    public static int RowsCopiedCount(this SqlBulkCopy bulkCopy)    {        if (_rowsCopiedField == null) _rowsCopiedField 			= typeof(SqlBulkCopy).GetField(_rowsCopiedFieldName, BindingFlags.NonPublic 			    | BindingFlags.GetField | BindingFlags.Instance);                   return (int)_rowsCopiedField.GetValue(bulkCopy);    }  }}'Add-Type -ReferencedAssemblies 'System.Data.dll' -TypeDefinition $source$null = [Reflection.Assembly]::LoadWithPartialName("System.Data")Function ConnectionString([string] $ServerName, [string] $DbName){  "Data Source=$ServerName;Initial Catalog=TrafficLogs;Trusted_Connection=True;Connection Timeout = 2000;"}Function GetOriginalTableRowCount($sqlConnection, $table){  $sqlConnection.open();  $OriginalRowCountCmd = "select OriginalCount = count(*) from " + $table;  $SqlCmd = New-Object  System.Data.SqlClient.SqlCommand($OriginalRowCountCmd, $sqlConnection)  $SqlCmd.CommandTimeout = 0;  $row_count = [Int64] $SqlCmd.ExecuteScalar()  $sqlConnection.Close();  return $row_count}Function GetNewTableRowCount($sqlConnection, $table){  $sqlConnection.open();  $RowCountCmd = "select NewCount = count(*) from " + $table   		+ " where CreationDate >= '"+($i.ToString("yyyy-MM-dd"))		  +"' and OriginalLogTable = 'Log_"+($i.ToString("yyyy_MM_dd"))+"'";  $SqlCmd = New-Object  System.Data.SqlClient.SqlCommand($RowCountCmd, $sqlConnection)  $SqlCmd.CommandTimeout = 0;  $row_count = [Int64] $SqlCmd.ExecuteScalar()  $sqlConnection.Close();  return $row_count}$cols = "col1", "col2", "col3", "...";for ($i = $StartDate; $i -lt $EndDate; $i = $i.AddDays(1)){  $MigrationStart = Get-Date  Write-Host "Migration started at: $MigrationStart"  $OldTableName = "HAProxyLogs.dbo.Log_"+($i.ToString("yyyy_MM_dd"))  $NewTableName = "TrafficLogs.dbo.HAProxyLogs_"+($i.ToString("yyyy_MM"))  Write-Host "Attempting to migrate "($i.ToString("yyyy_MM_dd"))" from " $OldTableName  " to " $NewTableName;  # build the connection string and open it  $SrcConnStr = ConnectionString $ServerName  $SrcConn  = New-Object System.Data.SqlClient.SQLConnection($SrcConnStr)  # whats the row count on the original table in HAProxyLogs  $originalRowCount = GetOriginalTableRowCount $SrcConn $OldTableName  Write-Host "Summary for " ($i.ToString("yyyy-MM-dd"))":   	  	There are "$originalRowCount" rows in table "$OldTableName  # make sure there is no data in the table currently for the day  $newRowCount = GetNewTableRowCount $SrcConn $NewTableName  if($newRowCount -gt 0){    Write-Host "Rows found in the destination table - aborting migration"    break;  }  else {    Write-Host "No data for the date in the destination table - continuing migration"  }  # select the data we need from the original table  $selectCmdText = "select "+ ($cols -join ', ')   		  +", OriginalLogTable = 'Log_"+($i.ToString("yyyy_MM_dd"))		  +"' from "+$OldTableName;  $SqlCommand = New-Object system.Data.SqlClient.SqlCommand($selectCmdText, $SrcConn)  $SqlCommand.CommandTimeout = 0;  $SrcConn.Open()  [System.Data.SqlClient.SqlDataReader] $SqlReader = $SqlCommand.ExecuteReader()  $destConnection = ConnectionString $ServerName  $bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($destConnection)  $bulkCopy.BatchSize = 1048576  $bulkCopy.BulkCopyTimeout = 0 # 20*60  $bulkCopy.DestinationTableName = $NewTableName  $bulkCopy.NotifyAfter = 500000  # taken from https://blog.netnerds.net/2015/05/getting-total-number-of-rows-copied-in-sqlbulkcopy-using-powershell/  $bulkCopy.Add_SqlRowscopied(    {Write-Host "$($args[1].RowsCopied)/$originalRowCount rows copied 	    from $OldTableName to $NewTableName" })  foreach ($col in $cols){    $bulkCopy.ColumnMappings.Add($col,$col)  }  try {    Write-Host "Starting Migration"    $bulkCopy.ColumnMappings.Add("OriginalLogTable", "OriginalLogTable");    $bulkCopy.WriteToServer($SqlReader)    $total = [System.Data.SqlClient.SqlBulkCopyExtension]::RowsCopiedCount($bulkcopy)    Write-Host "$total total rows written"  }  catch {    $ex = $_.Exception    Write-Error $ex.Message    While($ex.InnerException)    {      $ex = $ex.InnerException      Write-Error $ex.Message    }  }  Finally  {    $SqlReader.Close()    $bulkCopy.Close()    $SrcConn.Close()  }  # once done update statistics on the new table  $statsCmdText = "update statistics "+$NewTableName  	  	+" HAProxyLogs_"+($i.ToString("yyyy_MM"))+"_OriginalLogTable";  Invoke-DbaQuery -SqlInstance $ServerName -Query $statsCmdText;  Write-Host "After migration there are $total rows   		were written to $NewTableName table from $OldTableName";  if($originalRowCount -ne $total){    Write-Host "total rows migrated does not match. Stopping operation"    break;  }  $total = 0;}

alter database tempdb 	modify file (NAME = tempdev, FILENAME = 'F:\Data\tempdev.mdf', SIZE = 20000MB);alter database tempdb 	modify file (NAME = templog, FILENAME = 'F:\Data\templog.ldf', SIZE = 1000MB);alter database tempdb 	modify file (NAME = tempdev2, FILENAME = 'F:\Data\tempdev2.mdf', SIZE = 20000MB);alter database tempdb 	modify file (NAME = tempdev3, FILENAME = 'F:\Data\tempdev3.mdf', SIZE = 20000MB);alter database tempdb 	modify file (NAME = tempdev4, FILENAME = 'F:\Data\tempdev4.mdf', SIZE = 1000MB);

declare @from int                             declare @leap int                             declare @to int                             declare @datafile varchar(128)declare @cmd varchar (512)declare @starttime datetimedeclare @endtime datetime /*settings*/      set @from = 13533200            /*Current size in MB*/set @to = 11600000              /*Goal size in MB*/ set @datafile = 'HAProxyLogs1'  /*Datafile name*/set @leap =  10000            	/*Size of leaps in MB*/ while ((@from - @leap) > @to)                                 begin         set @starttime = getdate()         print concat('started: ', @starttime)                set @from = @from - @leap        set @cmd = 'DBCC SHRINKFILE (' + @datafile +', ' + cast(@from as varchar(20)) + ')'        print @cmd        exec(@cmd)        print '==>    SHRINK SCRIPT - '+ cast ((@from-@to) as  varchar (20)) + 'MB LEFT'                        set @endtime = getdate()         print concat('ended: ', getdate(), ' took :' , datediff(minute, @starttime, @endtime))    end set @cmd =  'DBCC SHRINKFILE (' + @datafile +', ' + cast(@to as varchar(20)) + ')'print @cmdexec(@cmd)

use [master]
alter database [TrafficLogs] modify file(name= TrafficLogs_Current, filename = 'E:\Data\TrafficLogs_Current.mdf');
alter database [TrafficLogs] modify file(name= TrafficLogs_log, filename = 'E:\Data\TrafficLogs_log.ldf');