February 11, 2013 at 11:13 am
I want to create empty copies of all the databases on my server, complete with table, sps, functions, users, permissions and jobs. I just don't want any data in it.
I figure I have a few options
1) Script everything with the Generate Scripts command database by database, then do the jobs. Leave it at that.
2) Use scripts to create empty copies of databases, backup databases and use backups when needed
Is there a way to just create empty backups?
Is there a way to export the structure to another instance of SQL Server with no data?
I'm still in the middle of searching, but I haven't found anything better than the scripting yet.
I like the idea of then using the scripts and backing up the results as SQL backups because then I'll have the opportunity to test the scripts.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
February 11, 2013 at 11:23 am
another way is to backup and restore the database, then delete everything in foreign key hierarchy order in the new copy.
also, i know you said no data, but from experience when i've done this, there are always lookup tables that should not have the data deleted...statuses, list of states, etc.
here's a solution from an old post i did on the same subject; it's still using sysreferences, the deprecated view for foreign keys.
note if you have circular references in your schema, you will need to stop this loop manually.
CREATE TABLE #Skipme(TableName varchar(255))
INSERT INTO #Skipme
SELECT 'tbCity' UNION ALL
SELECT 'tbState' UNION ALL
SELECT 'tbCounty' UNION ALL
SELECT 'OtherLookupTables'
INSERT INTO #Skipme
SELECT name from sys.objects
where LEFT(name,2) = 'LU'
OR LEFT(name,2) = 'TB'
declare @level int
set @level = 0
create table #tables (
id int not null primary key clustered,
TableName varchar(255) not null,
Level int not null)
insert into #tables (id, TableName, Level)
select object_id, '[' + schema_name(schema_id) + '].[' + rtrim(name) + ']' as TableName, 0
from sys.tables where type = 'U'
while @@rowcount > 0 begin
set @level = @level + 1
update rt set Level = @level
from #tables rt
inner join sysreferences fk on fk.rkeyid = rt.id
inner join #tables ft on ft.id = fk.fkeyid
where ft.Level = @level - 1
end
print 'USE ' + DB_NAME() + '
'
select 'TRUNCATE TABLE ' + TableName
from #tables
where level = 0
And TableName Not In (SELECT TableName from #Skipme)
select 'DELETE ' + TableName
from #tables
where level > 0
And TableName Not In (SELECT TableName from #Skipme)
order by level
drop table #tables
drop table #Skipme
Lowell
February 11, 2013 at 12:38 pm
I think I've got it.
There's an SSIS tool called Transfer SQL Server Object. I can transfer just about everything I want with this. I can use other steps in an SSIS package to move any of the data I want moved from lookup tables and such as Lowell suggested above.
I think I still have to transfer anything server level separately, like jobs and server level permissions, but it works well for everything else, a full database at a time.
The only thing you have to watch out for is creating the databases in the proper order if you have a view or a key that relies on a different database.
If I run into any difficulties with this approach, I'll post it here.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply