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开发者_如何学Python the same with a non-graphical tool?
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
加载中,请稍侯......
精彩评论