Database state loading after restore.

Hi,
I have sql server 2000 running on windows 2003 clusters. While i
tried to restore the database i got the message that The restore
operation was successful. But still the database is showing state
"Loading."
I restored the database with recovery (Leave database opeational in
EM). I kept the database for a day and it shows still status as
"Loading".
Any help for this is highly appreciated.
Regards,
Jay
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Are you sure it is not only a refers issue in EM? Did you try below:
RESTORE DATABASE dbname WITH RECOVERY
And if you try above, what messages do you get?
Also, if you open QA and say "USE dbname", any errors?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jigar Patel" <jigar_pcs@.rediffmail.com> wrote in message
news:uCLjUt4rDHA.2828@.tk2msftngp13.phx.gbl...
> Hi,
> I have sql server 2000 running on windows 2003 clusters. While i
> tried to restore the database i got the message that The restore
> operation was successful. But still the database is showing state
> "Loading."
> I restored the database with recovery (Leave database opeational in
> EM). I kept the database for a day and it shows still status as
> "Loading".
> Any help for this is highly appreciated.
> Regards,
> Jay
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||I am sure as i have done this operation for atleast 15 times as of now.
I tried everything.
When i try to use
Use Database
Go
The message comes saying the database is still loading is not available.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||So what errors/messages do you get from the below command?
RESTORE DATABASE dbname WITH RECOVERY
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jigar Patel" <jigar_pcs@.rediffmail.com> wrote in message
news:uovOaN7rDHA.2360@.TK2MSFTNGP09.phx.gbl...
> I am sure as i have done this operation for atleast 15 times as of now.
> I tried everything.
> When i try to use
> Use Database
> Go
> The message comes saying the database is still loading is not available.
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Database shows in read only mode ?

Database shows in read only mode when I did try to restore my database.But How to can I convert it to online mode?

pls help

Hi,

You can leave a restored database in one of the following states:

Leave the database ready to use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY) Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY) Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a standby file so that recovery effects can be reverted. (RESTORE WITH STANDBY)

Database setup for backup and recovery

I am new to SQLServer but a DB2 DBA.

I want to be able to backup a specific table and restore it. Actually, i may want to backup/restore several tables - a sub-set of tables on the database.

I understand that backup-recovery is at a database level, not a table-file-filrgroup level. Therefore i have to backup-restore a database, but i only want a table.

How do sites handle this. Are many databases created based on backup recovery requirements.

If so, then how do developers know what database tables reside in - given that there are now many databases created to handle recovery requirements. A synonymns/alias/views added ?

tia

glenn

If you put the table on its own filegroup, you can restore just that filegroup/table. Else, you will need to restore the database to a staging db and copy/transfer the table from the staging db to the real db.|||

Can i restore a single filegroup to a previous point in time (say 8am), but keep the rest of the database unchanged (say 10am).

Or, does the whole database have to be at the same point in time after a restore?

tia

Database setup for backup and recovery

I am new to SQLServer but a DB2 DBA.

I want to be able to backup a specific table and restore it. Actually, i may want to backup/restore several tables - a sub-set of tables on the database.

I understand that backup-recovery is at a database level, not a table-file-filrgroup level. Therefore i have to backup-restore a database, but i only want a table.

How do sites handle this. Are many databases created based on backup recovery requirements.

If so, then how do developers know what database tables reside in - given that there are now many databases created to handle recovery requirements. A synonymns/alias/views added ?

tia

glenn

If you put the table on its own filegroup, you can restore just that filegroup/table. Else, you will need to restore the database to a staging db and copy/transfer the table from the staging db to the real db.|||

Can i restore a single filegroup to a previous point in time (say 8am), but keep the rest of the database unchanged (say 10am).

Or, does the whole database have to be at the same point in time after a restore?

tia

Database says loading, cannot use it

An attempt to restore database left the database in a loading status. Still have backup and would like to restore, but cannot get database out of loading status. Get message that database is inaccessible and cannot be edited until load is complete. I have tried bouncing the server. Also tried restore with recovery.If you can't drop the database because of the state, open Query Analyzer and
try:
USE MASTER
GO
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
UPDATE sysdatabases SET status = 32768
WHERE name = 'db_name'
GO
Restart SQL Server, reconnect using Query Analyzer
USE MASTER
GO
DROP DATABASE db_name
If that doesn't work, repeat but changing status = 0
Then free up disk space, or whatever you need to do, and try to RESTORE
again (maybe you can allocate this restore to a different drive, if space is
the issue).
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"bscottparis" <anonymous@.discussions.microsoft.com> wrote in message
news:BD86FF00-33A2-4147-8B01-688F577190E9@.microsoft.com...
> An attempt to restore database left the database in a loading status.
Still have backup and would like to restore, but cannot get database out of
loading status. Get message that database is inaccessible and cannot be
edited until load is complete. I have tried bouncing the server. Also
tried restore with recovery.

Database save and restore

Hello,

A database EntryForm has a MenuStrip. How could the Menustrip implemented that it would have a Save and Restore data to the database for backup purposes?

What is the Menustrip for? None of the standard Items seems to work. What can be printed or previewed?

This question is NOT about how to save the data into the database from the EntryForm.

Thanks for any answer,

Hi,

Sorry but didn't get you!!!! what you are trying to achieve!? Is their any error in SQL Server Backup And/Or Restore!?

Hemantgiri S. Goswami

|||

Thanks Gos,

I have this small database entry front-end binded to a database. When this is published and run, there is nothing the user can do except what is implemented on it, which is save, delete, add new record to the database. These seems to be working.

When this is published, I can see the setup.exe. When it is run it does not seem to be installing anywhere, just opens up the program ready for action. Well, there is a shortcut in the StartMenu.

In theVisual Studio\Project\MyFirstProject\ I can see the *.mdf file as well as in the Visual Studio\Project\MyFirstPoject\MyFirstProject\bin\Release\

Greetings.

|||

HI,

You might ask this question to Visual Studio / .Net forum as it seems problem with the front end application.

Hemantgiri S. Goswami

|||

Hi Gos,

Right. There is definitely a problem with my application. (Newbie). How to implement the application that the user can save and restore the database?

Thanks,

|||

Hi Cesar,

If i understand properly, you may use SQL DMO to restore/backup database, their is a sample directory from where your SQL Server folder resides in your installation drive, refer http://www.codeproject.com/vb/net/SQLDBBackup.asp

Hemantgiri S. Goswami

|||

Thanks Gos,

A program which produces something, like a picture, a text, a spreadsheet, etc. has the feature that enable it's user to save and open it.

Lets call a small accounting application made by Visual Basic with Sql Express as "Income2007". This application has a DataEntryForm which has Save, Clear, Delete, NewRecord, Find Buttons and a TableViewForm. It also has a MenuStrip and a StatusStrip. That's it.

Can the "Income2007"'s MenuStrip implemented such a manner that would allow the user to save and open his data? What the user of the application would want a MenuStrip in "Income2007" for?

Have you seen Tabor's SqlServer Videos? He includes with the videos a DB Sample: MyCompany.zip, a 320KB file. This file is not an application file. It is a Database file. Is it not?

Can the user do the same, by the "Income2007"'s MenuStrip or other simple method supplied with this program?

The program's user uses the DataEntryForm to handle the database's delete, update, query, etc functions. As it should be. When the user finished he wants to save and take the data with him. Does he have to have SQL Server Manager Studio installed on his computer to able to accomplish this task?

-

As always, appreciate your answer,

|||

Hi Cesar,

Cesar Francisco wrote:

Thanks Gos,

A program which produces something, like a picture, a text, a spreadsheet, etc. has the feature that enable it's user to save and open it.

Lets call a small accounting application made by Visual Basic with Sql Express as "Income2007". This application has a DataEntryForm which has Save, Clear, Delete, NewRecord, Find Buttons and a TableViewForm. It also has a MenuStrip and a StatusStrip. That's it.

Can the "Income2007"'s MenuStrip implemented such a manner that would allow the user to save and open his data? What the user of the application would want a MenuStrip in "Income2007" for?

Have you seen Tabor's SqlServer Videos? He includes with the videos a DB Sample: MyCompany.zip, a 320KB file. This file is not an application file. It is a Database file. Is it not?

Can the user do the same, by the "Income2007"'s MenuStrip or other simple method supplied with this program?

The program's user uses the DataEntryForm to handle the database's delete, update, query, etc functions. As it should be. When the user finished he wants to save and take the data with him. Does he have to have SQL Server Manager Studio installed on his computer to able to accomplish this task?

-

As always, appreciate your answer,

Yes your program can save,insert,delete and update data within Database.

No he don't need to have Sql Server Management Studio installed on his machine but if it is Windows Forms application you have to deploy your package (you can built one using package and deploy wizard) Refer an exampler here http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=3805&lngWId=10

You may definately allow user to Save and Open a Data in SQL Server Database, when he clicks on Save you have to run insert query and saved record can be displayed in Grid and while he wants to open it for editing he/she click on the record which will populate all your textbox/combo/list box with record for editing. Kindly refer suggested link for Code.

Hemantgiri S. Goswami

|||

Thanks Gos - for your patience,

I am certainly learning about my lack of communication ability.

What is implemented so far?

"Income 2007" has

One Form, 1TabControl with 4TabPages. The Tabpages are DataEntry, DataEntryTableView, Query, QueryTableView. When the Form Loads, through the TableAdapter.Fill the DataEntryTableView is filled with data. The user can see all the data entered up to that point. In the DataEntry Tabpage the user can Save, Delete, Update the database's data.

Through the QueryEntry Tabpage and QueryView Tabpage the user can look at sorted data.

Through the Build>Publish, "Income 2007" is saved to a CD and is given to the user.

--

NOT Implemented:

The user calls: I would like to save the data (*.mdf) on the day's end and have it on a disk.

Why?

The user has two computers each one is located in a different city (City A and B), each one has a copy of "Income 2007". These computers are NOT networked. In City A, during the week, the user enters data through the DataEntry Tabpage into "Income 2007(A)" 's database. .

On the weekends he goes to City B. He opens "Income 2007(B)". At the very first, he has no data in it's database. If he has NO data in the database, he copies the data File (*.mdf) from "Income 2007(A)" into "Income 2007(B)" database. When he finished working he saves the data to a File. He travells back to City A, purges the data in "Income 2007(A)" and copies the data from "Income 2007(B)" into "Income 2007(A)" 's database.

I believed, that through an implemented MenuStrip the user can save *.mdf (the data of the database) to a File and take the data (not the database) with him (on a floppy or memory stick). Is there a way for the USER to save the data to a File? Definitely there should be a way. What kind of program is that where you can't save your data? A crippled, demo program maybe.

--

In resumen: the problem is NOT how to manipulate the database' data in the program, but rather how to enable the user of the windows application "Income 2007" to Save, Purge, Open the (*.mdf) in "Income 2007(A) or (B)".

Greetings,

|||

Hi,

If i understood correctly you are asking about backup of Database(is this what you mean by save data / .mdf & .ldf files) you may take a look at SQL DMO sample code, backup of SQL Database contains .mdf/.ldf files , and if you wish you can use sp_attach_db and sp_detach_db, but this will work after you are disconnected from Database. SQL DMO sample codes can be found in folder where sql server is installed.

Hemantgiri S. Goswami

|||

Hello again,

I suspect from your last answer that the answer to my question is NO. It seems that you need a copy of Managment Studio, which by the way, will overwhelm any causal user.

1. Now how this could be accomplished inside the application? Is there a way to implement backup from inside the application? Can a script written to open Studio in the background and perform this task?

2. As you said "but this will work after you are disconnected from Database." How could the user disconnect from the database and than save and later open the database from inside the application?

Illustration of my question:

Not long ago, I did download a small application called Ant Movie Catalog (http://www.antp.be/software/moviecatalog/sources) ~ 3MB. This was written in Borland Delphi 7. It is, as it says, a Movie Catalog. After installation there was nothing in it, it was just an empty application. (Exactly the same as my "Income 2007"). After collecting data through the interface, I did save the collected data into a file, in this case a *.amc (~11MB) and saved into a memory stick. How? By clicking on File>Save. To look at my or anybody else collection, the only thing I have to do is File>Open>navigate where the *.amd file is and presto.

Cheers,

|||

Hi,

1. refer http://www.google.co.in/search?hl=en&q=sql+dmo%2Bbackup&btnG=Search&meta= for code example to backup from inside application, i have done this in vb and vb.net

2. That is if you wants to Attach & Detach Database.

Hemantgiri S. Goswami

|||

Hello again,

I see that this question already draw "sunshine". I pray Gos, show the code and explain. Hey, I am a Newbie.

I really do not care to attach or detach the database. The user of my application (if ever) has to be able to do the basic functions that any application should have - as per indicated in the preceding message about the Ant Catalogue. I wonder if you had time to have a look at it.

The example on the site you pointed to is for VB 6.

-

This was a question recently rised on the Forum by Allen White 01-02-2007

Re: Schedule SQL Express Backup using SQL/DMO
is it possible to Schedule SQLExpress Backup using SQL/DMO? SQL Express does not support SQL Agent then how can I view jobs created using SQLDMO? In management studio ,jobs option is unavailable. I have written code to add JObSchedule/job/jobStep and program is running without errors .. but no way to find out whether job is actually created and executed.

Answer by dmouser: Because SQL Express doesn't include the SQL Server Agent the jobs you are creating can't be executed. You'd be best served to use Windows Scheduler to run the programs performing your backups.

-

|||

Hello all, seekers of truth and real solution,

Seemingly there is an overwhelming interest in this subject. As it should be! Are we asking for something outlandish, bizarre? What would be the "Word" for, if it could not save, and open a file created with it?
If VB 2005 Express was intended for hobbyist - how to demonstrate the "Hello World" application on a friend's computer? Installing the behemoth, enterprise strength, multi-user, bulletproof SQL Express 250MB first? But lets say the application have some limited merit, - should the user hire an expert/ buy other program, just save and restore the data?
The deprecated Access database might be the solution to this problem for hobbyists. I posted in "Solution! Access database....." what I got so far.
It shows some promise - plus it has a small footprint. Nevertheless, none of the experts on this circuit gave me tips (code, please) how to solve the perennial problem mentioned above. What? Is it too difficult? We are talking about Access, not SQL anymore. It is well in the realm of possibility. Beside the free "Ant Movie Catalog" (by the way, it's author - a selfless guy from Belgium - giving away the source code as well. Too bad it is written in Delphi 7!) -- I have a small commercial program based on Access but not in "Access". It can do all that (save, purge, restore).

Is somebody out there somewhere to give the code away for a fee (not free)? It is becoming such a wasted time looking for something so trivial.

Cheers,

|||

The solution for Access/Jet databases.

'Do not forget to add reference to

Microsoft ActiveX Data Object Recordset 2.8 Library,

Microsoft ADO Ext.2.8 for DDL and Security and

Microsoft Jet and Replication Objects 2.6 Library under the COM Tab.

Imports System.IO
Imports System.IO.File
Public Class Form3
Public Shared Sub Restart()
End Sub

'Watch out for the DialogBoxes: BackupInfo,RestoreRestart,NewDBRestart.DataBaseEraseWarning.

You have to make them,

'BACKUP A DATABASE

'This backup will add a timestamp Month/Day/Year-HoursMinutes to the original database (DB.mdb) file's name and moves that file into a BackUps folder. Do not forget to add this BackUps folder before publish your app.

Private Sub BackUp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUp.Click
Dim x As String
Dim f As String
Dim z As String
Dim g As String
Dim BCK As New BackupInfo
x = Format(Now(), "_MMddyy_HHmm")
z = "C:\Program Files\YourApplication\DB.mdb"
g = x + ".mdb"
f = "C:\Program Files\YourApplicationNameHere\BackUps\DB" & g & ""
File.Copy(z, f)
ToolStripStatusLabel1.Text = "Backup completed succesfully."
If BCK.ShowDialog() = DialogResult.OK Then
End If
End Sub

'RESTORE A DATABASE

'Find you Backup file with TimeStamp in the BackUps folder (or anywhere else you backed up your database).

'This will replace (overwrite the database) in the YourApplication folder.
Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RestoreDB.Click
Dim Filename As String
Dim R As New RestoreRestart
Dim overwrite As Boolean
overwrite = True
Dim xi As String
With OpenFileDialog1
'.Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*"
If .ShowDialog() = DialogResult.OK Then
Filename = .FileName
xi = "C:\Program Files\YourApplicationNameHere\DB.mdb"
File.Copy(Filename, xi, overwrite)
End If
End With

'Notify user in the ToolStrip (or MessageBox.Show("Data restored....")
ToolStripStatusLabel1.Text = "Data restored successfully"

'Restart application

If R.ShowDialog() = DialogResult.OK Then
Application.Restart()
End If
End Sub

'CREATE A NEW EMPTY DATABASE.
Private Sub CrNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CrNewDB.Click
Dim L As New DatabaseEraseWarning
Dim Cat As ADOX.Catalog
Cat = New ADOX.Catalog
Dim R2 As New NewDBRestart
If File.Exists("C:\Program Files\YourApplicationNameHere\DB.mdb") Then
If L.ShowDialog() = DialogResult.Cancel Then
Exit Sub
Else
File.Delete("C:\Program Files\YourApplicationNameHere\DB.mdb")
End If
End If
Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\ YourApplicationNameHere \
DB.mdb; Jet OLEDB:Engine Type=5")

Dim Cn As ADODB.Connection
'Dim Cat As ADOX.Catalog
Dim Tablename As ADOX.Table

'Taylor these according to your need - add so many column as you need.
Dim col As ADOX.Column = New ADOX.Column
Dim col1 As ADOX.Column = New ADOX.Column
Dim col2 As ADOX.Column = New ADOX.Column
Dim col3 As ADOX.Column = New ADOX.Column
Dim col4 As ADOX.Column = New ADOX.Column
Dim col5 As ADOX.Column = New ADOX.Column
Dim col6 As ADOX.Column = New ADOX.Column

Cn = New ADODB.Connection
Cat = New ADOX.Catalog
Tablename = New ADOX.Table

'Open the connection
Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\YourApplicationNameHere\DB.mdb;Jet OLEDB:Engine Type=5")
'Open the Catalog
Cat.ActiveConnection = Cn

'Create the table (you can name it anyway you want)
Tablename.Name = "Table1"

'Taylor these according to your need - add so many column as you need. Watch for the DataType!
col.Name = "ID"
col.Type = ADOX.DataTypeEnum.adInteger
col1.Name = "Serial"
col1.Type = ADOX.DataTypeEnum.adInteger
col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col2.Name = "FName"
col2.Type = ADOX.DataTypeEnum.adVarWChar
col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col3.Name = "LName"
col3.Type = ADOX.DataTypeEnum.adVarWChar
col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col4.Name = "DOB"
col4.Type = ADOX.DataTypeEnum.adDate
col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col5.Name = "Sex"
col5.Type = ADOX.DataTypeEnum.adVarWChar
col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col6.Name = "Ph1"
col6.Type = ADOX.DataTypeEnum.adVarWChar
col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable

Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID")

'You have to append all your columns you have created above
Tablename.Columns.Append(col)
Tablename.Columns.Append(col1)
Tablename.Columns.Append(col2)
Tablename.Columns.Append(col3)
Tablename.Columns.Append(col4)
Tablename.Columns.Append(col5)
Tablename.Columns.Append(col6)

'Append the newly created table to the Tables Collection
Cat.Tables.Append(Tablename)

'User notification (again you can use MessageBox.Show() if you want)

ToolStripStatusLabel1.Text = "A new empty database was created successfully"

'clean up objects
Tablename = Nothing
Cat = Nothing
Cn.Close()
Cn = Nothing

'This restart the application
If R2.ShowDialog() = DialogResult.OK Then
Application.Restart()
End If
End Sub

'COMPACT A DATABASE
'Have a Button with "CompactDB" design name
Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CompactDB.Click
Dim JRO As JRO.JetEngine
JRO = New JRO.JetEngine

'The first source is the original, the second is the compacted database under an other

name.

JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program

Files\VSoft\AppMiss\AmDB.mdb; Jet OLEDB:Engine Type=5",

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program

Files\VSoft\AppMiss\AmDBComp.mdb; JetOLEDB:Engine Type=5")

'Here the original (not compacted database is deleted)
File.Delete("C:\Program Files\YourApplicationNameHere\DB.mdb")

'Here the compacted database is renamed to the original database.
Rename(" C:\Program Files\YourApplicationNameHere\DBComp.mdb", "C:\Program

Files\ YourApplicationNameHere \DB.mdb")

'User notification
ToolStripStatusLabel1.Text = "The database was compacted successfully"
End Sub

End Class

Database save and restore

Hello,

A database EntryForm has a MenuStrip. How could the Menustrip implemented that it would have a Save and Restore data to the database for backup purposes?

What is the Menustrip for? None of the standard Items seems to work. What can be printed or previewed?

This question is NOT about how to save the data into the database from the EntryForm.

Thanks for any answer,

Hi,

Sorry but didn't get you!!!! what you are trying to achieve!? Is their any error in SQL Server Backup And/Or Restore!?

Hemantgiri S. Goswami

|||

Thanks Gos,

I have this small database entry front-end binded to a database. When this is published and run, there is nothing the user can do except what is implemented on it, which is save, delete, add new record to the database. These seems to be working.

When this is published, I can see the setup.exe. When it is run it does not seem to be installing anywhere, just opens up the program ready for action. Well, there is a shortcut in the StartMenu.

In theVisual Studio\Project\MyFirstProject\ I can see the *.mdf file as well as in the Visual Studio\Project\MyFirstPoject\MyFirstProject\bin\Release\

Greetings.

|||

HI,

You might ask this question to Visual Studio / .Net forum as it seems problem with the front end application.

Hemantgiri S. Goswami

|||

Hi Gos,

Right. There is definitely a problem with my application. (Newbie). How to implement the application that the user can save and restore the database?

Thanks,

|||

Hi Cesar,

If i understand properly, you may use SQL DMO to restore/backup database, their is a sample directory from where your SQL Server folder resides in your installation drive, refer http://www.codeproject.com/vb/net/SQLDBBackup.asp

Hemantgiri S. Goswami

|||

Thanks Gos,

A program which produces something, like a picture, a text, a spreadsheet, etc. has the feature that enable it's user to save and open it.

Lets call a small accounting application made by Visual Basic with Sql Express as "Income2007". This application has a DataEntryForm which has Save, Clear, Delete, NewRecord, Find Buttons and a TableViewForm. It also has a MenuStrip and a StatusStrip. That's it.

Can the "Income2007"'s MenuStrip implemented such a manner that would allow the user to save and open his data? What the user of the application would want a MenuStrip in "Income2007" for?

Have you seen Tabor's SqlServer Videos? He includes with the videos a DB Sample: MyCompany.zip, a 320KB file. This file is not an application file. It is a Database file. Is it not?

Can the user do the same, by the "Income2007"'s MenuStrip or other simple method supplied with this program?

The program's user uses the DataEntryForm to handle the database's delete, update, query, etc functions. As it should be. When the user finished he wants to save and take the data with him. Does he have to have SQL Server Manager Studio installed on his computer to able to accomplish this task?

-

As always, appreciate your answer,

|||

Hi Cesar,

Cesar Francisco wrote:

Thanks Gos,

A program which produces something, like a picture, a text, a spreadsheet, etc. has the feature that enable it's user to save and open it.

Lets call a small accounting application made by Visual Basic with Sql Express as "Income2007". This application has a DataEntryForm which has Save, Clear, Delete, NewRecord, Find Buttons and a TableViewForm. It also has a MenuStrip and a StatusStrip. That's it.

Can the "Income2007"'s MenuStrip implemented such a manner that would allow the user to save and open his data? What the user of the application would want a MenuStrip in "Income2007" for?

Have you seen Tabor's SqlServer Videos? He includes with the videos a DB Sample: MyCompany.zip, a 320KB file. This file is not an application file. It is a Database file. Is it not?

Can the user do the same, by the "Income2007"'s MenuStrip or other simple method supplied with this program?

The program's user uses the DataEntryForm to handle the database's delete, update, query, etc functions. As it should be. When the user finished he wants to save and take the data with him. Does he have to have SQL Server Manager Studio installed on his computer to able to accomplish this task?

-

As always, appreciate your answer,

Yes your program can save,insert,delete and update data within Database.

No he don't need to have Sql Server Management Studio installed on his machine but if it is Windows Forms application you have to deploy your package (you can built one using package and deploy wizard) Refer an exampler here http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=3805&lngWId=10

You may definately allow user to Save and Open a Data in SQL Server Database, when he clicks on Save you have to run insert query and saved record can be displayed in Grid and while he wants to open it for editing he/she click on the record which will populate all your textbox/combo/list box with record for editing. Kindly refer suggested link for Code.

Hemantgiri S. Goswami

|||

Thanks Gos - for your patience,

I am certainly learning about my lack of communication ability.

What is implemented so far?

"Income 2007" has

One Form, 1TabControl with 4TabPages. The Tabpages are DataEntry, DataEntryTableView, Query, QueryTableView. When the Form Loads, through the TableAdapter.Fill the DataEntryTableView is filled with data. The user can see all the data entered up to that point. In the DataEntry Tabpage the user can Save, Delete, Update the database's data.

Through the QueryEntry Tabpage and QueryView Tabpage the user can look at sorted data.

Through the Build>Publish, "Income 2007" is saved to a CD and is given to the user.

--

NOT Implemented:

The user calls: I would like to save the data (*.mdf) on the day's end and have it on a disk.

Why?

The user has two computers each one is located in a different city (City A and B), each one has a copy of "Income 2007". These computers are NOT networked. In City A, during the week, the user enters data through the DataEntry Tabpage into "Income 2007(A)" 's database. .

On the weekends he goes to City B. He opens "Income 2007(B)". At the very first, he has no data in it's database. If he has NO data in the database, he copies the data File (*.mdf) from "Income 2007(A)" into "Income 2007(B)" database. When he finished working he saves the data to a File. He travells back to City A, purges the data in "Income 2007(A)" and copies the data from "Income 2007(B)" into "Income 2007(A)" 's database.

I believed, that through an implemented MenuStrip the user can save *.mdf (the data of the database) to a File and take the data (not the database) with him (on a floppy or memory stick). Is there a way for the USER to save the data to a File? Definitely there should be a way. What kind of program is that where you can't save your data? A crippled, demo program maybe.

--

In resumen: the problem is NOT how to manipulate the database' data in the program, but rather how to enable the user of the windows application "Income 2007" to Save, Purge, Open the (*.mdf) in "Income 2007(A) or (B)".

Greetings,

|||

Hi,

If i understood correctly you are asking about backup of Database(is this what you mean by save data / .mdf & .ldf files) you may take a look at SQL DMO sample code, backup of SQL Database contains .mdf/.ldf files , and if you wish you can use sp_attach_db and sp_detach_db, but this will work after you are disconnected from Database. SQL DMO sample codes can be found in folder where sql server is installed.

Hemantgiri S. Goswami

|||

Hello again,

I suspect from your last answer that the answer to my question is NO. It seems that you need a copy of Managment Studio, which by the way, will overwhelm any causal user.

1. Now how this could be accomplished inside the application? Is there a way to implement backup from inside the application? Can a script written to open Studio in the background and perform this task?

2. As you said "but this will work after you are disconnected from Database." How could the user disconnect from the database and than save and later open the database from inside the application?

Illustration of my question:

Not long ago, I did download a small application called Ant Movie Catalog (http://www.antp.be/software/moviecatalog/sources) ~ 3MB. This was written in Borland Delphi 7. It is, as it says, a Movie Catalog. After installation there was nothing in it, it was just an empty application. (Exactly the same as my "Income 2007"). After collecting data through the interface, I did save the collected data into a file, in this case a *.amc (~11MB) and saved into a memory stick. How? By clicking on File>Save. To look at my or anybody else collection, the only thing I have to do is File>Open>navigate where the *.amd file is and presto.

Cheers,

|||

Hi,

1. refer http://www.google.co.in/search?hl=en&q=sql+dmo%2Bbackup&btnG=Search&meta= for code example to backup from inside application, i have done this in vb and vb.net

2. That is if you wants to Attach & Detach Database.

Hemantgiri S. Goswami

|||

Hello again,

I see that this question already draw "sunshine". I pray Gos, show the code and explain. Hey, I am a Newbie.

I really do not care to attach or detach the database. The user of my application (if ever) has to be able to do the basic functions that any application should have - as per indicated in the preceding message about the Ant Catalogue. I wonder if you had time to have a look at it.

The example on the site you pointed to is for VB 6.

-

This was a question recently rised on the Forum by Allen White 01-02-2007

Re: Schedule SQL Express Backup using SQL/DMO
is it possible to Schedule SQLExpress Backup using SQL/DMO? SQL Express does not support SQL Agent then how can I view jobs created using SQLDMO? In management studio ,jobs option is unavailable. I have written code to add JObSchedule/job/jobStep and program is running without errors .. but no way to find out whether job is actually created and executed.

Answer by dmouser: Because SQL Express doesn't include the SQL Server Agent the jobs you are creating can't be executed. You'd be best served to use Windows Scheduler to run the programs performing your backups.

-

|||

Hello all, seekers of truth and real solution,

Seemingly there is an overwhelming interest in this subject. As it should be! Are we asking for something outlandish, bizarre? What would be the "Word" for, if it could not save, and open a file created with it?
If VB 2005 Express was intended for hobbyist - how to demonstrate the "Hello World" application on a friend's computer? Installing the behemoth, enterprise strength, multi-user, bulletproof SQL Express 250MB first? But lets say the application have some limited merit, - should the user hire an expert/ buy other program, just save and restore the data?
The deprecated Access database might be the solution to this problem for hobbyists. I posted in "Solution! Access database....." what I got so far.
It shows some promise - plus it has a small footprint. Nevertheless, none of the experts on this circuit gave me tips (code, please) how to solve the perennial problem mentioned above. What? Is it too difficult? We are talking about Access, not SQL anymore. It is well in the realm of possibility. Beside the free "Ant Movie Catalog" (by the way, it's author - a selfless guy from Belgium - giving away the source code as well. Too bad it is written in Delphi 7!) -- I have a small commercial program based on Access but not in "Access". It can do all that (save, purge, restore).

Is somebody out there somewhere to give the code away for a fee (not free)? It is becoming such a wasted time looking for something so trivial.

Cheers,

|||

The solution for Access/Jet databases.

'Do not forget to add reference to

Microsoft ActiveX Data Object Recordset 2.8 Library,

Microsoft ADO Ext.2.8 for DDL and Security and

Microsoft Jet and Replication Objects 2.6 Library under the COM Tab.

Imports System.IO
Imports System.IO.File
Public Class Form3
Public Shared Sub Restart()
End Sub

'Watch out for the DialogBoxes: BackupInfo,RestoreRestart,NewDBRestart.DataBaseEraseWarning.

You have to make them,

'BACKUP A DATABASE

'This backup will add a timestamp Month/Day/Year-HoursMinutes to the original database (DB.mdb) file's name and moves that file into a BackUps folder. Do not forget to add this BackUps folder before publish your app.

Private Sub BackUp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUp.Click
Dim x As String
Dim f As String
Dim z As String
Dim g As String
Dim BCK As New BackupInfo
x = Format(Now(), "_MMddyy_HHmm")
z = "C:\Program Files\YourApplication\DB.mdb"
g = x + ".mdb"
f = "C:\Program Files\YourApplicationNameHere\BackUps\DB" & g & ""
File.Copy(z, f)
ToolStripStatusLabel1.Text = "Backup completed succesfully."
If BCK.ShowDialog() = DialogResult.OK Then
End If
End Sub

'RESTORE A DATABASE

'Find you Backup file with TimeStamp in the BackUps folder (or anywhere else you backed up your database).

'This will replace (overwrite the database) in the YourApplication folder.
Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RestoreDB.Click
Dim Filename As String
Dim R As New RestoreRestart
Dim overwrite As Boolean
overwrite = True
Dim xi As String
With OpenFileDialog1
'.Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*"
If .ShowDialog() = DialogResult.OK Then
Filename = .FileName
xi = "C:\Program Files\YourApplicationNameHere\DB.mdb"
File.Copy(Filename, xi, overwrite)
End If
End With

'Notify user in the ToolStrip (or MessageBox.Show("Data restored....")
ToolStripStatusLabel1.Text = "Data restored successfully"

'Restart application

If R.ShowDialog() = DialogResult.OK Then
Application.Restart()
End If
End Sub

'CREATE A NEW EMPTY DATABASE.
Private Sub CrNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CrNewDB.Click
Dim L As New DatabaseEraseWarning
Dim Cat As ADOX.Catalog
Cat = New ADOX.Catalog
Dim R2 As New NewDBRestart
If File.Exists("C:\Program Files\YourApplicationNameHere\DB.mdb") Then
If L.ShowDialog() = DialogResult.Cancel Then
Exit Sub
Else
File.Delete("C:\Program Files\YourApplicationNameHere\DB.mdb")
End If
End If
Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\ YourApplicationNameHere \
DB.mdb; Jet OLEDB:Engine Type=5")

Dim Cn As ADODB.Connection
'Dim Cat As ADOX.Catalog
Dim Tablename As ADOX.Table

'Taylor these according to your need - add so many column as you need.
Dim col As ADOX.Column = New ADOX.Column
Dim col1 As ADOX.Column = New ADOX.Column
Dim col2 As ADOX.Column = New ADOX.Column
Dim col3 As ADOX.Column = New ADOX.Column
Dim col4 As ADOX.Column = New ADOX.Column
Dim col5 As ADOX.Column = New ADOX.Column
Dim col6 As ADOX.Column = New ADOX.Column

Cn = New ADODB.Connection
Cat = New ADOX.Catalog
Tablename = New ADOX.Table

'Open the connection
Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\YourApplicationNameHere\DB.mdb;Jet OLEDB:Engine Type=5")
'Open the Catalog
Cat.ActiveConnection = Cn

'Create the table (you can name it anyway you want)
Tablename.Name = "Table1"

'Taylor these according to your need - add so many column as you need. Watch for the DataType!
col.Name = "ID"
col.Type = ADOX.DataTypeEnum.adInteger
col1.Name = "Serial"
col1.Type = ADOX.DataTypeEnum.adInteger
col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col2.Name = "FName"
col2.Type = ADOX.DataTypeEnum.adVarWChar
col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col3.Name = "LName"
col3.Type = ADOX.DataTypeEnum.adVarWChar
col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col4.Name = "DOB"
col4.Type = ADOX.DataTypeEnum.adDate
col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col5.Name = "Sex"
col5.Type = ADOX.DataTypeEnum.adVarWChar
col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col6.Name = "Ph1"
col6.Type = ADOX.DataTypeEnum.adVarWChar
col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable

Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID")

'You have to append all your columns you have created above
Tablename.Columns.Append(col)
Tablename.Columns.Append(col1)
Tablename.Columns.Append(col2)
Tablename.Columns.Append(col3)
Tablename.Columns.Append(col4)
Tablename.Columns.Append(col5)
Tablename.Columns.Append(col6)

'Append the newly created table to the Tables Collection
Cat.Tables.Append(Tablename)

'User notification (again you can use MessageBox.Show() if you want)

ToolStripStatusLabel1.Text = "A new empty database was created successfully"

'clean up objects
Tablename = Nothing
Cat = Nothing
Cn.Close()
Cn = Nothing

'This restart the application
If R2.ShowDialog() = DialogResult.OK Then
Application.Restart()
End If
End Sub

'COMPACT A DATABASE
'Have a Button with "CompactDB" design name
Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CompactDB.Click
Dim JRO As JRO.JetEngine
JRO = New JRO.JetEngine

'The first source is the original, the second is the compacted database under an other

name.

JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program

Files\VSoft\AppMiss\AmDB.mdb; Jet OLEDB:Engine Type=5",

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program

Files\VSoft\AppMiss\AmDBComp.mdb; JetOLEDB:Engine Type=5")

'Here the original (not compacted database is deleted)
File.Delete("C:\Program Files\YourApplicationNameHere\DB.mdb")

'Here the compacted database is renamed to the original database.
Rename(" C:\Program Files\YourApplicationNameHere\DBComp.mdb", "C:\Program

Files\ YourApplicationNameHere \DB.mdb")

'User notification
ToolStripStatusLabel1.Text = "The database was compacted successfully"
End Sub

End Class

Database save and restore

Hello,

A database EntryForm has a MenuStrip. How could the Menustrip implemented that it would have a Save and Restore data to the database for backup purposes?

What is the Menustrip for? None of the standard Items seems to work. What can be printed or previewed?

This question is NOT about how to save the data into the database from the EntryForm.

Thanks for any answer,

Hi,

Sorry but didn't get you!!!! what you are trying to achieve!? Is their any error in SQL Server Backup And/Or Restore!?

Hemantgiri S. Goswami

|||

Thanks Gos,

I have this small database entry front-end binded to a database. When this is published and run, there is nothing the user can do except what is implemented on it, which is save, delete, add new record to the database. These seems to be working.

When this is published, I can see the setup.exe. When it is run it does not seem to be installing anywhere, just opens up the program ready for action. Well, there is a shortcut in the StartMenu.

In theVisual Studio\Project\MyFirstProject\ I can see the *.mdf file as well as in the Visual Studio\Project\MyFirstPoject\MyFirstProject\bin\Release\

Greetings.

|||

HI,

You might ask this question to Visual Studio / .Net forum as it seems problem with the front end application.

Hemantgiri S. Goswami

|||

Hi Gos,

Right. There is definitely a problem with my application. (Newbie). How to implement the application that the user can save and restore the database?

Thanks,

|||

Hi Cesar,

If i understand properly, you may use SQL DMO to restore/backup database, their is a sample directory from where your SQL Server folder resides in your installation drive, refer http://www.codeproject.com/vb/net/SQLDBBackup.asp

Hemantgiri S. Goswami

|||

Thanks Gos,

A program which produces something, like a picture, a text, a spreadsheet, etc. has the feature that enable it's user to save and open it.

Lets call a small accounting application made by Visual Basic with Sql Express as "Income2007". This application has a DataEntryForm which has Save, Clear, Delete, NewRecord, Find Buttons and a TableViewForm. It also has a MenuStrip and a StatusStrip. That's it.

Can the "Income2007"'s MenuStrip implemented such a manner that would allow the user to save and open his data? What the user of the application would want a MenuStrip in "Income2007" for?

Have you seen Tabor's SqlServer Videos? He includes with the videos a DB Sample: MyCompany.zip, a 320KB file. This file is not an application file. It is a Database file. Is it not?

Can the user do the same, by the "Income2007"'s MenuStrip or other simple method supplied with this program?

The program's user uses the DataEntryForm to handle the database's delete, update, query, etc functions. As it should be. When the user finished he wants to save and take the data with him. Does he have to have SQL Server Manager Studio installed on his computer to able to accomplish this task?

-

As always, appreciate your answer,

|||

Hi Cesar,

Cesar Francisco wrote:

Thanks Gos,

A program which produces something, like a picture, a text, a spreadsheet, etc. has the feature that enable it's user to save and open it.

Lets call a small accounting application made by Visual Basic with Sql Express as "Income2007". This application has a DataEntryForm which has Save, Clear, Delete, NewRecord, Find Buttons and a TableViewForm. It also has a MenuStrip and a StatusStrip. That's it.

Can the "Income2007"'s MenuStrip implemented such a manner that would allow the user to save and open his data? What the user of the application would want a MenuStrip in "Income2007" for?

Have you seen Tabor's SqlServer Videos? He includes with the videos a DB Sample: MyCompany.zip, a 320KB file. This file is not an application file. It is a Database file. Is it not?

Can the user do the same, by the "Income2007"'s MenuStrip or other simple method supplied with this program?

The program's user uses the DataEntryForm to handle the database's delete, update, query, etc functions. As it should be. When the user finished he wants to save and take the data with him. Does he have to have SQL Server Manager Studio installed on his computer to able to accomplish this task?

-

As always, appreciate your answer,

Yes your program can save,insert,delete and update data within Database.

No he don't need to have Sql Server Management Studio installed on his machine but if it is Windows Forms application you have to deploy your package (you can built one using package and deploy wizard) Refer an exampler here http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=3805&lngWId=10

You may definately allow user to Save and Open a Data in SQL Server Database, when he clicks on Save you have to run insert query and saved record can be displayed in Grid and while he wants to open it for editing he/she click on the record which will populate all your textbox/combo/list box with record for editing. Kindly refer suggested link for Code.

Hemantgiri S. Goswami

|||

Thanks Gos - for your patience,

I am certainly learning about my lack of communication ability.

What is implemented so far?

"Income 2007" has

One Form, 1TabControl with 4TabPages. The Tabpages are DataEntry, DataEntryTableView, Query, QueryTableView. When the Form Loads, through the TableAdapter.Fill the DataEntryTableView is filled with data. The user can see all the data entered up to that point. In the DataEntry Tabpage the user can Save, Delete, Update the database's data.

Through the QueryEntry Tabpage and QueryView Tabpage the user can look at sorted data.

Through the Build>Publish, "Income 2007" is saved to a CD and is given to the user.

--

NOT Implemented:

The user calls: I would like to save the data (*.mdf) on the day's end and have it on a disk.

Why?

The user has two computers each one is located in a different city (City A and B), each one has a copy of "Income 2007". These computers are NOT networked. In City A, during the week, the user enters data through the DataEntry Tabpage into "Income 2007(A)" 's database. .

On the weekends he goes to City B. He opens "Income 2007(B)". At the very first, he has no data in it's database. If he has NO data in the database, he copies the data File (*.mdf) from "Income 2007(A)" into "Income 2007(B)" database. When he finished working he saves the data to a File. He travells back to City A, purges the data in "Income 2007(A)" and copies the data from "Income 2007(B)" into "Income 2007(A)" 's database.

I believed, that through an implemented MenuStrip the user can save *.mdf (the data of the database) to a File and take the data (not the database) with him (on a floppy or memory stick). Is there a way for the USER to save the data to a File? Definitely there should be a way. What kind of program is that where you can't save your data? A crippled, demo program maybe.

--

In resumen: the problem is NOT how to manipulate the database' data in the program, but rather how to enable the user of the windows application "Income 2007" to Save, Purge, Open the (*.mdf) in "Income 2007(A) or (B)".

Greetings,

|||

Hi,

If i understood correctly you are asking about backup of Database(is this what you mean by save data / .mdf & .ldf files) you may take a look at SQL DMO sample code, backup of SQL Database contains .mdf/.ldf files , and if you wish you can use sp_attach_db and sp_detach_db, but this will work after you are disconnected from Database. SQL DMO sample codes can be found in folder where sql server is installed.

Hemantgiri S. Goswami

|||

Hello again,

I suspect from your last answer that the answer to my question is NO. It seems that you need a copy of Managment Studio, which by the way, will overwhelm any causal user.

1. Now how this could be accomplished inside the application? Is there a way to implement backup from inside the application? Can a script written to open Studio in the background and perform this task?

2. As you said "but this will work after you are disconnected from Database." How could the user disconnect from the database and than save and later open the database from inside the application?

Illustration of my question:

Not long ago, I did download a small application called Ant Movie Catalog (http://www.antp.be/software/moviecatalog/sources) ~ 3MB. This was written in Borland Delphi 7. It is, as it says, a Movie Catalog. After installation there was nothing in it, it was just an empty application. (Exactly the same as my "Income 2007"). After collecting data through the interface, I did save the collected data into a file, in this case a *.amc (~11MB) and saved into a memory stick. How? By clicking on File>Save. To look at my or anybody else collection, the only thing I have to do is File>Open>navigate where the *.amd file is and presto.

Cheers,

|||

Hi,

1. refer http://www.google.co.in/search?hl=en&q=sql+dmo%2Bbackup&btnG=Search&meta= for code example to backup from inside application, i have done this in vb and vb.net

2. That is if you wants to Attach & Detach Database.

Hemantgiri S. Goswami

|||

Hello again,

I see that this question already draw "sunshine". I pray Gos, show the code and explain. Hey, I am a Newbie.

I really do not care to attach or detach the database. The user of my application (if ever) has to be able to do the basic functions that any application should have - as per indicated in the preceding message about the Ant Catalogue. I wonder if you had time to have a look at it.

The example on the site you pointed to is for VB 6.

-

This was a question recently rised on the Forum by Allen White 01-02-2007

Re: Schedule SQL Express Backup using SQL/DMO
is it possible to Schedule SQLExpress Backup using SQL/DMO? SQL Express does not support SQL Agent then how can I view jobs created using SQLDMO? In management studio ,jobs option is unavailable. I have written code to add JObSchedule/job/jobStep and program is running without errors .. but no way to find out whether job is actually created and executed.

Answer by dmouser: Because SQL Express doesn't include the SQL Server Agent the jobs you are creating can't be executed. You'd be best served to use Windows Scheduler to run the programs performing your backups.

-

|||

Hello all, seekers of truth and real solution,

Seemingly there is an overwhelming interest in this subject. As it should be! Are we asking for something outlandish, bizarre? What would be the "Word" for, if it could not save, and open a file created with it?
If VB 2005 Express was intended for hobbyist - how to demonstrate the "Hello World" application on a friend's computer? Installing the behemoth, enterprise strength, multi-user, bulletproof SQL Express 250MB first? But lets say the application have some limited merit, - should the user hire an expert/ buy other program, just save and restore the data?
The deprecated Access database might be the solution to this problem for hobbyists. I posted in "Solution! Access database....." what I got so far.
It shows some promise - plus it has a small footprint. Nevertheless, none of the experts on this circuit gave me tips (code, please) how to solve the perennial problem mentioned above. What? Is it too difficult? We are talking about Access, not SQL anymore. It is well in the realm of possibility. Beside the free "Ant Movie Catalog" (by the way, it's author - a selfless guy from Belgium - giving away the source code as well. Too bad it is written in Delphi 7!) -- I have a small commercial program based on Access but not in "Access". It can do all that (save, purge, restore).

Is somebody out there somewhere to give the code away for a fee (not free)? It is becoming such a wasted time looking for something so trivial.

Cheers,

|||

The solution for Access/Jet databases.

'Do not forget to add reference to

Microsoft ActiveX Data Object Recordset 2.8 Library,

Microsoft ADO Ext.2.8 for DDL and Security and

Microsoft Jet and Replication Objects 2.6 Library under the COM Tab.

Imports System.IO
Imports System.IO.File
Public Class Form3
Public Shared Sub Restart()
End Sub

'Watch out for the DialogBoxes: BackupInfo,RestoreRestart,NewDBRestart.DataBaseEraseWarning.

You have to make them,

'BACKUP A DATABASE

'This backup will add a timestamp Month/Day/Year-HoursMinutes to the original database (DB.mdb) file's name and moves that file into a BackUps folder. Do not forget to add this BackUps folder before publish your app.

Private Sub BackUp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUp.Click
Dim x As String
Dim f As String
Dim z As String
Dim g As String
Dim BCK As New BackupInfo
x = Format(Now(), "_MMddyy_HHmm")
z = "C:\Program Files\YourApplication\DB.mdb"
g = x + ".mdb"
f = "C:\Program Files\YourApplicationNameHere\BackUps\DB" & g & ""
File.Copy(z, f)
ToolStripStatusLabel1.Text = "Backup completed succesfully."
If BCK.ShowDialog() = DialogResult.OK Then
End If
End Sub

'RESTORE A DATABASE

'Find you Backup file with TimeStamp in the BackUps folder (or anywhere else you backed up your database).

'This will replace (overwrite the database) in the YourApplication folder.
Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RestoreDB.Click
Dim Filename As String
Dim R As New RestoreRestart
Dim overwrite As Boolean
overwrite = True
Dim xi As String
With OpenFileDialog1
'.Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*"
If .ShowDialog() = DialogResult.OK Then
Filename = .FileName
xi = "C:\Program Files\YourApplicationNameHere\DB.mdb"
File.Copy(Filename, xi, overwrite)
End If
End With

'Notify user in the ToolStrip (or MessageBox.Show("Data restored....")
ToolStripStatusLabel1.Text = "Data restored successfully"

'Restart application

If R.ShowDialog() = DialogResult.OK Then
Application.Restart()
End If
End Sub

'CREATE A NEW EMPTY DATABASE.
Private Sub CrNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CrNewDB.Click
Dim L As New DatabaseEraseWarning
Dim Cat As ADOX.Catalog
Cat = New ADOX.Catalog
Dim R2 As New NewDBRestart
If File.Exists("C:\Program Files\YourApplicationNameHere\DB.mdb") Then
If L.ShowDialog() = DialogResult.Cancel Then
Exit Sub
Else
File.Delete("C:\Program Files\YourApplicationNameHere\DB.mdb")
End If
End If
Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\ YourApplicationNameHere \
DB.mdb; Jet OLEDB:Engine Type=5")

Dim Cn As ADODB.Connection
'Dim Cat As ADOX.Catalog
Dim Tablename As ADOX.Table

'Taylor these according to your need - add so many column as you need.
Dim col As ADOX.Column = New ADOX.Column
Dim col1 As ADOX.Column = New ADOX.Column
Dim col2 As ADOX.Column = New ADOX.Column
Dim col3 As ADOX.Column = New ADOX.Column
Dim col4 As ADOX.Column = New ADOX.Column
Dim col5 As ADOX.Column = New ADOX.Column
Dim col6 As ADOX.Column = New ADOX.Column

Cn = New ADODB.Connection
Cat = New ADOX.Catalog
Tablename = New ADOX.Table

'Open the connection
Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\YourApplicationNameHere\DB.mdb;Jet OLEDB:Engine Type=5")
'Open the Catalog
Cat.ActiveConnection = Cn

'Create the table (you can name it anyway you want)
Tablename.Name = "Table1"

'Taylor these according to your need - add so many column as you need. Watch for the DataType!
col.Name = "ID"
col.Type = ADOX.DataTypeEnum.adInteger
col1.Name = "Serial"
col1.Type = ADOX.DataTypeEnum.adInteger
col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col2.Name = "FName"
col2.Type = ADOX.DataTypeEnum.adVarWChar
col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col3.Name = "LName"
col3.Type = ADOX.DataTypeEnum.adVarWChar
col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col4.Name = "DOB"
col4.Type = ADOX.DataTypeEnum.adDate
col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col5.Name = "Sex"
col5.Type = ADOX.DataTypeEnum.adVarWChar
col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col6.Name = "Ph1"
col6.Type = ADOX.DataTypeEnum.adVarWChar
col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable

Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID")

'You have to append all your columns you have created above
Tablename.Columns.Append(col)
Tablename.Columns.Append(col1)
Tablename.Columns.Append(col2)
Tablename.Columns.Append(col3)
Tablename.Columns.Append(col4)
Tablename.Columns.Append(col5)
Tablename.Columns.Append(col6)

'Append the newly created table to the Tables Collection
Cat.Tables.Append(Tablename)

'User notification (again you can use MessageBox.Show() if you want)

ToolStripStatusLabel1.Text = "A new empty database was created successfully"

'clean up objects
Tablename = Nothing
Cat = Nothing
Cn.Close()
Cn = Nothing

'This restart the application
If R2.ShowDialog() = DialogResult.OK Then
Application.Restart()
End If
End Sub

'COMPACT A DATABASE
'Have a Button with "CompactDB" design name
Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CompactDB.Click
Dim JRO As JRO.JetEngine
JRO = New JRO.JetEngine

'The first source is the original, the second is the compacted database under an other

name.

JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program

Files\VSoft\AppMiss\AmDB.mdb; Jet OLEDB:Engine Type=5",

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program

Files\VSoft\AppMiss\AmDBComp.mdb; JetOLEDB:Engine Type=5")

'Here the original (not compacted database is deleted)
File.Delete("C:\Program Files\YourApplicationNameHere\DB.mdb")

'Here the compacted database is renamed to the original database.
Rename(" C:\Program Files\YourApplicationNameHere\DBComp.mdb", "C:\Program

Files\ YourApplicationNameHere \DB.mdb")

'User notification
ToolStripStatusLabel1.Text = "The database was compacted successfully"
End Sub

End Class

Database restoring problem..HELP!! SOS

I am a noob of SQL2005, when i try to restore my database, it jz doesn't work and i've got this error..any expert here please lend me your hand..thx!!

http://img.photobucket.com/albums/v617/mv2squall_sx/restoredberror.png"

You want to override an existing database which tail has not been yet backuped, which measn that you will lose any changes that were made after the last transaction backup. If you know about that you will either have to confirm it or use another database name.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Database Restores; Terminates Abnormally

Hi to All!
i have data base restore problem. i have backed up data base with verify
the backup option last w now i have server crash and i have two
recent backups with me. When i restore the latest one it terminates
abnormally. when i restore the other backup it restores okay. what may
be the problem in restoring the latest backup or which optin i shuould
select in the options to restore the data sucessfully.
Thanx
*** Sent via Developersdex http://www.examnotes.net ***Hi Ghulam,
What error do you get when the restore fails ?
"Ghulam Farid" wrote:

> Hi to All!
> i have data base restore problem. i have backed up data base with verify
> the backup option last w now i have server crash and i have two
> recent backups with me. When i restore the latest one it terminates
> abnormally. when i restore the other backup it restores okay. what may
> be the problem in restoring the latest backup or which optin i shuould
> select in the options to restore the data sucessfully.
> Thanx
>
> *** Sent via Developersdex http://www.examnotes.net ***
>

Database restores using Enterprise Manager

Good afternoon. I am using MS SQL 2K and was wondering if it is possible to restore multiple back-up files (database and transaction logs) to a database, if you haven't created a back-up set, using Enterprise Manager. I know that you can write T-SQL to first restore the back-up file and each of the transaction log files, except the last one, with the option of norecovery, and then the last transaction log file, with recovery. Any help would be greatly appreciated. Thank you.

Chris

That the way, restoring the database files one by one.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks for the reply, Jens.

database restores - what actually happens?

If you have a database with 2 data files and you do a restore will sql server
fill one datafile first before starting on the second, or do both at the same
time in parallel to balance the load?
Anyone know any decent articles on what happens under the covers?
JohnHi
Does this help?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_aa_49r9.asp
If you only currently only have one data file, you will see speed
improvements by using two backup devices on different disks. This will mean
no change to the actual database.
John
"John" <John@.discussions.microsoft.com> wrote in message
news:6E75BBD9-88E8-4D4D-8278-E33285AC4217@.microsoft.com...
> If you have a database with 2 data files and you do a restore will sql
> server
> fill one datafile first before starting on the second, or do both at the
> same
> time in parallel to balance the load?
> Anyone know any decent articles on what happens under the covers?
> John

database restores - what actually happens?

If you have a database with 2 data files and you do a restore will sql server
fill one datafile first before starting on the second, or do both at the same
time in parallel to balance the load?
Anyone know any decent articles on what happens under the covers?
John
Hi
Does this help?
http://msdn.microsoft.com/library/de...ar_aa_49r9.asp
If you only currently only have one data file, you will see speed
improvements by using two backup devices on different disks. This will mean
no change to the actual database.
John
"John" <John@.discussions.microsoft.com> wrote in message
news:6E75BBD9-88E8-4D4D-8278-E33285AC4217@.microsoft.com...
> If you have a database with 2 data files and you do a restore will sql
> server
> fill one datafile first before starting on the second, or do both at the
> same
> time in parallel to balance the load?
> Anyone know any decent articles on what happens under the covers?
> John

database restores - what actually happens?

If you have a database with 2 data files and you do a restore will sql serve
r
fill one datafile first before starting on the second, or do both at the sam
e
time in parallel to balance the load?
Anyone know any decent articles on what happens under the covers?
JohnHi
Does this help?
http://msdn.microsoft.com/library/d...br />
49r9.asp
If you only currently only have one data file, you will see speed
improvements by using two backup devices on different disks. This will mean
no change to the actual database.
John
"John" <John@.discussions.microsoft.com> wrote in message
news:6E75BBD9-88E8-4D4D-8278-E33285AC4217@.microsoft.com...
> If you have a database with 2 data files and you do a restore will sql
> server
> fill one datafile first before starting on the second, or do both at the
> same
> time in parallel to balance the load?
> Anyone know any decent articles on what happens under the covers?
> John

database restored, publications gone

hi all,

our datbase was restore but the publications were all gone.

however replication monitor shows publication and subscription.

worst. can create the publication with the same name because

it raises an error saying that it can't drop publication becuase

there is a subscription in it.

how can i remove the publication when the are already gone in the publication

folder but exist in the replication monitor

thanks for the help

if you want to preserve replication when you restore a backup of a published database, specify "KEEP_REPLICATION" with your RESTORE command.

To remove replication from a published database, try sp_removedbreplication.

|||

thanks, i already resolve the issue.

i disabled the publications in the rep monitor and then delete its contents

Database restore.

Hi everyone,
We have a SQL Server 2000 Enterprise Ed. SP3a Server hosting a busy
database.
When Restoring I have trouble,because while I am killing current user
connections new connections are established and I cannot restore because the
database is in use.
What are my options to get round this issue ?
I connect remotely to the Server to restore.
Regards,Set the database to single user with the rollback option. See ALTER DATABASE in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:%23NKvABt4GHA.1012@.TK2MSFTNGP05.phx.gbl...
> Hi everyone,
> We have a SQL Server 2000 Enterprise Ed. SP3a Server hosting a busy database.
> When Restoring I have trouble,because while I am killing current user connections new connections
> are established and I cannot restore because the database is in use.
> What are my options to get round this issue ?
> I connect remotely to the Server to restore.
> Regards,
>|||Thanks.
I suppose it does not make any difference if done with ALTER DATABASE or
from Enterpise Manager - Options - Restrict Access - Single User.
Regards,
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Dfd4Gt4GHA.4616@.TK2MSFTNGP05.phx.gbl...
> Set the database to single user with the rollback option. See ALTER
> DATABASE in Books Online.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> news:%23NKvABt4GHA.1012@.TK2MSFTNGP05.phx.gbl...
>> Hi everyone,
>> We have a SQL Server 2000 Enterprise Ed. SP3a Server hosting a busy
>> database.
>> When Restoring I have trouble,because while I am killing current user
>> connections new connections are established and I cannot restore because
>> the database is in use.
>> What are my options to get round this issue ?
>> I connect remotely to the Server to restore.
>> Regards,
>|||EM doesn't have a rollback option. If users are connected, then the EM operation will fail.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OqU%23JXt4GHA.4888@.TK2MSFTNGP03.phx.gbl...
> Thanks.
> I suppose it does not make any difference if done with ALTER DATABASE or from Enterpise Manager -
> Options - Restrict Access - Single User.
> Regards,
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23Dfd4Gt4GHA.4616@.TK2MSFTNGP05.phx.gbl...
>> Set the database to single user with the rollback option. See ALTER DATABASE in Books Online.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
>> news:%23NKvABt4GHA.1012@.TK2MSFTNGP05.phx.gbl...
>> Hi everyone,
>> We have a SQL Server 2000 Enterprise Ed. SP3a Server hosting a busy database.
>> When Restoring I have trouble,because while I am killing current user connections new
>> connections are established and I cannot restore because the database is in use.
>> What are my options to get round this issue ?
>> I connect remotely to the Server to restore.
>> Regards,
>>
>

Database restore.

Hi everyone,
We have a SQL Server 2000 Enterprise Ed. SP3a Server hosting a busy
database.
When Restoring I have trouble,because while I am killing current user
connections new connections are established and I cannot restore because the
database is in use.
What are my options to get round this issue ?
I connect remotely to the Server to restore.
Regards,
Set the database to single user with the rollback option. See ALTER DATABASE in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:%23NKvABt4GHA.1012@.TK2MSFTNGP05.phx.gbl...
> Hi everyone,
> We have a SQL Server 2000 Enterprise Ed. SP3a Server hosting a busy database.
> When Restoring I have trouble,because while I am killing current user connections new connections
> are established and I cannot restore because the database is in use.
> What are my options to get round this issue ?
> I connect remotely to the Server to restore.
> Regards,
>
|||Thanks.
I suppose it does not make any difference if done with ALTER DATABASE or
from Enterpise Manager - Options - Restrict Access - Single User.
Regards,
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Dfd4Gt4GHA.4616@.TK2MSFTNGP05.phx.gbl...
> Set the database to single user with the rollback option. See ALTER
> DATABASE in Books Online.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> news:%23NKvABt4GHA.1012@.TK2MSFTNGP05.phx.gbl...
>
|||EM doesn't have a rollback option. If users are connected, then the EM operation will fail.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OqU%23JXt4GHA.4888@.TK2MSFTNGP03.phx.gbl...
> Thanks.
> I suppose it does not make any difference if done with ALTER DATABASE or from Enterpise Manager -
> Options - Restrict Access - Single User.
> Regards,
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23Dfd4Gt4GHA.4616@.TK2MSFTNGP05.phx.gbl...
>

Database restore.

Hi everyone,
We have a SQL Server 2000 Enterprise Ed. SP3a Server hosting a busy
database.
When Restoring I have trouble,because while I am killing current user
connections new connections are established and I cannot restore because the
database is in use.
What are my options to get round this issue ?
I connect remotely to the Server to restore.
Regards,Set the database to single user with the rollback option. See ALTER DATABASE
in Books Online.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:%23NKvABt4GHA.1012@.TK2MSFTNGP05.phx.gbl...
> Hi everyone,
> We have a SQL Server 2000 Enterprise Ed. SP3a Server hosting a busy databa
se.
> When Restoring I have trouble,because while I am killing current user conn
ections new connections
> are established and I cannot restore because the database is in use.
> What are my options to get round this issue ?
> I connect remotely to the Server to restore.
> Regards,
>|||Thanks.
I suppose it does not make any difference if done with ALTER DATABASE or
from Enterpise Manager - Options - Restrict Access - Single User.
Regards,
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Dfd4Gt4GHA.4616@.TK2MSFTNGP05.phx.gbl...
> Set the database to single user with the rollback option. See ALTER
> DATABASE in Books Online.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
> news:%23NKvABt4GHA.1012@.TK2MSFTNGP05.phx.gbl...
>|||EM doesn't have a rollback option. If users are connected, then the EM opera
tion will fail.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sezgin Rafet" <anonymous@.newsgroup.com> wrote in message
news:OqU%23JXt4GHA.4888@.TK2MSFTNGP03.phx.gbl...
> Thanks.
> I suppose it does not make any difference if done with ALTER DATABASE or f
rom Enterpise Manager -
> Options - Restrict Access - Single User.
> Regards,
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23Dfd4Gt4GHA.4616@.TK2MSFTNGP05.phx.gbl...
>

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.