Automated Restore

Hi,

I have 2 databases, one called "ManagementDB" and the other called "ManagementDBYesterday".

Basically I get asked very frequently to look at yesterdays data for various reasons and I thought it'd be a good idea to always have yesterdays data to hand instead of restoring all the time.

I have tried DTS but this takes too long, I've also thought of replication however the database is modified too frequently for this to be easliy implemented.

Restoring from a backup is quite quick so I thought if I could somehow automate this, I'd have a readily available copy of yesterdays data to hand.

I have a mon-sun daily backup so I need to write some SQL script to restore from the correct backup to the "yesterday" database.

I've only used restore via the enterprise manager and I was wondering if anyone here could help.

kind regards,

Mike,You need to create a scheduled job that will execute something like this:

restore database ManagementDBYesterday
from disk = 'backup_location\ManagementDB.bak' with recovery,
move 'ManagementDB_Data' to 'data_location\ManagementDBYesterday_Data.ndf',
move 'ManagementDB_Primary' to 'data_location\ManagementDBYesterday_Primary.mdf',
move 'ManagementDB_Log' to 'log_location\ManagementDBYesterday_Log.ldf'
If you have different logical file names for each database, you may also want to do this for clarity:

alter database ManagementDBYesterday
modify file (name=ManagementDB_Primary, newname=ManagementDBYesterday_Primary)

alter database ManagementDBYesterday
modify file (name=ManagementDB_Data, newname=ManagementDBYesterday_Data)

alter database ManagementDBYesterday
modify file (name=ManagementDB_Log, newname=ManagementDBYesterday_Log)

Automated Backup and restoring to a new machine

Hi all,
I am not sure if this is the correct place to ask this quesion. The problem
i am trying to solve is basically this. We do a nightly complete backup of
our database. The next day we copy the database to a different machine
restore it and run jobs on this database. I would like to automate this
process, but don't know the best solution.
We have 3 machines here. Server A is the main producation db machine, server
B is our backup machine and server C is where we restore the backed up DB and
run the jobs. Can someone please suggest what in theri opinion would be a
good solution for this. Thanks
Poorav
You might want to investigate log shipping for this functionality which
requires Enterprise Edition. However, you can use scripts off the Resource
Pack or off some internet sites to achieve the same effect if you only have
Standard Edition. The principle is not difficult - you have a backup job on
the principal server and you need a share to copy files to and a job on the
standby server to restore the database and subsequent log files('WITH NO
RECOVERY').
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Auto populated field

Hello,

I have SQL Server Server Man Studio Express 2005, currently having a problem with an auto populated field.

Basically I have a number populated everytime a new asset is added to my database, but at the moment the firled does not increment by 1 as I would like it to. Seems to assign the same number as a item already in the database and I have to go into the back end and change it manually.

Anyone know how this is easly sorted, the asset ID is not the primary key. Just for your info at the moment 'Identity Spec' is set to 'NO'.

Many thanks, Andrew

How are you currently trying to auto populate the number if Identity is set to No?

--Uncle Pete

|||This is a good question, sorry i only recently started using this software as inherited it of another person so very new to it.|||I have just checked and it will not allow me to change the Identity Spec to 'Yes'? Default value or binding is set to ((0)).|||

What is the datatype of the field?

If it is set to INT then you should be able to set the identity to yes.

|||

Hello, yes it is set to INT but dosen't seem that I can alter it?

|||

If you look at the identity field, you will see a plus sign, expand that. There you will be able to select Yes and set the seed value and increment. Be sure to set the seed value higher than what ever the highest current value is.

Also I see you said that default was set to (0), delete that, as it will conflict with the indentity.

|||

Thank you for your reply but I still cannot change the Identity Spec field?

It is setup the follwoing way:

Allow Nulls: Yes

Datat Type: int

Value or binding: ((0))

Condensed data type: int

Deterministic: Yes

Indexable: Yes

Full text Spec: No

Identity Spec: NO

Size: 4

everything else set to No or blank.

Many thanks, Andrew