Easy way to copy & empty a DB

Question

Post reply

Easy way to copy & empty a DB

a_ud

SSCrazy

Points: 2457
More actions
April 2, 2015 at 7:49 am

#299249

Some help needed here.
I'd like to create a blank copy of a SSIS 2005 back-end (that is a full copy, then empty the tables, to have the DB as it was on day 1). The DB has a standard no. of elements (tables, views, SPs, possibly more).
I'm no expert so pls no fancy suggestions on using restores that don't exist, or the like. All I'd like is a way to do it that requires little expertise. The only ones I can think of are:
1. Do a backup, then restore on a new different DB. Then delete some tables manually (with manual deletes).
2. Take a snapshot (not sure if the procedure afterwards is pretty much the same as with a backup).
I 'm not sure where the db files are, so I immagine the option of 'copy file and create a new DB with that file' is not available.
Thanks in advance, A.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

You are on the right track, i'd say;

i would go with the backup-restore-delete specifics route, same as you are thinking.

it guarantees to not miss any objects, and there's always certain tables (status tables, zip codes, cities, other lookup tables) that need to exist with their values.

that way you can concentrate on deleting the tables that are related to processes.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

a_ud SSCrazy Points: 2457 More actions · Answer 2

Thanks Lowell, that really helps.

In case I decided to go the snapshot track using a script like:

CREATE DATABASE <name>

ON <file_spec1> {,file_spec2}

AS SNAPSHOT OF <source_db_name>

my questions would be:

-Does the snapshot miss any elements (say, SPs)?

-I take <name> is the name of the new DB, and <source_db_name>, that of the old, but what is <file_spec1>? The complete file (with path and file name) of the .mdf & .ldf files? Just one of them?

Lowell SSC Guru Points: 323518 More actions · Answer 3

well, a snapshot is a readonly version of the existing database at the point in time you create the snapshot;

so it's basically still the original database, but saying ignore all changes in the log as of this snapshot date.

that wouldn't help you, really, since you can't alter or modify the data in snapshot right?

so you still need to backup and resotre so you have somethign you can fiddle with and remoe extraneous data.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

a_ud SSCrazy Points: 2457 More actions · Answer 4

Snapshot discarded then !! I've got the impression that this must be a lot simpler, though?

E.g, I've noticed that in SQL Management Studio if you right-click the DB, then Tasks>Generate scripts, then choose the desired objects, every component can be replicated (at least in theory ??).

Isn't this a method to script the whole DB? I assume once you've got it all you need is to change the name of the file (main .dbf + log .dbf), and then you'd be fine?

Thanks, p.

Lowell SSC Guru Points: 323518 More actions · Answer 5

in a general sense yes,it's probably good for 90% of what you need, but depending on your settings for your scripting options things can NOT be scripted. that's why a backup and restore is the best....you are guaranteed that no matter your script settings, you get an exact copy.the backup is an

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 6

If you don't want data, I'd say use the native scripting features to script the database and build the objects (or use something like SQL Compare).

I might still do this and if I needed specific table data moved, I'd use the Import/Export wizard to move limited data.

Disclosure, I work for Red Gate.