restrict deletion

What would be the best practice to prevent users who didn't create a record in sql from deleting? When a record is created I have the username who created the record in one of the fields. I was thinking maybe a query?

Thank you in advance.

I have used same practice.|||Thank you Javier.|||

Hi fpena,

You can just compare the username again to the current user to see if he is the owner of this record. If yes, delete it. All this can be done in a single stored procedure.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

|||could you point me on the right direction? Thank you.|||

Hi fpena,

You can use a single sentence to achieve that. Assume that there is a field in the table named RecordOwner, and your stored procedure passes in a parameter named @.Deleter. You can use

DELETE FROM Table1 WHERERowID=@.RowID ANDRecordOwner=@.Deleter

You can check the AffectedRows in your code. If it is 0, it means that the row does not exist or the deleter is not the owner, so that he cannot delete it.

|||i'm not sure i can accomplish what i'm looking for with that. i have a gridview visible to everyone all records visible no matter what userID owns the record what i'm trying to do is prevent a user who doesn't own a record from deleting when trying to delete. hope my question is clear enough. Thank you.|||

Hi fpena,

I'm sure that this will do what you need. You just need to modify the delete command you're currently using to check the ownership info.

If you have any questions on how to do this, please feel free to reply to my post.

|||

how is it done?

Restoring/moving model?

Hi
I want to alter Model so that its transaction logs and
datafiles are on different drives,so that new dbs created
on this server get the same configuration.
When I try to restore Model with a with_move option it
says I can't do that.
How can I acheive what I want?
Would remaning Model, and the creating a new db called
Model in the config I want work?Have a look at this old post :-
http://groups.google.co.uk/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=co4joN3eCHA.1308%40cpmsftngxa08
--
HTH
Ryan Waight, MCDBA, MCSE
"felix" <felix@.hotmail.com> wrote in message
news:007a01c38ce8$52b4d570$a301280a@.phx.gbl...
> Hi
> I want to alter Model so that its transaction logs and
> datafiles are on different drives,so that new dbs created
> on this server get the same configuration.
> When I try to restore Model with a with_move option it
> says I can't do that.
> How can I acheive what I want?
> Would remaning Model, and the creating a new db called
> Model in the config I want work?
>|||To move the model database, SQL Server must be started
with trace flag 3608 so that
it does not recover any database except
the master.
NOTE: You will not be able to access any user databases at
this time.
You should not perform any operations
other than the steps below while using
this trace flag.
To add trace flag 3608 as a SQL Server startup
parameter:
After adding trace flag 3608, perform the following
steps:
1. Stop and restart SQL Server.
2. Detach the model database as follows:
use master
go
sp_detach_db 'model'
go
3. Move the Model.mdf and Modellog.ldf files from D:\Mssql7
\Data to
E:\Sqldata(or any other drives).
4. Reattach the model database as follows:
use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\mod
ellog.ldf'
go
--e:\ or any other drives
5. Remove the -T3608 trace flag from the startup
parameters box in the
Enterprise Manager.
6. Stop and restart SQL Server. You can verify the change
in
file locations using
sp_helpfile:
use model
go
sp_helpfile
go
Koohyar
This posting is provided "AS IS" with no warranties, and
confers no rights.
http://www.microsoft.com/info/cpyright.htm
>--Original Message--
>Hi
>I want to alter Model so that its transaction logs and
>datafiles are on different drives,so that new dbs created
>on this server get the same configuration.
>When I try to restore Model with a with_move option it
>says I can't do that.
>How can I acheive what I want?
>Would remaning Model, and the creating a new db called
>Model in the config I want work?
>.
>|||To add to the other responses, the default location of new database data
and log files is not determined by the model database file locations.
The default file locations can be specified via Enterprise Manager under
server properties --> database settings.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"felix" <felix@.hotmail.com> wrote in message
news:007a01c38ce8$52b4d570$a301280a@.phx.gbl...
> Hi
> I want to alter Model so that its transaction logs and
> datafiles are on different drives,so that new dbs created
> on this server get the same configuration.
> When I try to restore Model with a with_move option it
> says I can't do that.
> How can I acheive what I want?
> Would remaning Model, and the creating a new db called
> Model in the config I want work?
>|||Thanks Dan,
Much more simple than I thought then! Your suggestion
worked a treat.
Felix
>--Original Message--
>To add to the other responses, the default location of
new database data
>and log files is not determined by the model database
file locations.
>The default file locations can be specified via
Enterprise Manager under
>server properties --> database settings.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>--
>SQL FAQ links (courtesy Neil Pike):
>http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
>http://www.sqlserverfaq.com
>http://www.mssqlserver.com/faq
>--
>"felix" <felix@.hotmail.com> wrote in message
>news:007a01c38ce8$52b4d570$a301280a@.phx.gbl...
>> Hi
>> I want to alter Model so that its transaction logs and
>> datafiles are on different drives,so that new dbs
created
>> on this server get the same configuration.
>> When I try to restore Model with a with_move option it
>> says I can't do that.
>> How can I acheive what I want?
>> Would remaning Model, and the creating a new db called
>> Model in the config I want work?
>
>.
>

Restoring Tran. Log files

I need to restore a database on my test server from that on my production
server.
Here's what i've done so far:
1) Created a new db on my testing server named 'main_test'
2) Restored 'main_test' using .bak file from my production db.
Now, I have several transaction log files I wan to incorporate as well.
When I use Enterprise Manager and select my first trn log file and select
'Transaction Log' on the 'General' tab, I get the following error:
"The preceeding resotre operation did not specify WITH NORECOVERY or WITH
STANDBY....."
Should I use Query Analyzer for this? What is the syntax?Eric,
After applying the trnasaction log, go to "options" tab and select "Leave
database nonoperational, but able to restore additional transaction logs", o
r
"Leave database operational. No additional transaction logs can be restored"
if you are restoring the last transaction log backup.
AMB
"Eric" wrote:

> I need to restore a database on my test server from that on my production
> server.
> Here's what i've done so far:
> 1) Created a new db on my testing server named 'main_test'
> 2) Restored 'main_test' using .bak file from my production db.
> Now, I have several transaction log files I wan to incorporate as well.
> When I use Enterprise Manager and select my first trn log file and select
> 'Transaction Log' on the 'General' tab, I get the following error:
> "The preceeding resotre operation did not specify WITH NORECOVERY or WITH
> STANDBY....."
> Should I use Query Analyzer for this? What is the syntax?
>|||Correction,

> After applying the trnasaction log, go to "options" tab and select "Leave
Before applying ...
AMB
"Alejandro Mesa" wrote:
> Eric,
> After applying the trnasaction log, go to "options" tab and select "Leave
> database nonoperational, but able to restore additional transaction logs",
or
> "Leave database operational. No additional transaction logs can be restore
d"
> if you are restoring the last transaction log backup.
>
> AMB
> "Eric" wrote:
>

Restoring to New server

I have created a new server running sql server 6.5 and I am trying to restore a databasefrm an existing 6.5 server to this new server . I got the master on but I cannot get any of the databases on. First the database was going in suspend mode, then after I resolved that I am getting size errors that I cannot seem to resolve even with expanding the devices.

What I'm I doing wrong? Did I miss a step? or is there a problem with the server setup?

Ans..Q1 What I'm I doing wrong?

A1 Maybe nothing (insufficient information).

Was the master backup used in the restore to the second server in fact current (assuming the restores (DB Loads) were performed to similar devices on the new server - using similar device orders, sizes and types, as those of the DB devices as they existed on the original server)? If not, some device data may be incorrect (most likely, incomplete). In that case it may be possible to use Disk ReInit for each device on the new server as necessary to address the issue (using the same logical and physical device names is easier if that is the case; however the 2k block size value entered for the size parameter must be the same as the production user devices at the times their backup dumps were taken.). Once complete, run Disk ReFit.

Q2 Did I miss a step?

A2 Maybe, maybe not (insufficient information). However, do make sure both servers have:
similar character set installs (collation),
similar service pack levels applied,
similar database device geometries.

Q3 or is there a problem with the server setup?

A3 To help rule out some potential issues with the server setup, do a dump and load of Pubs on the new server; if Pubs restores without issues, the basic server configuration is probably not the main issue.

Restoring to new database doesn't change logical DB name

I created a new database from a restore backup method. The database file names did get updated with the new database name, but the logical file name still remains the old file name.
Do we have to manually change the logical file name in the new database?
what is the use of logical name?
Please help!

The logical file will not change while u restore a db. you have to change it explicitly

alter database gpx mODIFY FILE (NAME=oldLogicalFilename,NEWNAME=NewLogicalFilename)

Madhu

|||

what is the use of logical name?

The name to represent the database data file. The logical name for each file is contained in the name column

The logical design of the database, including the tables and the relationships between them, is the core of an optimized relational database. A good logical database design can lay the foundation for optimal database and application performance. A poor logical database design can hinder the performance of the whole system.

Restoring the backedup database

Dear All,

I had created the backup for the exisiting datbase by writing the schedule which triggers at the particular time on a day. The backup format i had used is .bak. Now i want to restore these backup files into another database (fake database). When i am trying to restore the backup data, an error stating 'The backup set holds the backup of other than exsting <Database Name> database' is displayed and the restore operation is terminating abnormally. kindly give the excat soultion for this problem..

Regards,Try to rstore with the option "Force restore over existing database" (Enterprise Manager - All Tasks - Restore Database (option panel)

Originally posted by pardhu
Dear All,

I had created the backup for the exisiting datbase by writing the schedule which triggers at the particular time on a day. The backup format i had used is .bak. Now i want to restore these backup files into another database (fake database). When i am trying to restore the backup data, an error stating 'The backup set holds the backup of other than exsting <Database Name> database' is displayed and the restore operation is terminating abnormally. kindly give the excat soultion for this problem..

Regards,|||Hai dbadelphes,

I tried even by checking that option.

Originally posted by dbadelphes
Try to rstore with the option "Force restore over existing database" (Enterprise Manager - All Tasks - Restore Database (option panel)|||Make sure the database name is identical between these 2 servers.
BTW what command you're using to restore?|||Hai,

I am trying thru Enterprise Manager Wizard. I made the database names identical even.

Regards,

Originally posted by Satya
Make sure the database name is identical between these 2 servers.
BTW what command you're using to restore?|||From QA have you tried using WITH MOVE (no existing db1), or REPLACE (when you have a new db1)?

Try making sure that no db1 currently exists, then something like:
RESTORE DATABASE db1 FROM DISK='c:\db.doc'
WITH MOVE 'db1_Data' TO 'R:\Microsoft SQL Server\MSSQL\data\db1.mdf',
MOVE 'db1_Log' TO 'q:\Microsoft SQL Server\MSSQL\data\db1.ldf'

Refer to BOL for more information on BACKUP statement.sql

restoring sql 7 to sql 2000 converts views to tables ???

I just did a restore of a database created in SQL 7 and it seems that
SQL Server 2000 converted all the views to tables, which is no good.
Anyone know of a fix for this?
Did you actually do a restore or did you use DTS or the Import/Export wizard
? I've not seen this behaviour on a straight database restore (in fact I
can't see how it would be possible) but I have seen it using DTS
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"alloowishus" <alloowishus@.yahoo.com> wrote in message
news:b3c62b19.0404140825.6c8d44b9@.posting.google.c om...
> I just did a restore of a database created in SQL 7 and it seems that
> SQL Server 2000 converted all the views to tables, which is no good.
> Anyone know of a fix for this?

restoring sql 7 to sql 2000 converts views to tables ???

I just did a restore of a database created in SQL 7 and it seems that
SQL Server 2000 converted all the views to tables, which is no good.
Anyone know of a fix for this?Did you actually do a restore or did you use DTS or the Import/Export wizard
? I've not seen this behaviour on a straight database restore (in fact I
can't see how it would be possible) but I have seen it using DTS
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"alloowishus" <alloowishus@.yahoo.com> wrote in message
news:b3c62b19.0404140825.6c8d44b9@.posting.google.com...
> I just did a restore of a database created in SQL 7 and it seems that
> SQL Server 2000 converted all the views to tables, which is no good.
> Anyone know of a fix for this?sql

restoring sql 7 to sql 2000 converts views to tables ???

I just did a restore of a database created in SQL 7 and it seems that
SQL Server 2000 converted all the views to tables, which is no good.
Anyone know of a fix for this?Did you actually do a restore or did you use DTS or the Import/Export wizard
? I've not seen this behaviour on a straight database restore (in fact I
can't see how it would be possible) but I have seen it using DTS
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"alloowishus" <alloowishus@.yahoo.com> wrote in message
news:b3c62b19.0404140825.6c8d44b9@.posting.google.com...
> I just did a restore of a database created in SQL 7 and it seems that
> SQL Server 2000 converted all the views to tables, which is no good.
> Anyone know of a fix for this?

Restoring SQL 7 db's created on NT4-Svr onto a W2K-Svr

Hello,
I am trying to help a small charity whose old NT4 Server
has crashed and has had to be written-off. I have a
Veritas Backup Exec tape with all their SQL Server 7
(SP1) datases on it. The tape was created using the
appropriate Backup Exec SQL Agent. There are no SQL dump
files on it - only the Veritas live database backups.
The new server that the charity has purchased will not
run NT4 Server. It is loaded with W2K Server.
Is it possible to recover their system by performing a
fresh installation of SQL Server 7 onto the new W2K
Server and then restoring all the SQL Server 7 databases
(master , model, msdb + user databases) from the backup
tape into the fresh intallation?
Thanks to anyone who can help.
Paul> Is it possible to recover their system by performing a
> fresh installation of SQL Server 7 onto the new W2K
> Server and then restoring all the SQL Server 7 databases
> (master , model, msdb + user databases) from the backup
> tape into the fresh intallation?
Yes, that should be possible. You probably need the new install having the same service pack for the
system databases. However, I do not know in which way using Veritas might complicate this issue.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul" <sheenpaul@.hotmail.com> wrote in message news:044601c39b4d$83daaf80$a301280a@.phx.gbl...
> Hello,
> I am trying to help a small charity whose old NT4 Server
> has crashed and has had to be written-off. I have a
> Veritas Backup Exec tape with all their SQL Server 7
> (SP1) datases on it. The tape was created using the
> appropriate Backup Exec SQL Agent. There are no SQL dump
> files on it - only the Veritas live database backups.
> The new server that the charity has purchased will not
> run NT4 Server. It is loaded with W2K Server.
> Is it possible to recover their system by performing a
> fresh installation of SQL Server 7 onto the new W2K
> Server and then restoring all the SQL Server 7 databases
> (master , model, msdb + user databases) from the backup
> tape into the fresh intallation?
> Thanks to anyone who can help.
> Paul|||Thanks for your encouraging reply, Tibor. Here is an
update in case others get a similar issue.
The restore of the SQL 7 master database from the Veritas
backup tape is proving to be a problem (the restore job
hangs after only 5% with no error messages) but all other
system & user databases restore without a problem. I am
trying to resolve this matter with Veritas support and
will post again later.
>--Original Message--
>> Is it possible to recover their system by performing a
>> fresh installation of SQL Server 7 onto the new W2K
>> Server and then restoring all the SQL Server 7
databases
>> (master , model, msdb + user databases) from the backup
>> tape into the fresh intallation?
>Yes, that should be possible. You probably need the new
install having the same service pack for the
>system databases. However, I do not know in which way
using Veritas might complicate this issue.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Paul" <sheenpaul@.hotmail.com> wrote in message
news:044601c39b4d$83daaf80$a301280a@.phx.gbl...
>> Hello,
>> I am trying to help a small charity whose old NT4
Server
>> has crashed and has had to be written-off. I have a
>> Veritas Backup Exec tape with all their SQL Server 7
>> (SP1) datases on it. The tape was created using the
>> appropriate Backup Exec SQL Agent. There are no SQL
dump
>> files on it - only the Veritas live database backups.
>> The new server that the charity has purchased will not
>> run NT4 Server. It is loaded with W2K Server.
>> Is it possible to recover their system by performing a
>> fresh installation of SQL Server 7 onto the new W2K
>> Server and then restoring all the SQL Server 7
databases
>> (master , model, msdb + user databases) from the backup
>> tape into the fresh intallation?
>> Thanks to anyone who can help.
>> Paul
>
>.
>

restoring SQL 2000 on Windows 2000

i am trying to restore sql 2000 on Windows 2000 from a
tape backup created thru Veritas 8.6. The restore
completes successfully and everything seems fine except
for the fact that the sql service fails to start and when
i try to start sql, it returns the error message "the path
was not found"I'm guessing that one of your parameters in the registry point to some
location different then where you restored the files. Review the registry
entries in:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLSever\Parameters
Make sure all the parmeters poin to valid files, the ones you restore.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"malejandro" <malejandrdo@.armadillohomes.com> wrote in message
news:12d0401c44349$e884d6f0$a601280a@.phx
.gbl...
> i am trying to restore sql 2000 on Windows 2000 from a
> tape backup created thru Veritas 8.6. The restore
> completes successfully and everything seems fine except
> for the fact that the sql service fails to start and when
> i try to start sql, it returns the error message "the path
> was not found"

restoring SQL 2000 on Windows 2000

i am trying to restore sql 2000 on windows 2000 from a
tape backup created thru Veritas 8.6. The restore
completes successfully and everything seems fine except
for the fact that the sql service fails to start and when
i try to start sql, it returns the error message "the path
was not found"I'm guessing that one of your parameters in the registry point to some
location different then where you restored the files. Review the registry
entries in:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLSever\Parameters
Make sure all the parmeters poin to valid files, the ones you restore.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"malejandro" <malejandrdo@.armadillohomes.com> wrote in message
news:12d0401c44349$e884d6f0$a601280a@.phx.gbl...
> i am trying to restore sql 2000 on windows 2000 from a
> tape backup created thru Veritas 8.6. The restore
> completes successfully and everything seems fine except
> for the fact that the sql service fails to start and when
> i try to start sql, it returns the error message "the path
> was not found"

restoring SQL 2000 on Windows 2000

i am trying to restore sql 2000 on windows 2000 from a
tape backup created thru Veritas 8.6. The restore
completes successfully and everything seems fine except
for the fact that the sql service fails to start and when
i try to start sql, it returns the error message "the path
was not found"
I'm guessing that one of your parameters in the registry point to some
location different then where you restored the files. Review the registry
entries in:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLSever\P arameters
Make sure all the parmeters poin to valid files, the ones you restore.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"malejandro" <malejandrdo@.armadillohomes.com> wrote in message
news:12d0401c44349$e884d6f0$a601280a@.phx.gbl...
> i am trying to restore sql 2000 on windows 2000 from a
> tape backup created thru Veritas 8.6. The restore
> completes successfully and everything seems fine except
> for the fact that the sql service fails to start and when
> i try to start sql, it returns the error message "the path
> was not found"
sql

Restoring Publisher

We are testing SQL 2K Merge replication on a server and we need to restore
from a backup that was created right after the publication was setup. Do we
need to re-run the snapshot before we can re-create a subscriber? Thanks.
David
David,
if you want to reinitialize the subscriber, this will be necessary. In this
case, changes made on the subscriber since your backup will be lost. If you
just want to get things working again, and want to retain the recent
subscriber changes, you can restore the publisher database and synchronize
with the subscriber (assuming all this is on the same server).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||At this point, I don't care about the subscriber changes because we only
have 1 test anonymous laptop subscription. But in real production I
would want to know what can be done. For example, what if the publisher
crashes and we have to restore from a backup. Can the subscribers
simply synchronize with their changes against this newly restored
publisher? If so, do I have to re-run the snapshot? Thanks.
David
*** Sent via Developersdex http://www.codecomments.com ***
|||David,
as long as you have a recent backup in merge, you can restore and
synchronize. Changes are associated with generation numbers in the metadata
which is effectively working as a logical clock. This means that they'll
pick up all the changes made after they last synchronized with the
subscriber, from wherever they originated.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Restoring from 2000 backup to 2005 instance

I did a backup of a SQL2000 database (named Winstis) using the 2005 management studio. I then created a blank database on my 2005 instance (named Winstis). I then tried to do a database restore to the new 2005 database. I got an error:

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'Winstis' database. (Microsoft.SqlServer.Smo)

I even tried the option to Overwrite Existing database and I got a different error:

System.Data.SqlClient.SqlError: The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test.ldf'. (Microsoft.SqlServer.Smo)

Also the 2005 instance database is on C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Winstis_Data.mdf

and the 2000 instance is on
D:\Program Files\Microsoft SQL Server\MSSQL\Data\Winstis_Data.mdf

How can I accomplish getting the database from 2000 to 2005 with all tables, store procedures, and functions?you can restore from a 2000 backup to a 2005 server, but only if the db you are restoring to doesn't exist on the 2005 server at restore time.

so delete the winstis db on your 2005 instance then restore from the bak, should work ok then.|||Hi Jezemine,
I actually tried it that way as well. I finally got an answer from another forum. The key was using the move options on the RESTORE.

Thanks though.

Restoring from 2000 backup to 2005 instance

I did a backup of a 2000 database using the 2005 management studio. I then
created a blank database on my 2005 instance. I then tried to do a database
restore to the new 2005 database. I got an error:
System.Data.SqlClient.SqlError: The backup set holds a backup of a database
other than the existing 'Winstis' database. (Microsoft.SqlServer.Smo)
I even tried the option to Overwrite Existing database and I got a different
error:
System.Data.SqlClient.SqlError: The operating system returned the error '32
(The process cannot access the file because it is being used by another
process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test.ldf'. (Microsoft.
SqlServer.Smo)
Also the 2005 instance database is on C:\Program Files\Microsoft SQL Server\
MSSQL.1\MSSQL\Data\Winstis_Data.mdf
and the 2000 instance is on
D:\Program Files\Microsoft SQL Server\MSSQL\Data\Winstis_Data.mdf
How can I accomplish getting the database from 2000 to 2005?
--
Message posted via http://www.sqlmonster.comHello,
Do not create the blank database instead do the below steps:-
1. Do the SQL Server 2000 backup using Backup Database WITH INIT
2. Copy the .BAK file to SQL 2005 machine
3. Create the required folders for MDF and LDF in SQL 2005 server
3. Do the below command
RESTORE DATABASE Winstis FROM DISK='D:\backup\dbbackup2000.bak'
with MOVE 'LogicalMDFName' to 'd:\Data\Winstis_Data.mdf',
with MOVE 'LogicalLDFName' to 'd:\Data\Winstis_log.ldf',stats=2
Ge the logical MDF and LDF names using RESTORE FILELISTONLY command
Thanks
Hari
"dcuff via SQLMonster.com" <u12346@.uwe> wrote in message
news:6941157063204@.uwe...
>I did a backup of a 2000 database using the 2005 management studio. I then
> created a blank database on my 2005 instance. I then tried to do a
> database
> restore to the new 2005 database. I got an error:
> System.Data.SqlClient.SqlError: The backup set holds a backup of a
> database
> other than the existing 'Winstis' database. (Microsoft.SqlServer.Smo)
> I even tried the option to Overwrite Existing database and I got a
> different
> error:
> System.Data.SqlClient.SqlError: The operating system returned the error
> '32
> (The process cannot access the file because it is being used by another
> process.)' while attempting 'RestoreContainer::ValidateTargetForCreation'
> on
> 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test.ldf'. (Microsoft.
> SqlServer.Smo)
> Also the 2005 instance database is on C:\Program Files\Microsoft SQL
> Server\
> MSSQL.1\MSSQL\Data\Winstis_Data.mdf
> and the 2000 instance is on
> D:\Program Files\Microsoft SQL Server\MSSQL\Data\Winstis_Data.mdf
> How can I accomplish getting the database from 2000 to 2005?
> --
> Message posted via http://www.sqlmonster.com
>|||Thanks for the response Hari. This procedure will not affect the Winstis
database on SQL2000 will it. That is our production instance. I only want to
copy the database to the SQL2005 instance not a complete move. That way I can
test our ERP on it.
Thanks,
David
Hari Prasad wrote:
>Hello,
>Do not create the blank database instead do the below steps:-
>1. Do the SQL Server 2000 backup using Backup Database WITH INIT
>2. Copy the .BAK file to SQL 2005 machine
>3. Create the required folders for MDF and LDF in SQL 2005 server
>3. Do the below command
> RESTORE DATABASE Winstis FROM DISK='D:\backup\dbbackup2000.bak'
> with MOVE 'LogicalMDFName' to 'd:\Data\Winstis_Data.mdf',
> with MOVE 'LogicalLDFName' to 'd:\Data\Winstis_log.ldf',stats=2
>Ge the logical MDF and LDF names using RESTORE FILELISTONLY command
>Thanks
>Hari
>>I did a backup of a 2000 database using the 2005 management studio. I then
>> created a blank database on my 2005 instance. I then tried to do a
>[quoted text clipped - 25 lines]
>> How can I accomplish getting the database from 2000 to 2005?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1|||>I did a backup of a 2000 database using the 2005 management studio. I then
> created a blank database on my 2005 instance. I then tried to >do a
> database
You don't need to create a blank database. Do just restore commnd
"dcuff via SQLMonster.com" <u12346@.uwe> wrote in message
news:694268af1752f@.uwe...
> Thanks for the response Hari. This procedure will not affect the Winstis
> database on SQL2000 will it. That is our production instance. I only want
> to
> copy the database to the SQL2005 instance not a complete move. That way I
> can
> test our ERP on it.
> Thanks,
> David
> Hari Prasad wrote:
>>Hello,
>>Do not create the blank database instead do the below steps:-
>>1. Do the SQL Server 2000 backup using Backup Database WITH INIT
>>2. Copy the .BAK file to SQL 2005 machine
>>3. Create the required folders for MDF and LDF in SQL 2005 server
>>3. Do the below command
>> RESTORE DATABASE Winstis FROM DISK='D:\backup\dbbackup2000.bak'
>> with MOVE 'LogicalMDFName' to 'd:\Data\Winstis_Data.mdf',
>> with MOVE 'LogicalLDFName' to 'd:\Data\Winstis_log.ldf',stats=2
>>Ge the logical MDF and LDF names using RESTORE FILELISTONLY command
>>Thanks
>>Hari
>>I did a backup of a 2000 database using the 2005 management studio. I
>>then
>> created a blank database on my 2005 instance. I then tried to do a
>>[quoted text clipped - 25 lines]
>> How can I accomplish getting the database from 2000 to 2005?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>

Restoring File Backups II

Sorry, I want restore only two files ( one filegroup) of
database ( not full backup). I don't have the transaction
log backups created since the file backups. I don't care
about changes. I want restore only files and (optional)
last log backup.Typically file backups can only be restored on the same database where they
were performed. If you want to restore them to a different database, then
starting point for such a restore has to be a complete backup. If you want
to restore these file/filegroup backups to a different database then follow
the steps below :
- Restore complete backup with standby
- Restore all file/filegroup backups with standby
- Use DTS or BCP to get the data out of the filegroups (since you cannot
bring the restore database online unless you apply all transaction log
backups)
Also, you cannot restore only the last log backup. If you need changes from
the last log, you WILL need all log backups from the latest filegroup
backup that you applied.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Restoring File Backups II

Sorry, I want restore only two files ( one filegroup) of
database ( not full backup). I don't have the transaction
log backups created since the file backups. I don't care
about changes. I want restore only files and (optional)
last log backup.
Typically file backups can only be restored on the same database where they
were performed. If you want to restore them to a different database, then
starting point for such a restore has to be a complete backup. If you want
to restore these file/filegroup backups to a different database then follow
the steps below :
- Restore complete backup with standby
- Restore all file/filegroup backups with standby
- Use DTS or BCP to get the data out of the filegroups (since you cannot
bring the restore database online unless you apply all transaction log
backups)
Also, you cannot restore only the last log backup. If you need changes from
the last log, you WILL need all log backups from the latest filegroup
backup that you applied.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Restoring File Backups II

Sorry, I want restore only two files ( one filegroup) of
database ( not full backup). I don't have the transaction
log backups created since the file backups. I don't care
about changes. I want restore only files and (optional)
last log backup.Typically file backups can only be restored on the same database where they
were performed. If you want to restore them to a different database, then
starting point for such a restore has to be a complete backup. If you want
to restore these file/filegroup backups to a different database then follow
the steps below :
- Restore complete backup with standby
- Restore all file/filegroup backups with standby
- Use DTS or BCP to get the data out of the filegroups (since you cannot
bring the restore database online unless you apply all transaction log
backups)
Also, you cannot restore only the last log backup. If you need changes from
the last log, you WILL need all log backups from the latest filegroup
backup that you applied.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Restoring File Backups

"After restoring files, you must restore the transaction
log backups created since the file backups were created to
bring the database to a consistent state. The transaction
log backup can be rolled forward quickly, because only the
changes that apply to the restored files are applied."
I don't have the transaction log backups created since the
file backups. I don't care about changes. I want restore
only files and (optional) last log backup.
Can I do this ?
Hi,
You can do this. All you have to do is :-
1. Do a transaction log backup in your current database, provided your
recovery model is FULL or BULK_LOGGED
2. Now you can restore the full backup to a new database with NORECOVERY
option
See - RESTORE DATABASE command in books online
3. AFter the restore of FULL backup rectore the transaction log backup took
in step -1 with RECOVERY option
See - RESTORE LOG command in books online
Thanks
Hari
MCDBA
"MK" <anonymous@.discussions.microsoft.com> wrote in message
news:2e9ac01c46d6c$86a9d870$a601280a@.phx.gbl...
> "After restoring files, you must restore the transaction
> log backups created since the file backups were created to
> bring the database to a consistent state. The transaction
> log backup can be rolled forward quickly, because only the
> changes that apply to the restored files are applied."
> I don't have the transaction log backups created since the
> file backups. I don't care about changes. I want restore
> only files and (optional) last log backup.
> Can I do this ?