I am working through an older Wrox title "Beginning SQL Programming" and am having trouble copying a database. The book suggests making a copy of Northwind so that the copy can be altered without affecting the original data.

I have tried the Database Copy Wizard, but this only works if you are copying between servers. I want my copy on the same server, since I am only running one.

Any suggestions???

Hi there,

The easiest way to make a backup is to use the Export wizard. First, create a new, empty, default database on your server. Then right-click the database you want to back up and choose All Tasks | Export Data... Follow the Wizard and choose your new database on the Destination tab. Near the end, you get to select what you want to back up. Leave it set to all objects to back up, well,.... all objects ;) (including tables, sprocs, views etc). You can also select whether you want to copy the data as well.

The Wizard allows you to save the package, so you can run it again later.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Thanks for your help!

Hi!

How can i automate the procedure you explained above?
I need to create a daily copy of an existing database to another and the current date should also be stored in the name of the new database, i.e. 011404-database.

After seven days the oldest database should be overwritten.

So i have seven copies of one database.

is this possible (with sql-commands)?

Thanks for your help

Jan

Hi there,

Well, maybe this is possible, but it isn't going to be easy.

It should be relatively easy to create a new database based on the current date using a Script task that executes a SQL statement to create a new DB. I don't think you can create the new database from within regular DTS tasks, like Execute SQL because you need the current date somehow in your (dynamic) SQL statement. Then again, I could be wrong.

After the database has been created, the problems start. You need to pass the name of the database to the new connection objects. Again, not sure if you can do that dynamically, but maybe you can pass global parameters, for example.

But before you get into this too much: What you need sounds like a regular backup.... Aren't you better off with the standard backup procedures from SQL Server or from third party software?

Imar



---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Thanks for your fast reply.

Our customer wants a copy of the database every day beside the original db.

This is, if one of his employees deletes an entry or something else goes wrong he can easily recover the data from the backup db created the day before.

I dont know if the standard backup procedures are capable of this?

May be you know a tool which can handle this?

Or should i say, it isn't possible so i had to do it manually each day...think my customer wouldn't be happy about that.

Thanks again

Jan

Well, there may be another option.

Create 7 individual Copy packages, called Monday, Tuesday, Wednesday and so on. Each package copies the entire source database to a data with the same name as the package (Monday, Tuesday, etc).

Then you can schedule each package to run on the appropriate day, e.g. you'll want to run the Monday package on Tuesday, at 12:00:01 in the morning, for example.

This way, you have 7 concurrent databases that represent each day of the week. Not a pretty solution but it may do what you need.

There are backup packages, like ArcServe that (used to) do this. I think ArcServe for early versions of SQL Server (6.5) was really smart as it could restore individual tables, sprocs, etc. I never worked much with newer versions, but it may be worth checking it out at http://ca.com/software/brightstor/

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Hi again,

you said, the export wizard creates a package for future use.

Assume, I create this seven packages you mentioned with the export wizard.

Now, how can I execute a package via the system-scheduler?

Since I have no knowledge about the enterprise manager, it may has its own scheduler?

Could u give me a hint on how to do this?

THX!

Jan

Yes, it does. SQL Server comes with SQL Server Agent, that allows you to schedule packages.

Open the Enterprise Manager and then open Management. You'll see SQL Server Agent. You can open its properties (right-click and choose Properties) and check that it is configured correctly (account name, auto restart etc).

Then under SQL Server Agent you'll see Jobs. That allows you to create a new job that runs at an interval you can define (every second, once a month, once, etc).

Look under Services in the Windows Administrative tools to make sure that the SQL Server Agent starts when Windows starts.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

If you go to "Database Maintenance Plans" and follow the wizard for a new maintenance plan, you can set up an automatic backup, that will create full backups that are datetime stamped (on the file name) in the directory of your choosing. You can configure it to delete backups that are older than X weeks as well.

Peter
------------------------------------------------------
Work smarter, not harder.