December 17, 2002 at 8:38 pm
Is there any way to create an Empty database with the same structure & everything but without data from the specified Original Database automatically?
Thanks a lot.
December 17, 2002 at 8:57 pm
No, but you could script the entire db and rerun the script in a new db. Pretty simple.
Steve Jones
December 17, 2002 at 9:47 pm
quote:
No, but you could script the entire db and rerun the script in a new db. Pretty simple.Steve Jones
http://www.sqlservercentral.com/columnists/sjones
It may work if the stored procedures & triggers, views, functions are not encrypted, but if so, how can script out these things?
December 17, 2002 at 9:56 pm
quote:
No, but you could script the entire db and rerun the script in a new db. Pretty simple.Steve Jones
http://www.sqlservercentral.com/columnists/sjones
good idea
i work hard to finish my dream.
December 18, 2002 at 4:21 am
One option you have as long as you are sure the encrypted items do not use the 3 or 4 part name to reference objects (server.db.owner.object or db.owner.object) is this. All DBs are based on the template of the model db in the SQL install. If you could use the copy objects wizard to copy the items into model without the data. Thus you now have a template DB without data and all the objects. I am sure you may run into some issue so test into a blank DB before trying into model to make sure of all the things you have to do to be successful. I would also go ahead and set DBOptions and filegrowth options in model unless these may always vary.
December 18, 2002 at 4:35 am
One comment about scripting, if the model has already been modified with additional tables/procs your new db will have that stuff PLUS whatever you add to it. If you want exactly the same objects as the one you're copying from, you may need to drop objects as well.
If you're not sure which objects you need to copy and you have more than a few, you should consider a diff tool. I use SQL Compare, there are others.
Andy
December 23, 2002 at 8:36 am
If you truly want to have a db with no data you should be able to restore a db to a new db, with the data, and run a script like this:
use xyz_db
go
SET NOCOUNT ON
DECLARE @table_nm VARCHAR(40),
@cmd VARCHAR(500)
SELECT name
INTO #temp
FROM sysobjects
WHERE type = 'U'
ORDER BY name
WHILE EXISTS (SELECT 1 FROM #temp)
begin
SET ROWCOUNT 1
SELECT @cmd = 'TRUNCATE TABLE ' + name, @table_nm = name FROM #temp ORDER BY name
SET ROWCOUNT 0
PRINT 'SP_EXECUTESQL @stmt = ' + @cmd
SP_EXECUTESQL @stmt = @cmd
DELETE FROM #temp WHERE name = @table_nm
end
go
DROP TABLE #temp
go
Jeff
December 23, 2002 at 1:37 pm
But would this bypass the foreign key constraints?
Edited by - tgrignon@compusense.com on 12/23/2002 1:38:50 PM
December 27, 2002 at 12:22 pm
Can you see my red face?
You are quite right. A TRUNCATE TABLE would hit the FKs. It only side steps the triggers. The other option would be to write a script to delete any FKs before the truncate and put them back after (too bad you can't disable them for deletes). If I were to do this I would probably hack some code out of the sp_foreignkeys stored proc to build the FK creation script since you, no doubt, will be getting into compound column references.
The other option would be to do deletes. The issue here is that you will hit any delete triggers, you have to think about log issues and it will probably be slow. Here is the code (working up the constraint hierarchy) if you take this approach:
USE xyz_db
go
SET NOCOUNT ON
DECLARE @table_nm VARCHAR(256),
@cmd NVARCHAR(500),
@delete_stmt CHAR(11),
@delete_ord INT,
@row_cnt INT
CREATE TABLE #table (
id INT NOT NULL,
name VARCHAR(256) NOT NULL,
delete_ord INT NULL )
SELECT @delete_ord = 1, @row_cnt = 1, @delete_stmt = 'DELETE FROM'
-- get all of the tables that aren't referenced
INSERT INTO #table
SELECT so.id,
so.name,
1
FROM sysobjects so
WHERE so.type = 'U'
AND NOT EXISTS (
SELECT 1
FROM sysreferences sr
WHERE sr.rkeyid = so.id)
-- work through the remaining tables working your way up the constraint hierarchy
WHILE @row_cnt > 0
begin
SELECT @delete_ord = @delete_ord + 1
INSERT INTO #table
SELECT so.id,
so.name,
@delete_ord
FROM sysobjects so
WHERE so.type = 'U'
AND NOT EXISTS (
SELECT 1
FROM #table t1
WHERE t1.id = so.id )
AND EXISTS (
SELECT 1
FROM sysreferences sr,
#table t2
WHERE t2.id = sr.fkeyid
AND sr.rkeyid = so.id)
SELECT @row_cnt = @@rowcount
end
SELECT @delete_ord = @delete_ord + 1
-- catch any remaining tables
INSERT INTO #table
SELECT so.id,
so.name,
@delete_ord
FROM sysobjects so
WHERE so.type = 'U'
AND NOT EXISTS (
SELECT 1
FROM #table t1
WHERE t1.id = so.id )
-- start deleting data
WHILE EXISTS (SELECT 1 FROM #table)
begin
-- get a table to delete data from (starting with the child tables)
SET ROWCOUNT 1
SELECT @cmd = @delete_stmt + ' ' + name, @table_nm = name FROM #table ORDER BY delete_ord, name
-- delete the data in batches of 5000 so as not to overload the logs
SET ROWCOUNT 5000
SELECT 'SP_EXECUTESQL @stmt = ' + @cmd
WHILE @@rowcount > 0
begin
EXECUTE SP_EXECUTESQL @stmt = @cmd
end
-- delete the table just processed
DELETE FROM #table WHERE name = @table_nm
end
DROP TABLE #table
go
December 30, 2002 at 5:44 pm
Are you creating this new database on the same server...or a server available to DTS? Would it be possible to perform a DTS export and just not copy the data? Perhaps I am missing the point.
December 31, 2002 at 5:17 am
guarddata you're correct, that would be the simplest way to do it. Even if you want to create the empty database on another server... create the empty one locally and then run a full database backup on it. You can then transport this backup file to where ever you like and restore it on to the sql server
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply