SQL Server 2012 copy database without data

SQL Server 2012 copy database without data

回答1

1. Right click your database in your server, and select Tasks > Generate Scripts.
2. At Introduction step, click Next.
3. At Choose Objects, you can either select either the entire database (including functions, and etc) or specify what objects you want to generate. In your case, you should select Script entire databases and all database objects then hit Next.
4. Choose your file name and destination then click next
5. Review and then click next
6. Done!

You will see a .sql file generated in your specified folder. You can open that file in other servers and hit execute, it will then generate a exact same database.

 

回答2

You are missing the most important answer here: Introduced with MSSQL 2014 SP2 (2016-Jul-11), but later added to MSSQL 2012 with SP4 (2017-Oct-05) - the fastest and easiest way of preparing empty clone of your database is achievable via a DBCC CLONEDATABASE. Then according to the books online:

• Creates a new destination database that uses the same file layout as the source but with default file sizes from the model database.
• Creates an internal snapshot of the source database.
• Copies the system metadata from the source to the destination database.
• Copies all schema for all objects from the source to the destination database.
• Copies statistics for all indexes from the source to the destination database.

SYNTAX:

DBCC CLONEDATABASE   
(  
     [Source_DB_Name]
    ,[Target_DB_Name]
)
WITH NO_STATISTICS, NO_QUERYSTORE, VERIFY_CLONEDB, BACKUP_CLONEDB

REMARKS:

• The source database must be a user database. Cloning of system databases (master, model, msdb, tempdb, distribution database etc.)
  isn't allowed.

• The source database must be online or readable.

• A database that uses the same name as the clone database must not already exist.

• The command isn't in a user transaction.

• Requires SA server role

Here is a nice MS article about how to use it.

Second option:

Is to use a PowerShell module called dbatools.io which can be sourced directly form the project website or from the official PowerShell Gallery.

Once you have it, you can use this command:

Invoke-DbaDbClone 
    [-SqlInstance] <DbaInstanceParameter[]>] 
    [-SqlCredential] <PSCredential>] 
    [-Database] <String[]>] 
    [-InputObject] <Database[]>] 
    [-CloneDatabase] <String[]>] 
    [-ExcludeStatistics] 
    [-ExcludeQueryStore] 
    [-UpdateStatistics] 
    [-EnableException] 
    [-WhatIf] 
    [-Confirm] [<CommonParameters>]

As the dbatools.io is an open source project you can see what exactly happens in the background thanks to their publicly available GitHub repo.

And what you will find there is that they use DBCC CLONEDATABASE() to perform the core of this operation (but giving you ability to do much more).

 

回答3

You could generate the script for the database objects by right clicking it in Management Studio, then going to Tasks, Generate Scripts, choosing the objects you want to script, such as Tables, Views, etc. and there you have it.

 

 

作者:Chuck Lu    GitHub