T SQL Information schema?

Question

We have a lot of data and to save time on making backups I have been tasked with creating a copy of the current database which only contains specified company codes/companies.

I have been told to research into TSQL scripts, information schemas and bulk copies.Im just wondering what the best route is to go down and how to make a start I do have the latest database copy locally.

The script would work by

Allow us at the top to specify name of new database and a list of company codes or ids from company table.

And create a new database and copy over only the data that is relevant

I think you are over complicating things. You should try to keep your backup process simple and standard so that it just works. How much data do you actually have and how long does it currently take to backup? Have you considered using a combination of full, differential and transaction log backups? What about replication? It's not a backup, of course, but it achieves a similar result. — acfrancis, Oct 21 '13 at 15:25
Ive just been told to do this it doesn't mean I believe it is necessary.Maybe I havent explained it well enough , what I want is to do a back up schema script — user2520671, Oct 21 '13 at 15:31
"Back up schema script" suggests you only want to back up the table definitions, not the actual data in them. Is that what you mean? If you just want to selectively copy some of the data, you probably need BCP but look at this comparison: http://technet.microsoft.com/en-us/library/ms175937.aspx — acfrancis, Oct 21 '13 at 15:46

score 1 · Accepted Answer · answered Nov 19 '13 at 18:00

Probably not even close to the best way... but 1 of 100 ways you can do it.

declare @databasename nvarchar(100) = 'roflcopter'
, @destination = 'myschema.mytable'
declare @companylist nvarchar(max) = (select stuff( select ',' +convert(nvarchar(5),companyid) from companytable where database = @databasename),1,1,'')

declare @query nvarchar(max) 
= N'use ['+@databasename+']
insert into '+@destination+' /* maybe add dynamic columns here */
select /*same dynamic columns */
from mytable /*or another variable*/
where companyid in('''+@companylist+''')
'
exec (@query)

add your other variables and such to declare destination, future database name (if creating) and anything you want... dynamic sql can be slow, cumbersome and does reconnect. Might want to check the sp_executesql for differences between exec () and executesql

there are a ton of system procedures... ones that find FKs, column names, data types, pks... you can complicate it by building dynamically creating your current schema using (no help from me here) those system procedures, C# to easily 'generate scripts' and execute them.

May also be in the wrong direction you were thinking of.

T SQL Information schema?

1 Answers1