HELP!! Problem in database backup and restore!

Im trying to backup the databse by selecting the database source, backup type = full, name the backup set, and leave the rest as default. The backup seems to be successful.

The problem comes when I need to restore the backup database, I 've tried many times and a few different kinds of selection, all fails. I've selected the source for restore from device, that is the .bak file from backup folder and remains the rest of selection as default. Here is the error message I get:

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

Anyone can help to solve this problem?!

Create new Database. In Restore window-> Options tab, check "Force restore over existing database". And then try to restore. This may work for you.

Santosh

|||

Thankz santosh...

the solution u had provided.... had successfully solved the problem mention above....but there are some database that still meets another problem during the database restore

here is the error occurs after that....

TITLE: Microsoft SQL Server Management Studio Express

Restore failed for Server 'COMP1\SQLEXPRESS'. (Microsoft.SqlServer.Express.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=Restore+Server&LinkId=20476

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\JGSQL.mdf' cannot be overwritten. It is being used by database 'JGSQL'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476

BUTTONS:

OK

|||

I've try to do the database restore using sql command as below:

RESTORE DATABASE testJG

FROM DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Backup\JGsql.bak'

GO

But I get this error message.

Does this means that I need to restore the master database from systems database to the database testJG before I restore the backup database?

Msg 3102, Level 16, State 1, Line 1

RESTORE cannot process database 'testJG' because it is in use by this session. It is recommended that the master database be used when performing this operation.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

|||It was helpfull to me. Thanx! :)sql

HELP!! Problem in database backup and restore!

Im trying to backup the databse by selecting the database source, backup type = full, name the backup set, and leave the rest as default. The backup seems to be successful.

The problem comes when I need to restore the backup database, I 've tried many times and a few different kinds of selection, all fails. I've selected the source for restore from device, that is the .bak file from backup folder and remains the rest of selection as default. Here is the error message I get:

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

Anyone can help to solve this problem?!

Create new Database. In Restore window-> Options tab, check "Force restore over existing database". And then try to restore. This may work for you.

Santosh

|||

Thankz santosh...

the solution u had provided.... had successfully solved the problem mention above....but there are some database that still meets another problem during the database restore

here is the error occurs after that....

TITLE: Microsoft SQL Server Management Studio Express

Restore failed for Server 'COMP1\SQLEXPRESS'. (Microsoft.SqlServer.Express.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=Restore+Server&LinkId=20476

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\JGSQL.mdf' cannot be overwritten. It is being used by database 'JGSQL'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476

BUTTONS:

OK

|||

I've try to do the database restore using sql command as below:

RESTORE DATABASE testJG

FROM DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Backup\JGsql.bak'

GO

But I get this error message.

Does this means that I need to restore the master database from systems database to the database testJG before I restore the backup database?

Msg 3102, Level 16, State 1, Line 1

RESTORE cannot process database 'testJG' because it is in use by this session. It is recommended that the master database be used when performing this operation.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

|||It was helpfull to me. Thanx! :)

Help! Report Model Project doesn't like my primary key

I'm creating a report model in VS2005 I've created my data source fine and I have selected all the tables I want in the report model data view.

The problem is that for one of the tables it is refusing to acknowledge the promary key. If I try to create the report model it compains that the table doesn't have a primary key.

So I went into SQL Management Studio and checked the table, Lo and behold the primary key is there!!! I tried droping the primary key and recreating it but it still says there is no primary ley on the table.

Any ideas?!?Just did some fiddling and managed to find the problem.

There is a Unique Clustered Index on the table which the problem comes from, with the index it doesn't see the primary key, without it the key suddenly appears

Help! Date formatting nightmare!

I converted an Access Database to SQL Express. The dates were converted to datetime

I'm using VWD 2005

Here is the source of my date and the query.

sqlDate = (DateTime.Now.AddDays(-7))

sqlTxt ="SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID WHERE (Service_Orders.SDate_Entered >= CONVERT(DATETIME, '" + sqlDate +"', 101)) ORDER BY Service_Orders.SDate_Entered DESC"

This retrurns 0 records.

sqlDate =11/28/2005 12:23:27 AM from the function above.

The querywill return records with :

sqlDate ="2005-11-01 21:56:20"

I tried changing the CONVERT(DATETIME, '" + sqlDate + "',1XX from 100 to 120 with no luck

I know this must be an easy fix, but it is beyond me.

I need to know how to

1. convert my date to the dateformat from "11/28/2005 12:23:27 AM" to "2005-11-01 21:56:20"

or

2. find out how to use the CONVERT(DATETIME, '" + sqlDate + "', 1XX properly

Thanks for any help in advance!

Bill

I always convert them after I recieved the data from the database so the way how you do it depends on where you handle them afterwards. Datagrid has it's own dataformatstring property and incode you can do it like this for an example:
lblFormat.Text = String.Format("{0:dd.MM.yyyy}", myData("myField"))
Find outmore
|||

OK here is the problem:

When I run my query, the query return 0 rows.

I narrowed the problem down to the fact that it doesn't like my date.

I construct the date via the function sqlDate = (DateTime.Now.AddDays(-7))

When I pass that date into my query 0 rows are returned. If I hard code the date with

sqlDate ="2005-11-01 21:56:20" I get all the rows for that date.

Here is my query

sqlTxt ="SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID WHERE (Service_Orders.SDate_Entered >= CONVERT(DATETIME, '" + sqlDate +"', 102)) ORDER BY Service_Orders.SDate_Entered DESC"

The field SDate_Entered is a datetime field.

Who would make a product where a simple query is such a problem??

Help! Bad insert to a table which I can't delete now.

I made a mistake in my insert statement, the insert to destination table is
the same as the Source from table. Oops! Now I can't truncate, delete this
table at all. Any way to get rid of it and start again?
Thanks, Alpha
insert into
[TisSuite].[dbo].tblSource_info(extTID,RelatedEmployment,RelatedAutoAccident,RelatedOtherAccident,RelatedNotAccident,
ReleasePatientInfo,PlaceOfService,TypeOfService, EMG,COB)
--We default to not releasing patient info
select exttid,0,0,0,1,0,1,41,'','' from [TisSuite].[dbo].tblPCS order by
exttidOops, sorry. I was able to delete the table after all.
"Alpha" wrote:
> I made a mistake in my insert statement, the insert to destination table is
> the same as the Source from table. Oops! Now I can't truncate, delete this
> table at all. Any way to get rid of it and start again?
> Thanks, Alpha
> insert into
> [TisSuite].[dbo].tblSource_info(extTID,RelatedEmployment,RelatedAutoAccident,RelatedOtherAccident,RelatedNotAccident,
> ReleasePatientInfo,PlaceOfService,TypeOfService, EMG,COB)
> --We default to not releasing patient info
> select exttid,0,0,0,1,0,1,41,'','' from [TisSuite].[dbo].tblPCS order by
> exttid
>

Help! Bad insert to a table which I can't delete now.

I made a mistake in my insert statement, the insert to destination table is
the same as the Source from table. Oops! Now I can't truncate, delete this
table at all. Any way to get rid of it and start again?
Thanks, Alpha
insert into
[TisSuite].[dbo]. tblSource_info(extTID,RelatedEmployment,
RelatedAuto
Accident,RelatedOtherAccident,RelatedNot
Accident,
ReleasePatientInfo,PlaceOfService,TypeOf
Service, EMG,COB)
--We default to not releasing patient info
select exttid,0,0,0,1,0,1,41,'','' from [TisSuite].[dbo].tblPCS orde
r by
exttidOops, sorry. I was able to delete the table after all.
"Alpha" wrote:

> I made a mistake in my insert statement, the insert to destination table i
s
> the same as the Source from table. Oops! Now I can't truncate, delete th
is
> table at all. Any way to get rid of it and start again?
> Thanks, Alpha
> insert into
> [TisSuite].[dbo]. tblSource_info(extTID,RelatedEmployment,
RelatedAu
toAccident,RelatedOtherAccident,RelatedN
otAccident,
> ReleasePatientInfo,PlaceOfService,TypeO
fService, EMG,COB)
> --We default to not releasing patient info
> select exttid,0,0,0,1,0,1,41,'','' from [TisSuite].[dbo].tblPCS o
rder by
> exttid
>

Help! Bad insert to a table which I can't delete now.

I made a mistake in my insert statement, the insert to destination table is
the same as the Source from table. Oops! Now I can't truncate, delete this
table at all. Any way to get rid of it and start again?
Thanks, Alpha
insert into
[TisSuite].[dbo].tblSource_info(extTID,RelatedEmployment,RelatedAu toAccident,RelatedOtherAccident,RelatedNotAccident ,
ReleasePatientInfo,PlaceOfService,TypeOfService, EMG,COB)
--We default to not releasing patient info
select exttid,0,0,0,1,0,1,41,'','' from [TisSuite].[dbo].tblPCS order by
exttid
Oops, sorry. I was able to delete the table after all.
"Alpha" wrote:

> I made a mistake in my insert statement, the insert to destination table is
> the same as the Source from table. Oops! Now I can't truncate, delete this
> table at all. Any way to get rid of it and start again?
> Thanks, Alpha
> insert into
> [TisSuite].[dbo].tblSource_info(extTID,RelatedEmployment,RelatedAu toAccident,RelatedOtherAccident,RelatedNotAccident ,
> ReleasePatientInfo,PlaceOfService,TypeOfService, EMG,COB)
> --We default to not releasing patient info
> select exttid,0,0,0,1,0,1,41,'','' from [TisSuite].[dbo].tblPCS order by
> exttid
>