SQL Server 2008 - create database script (schema + data) with command-line

In SSMS when I click on the database "Tasks -> Generate Scripts", I get an output script.

How do I do the same with a non-graphical tool?

Answers


The graphical tool is just a wrapper around the SMO classes that actually implement scripting, like the Scripter class. There is an example of scripting all tables in a database with SMO in MSDN: Scripting:

//Connect to the local, default instance of SQL Server. 
{ 
   Server srv = default(Server); 
   srv = new Server(); 
   //Reference the AdventureWorks database. 
   Database db = default(Database); 
   db = srv.Databases("AdventureWorks"); 
   //Define a Scripter object and set the required scripting options. 
   Scripter scrp = default(Scripter); 
   scrp = new Scripter(srv); 
   scrp.Options.ScriptDrops = false; 
   scrp.Options.WithDependencies = true; 
   //Iterate through the tables in database and script each one. Display the script. 
   //Note that the StringCollection type needs the System.Collections.Specialized namespace to be included. 
   Table tb = default(Table); 
   Urn[] smoObjects = new Urn[2]; 
   foreach ( tb in db.Tables) { 
      smoObjects = new Urn[1]; 
      smoObjects(0) = tb.Urn; 
      if (tb.IsSystemObject == false) { 
         StringCollection sc = default(StringCollection); 
         sc = scrp.Script(smoObjects); 
         string st = null; 
         foreach ( st in sc) { 
            Console.WriteLine(st); 
         } 
      } 
   } 
} 

There are many more examples how to use it on various other sites.


You can use powershell to do this. An example here for scripting tables. http://www.simple-talk.com/sql/sql-tools/using-powershell-to-generate-table-creation-scripts/ I'm guessing that it should be possible to extend for other types of database object.

Edit Just noticed that the title says data as well as schema. I'm not aware of anything free that does this from the command line. Redgate SQL Compare Suite is automatable from the command line if you buy the right version or you could write an application/power shell script to do it.


For the data, you can use a bulk export utility called bcp which allows you to dump data from SQL Server tables into files, e.g. a CSV file, or a tab-delimited file.

I don't know of any SQL Server supplied utility that would create SQL scripts with INSERT statements as the SQL Server Management Studio does.


You can script schema and data using the SQL Server publishing wizard. This can be done from the command line. Version 1.4 is the one you want it's bundled with Visual Studio as of version 2008 and also with SQL Server 2008. You can find it in Program Files/Microsoft SQL server/90/Tools/Publishing/1.4/SqlPubWiz I beleive it's also a project on CodePlex.

Type SQlPubWiz /? to see command line options


I wrote an open source command line utility named SchemaZen that does this. It's much faster than scripting from management studio and it's output is more version control friendly. It supports scripting both schema and data.

To generate scripts run:

schemazen.exe script --server localhost --database db --scriptDir c:\somedir

Then to recreate the database from scripts run:

schemazen.exe create --server localhost --database db --scriptDir c:\somedir

Microsoft released a new tool last week called mssql-scripter. It's the command line version of the 'Generate Scripts' wizard in SSMS. The tool is a Python-based, open source command line tool and you can find the official announcement here. Essentially, the scripter allows you to generate T-SQL scripts (DDL and DML) for your database/database object as a .sql file. Here's a quick usage example to get you started:

$ pip install mssql-scripter
# script the database schema and data piped to a file.
$ mssql-scripter -S localhost -d AdventureWorks -U sa --schema-and-data  > ./adventureworks.sql

More usage examples are on our GitHub page here: https://github.com/Microsoft/sql-xplat-cli/blob/dev/doc/usage_guide.md


There is ready to use free open source command line schema export tool at http://exportsqlscript.codeplex.com/.

Command line driven utility to export MS SQL objects to script files suitable for database creation and revision control. Uses 2008R2 Server Management Objects (SMO) which are compatible with SQL Server 2000, SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2.

Had to install Windows Installer 4.5, .NET Framework 3.5 and Shared Management Objects with System CLR Types from SQL Server 2008 R2 Feature Pack to make it work.

Working export command example for SQL Server 2005:

ExportSQLScript.exe . dldb /ot:Tree /xt:UserDefinedTableTypes

you can download the powershell script

https://gallery.technet.microsoft.com/SCRIPTING-DB-DB-OBJECTS-DB-81bba072

the powershell script will script out db,db objects,db permissions, sql logins, sql permissions,sql jobs, sql linked server,sql mails, sql server triggers

$path = "E:\pruthvi\pruthvi\"

$servername="SRVBLRDBATST98\MSSQLSERVER1"
$query=@"
set nocount off

IF OBJECT_ID(N'tempdb..##temp1') IS NOT NULL
     DROP TABLE ##temp1

create table ##temp1(query varchar(1000))

insert into ##temp1 
select 'use '+db_name() +';'

insert into ##temp1 
select 'go'

/*creating database roles*/
insert into ##temp1
                    select 'if DATABASE_PRINCIPAL_ID('''+name+''')  is null exec sp_addrole '''+name+''';'  from sysusers
where issqlrole = 1 and (sid is not null and sid <> 0x0)

/*creating application roles*/
insert into ##temp1
                    select 'if DATABASE_PRINCIPAL_ID('+char(39)+name+char(39)+')
                    is null CREATE APPLICATION ROLE ['+name+'] WITH DEFAULT_SCHEMA = ['+
                    default_schema_name+'], Password='+char(39)+'Pass$w0rd123'+char(39)+' ;'
 from sys.database_principals
where type_desc='APPLICATION_ROLE'

insert into ##temp1 
                     select  
                                case  
                                          when state_desc='GRANT_WITH_GRANT_OPTION' 
                                                       then
                                                                substring (state_desc,0,6)+' '+permission_name+' to '+'['+USER_NAME(grantee_principal_id)+']'+' WITH 
GRANT OPTION ;'

                                                         else 
                                                                  state_desc+' '+permission_name+' to '+'['+USER_NAME(grantee_principal_id)+']'+' ;'
                    END
from sys.database_permissions 
where class=0 and USER_NAME(grantee_principal_id) not in ('dbo','guest','sys','information_schema')

insert into ##temp1 
                    select 
                               case 
                                         when state_desc='GRANT_WITH_GRANT_OPTION' 
                                                   then
                                                             substring (state_desc,0,6)+' '+permission_name+' on '+OBJECT_SCHEMA_NAME(major_id)+'.'+OBJECT_NAME
(major_id)
                                                             +' to '+'['+USER_NAME(grantee_principal_id)+']'+' with grant option ;'
                                                     else 
                                                              state_desc+' '+permission_name+' on '+OBJECT_SCHEMA_NAME(major_id)+'.'+OBJECT_NAME(major_id)
                                                              +' to '+'['+USER_NAME(grantee_principal_id)+']'+' ;'
                                  end
from sys.database_permissions where class=1 and USER_NAME(grantee_principal_id) not in ('public');

 insert into ##temp1 
                      select 
                                 case 
                                           when state_desc='GRANT_WITH_GRANT_OPTION' 
                                                     then
                                                              substring (state_desc,0,6)+' '+permission_name+' ON schema::['+sa.name+
                                                               '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                                       else
                                                               state_desc+' '+permission_name+' ON schema::['+sa.name+
                                                               '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                       COLLATE LATIN1_General_CI_AS  
                                      end
from sys.database_permissions dp inner join sys.schemas sa on
 sa.schema_id = dp.major_id where dp.class=3

 insert into ##temp1 
                     select 
                                 case 
                                            when state_desc='GRANT_WITH_GRANT_OPTION'
                                             then
                                                    substring (state_desc,0,6)+' '+permission_name+' ON APPLICATION  ROLE::['+sa.name+
                                                     '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                             else
                                                      state_desc+' '+permission_name+' ON  APPLICATION ROLE::['+sa.name+
                                                      '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                      COLLATE LATIN1_General_CI_AS  
                         end
from sys.database_permissions dp inner join sys.database_principals  sa on
 sa.principal_id = dp.major_id where dp.class=4 and sa.type='A'

 insert into ##temp1 
                      select 
                                 case 
                                          when state_desc='GRANT_WITH_GRANT_OPTION' 
                                           then
                                                  substring (state_desc,0,6)+' '+permission_name+' ON   ROLE::['+sa.name+
                                                  '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                           else
                                                   state_desc+' '+permission_name+' ON   ROLE::['+sa.name+
                                                    '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                     COLLATE LATIN1_General_CI_AS  
                                           end
 from sys.database_permissions dp inner join
sys.database_principals  sa on sa.principal_id = dp.major_id 
 where dp.class=4 and sa.type='R'

 insert into ##temp1 
                      select 
                                  case 
                                           when state_desc='GRANT_WITH_GRANT_OPTION' 
                                                       then
                                                               substring (state_desc,0,6)+' '+permission_name+' ON ASSEMBLY::['+sa.name+
                                                                '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                                        else
                                                                state_desc+' '+permission_name+' ON ASSEMBLY::['+sa.name+
                                                                 '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                                 COLLATE LATIN1_General_CI_AS  
                                       end
 from sys.database_permissions dp inner join sys.assemblies sa on
 sa.assembly_id = dp.major_id 
 where dp.class=5

 insert into ##temp1
                     select 
                                 case 
                                           when state_desc='GRANT_WITH_GRANT_OPTION' 
                                            then
                                                    substring (state_desc,0,6)+'  '+permission_name+' ON type::['
                                                    +SCHEMA_NAME(schema_id)+'].['+sa.name+
                                                    '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                            else
                                                    state_desc+' '+permission_name+' ON type::['
                                                    +SCHEMA_NAME(schema_id)+'].['+sa.name+
                                                     '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                     COLLATE LATIN1_General_CI_AS  
                                              end
 from sys.database_permissions dp inner join sys.types sa on
 sa.user_type_id = dp.major_id 
 where dp.class=6


 insert into ##temp1
                      select 
                                 case 
                                          when state_desc='GRANT_WITH_GRANT_OPTION' 
                                           then
                                                     substring (state_desc,0,6)+'  '+permission_name+' ON  XML SCHEMA COLLECTION::['+
                                                     SCHEMA_NAME(SCHEMA_ID)+'].['+sa.name+'] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                            else
                                                     state_desc+' '+permission_name+' ON  XML SCHEMA COLLECTION::['+
                                                     SCHEMA_NAME(SCHEMA_ID)+'].['+sa.name+'] to ['+user_name(dp.grantee_principal_id)+'];'
                                                     COLLATE LATIN1_General_CI_AS  
                                   end
 from sys.database_permissions dp inner join sys.xml_schema_collections sa on
 sa.xml_collection_id = dp.major_id 
 where dp.class=10



insert into ##temp1
                    select
                               case 
                                         when state_desc='GRANT_WITH_GRANT_OPTION' 
                                          then
                                                   substring (state_desc,0,6)+'  '+permission_name+' ON message type::['+sa.name+
                                                    '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                           else
                                                    state_desc+' '+permission_name+' ON message type::['+sa.name+
                                                    '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                     COLLATE LATIN1_General_CI_AS  
                                             end
 from sys.database_permissions dp inner join sys.service_message_types sa on
 sa.message_type_id = dp.major_id 
 where dp.class=15


 insert into ##temp1
                      select 
                                  case 
                                            when state_desc='GRANT_WITH_GRANT_OPTION' 
                                              then
                                                       substring (state_desc,0,6)+'  '+permission_name+' ON contract::['+sa.name+
                                                        '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                                else
                                                         state_desc+' '+permission_name+' ON contract::['+sa.name+
                                                         '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                         COLLATE LATIN1_General_CI_AS  
                                   end
 from sys.database_permissions dp inner join sys.service_contracts sa on
 sa.service_contract_id = dp.major_id 
 where dp.class=16



  insert into ##temp1
                      select 
                                 case 
                                           when state_desc='GRANT_WITH_GRANT_OPTION' 
                                            then
                                                      substring (state_desc,0,6)+'  '+permission_name+' ON SERVICE::['+sa.name+
                                                        '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                              else
                                                       state_desc+'  '+permission_name+' ON SERVICE::['+sa.name+
                                                        '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                        COLLATE LATIN1_General_CI_AS  
                                    end
 from sys.database_permissions dp inner join sys.services sa on
 sa.service_id = dp.major_id 
 where dp.class=17


 insert into ##temp1 
                      select 
                                   case 
                                              when state_desc='GRANT_WITH_GRANT_OPTION'
                                               then
                                                          substring (state_desc,0,6)+'  '+permission_name+' ON REMOTE SERVICE BINDING::['+sa.name+
                                                          '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                                 else
                                                          state_desc+' '+permission_name+' ON REMOTE SERVICE BINDING::['+sa.name+
                                                           '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                          COLLATE LATIN1_General_CI_AS  
                                      end
 from sys.database_permissions dp inner join sys.remote_service_bindings sa on
 sa.remote_service_binding_id = dp.major_id 
 where dp.class=18

 insert into ##temp1
                      select
                                  case 
                                            when state_desc='GRANT_WITH_GRANT_OPTION'
                                              then
                                                        substring (state_desc,0,6)+'  '+permission_name+' ON route::['+sa.name+
                                                        '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                                else
                                                          state_desc+' '+permission_name+' ON route::['+sa.name+
                                                          '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                         COLLATE LATIN1_General_CI_AS  
                                      end
 from sys.database_permissions dp inner join sys.routes sa on
 sa.route_id = dp.major_id 
 where dp.class=19

 insert into ##temp1 
                      select 
                                 case 
                                           when state_desc='GRANT_WITH_GRANT_OPTION' 
                                            then
                                                     substring (state_desc,0,6)+'  '+permission_name+' ON FULLTEXT CATALOG::['+sa.name+
                                                      '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                             else
                                                       state_desc+' '+permission_name+' ON FULLTEXT CATALOG::['+sa.name+
                                                       '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                        COLLATE LATIN1_General_CI_AS  
                                       end
 from sys.database_permissions dp inner join sys.fulltext_catalogs sa on
 sa.fulltext_catalog_id = dp.major_id 
 where dp.class=23

  insert into ##temp1 
                      select 
                                 case 
                                           when state_desc='GRANT_WITH_GRANT_OPTION'
                                            then
                                                        substring (state_desc,0,6)+'  '+permission_name+' ON SYMMETRIC KEY::['+sa.name+
                                                        '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                             else
                                                        state_desc+' '+permission_name+' ON SYMMETRIC KEY::['+sa.name+
                                                        '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                        COLLATE LATIN1_General_CI_AS  
                                             end
 from sys.database_permissions dp inner join sys.symmetric_keys sa on
 sa.symmetric_key_id = dp.major_id 
 where dp.class=24

 insert into ##temp1 
                      select 
                                  case 
                                           when state_desc='GRANT_WITH_GRANT_OPTION' 
                                             then
                                                       substring (state_desc,0,6)+'  '+permission_name+' ON certificate::['+sa.name+
                                                        '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                               else
                                                          state_desc+' '+permission_name+' ON certificate::['+sa.name+
                                                          '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                           COLLATE LATIN1_General_CI_AS  
                                   end
 from sys.database_permissions dp inner join sys.certificates sa on
 sa.certificate_id = dp.major_id 
 where dp.class=25


 insert into ##temp1 
                     select 
                                 case 
                                          when state_desc='GRANT_WITH_GRANT_OPTION' 
                                          then
                                                     substring (state_desc,0,6)+'  '+permission_name+' ON ASYMMETRIC KEY::['+sa.name+
                                                     '] to ['+user_name(dp.grantee_principal_id)+'] with grant option ;'
                                             else
                                                      state_desc+' '+permission_name+' ON ASYMMETRIC KEY::['+sa.name+
                                                       '] to ['+user_name(dp.grantee_principal_id)+'] ;'
                                                       COLLATE LATIN1_General_CI_AS  
                        end
 from sys.database_permissions dp inner join sys.asymmetric_keys sa on
 sa.asymmetric_key_id = dp.major_id 
 where dp.class=26

insert into ##temp1 
                     select  'exec sp_addrolemember ''' +p.NAME+''','+'['+m.NAME+']'+' ;'
FROM sys.database_role_members rm
JOIN sys.database_principals p
ON rm.role_principal_id = p.principal_id
JOIN sys.database_principals m
ON rm.member_principal_id = m.principal_id
where m.name not like 'dbo';




 select * from ##temp1  
"@


 $loginscript=@"
IF ((SELECT convert(int,substring(@@VERSION,21,5)))<2008)

       begin
             set nocount on
             declare @table table (query varchar(max))
                     insert into @table 
                                 select 'CREATE LOGIN '+QUOTENAME(name)+' FROM WINDOWS WITH                                      DEFAULT_DATABASE='+QUOTENAME(default_database_name)+' ;'
                                 from sys.server_principals where type in('U','G')

                     insert into @table 
                     select 'CREATE LOGIN ' + QUOTENAME(name)+
                     ' WITH PASSWORD =' +CONVERT(varchar(max), LOGINPROPERTY(name, 'PasswordHash'),1 )+' HASHED ,SID='+'0x' + CAST('' as XML).value('xs:hexBinary(sql:column("sid"))', 'varchar(MAX)')+', default_database=['+default_database_name+'],'+
                     case when is_policy_checked=0 then 'CHECK_POLICY = OFF' when is_policy_checked=1 then 'CHECK_POLICY = ON ' end+
                     case when is_expiration_checked=0 then ' , CHECK_EXPIRATION = OFF ' when is_policy_checked=1 then ', CHECK_EXPIRATION = ON' end+
                     '; '+case when is_disabled=1 then 'ALTER LOGIN ['+name+ '] DISABLE;' when is_disabled=0 then ' ' end
                     from sys.sql_logins  where name not like '%##%' and name not like '%sa%'

              select * from  @table 

    end 


   else 

       begin
             set nocount on
             declare @table1 table (query varchar(max))
                     insert into @table1
                                 select 'CREATE LOGIN '+QUOTENAME(name)+' FROM WINDOWS WITH DEFAULT_DATABASE='+QUOTENAME(default_database_name)+' ;'
                                 from sys.server_principals where type in('U','G')

                     insert into @table1
                                 select 'CREATE LOGIN ' + QUOTENAME(name)+
                                 ' WITH PASSWORD =' +CONVERT(varchar(max), LOGINPROPERTY(name, 'PasswordHash'),1 )+'    HASHED ,SID='+CONVERT(varchar(max), sid, 1)+', default_database=['+default_database_name+'],'+
                                 case when is_policy_checked=0 then 'CHECK_POLICY = OFF' when is_policy_checked=1 then 'CHECK_POLICY = ON ' end+
                                 case when is_expiration_checked=0 then ' , CHECK_EXPIRATION = OFF ' when is_policy_checked=1 then ', CHECK_EXPIRATION = ON' end+
                                 '; '+case when is_disabled=1 then 'ALTER LOGIN ['+name+ '] DISABLE;' when is_disabled=0 then ' ' end
                                 from sys.sql_logins where name not like '%##%' and name not like '%sa%'
              select * from  @table1 
      end
"@

$serverperm=@"
set nocount on

                 IF OBJECT_ID(N'tempdb..##servrole') IS NOT NULL
                     DROP TABLE ##servrole
             CREATE TABLE ##servrole (query varchar(1000))

                      insert into ##servrole
                      select 'use master;'
                      insert into ##servrole
                      select ' exec sp_addsrvrolemember '''+m.name+''','+p.name+';' FROM sys.server_role_members rm
                               JOIN sys.server_principals p
                               ON rm.role_principal_id = p.principal_id
                               JOIN sys.server_principals m
                               ON rm.member_principal_id = m.principal_id
                               where m.name not in ('sa','dbo','entity owner','information_schema','sys','public');
                        insert into ##servrole
                                select 
                                       case when sp.state_desc='GRANT_WITH_GRANT_OPTION' then
                                        substring (state_desc,0,6)+' '+permission_name+' to ['+srp.name+'] with grant option ;'
                                 else 
                                        state_desc+' '+permission_name+' to ['+srp.name+'] ;'
                                  end
                                  from sys.server_permissions sp
                                  join sys.server_principals srp on  sp.grantee_principal_id=srp.principal_id
                                  where srp.name not like '%##%' and
                                  srp.name not in ('sa','dbo','entity owner','information_schema','sys')

                                  and sp.type not in ('COSQ','CO');
        select query as ' ' from ##servrole where query is not null;
         go
        drop table ##servrole
          go

"@



[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $servername






$dbs=$s.Databases

foreach ($db in $dbs)

{

       $dbname = "$db".replace("[","").replace("]","")

       $dbpath = "$path"



       $db.script()| out-file $path$dbname.txt

        foreach ($tables in $db.tables)
        {
          $tables.script() |out-file $path$dbname.txt -append

              foreach ($index in $tables.Indexes)
                  {
                      $index.script() |out-file $path$dbname.txt -append

                   }

        }



        bcp "use $db ;select definition from sys.sql_modules " queryout $path$dbname"1".txt -c -t -T  -S  $servername




       gc $path$dbname.txt,$path$dbname"1".txt | out-file $path$dbname"2".txt



        $myTable=Invoke-Sqlcmd -Query $query -ServerInstance $servername -database $dbname
        $myTable|Format-Table -AutoSize|Out-String -Width 8192 |out-file $path$dbname"2".txt -append


       rm $path$dbname.txt,$path$dbname"1".txt


} 


$srv=new-object  "Microsoft.SqlServer.management.smo.server" $servername
$srv.JobServer.Jobs|%{$_.script()}|out-file $path"agentjobs".txt
$srv.LinkedServers|%{$_.script()}|out-file $path"linkedservers".txt
$srv.backupdevices|%{$_.script()}|out-file $path"backupdevices".txt
$srv.mail|%{$_.script()}|out-file $path"mail".txt
$srv.triggers|%{$_.script()}|out-file $path"servertriggers".txt

$myTable=Invoke-Sqlcmd -Query $loginscript -ServerInstance $servername -database master
$myTable|Format-Table -AutoSize|Out-String -Width 8192 |out-file $path"serverlogins".txt -append


$servperm=Invoke-Sqlcmd -Query $serverperm -ServerInstance $servername -database master
$servperm|Format-Table -AutoSize|Out-String -Width 8192 |out-file $path"serverpermission".txt -append

Need Your Help

convert array from inside of returned object into select item list js

jquery html json html-select

I've looked at several examples here on how to do this and the answer still alludes me.

Slickgrid inside jquery accordion columns not displaying properly

javascript jquery slickgrid

I am using slickgrid inside a jquery accordion and whenever the page refreshes and the accordion is expanded the columns inside the grid are all out of order and destroyed. I tried using