-1

I to perform an operation a part of which has me looking for a way to create a copy of SQL DB on same server. I tried the suggestion given at Copy SQL Server database with PowerShell script . However the resulting copy is about a quarter size of the actual DB.

Ideas anyone?

Thanks

samthesorcerer
  • 1
  • 3
  • Database size has little to do with its actual contents. Databases are often created at specific size, and most of the contents are empty. Scripted DB is likely to be of the actual data size, unless you've scripted creation statements to include file sizes. – vonPryz Dec 05 '19 at 12:33
  • You are looking for a way to automate this via script? Otherwise a quick Google search yields many sources of information how to do this, like: https://stackoverflow.com/questions/3829271/how-can-i-clone-an-sql-server-database-on-the-same-server-in-sql-server-2008-exp – robbpriestley Dec 05 '19 at 15:21
  • @robbpriestley yes I am trying to automate this. Like I stated, this just small part of a larger operation I need to perform. – samthesorcerer Dec 06 '19 at 04:35
  • @vonPryz May I ask you to elaborate on what do you mean when you say "Databases are often created at specific size, and most of the contents are empty. Scripted DB is likely to be of the actual data size". Thanks – samthesorcerer Dec 06 '19 at 04:36
  • Sql Server aims to minimize disk operations, as disk is slow. It's more efficient to allocate, say, 100 GB of disk space to database data, even if most of it is not used. When data is added some day, neither Windows nor Sql Server is required to do extra housekeeping to expand the data file. – vonPryz Dec 06 '19 at 07:26

3 Answers3

1

If your PowerShell solution is working except you are noticing a file-size discrepancy with the newly copied database compared to the source database, it may not be an actual problem.

SQL Server database and log sizes are variable and are typically not an exact indication of the amount of data they contain. The copied database may be "optimized" in terms of its disk file usage in a way that the source database is currently not.

There are three things you can do to convince yourself you have a working solution.

  1. Run the Shrink command on both of the databases to free space and see if the resulting disk files are more similar in terms of size https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkdatabase-transact-sql?view=sql-server-ver15
  2. Write a benchmark script that compares record counts on all the tables. If you see a discrepancy in the record count, you know you have a problem.

Example benchmark script:

declare @sourceCount int;
declare @copyCount int;

set @sourceCount = (select count(*) from SourceDb.dbo.SomeTable);
set @copyCount = (select count(*) from CopyDb.dbo.SomeTable);

if @sourceCount <> @copyCount
begin
    select 'PROBLEM!'
end

-- Now repeat for all the other tables
  1. Use a SQL data comparison tool to automate the method in step 2. above. There are many such tools including the one built-in to Visual Studio 2019 (Tools menu) or otherwise: https://learn.microsoft.com/en-us/sql/ssdt/how-to-compare-and-synchronize-the-data-of-two-databases?view=sql-server-ver15

However, all these methods will only work reliably if you can ensure that either database isn't receiving updates during the copy/benchmarking process. If someone is accessing one or the other of the databases independently while you are measuring, and they alter the data independently while you are measuring, your results would be invalidated, possibly without your knowing.

robbpriestley
  • 3,050
  • 2
  • 24
  • 36
0

EDIT

I managed to make it work as soon as I have started using the SqlServer module instead of the SQLPS module, because the latter had long been deprecated. I edited the answer I am referring to in my initial post, below.

I was having a similar error implementing this. Tried literally everything, it just wouldn't work. What did work for me, was generating a script through the ScriptTransfer method, create the new database and then apply the script to the new database through Invoke-SqlCmd. I have posted a detailed explanation and code in this answer.

ccoutinho
  • 3,308
  • 5
  • 39
  • 47
-1

Okay. I managed to implement this. And anybody who needs to do this in future, please try this:

Import-Module SQLPS -DisableNameChecking

$SQLInstanceName = "$env:COMPUTERNAME\sqlexpress"
$SourceDBName   = "xxx"
$CopyDBName = "${SourceDBName}_copy"
$Server  = New-Object -TypeName 'Microsoft.SqlServer.Management.Smo.Server' -ArgumentList $SQLInstanceName
$SourceDB = $Server.Databases[$SourceDBName]
$CopyDB = New-Object -TypeName 'Microsoft.SqlServer.Management.SMO.Database' -ArgumentList $Server , $CopyDBName

# Delete any existing copy
Try
{
    Invoke-Sqlcmd -ServerInstance "$SQLInstanceName" -Query "Drop database $CopyDBName;" -Username "***" -Password "****" -Verbose
}
Catch
{
    Write-Output 'Failed to delete database'
}

$CopyDB.create()

$ObjTransfer = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Transfer -ArgumentList $SourceDB
$ObjTransfer.DestinationDatabase = $CopyDBName
$ObjTransfer.DestinationServer = $Server.Name
$ObjTransfer.DestinationLoginSecure = $true
$ObjTransfer.CopyData = $true
$ObjTransfer.CopyAllObjects = $false
$ObjTransfer.CopyAllDatabaseTriggers = $true
$ObjTransfer.CopyAllDefaults = $true
$ObjTransfer.CopyAllRoles = $true
$ObjTransfer.CopyAllRules = $true
$ObjTransfer.CopyAllSchemas = $true
$ObjTransfer.CopyAllSequences = $true
$ObjTransfer.CopyAllSqlAssemblies = $true
$ObjTransfer.CopyAllSynonyms = $true
$ObjTransfer.CopyAllTables = $true
$ObjTransfer.CopyAllViews = $true
$ObjTransfer.CopyAllStoredProcedures = $true
$ObjTransfer.CopyAllUserDefinedAggregates = $true
$ObjTransfer.CopyAllUserDefinedDataTypes = $true
$ObjTransfer.CopyAllUserDefinedTableTypes = $true
$ObjTransfer.CopyAllUserDefinedTypes = $true
$ObjTransfer.CopyAllUserDefinedFunctions = $true
$ObjTransfer.CopyAllUsers = $true
$ObjTransfer.PreserveDbo = $true
$ObjTransfer.Options.AllowSystemObjects = $false
$ObjTransfer.Options.ContinueScriptingOnError = $true
$ObjTransfer.Options.IncludeDatabaseRoleMemberships = $true
$ObjTransfer.Options.Indexes = $true
$ObjTransfer.Options.Permissions = $true
$ObjTransfer.Options.WithDependencies = $true
$ObjTransfer.TransferData()
samthesorcerer
  • 1
  • 3