Database Snapshot on tempdb

Hi Experts:

Can you please offer an explanation on why a database snapshot cannot be created on tempdb?. Is it because the tempdb is created everytime fresh when SQL Server is restarted or some other reason?.

Any insight is appreciated.

Thanks

Ankith

Can I answer this with a question? Why would you want a snapshot of tempdb?

Anyway, the books online say: "Snapshots of the model, master, and tempdb databases are prohibited." These are system databases and you normally wouldn't want to mess with them too much imo.

|||

Hi:

I am not creating a snapshot on tempdb. I just want to know the techinical reason behind it.

Thanks

|||

Probably the primary reason is that tempdb is a temporary store for use by the engine for many situations, worktables, row versions, etc. having a snapshot does not have a purpose and would not work because of these.

|||

Hi Simon:

Thank you for the explanation.

Ankith.

|||

The actual technical reason is that database snapshots undergo restart recovery to rollback any active transactions at the time the snapshot is created. Since TEMPDB cannot be recovered (it is recreated each time the server starts), you cannot create a snapshot on it.

Thanks,
--R

sql

Database Snapshot on tempdb

Hi Experts:

Can you please offer an explanation on why a database snapshot cannot be created on tempdb?. Is it because the tempdb is created everytime fresh when SQL Server is restarted or some other reason?.

Any insight is appreciated.

Thanks

Ankith

Can I answer this with a question? Why would you want a snapshot of tempdb?

Anyway, the books online say: "Snapshots of the model, master, and tempdb databases are prohibited." These are system databases and you normally wouldn't want to mess with them too much imo.

|||

Hi:

I am not creating a snapshot on tempdb. I just want to know the techinical reason behind it.

Thanks

|||

Probably the primary reason is that tempdb is a temporary store for use by the engine for many situations, worktables, row versions, etc. having a snapshot does not have a purpose and would not work because of these.

|||

Hi Simon:

Thank you for the explanation.

Ankith.

|||

The actual technical reason is that database snapshots undergo restart recovery to rollback any active transactions at the time the snapshot is created. Since TEMPDB cannot be recovered (it is recreated each time the server starts), you cannot create a snapshot on it.

Thanks,
--R

database size increased twice

We had to move exsiting database on new server and ran querey copying all re
cords into new table on new server.
System said that new table was created successfully and all records were cop
ied.
We decided to backup this new database, but it failed. Investigating of back
up problem led us to the point where we realised that our actuall database b
ecome 2 times larger - 150GB insted of 75 (original size).
Kind of lost where this additional chunk came from.
Will be thankful for any suggestions,
Alan.Alan,
Did you create new database with the same data device and log device as the
original one?
Did you try to shrink the data files using DBCC?
larry
"ALan" <anonymous@.discussions.microsoft.com> wrote in message
news:49C0FD90-FFFA-4CE5-B8F8-0A43E1E8B22D@.microsoft.com...

quote:

---

> We had to move exsiting database on new server and ran querey copying all

---

records into new table on new server.

quote:

---

> System said that new table was created successfully and all records were

---

copied.

quote:

---

> We decided to backup this new database, but it failed. Investigating of

---

backup problem led us to the point where we realised that our actuall
database become 2 times larger - 150GB insted of 75 (original size).

quote:

---

> Kind of lost where this additional chunk came from.
> Will be thankful for any suggestions,
> Alan.

---

Database size

Hi, i'm using SQL Server 2005 Express Edition with advance services. Created a small database with 21 tables and 66 simple stored procedures. The database should not take up huge data in a short period of time. However, when i right click and check out the Database Properties, i noticed that the database size has grown to 1700 MB and available space is only about 0.9 MB. But when i locate the respective .mdf and .ldf file file, the file size of mdf is only about 4000KB and ldf file is about 1,650,000 KB.

I understand that SQL Server Express Edition allows up to 4GB of data file for each database created. But i'm puzzled and would like to know what is actually meant by the databse size and available space that i see via Database Properties. It seems worrying. Can anyone guide me? Thx in advance.

It sounds like your database is running in Full Recovery Model, and your transaction log (the ldf file) is growing. If so, you need to periodically back up your transaction log, which will allow it to release space internally. Then you will need to shrink the transaction log file to get the space back externally.

If you are running in Simple Recovery Model, then you may have a problem with an open transaction.

Database Setup Package/Installation

Hi Folks,
I've created a SQL Server database and converted it to Oracle (an app we
made supports MSSQL and Oracle) and I'd like to be able to provide a
"server-side" installation that clients can run on their database server
that would automatically create the MSSQL or Oracle database, the tables,
index, relationships, etc. and insert the needed data for a "new" database.
I have very little experience in the realm of database setup & installation.
Has anyone ever done this before and if so, what product/technology did you
use? I've been searching the newsgroups and haven't found much useful info
on the topic. Am I looking in the wrong place?
Thanks,
Jason-- Jason wrote: --
>> Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app we
> made supports MSSQL and Oracle) and I'd like to be able to provide a
> "server-side" installation that clients can run on their database server
> that would automatically create the MSSQL or Oracle database, the tables,
> index, relationships, etc. and insert the needed data for a "new" database.
--
Hi Jason,
This is a fairly trivial exercise. Have a look at the sample *.sql scripts that automatically creates the sample Northwind and Pubs database when you install SQL Server. They will give you some idea on how to automatically create a database and objects within that database.
The files are instnwnd.sql and instpubs.sql to install Northwind and Pubs, respectively. The files can be found on your <drive>:\Program Files\Microsoft SQL Server\MSSQL\Install folder
Hope this helps,
-Eric Cárdenas
SQL Server support|||You could, from your install program, either spawn OSQL with your script file (see Eric's post) as
input parameter. Or you can write your own app which reads the file and execute the TSQL statements
in the script file. Or you could, of course, embed the various CREATE statements inside your setup
programs source code.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jason" <jasonmauss_nospam@.vsdotnetguru.com> wrote in message
news:%23KeLsTSuDHA.2244@.TK2MSFTNGP09.phx.gbl...
> Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app we
> made supports MSSQL and Oracle) and I'd like to be able to provide a
> "server-side" installation that clients can run on their database server
> that would automatically create the MSSQL or Oracle database, the tables,
> index, relationships, etc. and insert the needed data for a "new" database.
> I have very little experience in the realm of database setup & installation.
> Has anyone ever done this before and if so, what product/technology did you
> use? I've been searching the newsgroups and haven't found much useful info
> on the topic. Am I looking in the wrong place?
> Thanks,
> Jason
>|||I would probably
1. Script out Your DB. This will enable you to create a blank version of
your DB somewhere else.
2. Have your standing data in text files and use BULK INSERT or bcp
statements to load it into the tables.
3. Use osql to load the scripts you have created.
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\co
prompt.chm::/cp_osql_1wxl.htm)
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Jason" <jasonmauss_nospam@.vsdotnetguru.com> wrote in message
news:%23KeLsTSuDHA.2244@.TK2MSFTNGP09.phx.gbl...
> Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app
we
> made supports MSSQL and Oracle) and I'd like to be able to provide a
> "server-side" installation that clients can run on their database server
> that would automatically create the MSSQL or Oracle database, the tables,
> index, relationships, etc. and insert the needed data for a "new"
database.
> I have very little experience in the realm of database setup &
installation.
> Has anyone ever done this before and if so, what product/technology did
you
> use? I've been searching the newsgroups and haven't found much useful info
> on the topic. Am I looking in the wrong place?
> Thanks,
> Jason
>|||Thanks for the responses from everyone. I'm aware I can provide someone with
.sql scripts...what I'm more interested in though is an installation I can
provide that goes something like this:
User runs Setup.exe to luanch the setup.
During Setup, one of the screens prompts them to enter the db information
(name of the server, un/pw)
Then setup takes that information and runs the DDL scripts against the
server they provided.
Are there any products (like Wise or Installshield maybe) that help automate
the creation of setups to do this or am I looking at needing to write my own
custom setup program?
Jason
"Eric Cardenas" <anonymous@.discussions.microsoft.com> wrote in message
news:BE41F8CB-4DBA-439D-A034-32CF737D0ED0@.microsoft.com...
> -- Jason wrote: --
> >> Hi Folks,
> > I've created a SQL Server database and converted it to Oracle (an app
we
> > made supports MSSQL and Oracle) and I'd like to be able to provide a
> > "server-side" installation that clients can run on their database
server
> > that would automatically create the MSSQL or Oracle database, the
tables,
> > index, relationships, etc. and insert the needed data for a "new"
database.
> --
> Hi Jason,
> This is a fairly trivial exercise. Have a look at the sample *.sql scripts
that automatically creates the sample Northwind and Pubs database when you
install SQL Server. They will give you some idea on how to automatically
create a database and objects within that database.
> The files are instnwnd.sql and instpubs.sql to install Northwind and Pubs,
respectively. The files can be found on your <drive>:\Program
Files\Microsoft SQL Server\MSSQL\Install folder
> Hope this helps,
> -Eric Cárdenas
> SQL Server support
>

Database Setup Package/Installation

Hi Folks,
I've created a SQL Server database and converted it to Oracle (an app we
made supports MSSQL and Oracle) and I'd like to be able to provide a
"server-side" installation that clients can run on their database server
that would automatically create the MSSQL or Oracle database, the tables,
index, relationships, etc. and insert the needed data for a "new" database.
I have very little experience in the realm of database setup & installation.
Has anyone ever done this before and if so, what product/technology did you
use? I've been searching the newsgroups and haven't found much useful info
on the topic. Am I looking in the wrong place?
Thanks,
JasonYou can create scripts to do this. You can find similar
scripts from the installation of databases on SQL Server.
For an example, check the script that creates the Northwinds
database. The file is instnwnd.sql and it's located in the
install subdirectory under MSSQL, e.g.
C:\Program Files\Microsoft SQL Server\MSSQL\Install
-Sue
On Tue, 2 Dec 2003 14:35:53 -0800, "Jason"
<jasonmauss_nospam@.vsdotnetguru.com> wrote:

quote:

---

>Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app w
e
>made supports MSSQL and Oracle) and I'd like to be able to provide a
>"server-side" installation that clients can run on their database server
>that would automatically create the MSSQL or Oracle database, the tables,
>index, relationships, etc. and insert the needed data for a "new" database.
>I have very little experience in the realm of database setup & installation
.
>Has anyone ever done this before and if so, what product/technology did you
>use? I've been searching the newsgroups and haven't found much useful info
>on the topic. Am I looking in the wrong place?
>Thanks,
>Jason
>

---

|||You could, from your install program, either spawn OSQL with your script fil
e (see Eric's post) as
input parameter. Or you can write your own app which reads the file and exec
ute the TSQL statements
in the script file. Or you could, of course, embed the various CREATE statem
ents inside your setup
programs source code.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Jason" <jasonmauss_nospam@.vsdotnetguru.com> wrote in message
news:%23KeLsTSuDHA.2244@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app
we
> made supports MSSQL and Oracle) and I'd like to be able to provide a
> "server-side" installation that clients can run on their database server
> that would automatically create the MSSQL or Oracle database, the tables,
> index, relationships, etc. and insert the needed data for a "new" database
.
> I have very little experience in the realm of database setup & installatio
n.
> Has anyone ever done this before and if so, what product/technology did yo
u
> use? I've been searching the newsgroups and haven't found much useful info
> on the topic. Am I looking in the wrong place?
> Thanks,
> Jason
>

---

|||I would probably
1. Script out Your DB. This will enable you to create a blank version of
your DB somewhere else.
2. Have your standing data in text files and use BULK INSERT or bcp
statements to load it into the tables.
3. Use osql to load the scripts you have created.
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\co
prompt.chm::/cp_osql_1wxl.htm)
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Jason" <jasonmauss_nospam@.vsdotnetguru.com> wrote in message
news:%23KeLsTSuDHA.2244@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi Folks,
> I've created a SQL Server database and converted it to Oracle (an app

---

we

quote:

---

> made supports MSSQL and Oracle) and I'd like to be able to provide a
> "server-side" installation that clients can run on their database server
> that would automatically create the MSSQL or Oracle database, the tables,
> index, relationships, etc. and insert the needed data for a "new"

---

database.

quote:

---

> I have very little experience in the realm of database setup &

---

installation.

quote:

---

> Has anyone ever done this before and if so, what product/technology did

---

you

quote:

---

> use? I've been searching the newsgroups and haven't found much useful info
> on the topic. Am I looking in the wrong place?
> Thanks,
> Jason
>

---

|||Thanks for the responses from everyone. I'm aware I can provide someone with
.sql scripts...what I'm more interested in though is an installation I can
provide that goes something like this:
User runs Setup.exe to luanch the setup.
During Setup, one of the screens prompts them to enter the db information
(name of the server, un/pw)
Then setup takes that information and runs the DDL scripts against the
server they provided.
Are there any products (like Wise or Installshield maybe) that help automate
the creation of setups to do this or am I looking at needing to write my own
custom setup program?
Jason
"Eric Cardenas" <anonymous@.discussions.microsoft.com> wrote in message
news:BE41F8CB-4DBA-439D-A034-32CF737D0ED0@.microsoft.com...

quote:

---

> -- Jason wrote: --
we[QUOTE]
server[QUOTE]
tables,[QUOTE]
database.[QUOTE]
> --
> Hi Jason,
> This is a fairly trivial exercise. Have a look at the sample *.sql scripts

---

that automatically creates the sample Northwind and Pubs database when you
install SQL Server. They will give you some idea on how to automatically
create a database and objects within that database.

quote:

---

> The files are instnwnd.sql and instpubs.sql to install Northwind and Pubs,

---

respectively. The files can be found on your <drive>:\Program
Files\Microsoft SQL Server\MSSQL\Install folder

quote:

---

> Hope this helps,
> -Eric Crdenas
> SQL Server support
>

---

Database Security

Hi,
I have created a database in server SRV1 with user 'aaa' as database owner
Know if some body detach this database from SRV1 and attach them on other
server same SRV2 with defrent sa and defrent users 'SA' user in SRV2 has ful
l
access to may database
How can restric my database for other server and other sa youser ther ?
Tanks .
Daryoush AjamiHi,
First of all restirct the access to your SQL Server. In this case no one can
detach the database and attach in SRV2.
FYI, If he have detach database access in sql server and copy the files from
operating system then he will be able to
attach the database in his server and view all tables and objects.
Thanks
hari
SQL Server MVP
"Ajami" <Ajami@.discussions.microsoft.com> wrote in message
news:34C340B7-2451-4E9C-8227-EA0AA0DF7573@.microsoft.com...
> Hi,
> I have created a database in server SRV1 with user 'aaa' as database owner
> Know if some body detach this database from SRV1 and attach them on other
> server same SRV2 with defrent sa and defrent users 'SA' user in SRV2 has
> full
> access to may database
> How can restric my database for other server and other sa youser ther ?
> Tanks .
> --
> Daryoush Ajami

Database Runtime

I am Using Sql Server 2005 enterprise edition to develop an accounting system.
now i want to distribute this software to other cleints.
I have created a setup which includes all the components required for the run time of the software but i dont know how to include the sql server database in it.
as i know that i have created access based software i used to include the database file in the setup and it would work but in the case of sql server i dont know how to do it .
is there any runtime of sql server which needs to be downloaded or any other way out.SQL Server Express is free of charge.|||I just seached information on sql server expres edition. it Is quite good but what if i need my application to run on network.
is it possible in sql server express edition to run my application on network.|||and another thing as i ve created my application in sql server enterprise manager is it possible to convert my database in sql server express edition|||See here for the differncies between Express and Enterprise editions
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Database Roles being modified by unknown process

I have two databases on two separate servers, with Merge replication running
between them. DB1 and DB2 both have the same 3 roles I created for user
access. Twice within the last week, the roles on DB2, the subscriber, have
changed removing most of their table permissions, but not all. Does anyone
know of something that could be causing this?
Thanks
Profiler is your friend - yo can trace Grant, Revoke and Deny commands like
all other commands and procedures. Check the "Security Audit Event Classes"
topic in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"kahunaVA" <kahunaVA@.discussions.microsoft.com> wrote in message
news:6A920DFD-EFDC-4D23-8D97-4B51E65F6F40@.microsoft.com...
> I have two databases on two separate servers, with Merge replication
running
> between them. DB1 and DB2 both have the same 3 roles I created for user
> access. Twice within the last week, the roles on DB2, the subscriber,
have
> changed removing most of their table permissions, but not all. Does anyone
> know of something that could be causing this?
> Thanks

Database Restore, Error 3154 and

Hi all,

I am trying to restore and SQL 2000 database into a new SQL 2005 database. I performed by SQL 2000 backup and created a blank database FERS_Production in SQL 2005. FERS_Production was the original name of the database in the SQL 2000 instance.

I have tried giving the new database the same name as the original and a different name to the original database

(Below is the scripted T-SQL that I get from the DB Admin tool

RESTORE DATABASE [Fers_Production]
FILE = N'FERS_Production_dat',
FILE = N'FERS_Production_log'
FROM DISK = N'D:\Microsoft SQL Server (2000)\MSSQL\Backup\Fers_Production\Fers_Production_db_200607270206.BAK'
WITH FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10
GO

When I run this I get the following error.

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'Fers_Production' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Other searches I have performed trying to fix this problem have said to use the REPLACE clause with the RESTORE DATABASE command, but as you can see I am doing that.

Also I no longer have SQL 2000 installed so I cannot try to do a DTS copy which was another suggestion I came across.

Any help is much appriciated, many thanks

Derek

Hi all,

Since I was having problems with a SQL 2000 database to SQL 2005 restore (which I have posted seperately) I tried copying the data files to a new folder and just attaching to the SQL 2000 database file from the SQL 2005 managment studio but I get the following error (I am runing service pack 1 for SQL 2005)

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'DATABASESERVER'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

A system assertion check has failed. Check the SQL Server error log for details
Could not open new database 'Fers_Production'. CREATE DATABASE is aborted.
Location: IndexDataSet.cpp:12001
Expression: retCode == INSERT_SUCCESSFUL
SPID: 53
Process ID: 1092 (Microsoft SQL Server, Error: 3624)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=3624&LinkId=20476

Unfortunately the link that MS provide says there is no aditional info.

Thanks

Derek

|||

The restore process cannot restore the database from the backup file because there is already a database called Fers_Production present on your SQL 2005 server. Try deleting the Fers_Production database you created and then do the restore of the backup file.

|||

Thanks Andy. I restored the database to a name that did not already exist in the server and that seemed to do the trick as you suggested.

I had been used to being able to restore over an existing database but probably this could not work due to the backup being a SQL 2000 db and the new db is SQL 2005.

Thanks for your help.

Derek

|||I have merged these threads, as the error seems to be the same in both cases.|||

Derek,

Was your database attached with the .ldf and .mdf files in a specific location and then you detached the database, moved the files and tried to reattach the database? If this is the case, move the files back to the original location and reattach the database, then run this in the query window. Modify the part in red to where you want the new location of the files to be.

use fers_production
go
Alter database fers_production modify file (name = fers_production, filename = 'F:\Sqldata\fers_production.mdf')
go
Alter database fers_production modify file (name = fers_production_log, filename = 'F:\Sqllogs\fers_production.ldf')
go

Then restart SQL Server after you have done this.

|||

Thanks again Andy,

I have been caught up with other things hence the delay in my saying thanks.

I will keep that last suggestion in my notes as that my be useful at other times. I had manually moved the original files, I must remember not to do that in future.

Cheers

Derek

|||

Backup File = mydatabase.bak

1. Run Microsoft SQL Server Management Studio application.

2. If mydatabase is in Databases : delete mydatabase.

3. Right Click to Databases and select Restore Database ....

4. Destination for restore -> To database: mydatabase

Source for restore -> select From device -> Specify the backup media and select the backup sets to restore

Select Options from Select a page and in Restore the database file as: type the fullpath for the mydatabase new location

(for initdb_Data line with .mdf extension and for initdb_Log line with .ldf extension

ex.:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase.mdf

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase.ldf).

5. Press OK button.

Have fun!

|||

Hi,

I think I have a similar problem. Correct me if I am reading your answer wrong, Andy, but does it mean you cannot restore a database "on top" of existing db? (I must be wrong)
Here is the description of my problem,

I am trying to restore a SQL 2000 database to an existing SQL 2005 DB (and change the name of the db on the way). However when I attempt to do it I get following error

System.Data.SqlError: RESTORE cannot process database <<database name>> because it is in useby this session. It is recommended that the master databse be used when performing this operation.

I am not sure what it means that the datasbe is used by this session - is the the SQL management studio client opened? oh, btw. I've tried foing the same when i had mster database opened in the studio and got to a restore dialog from there, but no luck.
Any comments?

Regards,

Jacek

|||

Try also removing the NOUNLOAD option - you should then be able to restore over an existing database (still need REPLACE as well)

I used the following code to successfully restore a SDQL 200 backup file to a databasde with the same name in SQL 2005 that already existed.

RESTORE DATABASE [ELF2] FROM DISK = N'Z:\ELF2' WITH FILE = 1, REPLACE,
STATS = 10
GO

|||

Barry many thanks for this!

I was converting from msde 2000, I upgraded the server to 2005 express, and believed that this was enough to convert it, indeed some things will not work if you do this upgrade, then backup and then try to restore, which added fuel to my believe that upgrading the server also does the database. But apparently not completely. So after 24hrs of messing thanks for this tip.

I am creating live deployment script that due to Vistas security has now been moved from batch files called post-MSI (which now make the MSI fail in vista) So I call them now from inside the application itself on first boot-up. Here is the script: if you want to get an example .bak download the trial from http://www.SalonSoftwareSystem.com and see the c:\install directory for the .bak. I'm glad Vista is protecting the layman but its been a good 2 months of effort to get our install vista happy.

I think the real trick is to accept the system default .MDF .LDF paths although as developers we feel it is messy and unpredictable it is safer and Vista compatible.

--live copy
use tempdb

create database Platinum
go

alter database Platinum set single_user with rollback immediate
go

alter database Platinum set multi_user with rollback immediate
go

--if it has a name it will restore over the system decided path
RESTORE DATABASE [Platinum] FROM DISK = N'C:\install\Platinum.bak' WITH FILE = 1, REPLACE,
STATS = 10
GO

ALTER database Platinum set recovery SIMPLE
GO

--Training Copy exactly the same copy
use tempdb

create database PlatinumTraining
go

alter database PlatinumTraining set single_user with rollback immediate
go

alter database PlatinumTraining set multi_user with rollback immediate
go

RESTORE DATABASE [PlatinumTraining] FROM DISK = N'C:\install\Platinum.bak' WITH FILE = 1, REPLACE,
STATS = 10
GO

ALTER database PlatinumTraining set recovery SIMPLE
GO

|||

"The backup set holds a backup of a database other than the existing 'Fers_Production' database."

Make sure you go to the options of the restore database screen in 2005 - make sure you have "overwrite existing database" selected.

Database Restore, Error 3154 and

Hi all,

I am trying to restore and SQL 2000 database into a new SQL 2005 database. I performed by SQL 2000 backup and created a blank database FERS_Production in SQL 2005. FERS_Production was the original name of the database in the SQL 2000 instance.

I have tried giving the new database the same name as the original and a different name to the original database

(Below is the scripted T-SQL that I get from the DB Admin tool

RESTORE DATABASE [Fers_Production]
FILE = N'FERS_Production_dat',
FILE = N'FERS_Production_log'
FROM DISK = N'D:\Microsoft SQL Server (2000)\MSSQL\Backup\Fers_Production\Fers_Production_db_200607270206.BAK'
WITH FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10
GO

When I run this I get the following error.

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'Fers_Production' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Other searches I have performed trying to fix this problem have said to use the REPLACE clause with the RESTORE DATABASE command, but as you can see I am doing that.

Also I no longer have SQL 2000 installed so I cannot try to do a DTS copy which was another suggestion I came across.

Any help is much appriciated, many thanks

Derek

Hi all,

Since I was having problems with a SQL 2000 database to SQL 2005 restore (which I have posted seperately) I tried copying the data files to a new folder and just attaching to the SQL 2000 database file from the SQL 2005 managment studio but I get the following error (I am runing service pack 1 for SQL 2005)

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'DATABASESERVER'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

A system assertion check has failed. Check the SQL Server error log for details
Could not open new database 'Fers_Production'. CREATE DATABASE is aborted.
Location: IndexDataSet.cpp:12001
Expression: retCode == INSERT_SUCCESSFUL
SPID: 53
Process ID: 1092 (Microsoft SQL Server, Error: 3624)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=3624&LinkId=20476

Unfortunately the link that MS provide says there is no aditional info.

Thanks

Derek

|||

The restore process cannot restore the database from the backup file because there is already a database called Fers_Production present on your SQL 2005 server. Try deleting the Fers_Production database you created and then do the restore of the backup file.

|||

Thanks Andy. I restored the database to a name that did not already exist in the server and that seemed to do the trick as you suggested.

I had been used to being able to restore over an existing database but probably this could not work due to the backup being a SQL 2000 db and the new db is SQL 2005.

Thanks for your help.

Derek

|||I have merged these threads, as the error seems to be the same in both cases.|||

Derek,

Was your database attached with the .ldf and .mdf files in a specific location and then you detached the database, moved the files and tried to reattach the database? If this is the case, move the files back to the original location and reattach the database, then run this in the query window. Modify the part in red to where you want the new location of the files to be.

use fers_production
go
Alter database fers_production modify file (name = fers_production, filename = 'F:\Sqldata\fers_production.mdf')
go
Alter database fers_production modify file (name = fers_production_log, filename = 'F:\Sqllogs\fers_production.ldf')
go

Then restart SQL Server after you have done this.

|||

Thanks again Andy,

I have been caught up with other things hence the delay in my saying thanks.

I will keep that last suggestion in my notes as that my be useful at other times. I had manually moved the original files, I must remember not to do that in future.

Cheers

Derek

|||

Backup File = mydatabase.bak

1. Run Microsoft SQL Server Management Studio application.

2. If mydatabase is in Databases : delete mydatabase.

3. Right Click to Databases and select Restore Database ....

4. Destination for restore -> To database: mydatabase

Source for restore -> select From device -> Specify the backup media and select the backup sets to restore

Select Options from Select a page and in Restore the database file as: type the fullpath for the mydatabase new location

(for initdb_Data line with .mdf extension and for initdb_Log line with .ldf extension

ex.:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase.mdf

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase.ldf).

5. Press OK button.

Have fun!

|||

Hi,

I think I have a similar problem. Correct me if I am reading your answer wrong, Andy, but does it mean you cannot restore a database "on top" of existing db? (I must be wrong)
Here is the description of my problem,

I am trying to restore a SQL 2000 database to an existing SQL 2005 DB (and change the name of the db on the way). However when I attempt to do it I get following error

System.Data.SqlError: RESTORE cannot process database <<database name>> because it is in useby this session. It is recommended that the master databse be used when performing this operation.

I am not sure what it means that the datasbe is used by this session - is the the SQL management studio client opened? oh, btw. I've tried foing the same when i had mster database opened in the studio and got to a restore dialog from there, but no luck.
Any comments?

Regards,

Jacek

|||

Try also removing the NOUNLOAD option - you should then be able to restore over an existing database (still need REPLACE as well)

I used the following code to successfully restore a SDQL 200 backup file to a databasde with the same name in SQL 2005 that already existed.

RESTORE DATABASE [ELF2] FROM DISK = N'Z:\ELF2' WITH FILE = 1, REPLACE,
STATS = 10
GO

|||

Barry many thanks for this!

I was converting from msde 2000, I upgraded the server to 2005 express, and believed that this was enough to convert it, indeed some things will not work if you do this upgrade, then backup and then try to restore, which added fuel to my believe that upgrading the server also does the database. But apparently not completely. So after 24hrs of messing thanks for this tip.

I am creating live deployment script that due to Vistas security has now been moved from batch files called post-MSI (which now make the MSI fail in vista) So I call them now from inside the application itself on first boot-up. Here is the script: if you want to get an example .bak download the trial from http://www.SalonSoftwareSystem.com and see the c:\install directory for the .bak. I'm glad Vista is protecting the layman but its been a good 2 months of effort to get our install vista happy.

I think the real trick is to accept the system default .MDF .LDF paths although as developers we feel it is messy and unpredictable it is safer and Vista compatible.

--live copy
use tempdb

create database Platinum
go

alter database Platinum set single_user with rollback immediate
go

alter database Platinum set multi_user with rollback immediate
go

--if it has a name it will restore over the system decided path
RESTORE DATABASE [Platinum] FROM DISK = N'C:\install\Platinum.bak' WITH FILE = 1, REPLACE,
STATS = 10
GO

ALTER database Platinum set recovery SIMPLE
GO

--Training Copy exactly the same copy
use tempdb

create database PlatinumTraining
go

alter database PlatinumTraining set single_user with rollback immediate
go

alter database PlatinumTraining set multi_user with rollback immediate
go

RESTORE DATABASE [PlatinumTraining] FROM DISK = N'C:\install\Platinum.bak' WITH FILE = 1, REPLACE,
STATS = 10
GO

ALTER database PlatinumTraining set recovery SIMPLE
GO

|||

"The backup set holds a backup of a database other than the existing 'Fers_Production' database."

Make sure you go to the options of the restore database screen in 2005 - make sure you have "overwrite existing database" selected.

Database Restore, Error 3154 and

Hi all,

I am trying to restore and SQL 2000 database into a new SQL 2005 database. I performed by SQL 2000 backup and created a blank database FERS_Production in SQL 2005. FERS_Production was the original name of the database in the SQL 2000 instance.

I have tried giving the new database the same name as the original and a different name to the original database

(Below is the scripted T-SQL that I get from the DB Admin tool

RESTORE DATABASE [Fers_Production]
FILE = N'FERS_Production_dat',
FILE = N'FERS_Production_log'
FROM DISK = N'D:\Microsoft SQL Server (2000)\MSSQL\Backup\Fers_Production\Fers_Production_db_200607270206.BAK'
WITH FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10
GO

When I run this I get the following error.

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'Fers_Production' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Other searches I have performed trying to fix this problem have said to use the REPLACE clause with the RESTORE DATABASE command, but as you can see I am doing that.

Also I no longer have SQL 2000 installed so I cannot try to do a DTS copy which was another suggestion I came across.

Any help is much appriciated, many thanks

Derek

Hi all,

Since I was having problems with a SQL 2000 database to SQL 2005 restore (which I have posted seperately) I tried copying the data files to a new folder and just attaching to the SQL 2000 database file from the SQL 2005 managment studio but I get the following error (I am runing service pack 1 for SQL 2005)

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'DATABASESERVER'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

A system assertion check has failed. Check the SQL Server error log for details
Could not open new database 'Fers_Production'. CREATE DATABASE is aborted.
Location: IndexDataSet.cpp:12001
Expression: retCode == INSERT_SUCCESSFUL
SPID: 53
Process ID: 1092 (Microsoft SQL Server, Error: 3624)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=3624&LinkId=20476

Unfortunately the link that MS provide says there is no aditional info.

Thanks

Derek

|||

The restore process cannot restore the database from the backup file because there is already a database called Fers_Production present on your SQL 2005 server. Try deleting the Fers_Production database you created and then do the restore of the backup file.

|||

Thanks Andy. I restored the database to a name that did not already exist in the server and that seemed to do the trick as you suggested.

I had been used to being able to restore over an existing database but probably this could not work due to the backup being a SQL 2000 db and the new db is SQL 2005.

Thanks for your help.

Derek

|||I have merged these threads, as the error seems to be the same in both cases.|||

Derek,

Was your database attached with the .ldf and .mdf files in a specific location and then you detached the database, moved the files and tried to reattach the database? If this is the case, move the files back to the original location and reattach the database, then run this in the query window. Modify the part in red to where you want the new location of the files to be.

use fers_production
go
Alter database fers_production modify file (name = fers_production, filename = 'F:\Sqldata\fers_production.mdf')
go
Alter database fers_production modify file (name = fers_production_log, filename = 'F:\Sqllogs\fers_production.ldf')
go

Then restart SQL Server after you have done this.

|||

Thanks again Andy,

I have been caught up with other things hence the delay in my saying thanks.

I will keep that last suggestion in my notes as that my be useful at other times. I had manually moved the original files, I must remember not to do that in future.

Cheers

Derek

|||

Backup File = mydatabase.bak

1. Run Microsoft SQL Server Management Studio application.

2. If mydatabase is in Databases : delete mydatabase.

3. Right Click to Databases and select Restore Database ....

4. Destination for restore -> To database: mydatabase

Source for restore -> select From device -> Specify the backup media and select the backup sets to restore

Select Options from Select a page and in Restore the database file as: type the fullpath for the mydatabase new location

(for initdb_Data line with .mdf extension and for initdb_Log line with .ldf extension

ex.:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase.mdf

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase.ldf).

5. Press OK button.

Have fun!

|||

Hi,

I think I have a similar problem. Correct me if I am reading your answer wrong, Andy, but does it mean you cannot restore a database "on top" of existing db? (I must be wrong)
Here is the description of my problem,

I am trying to restore a SQL 2000 database to an existing SQL 2005 DB (and change the name of the db on the way). However when I attempt to do it I get following error

System.Data.SqlError: RESTORE cannot process database <<database name>> because it is in useby this session. It is recommended that the master databse be used when performing this operation.

I am not sure what it means that the datasbe is used by this session - is the the SQL management studio client opened? oh, btw. I've tried foing the same when i had mster database opened in the studio and got to a restore dialog from there, but no luck.
Any comments?

Regards,

Jacek

|||

Try also removing the NOUNLOAD option - you should then be able to restore over an existing database (still need REPLACE as well)

I used the following code to successfully restore a SDQL 200 backup file to a databasde with the same name in SQL 2005 that already existed.

RESTORE DATABASE [ELF2] FROM DISK = N'Z:\ELF2' WITH FILE = 1, REPLACE,
STATS = 10
GO

|||

Barry many thanks for this!

I was converting from msde 2000, I upgraded the server to 2005 express, and believed that this was enough to convert it, indeed some things will not work if you do this upgrade, then backup and then try to restore, which added fuel to my believe that upgrading the server also does the database. But apparently not completely. So after 24hrs of messing thanks for this tip.

I am creating live deployment script that due to Vistas security has now been moved from batch files called post-MSI (which now make the MSI fail in vista) So I call them now from inside the application itself on first boot-up. Here is the script: if you want to get an example .bak download the trial from http://www.SalonSoftwareSystem.com and see the c:\install directory for the .bak. I'm glad Vista is protecting the layman but its been a good 2 months of effort to get our install vista happy.

I think the real trick is to accept the system default .MDF .LDF paths although as developers we feel it is messy and unpredictable it is safer and Vista compatible.

--live copy
use tempdb

create database Platinum
go

alter database Platinum set single_user with rollback immediate
go

alter database Platinum set multi_user with rollback immediate
go

--if it has a name it will restore over the system decided path
RESTORE DATABASE [Platinum] FROM DISK = N'C:\install\Platinum.bak' WITH FILE = 1, REPLACE,
STATS = 10
GO

ALTER database Platinum set recovery SIMPLE
GO

--Training Copy exactly the same copy
use tempdb

create database PlatinumTraining
go

alter database PlatinumTraining set single_user with rollback immediate
go

alter database PlatinumTraining set multi_user with rollback immediate
go

RESTORE DATABASE [PlatinumTraining] FROM DISK = N'C:\install\Platinum.bak' WITH FILE = 1, REPLACE,
STATS = 10
GO

ALTER database PlatinumTraining set recovery SIMPLE
GO

|||

"The backup set holds a backup of a database other than the existing 'Fers_Production' database."

Make sure you go to the options of the restore database screen in 2005 - make sure you have "overwrite existing database" selected.

Database Restore, Error 3154 and

Hi all,

I am trying to restore and SQL 2000 database into a new SQL 2005 database. I performed by SQL 2000 backup and created a blank database FERS_Production in SQL 2005. FERS_Production was the original name of the database in the SQL 2000 instance.

I have tried giving the new database the same name as the original and a different name to the original database

(Below is the scripted T-SQL that I get from the DB Admin tool

RESTORE DATABASE [Fers_Production]
FILE = N'FERS_Production_dat',
FILE = N'FERS_Production_log'
FROM DISK = N'D:\Microsoft SQL Server (2000)\MSSQL\Backup\Fers_Production\Fers_Production_db_200607270206.BAK'
WITH FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10
GO

When I run this I get the following error.

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'Fers_Production' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Other searches I have performed trying to fix this problem have said to use the REPLACE clause with the RESTORE DATABASE command, but as you can see I am doing that.

Also I no longer have SQL 2000 installed so I cannot try to do a DTS copy which was another suggestion I came across.

Any help is much appriciated, many thanks

Derek

Hi all,

Since I was having problems with a SQL 2000 database to SQL 2005 restore (which I have posted seperately) I tried copying the data files to a new folder and just attaching to the SQL 2000 database file from the SQL 2005 managment studio but I get the following error (I am runing service pack 1 for SQL 2005)

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'DATABASESERVER'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

A system assertion check has failed. Check the SQL Server error log for details
Could not open new database 'Fers_Production'. CREATE DATABASE is aborted.
Location: IndexDataSet.cpp:12001
Expression: retCode == INSERT_SUCCESSFUL
SPID: 53
Process ID: 1092 (Microsoft SQL Server, Error: 3624)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=3624&LinkId=20476

Unfortunately the link that MS provide says there is no aditional info.

Thanks

Derek

|||

The restore process cannot restore the database from the backup file because there is already a database called Fers_Production present on your SQL 2005 server. Try deleting the Fers_Production database you created and then do the restore of the backup file.

|||

Thanks Andy. I restored the database to a name that did not already exist in the server and that seemed to do the trick as you suggested.

I had been used to being able to restore over an existing database but probably this could not work due to the backup being a SQL 2000 db and the new db is SQL 2005.

Thanks for your help.

Derek

|||I have merged these threads, as the error seems to be the same in both cases.|||

Derek,

Was your database attached with the .ldf and .mdf files in a specific location and then you detached the database, moved the files and tried to reattach the database? If this is the case, move the files back to the original location and reattach the database, then run this in the query window. Modify the part in red to where you want the new location of the files to be.

use fers_production
go
Alter database fers_production modify file (name = fers_production, filename = 'F:\Sqldata\fers_production.mdf')
go
Alter database fers_production modify file (name = fers_production_log, filename = 'F:\Sqllogs\fers_production.ldf')
go

Then restart SQL Server after you have done this.

|||

Thanks again Andy,

I have been caught up with other things hence the delay in my saying thanks.

I will keep that last suggestion in my notes as that my be useful at other times. I had manually moved the original files, I must remember not to do that in future.

Cheers

Derek

|||

Backup File = mydatabase.bak

1. Run Microsoft SQL Server Management Studio application.

2. If mydatabase is in Databases : delete mydatabase.

3. Right Click to Databases and select Restore Database ....

4. Destination for restore -> To database: mydatabase

Source for restore -> select From device -> Specify the backup media and select the backup sets to restore

Select Options from Select a page and in Restore the database file as: type the fullpath for the mydatabase new location

(for initdb_Data line with .mdf extension and for initdb_Log line with .ldf extension

ex.:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase.mdf

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase.ldf).

5. Press OK button.

Have fun!

|||

Hi,

I think I have a similar problem. Correct me if I am reading your answer wrong, Andy, but does it mean you cannot restore a database "on top" of existing db? (I must be wrong)
Here is the description of my problem,

I am trying to restore a SQL 2000 database to an existing SQL 2005 DB (and change the name of the db on the way). However when I attempt to do it I get following error

System.Data.SqlError: RESTORE cannot process database <<database name>> because it is in useby this session. It is recommended that the master databse be used when performing this operation.

I am not sure what it means that the datasbe is used by this session - is the the SQL management studio client opened? oh, btw. I've tried foing the same when i had mster database opened in the studio and got to a restore dialog from there, but no luck.
Any comments?

Regards,

Jacek

|||

Try also removing the NOUNLOAD option - you should then be able to restore over an existing database (still need REPLACE as well)

I used the following code to successfully restore a SDQL 200 backup file to a databasde with the same name in SQL 2005 that already existed.

RESTORE DATABASE [ELF2] FROM DISK = N'Z:\ELF2' WITH FILE = 1, REPLACE,
STATS = 10
GO

|||

Barry many thanks for this!

I was converting from msde 2000, I upgraded the server to 2005 express, and believed that this was enough to convert it, indeed some things will not work if you do this upgrade, then backup and then try to restore, which added fuel to my believe that upgrading the server also does the database. But apparently not completely. So after 24hrs of messing thanks for this tip.

I am creating live deployment script that due to Vistas security has now been moved from batch files called post-MSI (which now make the MSI fail in vista) So I call them now from inside the application itself on first boot-up. Here is the script: if you want to get an example .bak download the trial from http://www.SalonSoftwareSystem.com and see the c:\install directory for the .bak. I'm glad Vista is protecting the layman but its been a good 2 months of effort to get our install vista happy.

I think the real trick is to accept the system default .MDF .LDF paths although as developers we feel it is messy and unpredictable it is safer and Vista compatible.

--live copy
use tempdb

create database Platinum
go

alter database Platinum set single_user with rollback immediate
go

alter database Platinum set multi_user with rollback immediate
go

--if it has a name it will restore over the system decided path
RESTORE DATABASE [Platinum] FROM DISK = N'C:\install\Platinum.bak' WITH FILE = 1, REPLACE,
STATS = 10
GO

ALTER database Platinum set recovery SIMPLE
GO

--Training Copy exactly the same copy
use tempdb

create database PlatinumTraining
go

alter database PlatinumTraining set single_user with rollback immediate
go

alter database PlatinumTraining set multi_user with rollback immediate
go

RESTORE DATABASE [PlatinumTraining] FROM DISK = N'C:\install\Platinum.bak' WITH FILE = 1, REPLACE,
STATS = 10
GO

ALTER database PlatinumTraining set recovery SIMPLE
GO

|||

"The backup set holds a backup of a database other than the existing 'Fers_Production' database."

Make sure you go to the options of the restore database screen in 2005 - make sure you have "overwrite existing database" selected.

Database Restore, Error 3154 and

Hi all,

I am trying to restore and SQL 2000 database into a new SQL 2005 database. I performed by SQL 2000 backup and created a blank database FERS_Production in SQL 2005. FERS_Production was the original name of the database in the SQL 2000 instance.

I have tried giving the new database the same name as the original and a different name to the original database

(Below is the scripted T-SQL that I get from the DB Admin tool

RESTORE DATABASE [Fers_Production]
FILE = N'FERS_Production_dat',
FILE = N'FERS_Production_log'
FROM DISK = N'D:\Microsoft SQL Server (2000)\MSSQL\Backup\Fers_Production\Fers_Production_db_200607270206.BAK'
WITH FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10
GO

When I run this I get the following error.

Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing 'Fers_Production' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Other searches I have performed trying to fix this problem have said to use the REPLACE clause with the RESTORE DATABASE command, but as you can see I am doing that.

Also I no longer have SQL 2000 installed so I cannot try to do a DTS copy which was another suggestion I came across.

Any help is much appriciated, many thanks

Derek

Hi all,

Since I was having problems with a SQL 2000 database to SQL 2005 restore (which I have posted seperately) I tried copying the data files to a new folder and just attaching to the SQL 2000 database file from the SQL 2005 managment studio but I get the following error (I am runing service pack 1 for SQL 2005)

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'DATABASESERVER'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

A system assertion check has failed. Check the SQL Server error log for details
Could not open new database 'Fers_Production'. CREATE DATABASE is aborted.
Location: IndexDataSet.cpp:12001
Expression: retCode == INSERT_SUCCESSFUL
SPID: 53
Process ID: 1092 (Microsoft SQL Server, Error: 3624)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=3624&LinkId=20476

Unfortunately the link that MS provide says there is no aditional info.

Thanks

Derek

|||

The restore process cannot restore the database from the backup file because there is already a database called Fers_Production present on your SQL 2005 server. Try deleting the Fers_Production database you created and then do the restore of the backup file.

|||

Thanks Andy. I restored the database to a name that did not already exist in the server and that seemed to do the trick as you suggested.

I had been used to being able to restore over an existing database but probably this could not work due to the backup being a SQL 2000 db and the new db is SQL 2005.

Thanks for your help.

Derek

|||I have merged these threads, as the error seems to be the same in both cases.|||

Derek,

Was your database attached with the .ldf and .mdf files in a specific location and then you detached the database, moved the files and tried to reattach the database? If this is the case, move the files back to the original location and reattach the database, then run this in the query window. Modify the part in red to where you want the new location of the files to be.

use fers_production
go
Alter database fers_production modify file (name = fers_production, filename = 'F:\Sqldata\fers_production.mdf')
go
Alter database fers_production modify file (name = fers_production_log, filename = 'F:\Sqllogs\fers_production.ldf')
go

Then restart SQL Server after you have done this.

|||

Thanks again Andy,

I have been caught up with other things hence the delay in my saying thanks.

I will keep that last suggestion in my notes as that my be useful at other times. I had manually moved the original files, I must remember not to do that in future.

Cheers

Derek

|||

Backup File = mydatabase.bak

1. Run Microsoft SQL Server Management Studio application.

2. If mydatabase is in Databases : delete mydatabase.

3. Right Click to Databases and select Restore Database ....

4. Destination for restore -> To database: mydatabase

Source for restore -> select From device -> Specify the backup media and select the backup sets to restore

Select Options from Select a page and in Restore the database file as: type the fullpath for the mydatabase new location

(for initdb_Data line with .mdf extension and for initdb_Log line with .ldf extension

ex.:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase.mdf

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mydatabase.ldf).

5. Press OK button.

Have fun!

|||

Hi,

I think I have a similar problem. Correct me if I am reading your answer wrong, Andy, but does it mean you cannot restore a database "on top" of existing db? (I must be wrong)
Here is the description of my problem,

I am trying to restore a SQL 2000 database to an existing SQL 2005 DB (and change the name of the db on the way). However when I attempt to do it I get following error

System.Data.SqlError: RESTORE cannot process database <<database name>> because it is in useby this session. It is recommended that the master databse be used when performing this operation.

I am not sure what it means that the datasbe is used by this session - is the the SQL management studio client opened? oh, btw. I've tried foing the same when i had mster database opened in the studio and got to a restore dialog from there, but no luck.
Any comments?

Regards,

Jacek

|||

Try also removing the NOUNLOAD option - you should then be able to restore over an existing database (still need REPLACE as well)

I used the following code to successfully restore a SDQL 200 backup file to a databasde with the same name in SQL 2005 that already existed.

RESTORE DATABASE [ELF2] FROM DISK = N'Z:\ELF2' WITH FILE = 1, REPLACE,
STATS = 10
GO

|||

Barry many thanks for this!

I was converting from msde 2000, I upgraded the server to 2005 express, and believed that this was enough to convert it, indeed some things will not work if you do this upgrade, then backup and then try to restore, which added fuel to my believe that upgrading the server also does the database. But apparently not completely. So after 24hrs of messing thanks for this tip.

I am creating live deployment script that due to Vistas security has now been moved from batch files called post-MSI (which now make the MSI fail in vista) So I call them now from inside the application itself on first boot-up. Here is the script: if you want to get an example .bak download the trial from http://www.SalonSoftwareSystem.com and see the c:\install directory for the .bak. I'm glad Vista is protecting the layman but its been a good 2 months of effort to get our install vista happy.

I think the real trick is to accept the system default .MDF .LDF paths although as developers we feel it is messy and unpredictable it is safer and Vista compatible.

--live copy
use tempdb

create database Platinum
go

alter database Platinum set single_user with rollback immediate
go

alter database Platinum set multi_user with rollback immediate
go

--if it has a name it will restore over the system decided path
RESTORE DATABASE [Platinum] FROM DISK = N'C:\install\Platinum.bak' WITH FILE = 1, REPLACE,
STATS = 10
GO

ALTER database Platinum set recovery SIMPLE
GO

--Training Copy exactly the same copy
use tempdb

create database PlatinumTraining
go

alter database PlatinumTraining set single_user with rollback immediate
go

alter database PlatinumTraining set multi_user with rollback immediate
go

RESTORE DATABASE [PlatinumTraining] FROM DISK = N'C:\install\Platinum.bak' WITH FILE = 1, REPLACE,
STATS = 10
GO

ALTER database PlatinumTraining set recovery SIMPLE
GO

|||

"The backup set holds a backup of a database other than the existing 'Fers_Production' database."

Make sure you go to the options of the restore database screen in 2005 - make sure you have "overwrite existing database" selected.

Database Restore Problem

I've created a Restore Job with owner sa, run as dbo, to refresh a Test database from Production.

After ONE successful refresh from the Production database, this
job fails with:
Msg 3101, Sev 16: Exclusive access could not be obtained because the
database is in use. [SQLSTATE 42000]
Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE
42000]

There are no users connected to the database or processes running against it.

The syntax is as follows:
ALTER DATABASE DBA_pfaids_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE DBA_pfaids_test
FROM DISK = 'd:\PFAIDS\restore\PRODbackup.bak'
WITH REPLACE,
MOVE 'SS2K_prod_empty_Data' TO 'D:\MSSQL\DATA\DBA_pfaids_test_Data.MDF',
MOVE 'SS2K_prod_empty_Log' TO 'D:\MSSQL\DATA\DBA_pfaids_test_Log.LDF'

ALTER DATABASE DBA_pfaids_test SET MULTI_USER

Does this have something to do with the initial refresh from Production?
How can I get it to work?

Thanks.
MaryYou probably have either a (some) thoughtless user(s) who leave their connections open at the end of the day, or scheduled jobs that access the database.

You cannot put a database in single user mode while others are attached. You could preface your script with a call to sp_who2 and capture the returned data in a table. Then use a cursor to inspect the table, and when you find a spid attached to the database you want to restore, kill the spid.

Once you have finished with the cursor, close and deallocate. repeat until no losers (oops ... 'xcuse me ... users) are left in the database. When that occurs, execute the alter to put it into single_user, and proceed with the restore.

Things would run so much better if we just didn't have to let users into our databases ;)|||Thanks for you reply.

Actually, users do not access this database. I created it for testing purposes only, and they don't even know it exists. The restore works fine in Enterprise Manager, but fails when doing it with T-SQL. (I am trying to automate the restore).|||Things would run so much better if we just didn't have to let users into our databases ;)

~"This job would be great if it were'nt for the customers"
-Clerks|||The microsoft tools are getting a bit annoying with how "helpful" they are trying to be. If you have not already, shut down enterprise manager, and close the object browser in Query Analyzer. These two things are the top cause of this error. If that does not help, check to see if you have any extra windows open in Query Analyzer.|||Also check if the QA window you're trying to do the restore in isn't connected to the same database (switch to master to be sure).

Lex

database restore

Hi,
I have a problem with db restore.
everytime I restore the full database( on newly created db) on this one
perticular server for this one backup, the database goes in loading stage
and does not come back.
Does any body know what is happening?
Sam
Make sure you specify the RECOVERY option for the restore command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sam" <samirsoni@.hotmail.com> wrote in message news:%23XTHLkqHFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a problem with db restore.
> everytime I restore the full database( on newly created db) on this one perticular server for this
> one backup, the database goes in loading stage and does not come back.
> Does any body know what is happening?
> Sam
>

database restore

Hi,
I have a problem with db restore.
everytime I restore the full database( on newly created db) on this one
perticular server for this one backup, the database goes in loading stage
and does not come back.
Does any body know what is happening?
SamMake sure you specify the RECOVERY option for the restore command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sam" <samirsoni@.hotmail.com> wrote in message news:%23XTHLkqHFHA.2648@.TK2MSFTNGP14.phx.gbl.
.
> Hi,
> I have a problem with db restore.
> everytime I restore the full database( on newly created db) on this one pe
rticular server for this
> one backup, the database goes in loading stage and does not come back.
> Does any body know what is happening?
> Sam
>

database restore

Hi,
I have a problem with db restore.
everytime I restore the full database( on newly created db) on this one
perticular server for this one backup, the database goes in loading stage
and does not come back.
Does any body know what is happening?
SamMake sure you specify the RECOVERY option for the restore command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sam" <samirsoni@.hotmail.com> wrote in message news:%23XTHLkqHFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have a problem with db restore.
> everytime I restore the full database( on newly created db) on this one perticular server for this
> one backup, the database goes in loading stage and does not come back.
> Does any body know what is happening?
> Sam
>

Database Relationships

Hi,

Im managing our companys database which has approx 250 tables in it.
It is a design i have inherited from the people who originally created it, and i'm just having a look at all the foreign key relationships in it.

The actually have not defined many of these relationships. I'm just working through it now and placing these in now.

My question is this: Being such a complex database, can you have too many relationship?
For example, columns 'product_ID','Customer_ID', 'Branch_ID', and 'User_ID' occur in around 50 tables each.

If I enforce all these relationships, is it going to have a negative impact on the database? Is there any other factors i should consider? Is there a limit to how many relationships you should enforce?

Any help would be appreciated.

Josh DolanIMO it would really depend on who is access the database and how...

Sometime enforcing foreign key contraints is going to cause more problems then they are going to solve... but it should be evaluated on a case per case basis...|||I don't have any problem with enforcing all the constraints i want to implement. I would rather deal with the problems that it may occur and have them implemented than nothing at all.

I'm just thinking from the performance and database design angle.
Is it going to degrade performance that much? And, it is good database design to enforce most if not all foreign keys?

Thanks,

Josh|||Providing the foriegn key contraints are valid then IMO you can do no harm in implementing them providing you are happy to deal with possible issues.

Performace may be affected but not to any real extent IMO.|||You don't have too many relationships. They are necessary to maintain the relational integrity of your data, and if you remove them and allow inconsistencies to develop in your data then the extra coding and admin you will need to do to account for the errors WILL slow down your system.

It is more likely that you have too many tables, not too many relationships. Unfortunately, if there is already an interface and/or reporting structure developed around this schema, you are probably stuck with it.

The next time your company is considering developing an important database, advise them to hire a professional database designer to help them. A database is as important to an application as a foundation is important to a house. When I had my home built, I didn't go buy a book at Home Depot and then start pouring concrete myself. I hired professionals.

blindman

Database refresh question

There's a sql server 2000 database that was created as a copy of another db, let's say db1 and copy_of_db1. db1 has been updated (structure and data) since copy_of_db1 was created, while copy_of_db1 has remained static. I now need to update copy_of_db1 to be in sync with db1 and use copy_of_db1 so I can drop db1. What would be the fastest and most efficient way to update copy_of_db1 to mirror the current db1?

Backup the updated one and restore it, in the Backup and restore wizard choose the restore from device option you also have the option to change the name of the restored one. Hope this helps.

|||Thanks!