I am making backup for my tables in the database and I am generating scripts (from task) in SQL Server Management Studio.

There's about 30-40 tables that I am interested to backup including constraint,indexes, etc. I'm not doing a full backup. There's like 300 tables in the database. The entire database is so big and doing a full backup would take way too long, hence only backup certain tables.

However, in "Save or Publish script" screen, it's stuck there for very long claiming the tables are "preparing". Is there any solution to this?

Is it normal to wait that long? (1 hour)

  • What is the wait type for your session? For fullbackup how long is too long? Have you considered a combination of full and diff backup. Have you considered some of the built-in optimization switch? – SqlWorldWide Sep 28 '17 at 10:33
  • 3
    Is this question the direct result of your attempts to apply sepupic's answer to your previous question? If so, what is it that you are actually trying to do? The wording of your other question seems to suggest as if you want to create new copies of existing tables in the same database, but now you are talking about backup. So what is your goal? Is it one or the other? Or is it something else entirely? – Andriy M Sep 28 '17 at 11:32
  • To clarify: You are using "Tasks" -> "Generate Scripts" to create scripts that will recreate certain tables from your (very large) database, both the table structure, and the current table data, correct? I don't think I've ever seen this hang for an hour (much less hang for an hour then complete successfully). I have seen it fail when just scripting out the structure alone of a very large database (hundreds of tables, most with triggers, indexes, etc.) due to a lack of sufficient memory. – RDFozz Sep 28 '17 at 15:08
  • RDFozz, Yes that's exactly what I am doing. It's just weird to see the process stuck there for a very long time. I thought i might done something wrong but i highly doubt it. – Lord Jesus Sep 29 '17 at 1:56
  • Andriy, Yes it's a direct result to sepupic's solution. My goal is to create a backup of certain tables from an existing database with tables. To put it in a simpler way, I want to clone some of the existing tables in the current database with all the same constraint,indexes,triggers,stats and etc. I'll probably store the "clone" tables in the same database as the current one. Is it possible or it's better to store new ones in a seperate database? – Lord Jesus Sep 29 '17 at 1:59

An alternative to scripting objects using SSMS is to use Powershell.

Open a Windows command prompt and fire up Powershell

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null

#The SQL Server instance to want to connect to - This is the default instance on my computer
$server = New-Object Microsoft.SqlServer.Management.Smo.Server(".");

#The database you want to script objects from
$database = $server.Databases["Test"];

#Instantiating the Powershell Scripter object 
#so we can easily define how we want to script the object
$scripter = new-object Microsoft.SqlServer.Management.Smo.Scripter($server);

#Do we want Batch Terminators in the script? (GO statements)
$scripter.options.ScriptBatchTerminator = $true

#Where do we want to store file containing the scripted objects?
$scripter.options.FileName = "C:\Scripts\ScriptedTables.sql"

#Don't display the objects we're scripting, just output them to the file
$scripter.options.ToFileOnly = $true

#Script any permissions defined on the objects
$scripter.options.Permissions = $true

#Script all Dri objects 
#For more innformation about DriAll - (https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.scriptingoptions_properties.aspx)
$scripter.options.DriAll = $true

#Script table triggers
$scripter.options.Triggers = $true

#Script table indexes
$scripter.options.Indexes = $true

#A comma separated list of the tables you want to script
$table = $database.Tables["account","DateRange","DateDimension"];

#Invoke the scripting method to produce the output script file

For more information about the available Powershell scriping options, check here.

Not the answer you're looking for? Browse other questions tagged or ask your own question.