I am having problems to copy a table into another one
SELECT * INTO UserCopy FROM User WHERE User.ID IN (SELECT MAX(ID) AS LastId FROM Category GROUP BY CatUser)
with SELECT MAX(ID) AS LastId FROM Category GROUP BY CatUser I get : 1,2,3,10
now if I look the result in my table Usercopy I get the values for : 1,2,3,9,10
what can be the problem ? where does the 9 comes from
thank youwas the table empty when you started|||userCopy of course is empty because it is just created
user is full and category too
I want to extract datas from user depending on an ID list in category
thank you for helping|||I created table to test data and as I first though first
your logic is correct
If you return 1,2,3,10
that is what should be used to create your table.
Try to hardcode 1,2,3,10 and see what that returns.
or
just run the select instead of the into and what is returned.
Could you be looking at usercopy created by another owner.
Could you be selecting from a user a category table from another owner.|||ok I try ! and i come back :-)
thank you|||read the sticky at the top of the forum and posts what it asks you to give...
Showing posts with label copying. Show all posts
Showing posts with label copying. Show all posts
Monday, March 26, 2012
Saturday, February 25, 2012
Problem copying tables to another server with Primary and Foreign Key constrains
How can I copy a table with a Primary Key to another server's table of
the same name without receiving the following error: (Error at
Destination for Row number 489. Errors encountered so far in this
task:1.The statement has been terminated. Violation of PRIMARY KEY
constraint 'PK_INDIVIDUALS'. Cannot insert duplicate key in object
'INDIVIDUALS'.)
I want to copy about 20 interrelated cascaded tables with Primary and
Foreign Keys to another server's tables with the identical structure
and not receive the above error. I suggested dropping the tables and
recreating them, but my co-worker feel that this would not work because
of the interrelationship of the tables.
I tried to manually delete a table that is part of this group and
received Error 3726: Could not drop object 'dbo.INDIVIDUALS' because
it is referenced by a FOREIGN KEY constraint.
THE REASON FOR MY INQUIRY IS BECAUSE WE HAVE BEEN EXPERIENCING PROBLEM
WITH REPLICATION AND THESE TABLES ARE THE ARTICLES THAT WE BE
REPLICATED.
Any Suggestion?Some odd questions:
What field is the primary key? Is it the replication id? Is it an Identity
Field ? Some other field?
Instead of deleteing the target table you could try truncating it.
Your error says you are trying to load a duplicate key. The only answers are
Delete, Truncate, Drop or Remove Constraint.
What is the problem you are trying to solve? If it is just having a copy of
the database 'somewhere else', you could consider a backup and restore cycle.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"war_wheelan@.yahoo.com" wrote:
> How can I copy a table with a Primary Key to another server's table of
> the same name without receiving the following error: (Error at
> Destination for Row number 489. Errors encountered so far in this
> task:1.The statement has been terminated. Violation of PRIMARY KEY
> constraint 'PK_INDIVIDUALS'. Cannot insert duplicate key in object
> 'INDIVIDUALS'.)
> I want to copy about 20 interrelated cascaded tables with Primary and
> Foreign Keys to another server's tables with the identical structure
> and not receive the above error. I suggested dropping the tables and
> recreating them, but my co-worker feel that this would not work because
> of the interrelationship of the tables.
> I tried to manually delete a table that is part of this group and
> received Error 3726: Could not drop object 'dbo.INDIVIDUALS' because
> it is referenced by a FOREIGN KEY constraint.
> THE REASON FOR MY INQUIRY IS BECAUSE WE HAVE BEEN EXPERIENCING PROBLEM
> WITH REPLICATION AND THESE TABLES ARE THE ARTICLES THAT WE BE
> REPLICATED.
> Any Suggestion?
>|||What field is the primary key? Is it the replication id? Is it an
Identity Field ? Some other field? THE PRIMARY KEY VARIES FROM TABLE
TO TABLE.
Instead of deleteing the target table you could try truncating it. I
AM NOT A T-SQL PROGRAMMER SO IN NON PROGRAMMING TERMS - CAN I DROP THE
KEYS ON THE DESTINATION TABLE(S) I.E. PRIMARY AND FOREIGN KEYS THEN
COPY THE NEW TABLES TO THE DESTINATION? WOULD THIS RE-ESTABLISH THE
ORIGINAL PRIMARY AND FOREIGN KEYS WITHOUT CORRUPTING THE
DATABASE/TABLES AND THEIR RELATIONSHIPS?
Your error says you are trying to load a duplicate key. The only
answers are Delete, Truncate, Drop or Remove Constraint. SAME QUESTION
AS ABOVE, BUT PERHAPS THE REMOVE CONTRAINT OPTION WOULD WORK. NEW
QUESTION: WOULD SQL LET ME REMOVE A CONTRAINT WITH INTER-RELATIONSHIPS
TO OTHER TABLES OR WOULD IT FAIL BECAUSE OF THE INTER-RELATIONSHIPS.
What is the problem you are trying to solve? If it is just having a
copy of the database 'somewhere else', you could consider a backup and
restore cycle. THE PROLEM IS THAT THESE TABLES WOULD BE REPLICATE IF
REPLICATION WERE WORKING. REPLICATION HAS NOT BEEN WORKING FOR ABOUT A
MONTH SO THE PRIMARY AND SECONDARY DATABASES ARE OUT OF SYNC. IF THE
PRIMARY SERVER GOES DOWN WE WOULD BE MISSING A MONTHS WORTH OF CHANGES.
ERGO BACK TO MY ORIGINAL QUESTION (I want to copy about 20
interrelated cascaded tables with Primary and Foreign Keys to another
server's tables with the identical structure and not receive the above
error.
Thanks for you response. I am trying to get suggestions for my PART
TIME T-SQL programmer.
the same name without receiving the following error: (Error at
Destination for Row number 489. Errors encountered so far in this
task:1.The statement has been terminated. Violation of PRIMARY KEY
constraint 'PK_INDIVIDUALS'. Cannot insert duplicate key in object
'INDIVIDUALS'.)
I want to copy about 20 interrelated cascaded tables with Primary and
Foreign Keys to another server's tables with the identical structure
and not receive the above error. I suggested dropping the tables and
recreating them, but my co-worker feel that this would not work because
of the interrelationship of the tables.
I tried to manually delete a table that is part of this group and
received Error 3726: Could not drop object 'dbo.INDIVIDUALS' because
it is referenced by a FOREIGN KEY constraint.
THE REASON FOR MY INQUIRY IS BECAUSE WE HAVE BEEN EXPERIENCING PROBLEM
WITH REPLICATION AND THESE TABLES ARE THE ARTICLES THAT WE BE
REPLICATED.
Any Suggestion?Some odd questions:
What field is the primary key? Is it the replication id? Is it an Identity
Field ? Some other field?
Instead of deleteing the target table you could try truncating it.
Your error says you are trying to load a duplicate key. The only answers are
Delete, Truncate, Drop or Remove Constraint.
What is the problem you are trying to solve? If it is just having a copy of
the database 'somewhere else', you could consider a backup and restore cycle.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"war_wheelan@.yahoo.com" wrote:
> How can I copy a table with a Primary Key to another server's table of
> the same name without receiving the following error: (Error at
> Destination for Row number 489. Errors encountered so far in this
> task:1.The statement has been terminated. Violation of PRIMARY KEY
> constraint 'PK_INDIVIDUALS'. Cannot insert duplicate key in object
> 'INDIVIDUALS'.)
> I want to copy about 20 interrelated cascaded tables with Primary and
> Foreign Keys to another server's tables with the identical structure
> and not receive the above error. I suggested dropping the tables and
> recreating them, but my co-worker feel that this would not work because
> of the interrelationship of the tables.
> I tried to manually delete a table that is part of this group and
> received Error 3726: Could not drop object 'dbo.INDIVIDUALS' because
> it is referenced by a FOREIGN KEY constraint.
> THE REASON FOR MY INQUIRY IS BECAUSE WE HAVE BEEN EXPERIENCING PROBLEM
> WITH REPLICATION AND THESE TABLES ARE THE ARTICLES THAT WE BE
> REPLICATED.
> Any Suggestion?
>|||What field is the primary key? Is it the replication id? Is it an
Identity Field ? Some other field? THE PRIMARY KEY VARIES FROM TABLE
TO TABLE.
Instead of deleteing the target table you could try truncating it. I
AM NOT A T-SQL PROGRAMMER SO IN NON PROGRAMMING TERMS - CAN I DROP THE
KEYS ON THE DESTINATION TABLE(S) I.E. PRIMARY AND FOREIGN KEYS THEN
COPY THE NEW TABLES TO THE DESTINATION? WOULD THIS RE-ESTABLISH THE
ORIGINAL PRIMARY AND FOREIGN KEYS WITHOUT CORRUPTING THE
DATABASE/TABLES AND THEIR RELATIONSHIPS?
Your error says you are trying to load a duplicate key. The only
answers are Delete, Truncate, Drop or Remove Constraint. SAME QUESTION
AS ABOVE, BUT PERHAPS THE REMOVE CONTRAINT OPTION WOULD WORK. NEW
QUESTION: WOULD SQL LET ME REMOVE A CONTRAINT WITH INTER-RELATIONSHIPS
TO OTHER TABLES OR WOULD IT FAIL BECAUSE OF THE INTER-RELATIONSHIPS.
What is the problem you are trying to solve? If it is just having a
copy of the database 'somewhere else', you could consider a backup and
restore cycle. THE PROLEM IS THAT THESE TABLES WOULD BE REPLICATE IF
REPLICATION WERE WORKING. REPLICATION HAS NOT BEEN WORKING FOR ABOUT A
MONTH SO THE PRIMARY AND SECONDARY DATABASES ARE OUT OF SYNC. IF THE
PRIMARY SERVER GOES DOWN WE WOULD BE MISSING A MONTHS WORTH OF CHANGES.
ERGO BACK TO MY ORIGINAL QUESTION (I want to copy about 20
interrelated cascaded tables with Primary and Foreign Keys to another
server's tables with the identical structure and not receive the above
error.
Thanks for you response. I am trying to get suggestions for my PART
TIME T-SQL programmer.
Problem copying table
We upgraded to SQL Server 2005 & I'm having trouble with the Import and Export Wizard in the Management Studio. We periodically need to export some data to a different database to save it while we update the 'real' database which basically starts it over with empty tables. Under SQL Server 2000 that wasn't a problem. Under 2005 it is.
The database comes from a vendor and nearly every table in it has a timestamp column & when I try to import/export the rows I get a Validation Error: Error 0xc0202048: Data Flow Task: Attempting insertion into the row version column "timestamp". Cannot insert into a row version column.
Now I can write a query that omits the timestamp column & the import/export works perfectly-but a couple of the tables have over a hundred fields! (Oh, what I'd give for an 'except' analog to the * selection.)
Any suggestion how to get around this? Thanks.You cannot insert specific values to a column when the datatype is timestamp. Depending on the source of your data, you most likely want to change the datatype of the destination column to either binary(8), varbinary(8) or datetime. More information about timestamp columns in SQL Server is available in BOL (http://msdn2.microsoft.com/en-us/library/ms182776.aspx).|||You know, I thought my question was clear. I know what the problem is, what I don't know is how to solve it-and since the app comes from a vendor changing it myself isn't an option. (Is it really an option for you, roac, or do you just not work with applications you don't develop yourself?)
FWIW I submitted a request to the vendor to remove/change the timestamp column-no response, yet, which is why I'm looking for a solution that I can implement.
So far it looks like I'm stuck-either wait for the vendor to make the change or start writing queries. I figure at least two solid days' work to write the queries, and then there's testing & keeping them updated.|||Ah, I see now, I obviously overlooked a few details :)
Hopefully you have a test-environment where you can try the following procedure:
1. Backup the database
2. Restore on alternate location
3. Use ApexSQL Diff (http://www.apexsql.com/sql_tools_diff.asp) to script changes to structure
4. Restore database where backup was taken
5. Run script to apply changes in structure.
As far as I can see this approach should work. If the database is not deleted and recreated during upgrade, and you are using Enterprise Edition, you could use a Database Snapshot instead of backups.|||Wow, Calvin. That was a pretty rude response to someone who honestly was trying to help you.
I have a script that might assist you in writing queries for these lengthy tables, but I hesitate to post it because it may not be exactly what you want.|||roac, I'll take a look at ApexSQL. Right off I don't see how it will help as your description sounds like a way to change the timestamp column and what I need is a way to backup/restore all the data except the timestamp column. But maybe ApexSQL will let me do that-I'll take a look & thanks.
Blindman, I'd be happy to take a look at your script. Thanks.|||ApexSQL diff is a tool for scripting the code neccessary to change one version of a data structure to another. So, you can create a script that change the data structure of the data that you already have to the version created by the application's upgrade.|||Attached is a script for creating formatted lists of column names for easy cut-n-paste into code. Make sure when you run it that you have QA set to output results in text mode.
The database comes from a vendor and nearly every table in it has a timestamp column & when I try to import/export the rows I get a Validation Error: Error 0xc0202048: Data Flow Task: Attempting insertion into the row version column "timestamp". Cannot insert into a row version column.
Now I can write a query that omits the timestamp column & the import/export works perfectly-but a couple of the tables have over a hundred fields! (Oh, what I'd give for an 'except' analog to the * selection.)
Any suggestion how to get around this? Thanks.You cannot insert specific values to a column when the datatype is timestamp. Depending on the source of your data, you most likely want to change the datatype of the destination column to either binary(8), varbinary(8) or datetime. More information about timestamp columns in SQL Server is available in BOL (http://msdn2.microsoft.com/en-us/library/ms182776.aspx).|||You know, I thought my question was clear. I know what the problem is, what I don't know is how to solve it-and since the app comes from a vendor changing it myself isn't an option. (Is it really an option for you, roac, or do you just not work with applications you don't develop yourself?)
FWIW I submitted a request to the vendor to remove/change the timestamp column-no response, yet, which is why I'm looking for a solution that I can implement.
So far it looks like I'm stuck-either wait for the vendor to make the change or start writing queries. I figure at least two solid days' work to write the queries, and then there's testing & keeping them updated.|||Ah, I see now, I obviously overlooked a few details :)
Hopefully you have a test-environment where you can try the following procedure:
1. Backup the database
2. Restore on alternate location
3. Use ApexSQL Diff (http://www.apexsql.com/sql_tools_diff.asp) to script changes to structure
4. Restore database where backup was taken
5. Run script to apply changes in structure.
As far as I can see this approach should work. If the database is not deleted and recreated during upgrade, and you are using Enterprise Edition, you could use a Database Snapshot instead of backups.|||Wow, Calvin. That was a pretty rude response to someone who honestly was trying to help you.
I have a script that might assist you in writing queries for these lengthy tables, but I hesitate to post it because it may not be exactly what you want.|||roac, I'll take a look at ApexSQL. Right off I don't see how it will help as your description sounds like a way to change the timestamp column and what I need is a way to backup/restore all the data except the timestamp column. But maybe ApexSQL will let me do that-I'll take a look & thanks.
Blindman, I'd be happy to take a look at your script. Thanks.|||ApexSQL diff is a tool for scripting the code neccessary to change one version of a data structure to another. So, you can create a script that change the data structure of the data that you already have to the version created by the application's upgrade.|||Attached is a script for creating formatted lists of column names for easy cut-n-paste into code. Make sure when you run it that you have QA set to output results in text mode.
Problem Copying Table
I am trying to copy a table to another SQL 2000 Database, but I
continually get errors.
When I right-click, choose All-Tasks, Export-Data and go through the
DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
at all visible data, there appears to be no difference than the 100
rows before it).
Failed: Copying to a different database.
Failed: Copying to the Same database with a different table name.
Succeeded: Exporting to a Text file (not helpful however)
Different tables copy just fine.
The design is a primary key (integer, auto incrementing), 10 text
fields and another integer field.
Has anyone ever run into this silly/unhelpful error message before?berwiki wrote:
> I am trying to copy a table to another SQL 2000 Database, but I
> continually get errors.
> When I right-click, choose All-Tasks, Export-Data and go through the
> DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
> at all visible data, there appears to be no difference than the 100
> rows before it).
> Failed: Copying to a different database.
> Failed: Copying to the Same database with a different table name.
> Succeeded: Exporting to a Text file (not helpful however)
> Different tables copy just fine.
> The design is a primary key (integer, auto incrementing), 10 text
> fields and another integer field.
> Has anyone ever run into this silly/unhelpful error message before?
>
Try doing it without the wizard. Generate the DDL script for the table,
then run that script in the new database. Use INSERT INTO
newdatabase.dbo.newtable SELECT fieldlist FROM olddatabase.dbo.oldtable
to move the data.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
continually get errors.
When I right-click, choose All-Tasks, Export-Data and go through the
DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
at all visible data, there appears to be no difference than the 100
rows before it).
Failed: Copying to a different database.
Failed: Copying to the Same database with a different table name.
Succeeded: Exporting to a Text file (not helpful however)
Different tables copy just fine.
The design is a primary key (integer, auto incrementing), 10 text
fields and another integer field.
Has anyone ever run into this silly/unhelpful error message before?berwiki wrote:
> I am trying to copy a table to another SQL 2000 Database, but I
> continually get errors.
> When I right-click, choose All-Tasks, Export-Data and go through the
> DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
> at all visible data, there appears to be no difference than the 100
> rows before it).
> Failed: Copying to a different database.
> Failed: Copying to the Same database with a different table name.
> Succeeded: Exporting to a Text file (not helpful however)
> Different tables copy just fine.
> The design is a primary key (integer, auto incrementing), 10 text
> fields and another integer field.
> Has anyone ever run into this silly/unhelpful error message before?
>
Try doing it without the wizard. Generate the DDL script for the table,
then run that script in the new database. Use INSERT INTO
newdatabase.dbo.newtable SELECT fieldlist FROM olddatabase.dbo.oldtable
to move the data.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Problem Copying Table
I am trying to copy a table to another SQL 2000 Database, but I
continually get errors.
When I right-click, choose All-Tasks, Export-Data and go through the
DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
at all visible data, there appears to be no difference than the 100
rows before it).
Failed: Copying to a different database.
Failed: Copying to the Same database with a different table name.
Succeeded: Exporting to a Text file (not helpful however)
Different tables copy just fine.
The design is a primary key (integer, auto incrementing), 10 text
fields and another integer field.
Has anyone ever run into this silly/unhelpful error message before?
berwiki wrote:
> I am trying to copy a table to another SQL 2000 Database, but I
> continually get errors.
> When I right-click, choose All-Tasks, Export-Data and go through the
> DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
> at all visible data, there appears to be no difference than the 100
> rows before it).
> Failed: Copying to a different database.
> Failed: Copying to the Same database with a different table name.
> Succeeded: Exporting to a Text file (not helpful however)
> Different tables copy just fine.
> The design is a primary key (integer, auto incrementing), 10 text
> fields and another integer field.
> Has anyone ever run into this silly/unhelpful error message before?
>
Try doing it without the wizard. Generate the DDL script for the table,
then run that script in the new database. Use INSERT INTO
newdatabase.dbo.newtable SELECT fieldlist FROM olddatabase.dbo.oldtable
to move the data.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
continually get errors.
When I right-click, choose All-Tasks, Export-Data and go through the
DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
at all visible data, there appears to be no difference than the 100
rows before it).
Failed: Copying to a different database.
Failed: Copying to the Same database with a different table name.
Succeeded: Exporting to a Text file (not helpful however)
Different tables copy just fine.
The design is a primary key (integer, auto incrementing), 10 text
fields and another integer field.
Has anyone ever run into this silly/unhelpful error message before?
berwiki wrote:
> I am trying to copy a table to another SQL 2000 Database, but I
> continually get errors.
> When I right-click, choose All-Tasks, Export-Data and go through the
> DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
> at all visible data, there appears to be no difference than the 100
> rows before it).
> Failed: Copying to a different database.
> Failed: Copying to the Same database with a different table name.
> Succeeded: Exporting to a Text file (not helpful however)
> Different tables copy just fine.
> The design is a primary key (integer, auto incrementing), 10 text
> fields and another integer field.
> Has anyone ever run into this silly/unhelpful error message before?
>
Try doing it without the wizard. Generate the DDL script for the table,
then run that script in the new database. Use INSERT INTO
newdatabase.dbo.newtable SELECT fieldlist FROM olddatabase.dbo.oldtable
to move the data.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Problem Copying Table
I am trying to copy a table to another SQL 2000 Database, but I
continually get errors.
When I right-click, choose All-Tasks, Export-Data and go through the
DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
at all visible data, there appears to be no difference than the 100
rows before it).
Failed: Copying to a different database.
Failed: Copying to the Same database with a different table name.
Succeeded: Exporting to a Text file (not helpful however)
Different tables copy just fine.
The design is a primary key (integer, auto incrementing), 10 text
fields and another integer field.
Has anyone ever run into this silly/unhelpful error message before?berwiki wrote:
> I am trying to copy a table to another SQL 2000 Database, but I
> continually get errors.
> When I right-click, choose All-Tasks, Export-Data and go through the
> DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
> at all visible data, there appears to be no difference than the 100
> rows before it).
> Failed: Copying to a different database.
> Failed: Copying to the Same database with a different table name.
> Succeeded: Exporting to a Text file (not helpful however)
> Different tables copy just fine.
> The design is a primary key (integer, auto incrementing), 10 text
> fields and another integer field.
> Has anyone ever run into this silly/unhelpful error message before?
>
Try doing it without the wizard. Generate the DDL script for the table,
then run that script in the new database. Use INSERT INTO
newdatabase.dbo.newtable SELECT fieldlist FROM olddatabase.dbo.oldtable
to move the data.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
continually get errors.
When I right-click, choose All-Tasks, Export-Data and go through the
DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
at all visible data, there appears to be no difference than the 100
rows before it).
Failed: Copying to a different database.
Failed: Copying to the Same database with a different table name.
Succeeded: Exporting to a Text file (not helpful however)
Different tables copy just fine.
The design is a primary key (integer, auto incrementing), 10 text
fields and another integer field.
Has anyone ever run into this silly/unhelpful error message before?berwiki wrote:
> I am trying to copy a table to another SQL 2000 Database, but I
> continually get errors.
> When I right-click, choose All-Tasks, Export-Data and go through the
> DTS settings, I get an 'Unspecified Error' on row 107 (which, looking
> at all visible data, there appears to be no difference than the 100
> rows before it).
> Failed: Copying to a different database.
> Failed: Copying to the Same database with a different table name.
> Succeeded: Exporting to a Text file (not helpful however)
> Different tables copy just fine.
> The design is a primary key (integer, auto incrementing), 10 text
> fields and another integer field.
> Has anyone ever run into this silly/unhelpful error message before?
>
Try doing it without the wizard. Generate the DDL script for the table,
then run that script in the new database. Use INSERT INTO
newdatabase.dbo.newtable SELECT fieldlist FROM olddatabase.dbo.oldtable
to move the data.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Problem copying SQL DB file
Hi,
I have a VB project which uses SQL Server 2005 Express. When the project first runs, it copies its DB files from the data directory to a directory created on the C: drive. This works fine.
In a later part of the application, it is necessary to copy the DB file to another location e.g. memory stick so that it can be used to merge data onto another PC running the same application.
The data merging works too.
My problem is, despite the fact that it worked okay this morning, I am now unable to copy the DB into a second location (memory stick or location on local disc). I get an exception stating that the file is in use.
I have no other applications running (I shut down Visual Studio and was running the installed version).
What is more, the form that implements the file copying, first closes down all other forms in the application to ensure that nothing is connected to the DB.
Furthermore, the form that performs this task has no DB connections whatsoever - it simply allows the user to select a location, then creates a directory and copies the DB files to it.
If I copy/paste the code plus the FolderBrowserDialog into the first form that copies the original database out of the data directory - it works!
The code for the whole form is below - I would be hugely grateful for any ideas ...
Public Class Export
Private Sub Export_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
End Sub
Private Sub NextBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles NextBtn.Click
'Close all other windows to ensure that the databases are not in use.
Dim Counter As Integer = My.Application.OpenForms.Count
Do While Counter > 1
For Each f As Form In My.Application.OpenForms
If Not (f.GetType Is GetType(Export)) Then
f.Close()
Exit For
End If
Next
Counter -= 1
Loop
' Set the Help text description for the FolderBrowserDialog.
Me.FolderBrowserDialog.Description = _
"Select a location to export the data to e.g. a memory stick. Or, you can export to a local directory and then burn to CD or e-mail to another user."
' Do not allow the user to create New files via the FolderBrowserDialog.
Me.FolderBrowserDialog.ShowNewFolderButton = False
' Default to the My Documents folder.
Me.FolderBrowserDialog.RootFolder = Environment.SpecialFolder.MyComputer
Dim result As DialogResult = FolderBrowserDialog.ShowDialog()
Dim foldername As String
If result = DialogResult.OK Then
foldername = FolderBrowserDialog.SelectedPath
Try
If Not My.Computer.FileSystem.DirectoryExists(foldername & "\ClubSecExportMerge") Then
My.Computer.FileSystem.CreateDirectory(foldername & "\ClubSecExportMerge")
MsgBox("Merge Directory created.")
End If
My.Computer.FileSystem.CopyFile("C:\Clubsec\ClubSecDB.mdf", foldername & "\ClubsecExportMerge\MergeDB.mdf", True)
My.Computer.FileSystem.CopyFile("C:\Clubsec\ClubSecDB_log.ldf", foldername & "\ClubsecExportMerge\MergeDB_log.ldf", True)
MsgBox("Database merge files successfully copied to " & foldername & " \ClubSecExportMerge.", MsgBoxStyle.OkOnly, "Operation successful")
Catch ex As Exception
MsgBox("There was an error." & Chr(13) & "Export of database was not achieved." & Chr(13) & ex.ToString, MsgBoxStyle.OkOnly, "Operation failed.")
Exit Sub
End Try
Else ' Cancel button pressed
MsgBox("No path selected. Data not exported", MsgBoxStyle.OkOnly, "Operation cancelled.")
End If
Dim home As New HomeScreen
home.Show()
Me.Close()
End Sub
End Class
Accroding ot the mdf and ldf file your are using a SQL Server instance for storing some data. are the mdf files attached to any SQL Server ? If yes, is it a user instance or a server instance. If you don′t know post your connection string of your application here. The file is probably in use by any SQL Server instance.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Jens,
Thanks for the response. I don't know much about user instances but I thought they defaulted to False. When I looked at the connection strings, they are true. So I changed them to false. The app ran but I still couldn't copy the files to a new directory.
I noticed that in the Database Explorer, the database filename shown was different to what was intended and referred back to the project, within my Visual Studio directory. I changed this to point at the database files on the C: drive and now nothing works.
I get the first exception below when I try to run the app. Yesterday, most of it worked! I could even get the file copying to work if I pasted the code into a different form. Now nothing works!
The current connection string is as follows:
Data Source=.\SQLEXPRESS;AttachDbFilename=C:\ClubSec\ClubSecDB.mdf;Integrated Security=True;User Instance=False
Just by way of information, I will explain a little more about what I am trying to achieve. I'm new to VB and SQL Express and only know the stuff learned from the online videos and a book. I created the application without worrying about connection strings, user instances etc as they were all handled by the IDE. However, I then ran into a problem - if I update the app, the database files will overwrite the user data in the data directory. Seriously bad news!
Rather than try to be too clever, I thought ok, all I need do is chek whether a directory called C:\ClubSec exists on app startup. If it doesn't, it is the first ever run - create the directory and copy the DB files to it from the data directory and use these files in future. That way, any app updates won't affect the user data. This all semed to work fine.
I now wanted to add an Export/Import data feature as different users of the app could be changing data and there would be a requirement to merge it. To achieve this, the user is allowed to select a directory on any drive (intended to be a memory stick in real life) at which point their DB files are copied to a directory called ExportMerge in the chosen location. This worked yesterday and will work if I paste the code into a different form other than the one it's in!
Next, for an import, the ExportMerge directory is located and the contents copied to the Import Merge directory within Clubsec. This works fine and the merge happens (except for a few logic bugs that I've been too busy to address yet!)
Excuse me while a go and find a convenient wall upon which to bang my head several times!
UPDATE:
I have read that ADO.net maintains open connections sometimes which are eventually discarded. I have discovered that if I close all forms with data access then use a timer to try a copy my directories every so often, after 5-6 minutes they are freed up and I can perform the copy. I also tried to disable pooling but this had no effect although I can't be sure that I successfully disabled it because any time I change any connection properties - they change back again!
Regards,
Rich
First message
System.Data.SqlClient.SqlException was unhandled
Class=16
ErrorCode=-2146232060
LineNumber=65536
Message="Unable to open the physical file "C:\Clubsec\ClubSecDB.mdf". Operating system error 32: "32(error not found)".
Unable to open the physical file "C:\Clubsec\ClubSecDB_log.ldf". Operating system error 32: "32(error not found)".
Cannot open user default database. Login failed.
Login failed for user '048789520110\Richard'.
File activation failure. The physical file name "C:\Clubsec\ClubSecDB_log.ldf" may be incorrect."
Number=5120
Procedure=""
Server=".\SQLEXPRESS"
Source=".Net SqlClient Data Provider"
State=101
StackTrace:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at ClubsecDev3.ClubSecDBDataSetTableAdapters.UsersTableAdapter.Fill(UsersDataTable dataTable) in D:\Documents and Settings\Richard\My Documents\Visual Studio 2005\Projects\ClubsecDev3\ClubsecDev3\ClubsecDev3\ClubSecDBDataSet.Designer.vb:line 7521
at ClubsecDev3.Logon.Logon_Load(Object sender, EventArgs e) in D:\Documents and Settings\Richard\My Documents\Visual Studio 2005\Projects\ClubsecDev3\ClubsecDev3\ClubsecDev3\Logon.vb:line 167
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
at System.Windows.Forms.Control.set_Visible(Boolean value)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at ClubsecDev3.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
Then, as an experiment, tried to close the connection in Database explorer and got this one:
System.Data.SqlClient.SqlException was unhandled
Class=11
ErrorCode=-2146232060
LineNumber=65536
Message="Cannot open user default database. Login failed.
Login failed for user '048789520110\Richard'."
Number=4064
Procedure=""
Server=".\SQLEXPRESS"
Source=".Net SqlClient Data Provider"
State=1
StackTrace:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at ClubsecDev3.ClubSecDBDataSetTableAdapters.UsersTableAdapter.Fill(UsersDataTable dataTable) in D:\Documents and Settings\Richard\My Documents\Visual Studio 2005\Projects\ClubsecDev3\ClubsecDev3\ClubsecDev3\ClubSecDBDataSet.Designer.vb:line 7521
at ClubsecDev3.Logon.Logon_Load(Object sender, EventArgs e) in D:\Documents and Settings\Richard\My Documents\Visual Studio 2005\Projects\ClubsecDev3\ClubsecDev3\ClubsecDev3\Logon.vb:line 167
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
at System.Windows.Forms.Control.set_Visible(Boolean value)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at ClubsecDev3.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
problem copying files.
Hi all,
I have a folder called Attach which contains a subfolder folder each
userid. Everyone has full access to the the files in the Attach folder. But
the subfolders are private to each user. I need to write a sql stored
procedure to copy files from the subfolders into the Attach folder and
execute it as a Sql job.
I have been able to write a stored procedure that copies the files from
subfolders into the Attach folder using UNC path. I use the windows Copy
command in the xp_cmdshell procedure. But the problem is that the File
Permissions also get copied along with the files. So the files remain
private to the user even though they are copied into the attach folder. Is
there anyway I can inherit the permissions from the Attach folder when I
copy the files into the Attach folder.
Thanks.Are you sure you're using "Copy" and not moving the file? When you copy the
file will inherit ACLs from the containing folder, when you move a file to a
new location on the same volumn the file retains its ACL permissions. Double
check that you are performing a copy. If you do need to move this file, stil
l
do a copy and then just delete the old one.
Cheers,
Steve Goodyear
Vancouver, Canada
"sql" wrote:
> Hi all,
> I have a folder called Attach which contains a subfolder folder each
> userid. Everyone has full access to the the files in the Attach folder. Bu
t
> the subfolders are private to each user. I need to write a sql stored
> procedure to copy files from the subfolders into the Attach folder and
> execute it as a Sql job.
> I have been able to write a stored procedure that copies the files from
> subfolders into the Attach folder using UNC path. I use the windows Copy
> command in the xp_cmdshell procedure. But the problem is that the File
> Permissions also get copied along with the files. So the files remain
> private to the user even though they are copied into the attach folder. Is
> there anyway I can inherit the permissions from the Attach folder when I
> copy the files into the Attach folder.
> Thanks.
>
>|||Thanks Steve.
I was using Move. But we had the same problem. So I changed the procedure
few days back to copy and delete the files and it appeared to be working
fine for the users we were using in our test. But now it seems to be working
well for some users but not all.
Thanks.
"Steve Goodyear" <SteveGoodyear@.discussions.microsoft.com> wrote in message
news:5773C1E5-6F9A-43C7-B910-C3BCEE9865C9@.microsoft.com...
> Are you sure you're using "Copy" and not moving the file? When you copy
the
> file will inherit ACLs from the containing folder, when you move a file to
a
> new location on the same volumn the file retains its ACL permissions.
Double
> check that you are performing a copy. If you do need to move this file,
still
> do a copy and then just delete the old one.
> Cheers,
> Steve Goodyear
> Vancouver, Canada
> "sql" wrote:
>
But
from
Is
I have a folder called Attach which contains a subfolder folder each
userid. Everyone has full access to the the files in the Attach folder. But
the subfolders are private to each user. I need to write a sql stored
procedure to copy files from the subfolders into the Attach folder and
execute it as a Sql job.
I have been able to write a stored procedure that copies the files from
subfolders into the Attach folder using UNC path. I use the windows Copy
command in the xp_cmdshell procedure. But the problem is that the File
Permissions also get copied along with the files. So the files remain
private to the user even though they are copied into the attach folder. Is
there anyway I can inherit the permissions from the Attach folder when I
copy the files into the Attach folder.
Thanks.Are you sure you're using "Copy" and not moving the file? When you copy the
file will inherit ACLs from the containing folder, when you move a file to a
new location on the same volumn the file retains its ACL permissions. Double
check that you are performing a copy. If you do need to move this file, stil
l
do a copy and then just delete the old one.
Cheers,
Steve Goodyear
Vancouver, Canada
"sql" wrote:
> Hi all,
> I have a folder called Attach which contains a subfolder folder each
> userid. Everyone has full access to the the files in the Attach folder. Bu
t
> the subfolders are private to each user. I need to write a sql stored
> procedure to copy files from the subfolders into the Attach folder and
> execute it as a Sql job.
> I have been able to write a stored procedure that copies the files from
> subfolders into the Attach folder using UNC path. I use the windows Copy
> command in the xp_cmdshell procedure. But the problem is that the File
> Permissions also get copied along with the files. So the files remain
> private to the user even though they are copied into the attach folder. Is
> there anyway I can inherit the permissions from the Attach folder when I
> copy the files into the Attach folder.
> Thanks.
>
>|||Thanks Steve.
I was using Move. But we had the same problem. So I changed the procedure
few days back to copy and delete the files and it appeared to be working
fine for the users we were using in our test. But now it seems to be working
well for some users but not all.
Thanks.
"Steve Goodyear" <SteveGoodyear@.discussions.microsoft.com> wrote in message
news:5773C1E5-6F9A-43C7-B910-C3BCEE9865C9@.microsoft.com...
> Are you sure you're using "Copy" and not moving the file? When you copy
the
> file will inherit ACLs from the containing folder, when you move a file to
a
> new location on the same volumn the file retains its ACL permissions.
Double
> check that you are performing a copy. If you do need to move this file,
still
> do a copy and then just delete the old one.
> Cheers,
> Steve Goodyear
> Vancouver, Canada
> "sql" wrote:
>
But
from
Is
Problem copying DTS jobs with Database Copy Wizard
I am using the Database Copy Wizard to copy a database from one server
to the other. Here is the server information:
Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
2000 SP4
I initiate the DCW from the Source server and use the sa account to
authenticate on both servers. I need to copy all logins, jobs, etc..
so I check the appropriate boxes for this. The copy completes
successfully.
When I log onto the destination server, the database is there, logins
are there, but I don't see anything under Data Transformation Services
- Local Packages.
Do I need to turn something on/off because of the different operating
systems? I have done this successfully several times before - the
only difference this time is the OS.
Thank you!
KarriB
I dont know Exactly What u mena when you said you have copied DTS with
Database copy wizard.
Database Copy Wizard is Implemented as a DTS by Microsoft.
you can only Copy Databases or Logins/SP or Jobs etc but not DTS
Packages.
When we RUn this Wizard, it can be Run Immideatly or Scheduled for
later RUN. (option in the Last Screens of the Wizard)
If you mean you want to save this DTS, you cna do So by specifying so
on the last screens.
Maninder
MCDBA
On Jan 30, 1:00 pm, zell...@.avaya.com wrote:
> I am using the Database Copy Wizard to copy a database from one server
> to the other. Here is the server information:
> Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
> Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
> 2000 SP4
> I initiate the DCW from the Source server and use the sa account to
> authenticate on both servers. I need to copy all logins, jobs, etc..
> so I check the appropriate boxes for this. The copy completes
> successfully.
> When I log onto the destination server, the database is there, logins
> are there, but I don't see anything under Data Transformation Services
> - Local Packages.
> Do I need to turn something on/off because of the different operating
> systems? I have done this successfully several times before - the
> only difference this time is the OS.
> Thank you!
> KarriB
to the other. Here is the server information:
Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
2000 SP4
I initiate the DCW from the Source server and use the sa account to
authenticate on both servers. I need to copy all logins, jobs, etc..
so I check the appropriate boxes for this. The copy completes
successfully.
When I log onto the destination server, the database is there, logins
are there, but I don't see anything under Data Transformation Services
- Local Packages.
Do I need to turn something on/off because of the different operating
systems? I have done this successfully several times before - the
only difference this time is the OS.
Thank you!
KarriB
I dont know Exactly What u mena when you said you have copied DTS with
Database copy wizard.
Database Copy Wizard is Implemented as a DTS by Microsoft.
you can only Copy Databases or Logins/SP or Jobs etc but not DTS
Packages.
When we RUn this Wizard, it can be Run Immideatly or Scheduled for
later RUN. (option in the Last Screens of the Wizard)
If you mean you want to save this DTS, you cna do So by specifying so
on the last screens.
Maninder
MCDBA
On Jan 30, 1:00 pm, zell...@.avaya.com wrote:
> I am using the Database Copy Wizard to copy a database from one server
> to the other. Here is the server information:
> Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
> Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
> 2000 SP4
> I initiate the DCW from the Source server and use the sa account to
> authenticate on both servers. I need to copy all logins, jobs, etc..
> so I check the appropriate boxes for this. The copy completes
> successfully.
> When I log onto the destination server, the database is there, logins
> are there, but I don't see anything under Data Transformation Services
> - Local Packages.
> Do I need to turn something on/off because of the different operating
> systems? I have done this successfully several times before - the
> only difference this time is the OS.
> Thank you!
> KarriB
Problem copying DTS jobs with Database Copy Wizard
I am using the Database Copy Wizard to copy a database from one server
to the other. Here is the server information:
Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
2000 SP4
I initiate the DCW from the Source server and use the sa account to
authenticate on both servers. I need to copy all logins, jobs, etc..
so I check the appropriate boxes for this. The copy completes
successfully.
When I log onto the destination server, the database is there, logins
are there, but I don't see anything under Data Transformation Services
- Local Packages.
Do I need to turn something on/off because of the different operating
systems? I have done this successfully several times before - the
only difference this time is the OS.
Thank you!
KarriBI dont know Exactly What u mena when you said you have copied DTS with
Database copy wizard.
Database Copy Wizard is Implemented as a DTS by Microsoft.
you can only Copy Databases or Logins/SP or Jobs etc but not DTS
Packages.
When we RUn this Wizard, it can be Run Immideatly or Scheduled for
later RUN. (option in the Last Screens of the Wizard)
If you mean you want to save this DTS, you cna do So by specifying so
on the last screens.
Maninder
MCDBA
On Jan 30, 1:00 pm, zell...@.avaya.com wrote:
> I am using the Database Copy Wizard to copy a database from one server
> to the other. Here is the server information:
> Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
> Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
> 2000 SP4
> I initiate the DCW from the Source server and use the sa account to
> authenticate on both servers. I need to copy all logins, jobs, etc..
> so I check the appropriate boxes for this. The copy completes
> successfully.
> When I log onto the destination server, the database is there, logins
> are there, but I don't see anything under Data Transformation Services
> - Local Packages.
> Do I need to turn something on/off because of the different operating
> systems? I have done this successfully several times before - the
> only difference this time is the OS.
> Thank you!
> KarriB
to the other. Here is the server information:
Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
2000 SP4
I initiate the DCW from the Source server and use the sa account to
authenticate on both servers. I need to copy all logins, jobs, etc..
so I check the appropriate boxes for this. The copy completes
successfully.
When I log onto the destination server, the database is there, logins
are there, but I don't see anything under Data Transformation Services
- Local Packages.
Do I need to turn something on/off because of the different operating
systems? I have done this successfully several times before - the
only difference this time is the OS.
Thank you!
KarriBI dont know Exactly What u mena when you said you have copied DTS with
Database copy wizard.
Database Copy Wizard is Implemented as a DTS by Microsoft.
you can only Copy Databases or Logins/SP or Jobs etc but not DTS
Packages.
When we RUn this Wizard, it can be Run Immideatly or Scheduled for
later RUN. (option in the Last Screens of the Wizard)
If you mean you want to save this DTS, you cna do So by specifying so
on the last screens.
Maninder
MCDBA
On Jan 30, 1:00 pm, zell...@.avaya.com wrote:
> I am using the Database Copy Wizard to copy a database from one server
> to the other. Here is the server information:
> Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
> Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
> 2000 SP4
> I initiate the DCW from the Source server and use the sa account to
> authenticate on both servers. I need to copy all logins, jobs, etc..
> so I check the appropriate boxes for this. The copy completes
> successfully.
> When I log onto the destination server, the database is there, logins
> are there, but I don't see anything under Data Transformation Services
> - Local Packages.
> Do I need to turn something on/off because of the different operating
> systems? I have done this successfully several times before - the
> only difference this time is the OS.
> Thank you!
> KarriB
Problem copying DTS jobs with Database Copy Wizard
I am using the Database Copy Wizard to copy a database from one server
to the other. Here is the server information:
Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
2000 SP4
I initiate the DCW from the Source server and use the sa account to
authenticate on both servers. I need to copy all logins, jobs, etc..
so I check the appropriate boxes for this. The copy completes
successfully.
When I log onto the destination server, the database is there, logins
are there, but I don't see anything under Data Transformation Services
- Local Packages.
Do I need to turn something on/off because of the different operating
systems? I have done this successfully several times before - the
only difference this time is the OS.
Thank you!
KarriBI dont know Exactly What u mena when you said you have copied DTS with
Database copy wizard.
Database Copy Wizard is Implemented as a DTS by Microsoft.
you can only Copy Databases or Logins/SP or Jobs etc but not DTS
Packages.
When we RUn this Wizard, it can be Run Immideatly or Scheduled for
later RUN. (option in the Last Screens of the Wizard)
If you mean you want to save this DTS, you cna do So by specifying so
on the last screens.
Maninder
MCDBA
On Jan 30, 1:00 pm, zell...@.avaya.com wrote:
> I am using the Database Copy Wizard to copy a database from one server
> to the other. Here is the server information:
> Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
> Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
> 2000 SP4
> I initiate the DCW from the Source server and use the sa account to
> authenticate on both servers. I need to copy all logins, jobs, etc..
> so I check the appropriate boxes for this. The copy completes
> successfully.
> When I log onto the destination server, the database is there, logins
> are there, but I don't see anything under Data Transformation Services
> - Local Packages.
> Do I need to turn something on/off because of the different operating
> systems? I have done this successfully several times before - the
> only difference this time is the OS.
> Thank you!
> KarriB
to the other. Here is the server information:
Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
2000 SP4
I initiate the DCW from the Source server and use the sa account to
authenticate on both servers. I need to copy all logins, jobs, etc..
so I check the appropriate boxes for this. The copy completes
successfully.
When I log onto the destination server, the database is there, logins
are there, but I don't see anything under Data Transformation Services
- Local Packages.
Do I need to turn something on/off because of the different operating
systems? I have done this successfully several times before - the
only difference this time is the OS.
Thank you!
KarriBI dont know Exactly What u mena when you said you have copied DTS with
Database copy wizard.
Database Copy Wizard is Implemented as a DTS by Microsoft.
you can only Copy Databases or Logins/SP or Jobs etc but not DTS
Packages.
When we RUn this Wizard, it can be Run Immideatly or Scheduled for
later RUN. (option in the Last Screens of the Wizard)
If you mean you want to save this DTS, you cna do So by specifying so
on the last screens.
Maninder
MCDBA
On Jan 30, 1:00 pm, zell...@.avaya.com wrote:
> I am using the Database Copy Wizard to copy a database from one server
> to the other. Here is the server information:
> Source Server: Windows 2000 Server SP4, SQL Server 2000 SP4
> Destination Server: Windows 2003 Enterprise Edition SP1, SQL Server
> 2000 SP4
> I initiate the DCW from the Source server and use the sa account to
> authenticate on both servers. I need to copy all logins, jobs, etc..
> so I check the appropriate boxes for this. The copy completes
> successfully.
> When I log onto the destination server, the database is there, logins
> are there, but I don't see anything under Data Transformation Services
> - Local Packages.
> Do I need to turn something on/off because of the different operating
> systems? I have done this successfully several times before - the
> only difference this time is the OS.
> Thank you!
> KarriB
problem copying database with Microsoft SQl Server Management Studio
I am trying to copy a database from the server on my web host(crystaltech) to my new local install of MS SQL Server 2005 Enterprise (trial version).
I go to Tasks>Copy Database which opens the wizard. When I get to the select database screen I get this error:
Server user 'username' is not a valid user in database '123foo'
(123foo is the first database of all those on the server)
I need have it look only at my database which I have permissions for to copy it. How do I do this?
I tried copying the tables the other way(import data) but it dropped all my identities and primary keys which is a pain to recreate. Is there a way to copy tables from one server to another without losing primary keys and identity fields?
Thanks for any help!
I think you can use the import export option and specify them with primary keys and identities also other option is you can try scripting the tables and run the script in SSMS........
|||Thats is some sort of Bug in SSMS. You will have to assign a valid database owner top the database as the existing database owner is no server principal on your computer, this can be either based on the fact that you deleted the server principal or as the most common reason, you restored a database backup which is from another computer or a certain point in time where the current database owner did not exist on the computer. To fix that use sp_changedbowner (described int he BOL) to change the db_owner.
Jens K. Suessmeyer
http://www.sqlserver2005.de
problem copying database with Microsoft SQl Server Management Studio
I am trying to copy a database from the server on my web host(crystaltech) to my new local install of MS SQL Server 2005 Enterprise (trial version).
I go to Tasks>Copy Database which opens the wizard. When I get to the select database screen I get this error:
Server user 'username' is not a valid user in database '123foo'
(123foo is the first database of all those on the server)
I need have it look only at my database which I have permissions for to copy it. How do I do this?
I tried copying the tables the other way(import data) but it dropped all my identities and primary keys which is a pain to recreate. Is there a way to copy tables from one server to another without losing primary keys and identity fields?
Thanks for any help!
I think you can use the import export option and specify them with primary keys and identities also other option is you can try scripting the tables and run the script in SSMS........
|||Thats is some sort of Bug in SSMS. You will have to assign a valid database owner top the database as the existing database owner is no server principal on your computer, this can be either based on the fact that you deleted the server principal or as the most common reason, you restored a database backup which is from another computer or a certain point in time where the current database owner did not exist on the computer. To fix that use sp_changedbowner (described int he BOL) to change the db_owner.
Jens K. Suessmeyer
http://www.sqlserver2005.de
problem copying database to new server!
I am trying to copy a
dbase from one server to another so first created a backup file from source
server,then copied it to destination server. Then created an empty dbase
with same
name on destination server, then selected backup and created a backup file
dbase.bak. I then replaced the backup
file on the destination server with the one from the source server and tried
to restore
from that file. I get the following error, can not find file id 3 on device
c:\program files\...Backup\dbase.bak
thanks.
Paul G
Software engineer.
Your problem is that the original file destination of the database is
stored in the backup file. SO it can be that the stored destination
folder isn=B4t present in your restore system. With specifying a new
restore destination while issueing the command (via GUI or via TSQL
[RESTORE DATABASE]) that should do the trick.
HTH, Jens Suessmeyer.
|||Hi thanks for the response. I tried RESTORE database 'name' from disk
='c:\filebak' from sql analyzer simular to what you suggested and it worked.
Paul G
Software engineer.
"Jens" wrote:
> Your problem is that the original file destination of the database is
> stored in the backup file. SO it can be that the stored destination
> folder isn′t present in your restore system. With specifying a new
> restore destination while issueing the command (via GUI or via TSQL
> [RESTORE DATABASE]) that should do the trick.
> HTH, Jens Suessmeyer.
>
dbase from one server to another so first created a backup file from source
server,then copied it to destination server. Then created an empty dbase
with same
name on destination server, then selected backup and created a backup file
dbase.bak. I then replaced the backup
file on the destination server with the one from the source server and tried
to restore
from that file. I get the following error, can not find file id 3 on device
c:\program files\...Backup\dbase.bak
thanks.
Paul G
Software engineer.
Your problem is that the original file destination of the database is
stored in the backup file. SO it can be that the stored destination
folder isn=B4t present in your restore system. With specifying a new
restore destination while issueing the command (via GUI or via TSQL
[RESTORE DATABASE]) that should do the trick.
HTH, Jens Suessmeyer.
|||Hi thanks for the response. I tried RESTORE database 'name' from disk
='c:\filebak' from sql analyzer simular to what you suggested and it worked.
Paul G
Software engineer.
"Jens" wrote:
> Your problem is that the original file destination of the database is
> stored in the backup file. SO it can be that the stored destination
> folder isn′t present in your restore system. With specifying a new
> restore destination while issueing the command (via GUI or via TSQL
> [RESTORE DATABASE]) that should do the trick.
> HTH, Jens Suessmeyer.
>
problem copying database to new server!
I am trying to copy a
dbase from one server to another so first created a backup file from source
server,then copied it to destination server. Then created an empty dbase
with same
name on destination server, then selected backup and created a backup file
dbase.bak. I then replaced the backup
file on the destination server with the one from the source server and tried
to restore
from that file. I get the following error, can not find file id 3 on device
c:\program files\...Backup\dbase.bak
thanks.
--
Paul G
Software engineer.Your problem is that the original file destination of the database is
stored in the backup file. SO it can be that the stored destination
folder isn=B4t present in your restore system. With specifying a new
restore destination while issueing the command (via GUI or via TSQL
[RESTORE DATABASE]) that should do the trick.
HTH, Jens Suessmeyer.|||Hi thanks for the response. I tried RESTORE database 'name' from disk
='c:\filebak' from sql analyzer simular to what you suggested and it worked.
--
Paul G
Software engineer.
"Jens" wrote:
> Your problem is that the original file destination of the database is
> stored in the backup file. SO it can be that the stored destination
> folder isn´t present in your restore system. With specifying a new
> restore destination while issueing the command (via GUI or via TSQL
> [RESTORE DATABASE]) that should do the trick.
> HTH, Jens Suessmeyer.
>
dbase from one server to another so first created a backup file from source
server,then copied it to destination server. Then created an empty dbase
with same
name on destination server, then selected backup and created a backup file
dbase.bak. I then replaced the backup
file on the destination server with the one from the source server and tried
to restore
from that file. I get the following error, can not find file id 3 on device
c:\program files\...Backup\dbase.bak
thanks.
--
Paul G
Software engineer.Your problem is that the original file destination of the database is
stored in the backup file. SO it can be that the stored destination
folder isn=B4t present in your restore system. With specifying a new
restore destination while issueing the command (via GUI or via TSQL
[RESTORE DATABASE]) that should do the trick.
HTH, Jens Suessmeyer.|||Hi thanks for the response. I tried RESTORE database 'name' from disk
='c:\filebak' from sql analyzer simular to what you suggested and it worked.
--
Paul G
Software engineer.
"Jens" wrote:
> Your problem is that the original file destination of the database is
> stored in the backup file. SO it can be that the stored destination
> folder isn´t present in your restore system. With specifying a new
> restore destination while issueing the command (via GUI or via TSQL
> [RESTORE DATABASE]) that should do the trick.
> HTH, Jens Suessmeyer.
>
problem copying database to new server!
I am trying to copy a
dbase from one server to another so first created a backup file from source
server,then copied it to destination server. Then created an empty dbase
with same
name on destination server, then selected backup and created a backup file
dbase.bak. I then replaced the backup
file on the destination server with the one from the source server and tried
to restore
from that file. I get the following error, can not find file id 3 on device
c:\program files\...Backup\dbase.bak
thanks.
--
Paul G
Software engineer.Your problem is that the original file destination of the database is
stored in the backup file. SO it can be that the stored destination
folder isn=B4t present in your restore system. With specifying a new
restore destination while issueing the command (via GUI or via TSQL
[RESTORE DATABASE]) that should do the trick.
HTH, Jens Suessmeyer.|||Hi thanks for the response. I tried RESTORE database 'name' from disk
='c:\filebak' from sql analyzer simular to what you suggested and it worked.
--
Paul G
Software engineer.
"Jens" wrote:
> Your problem is that the original file destination of the database is
> stored in the backup file. SO it can be that the stored destination
> folder isn′t present in your restore system. With specifying a new
> restore destination while issueing the command (via GUI or via TSQL
> [RESTORE DATABASE]) that should do the trick.
> HTH, Jens Suessmeyer.
>
dbase from one server to another so first created a backup file from source
server,then copied it to destination server. Then created an empty dbase
with same
name on destination server, then selected backup and created a backup file
dbase.bak. I then replaced the backup
file on the destination server with the one from the source server and tried
to restore
from that file. I get the following error, can not find file id 3 on device
c:\program files\...Backup\dbase.bak
thanks.
--
Paul G
Software engineer.Your problem is that the original file destination of the database is
stored in the backup file. SO it can be that the stored destination
folder isn=B4t present in your restore system. With specifying a new
restore destination while issueing the command (via GUI or via TSQL
[RESTORE DATABASE]) that should do the trick.
HTH, Jens Suessmeyer.|||Hi thanks for the response. I tried RESTORE database 'name' from disk
='c:\filebak' from sql analyzer simular to what you suggested and it worked.
--
Paul G
Software engineer.
"Jens" wrote:
> Your problem is that the original file destination of the database is
> stored in the backup file. SO it can be that the stored destination
> folder isn′t present in your restore system. With specifying a new
> restore destination while issueing the command (via GUI or via TSQL
> [RESTORE DATABASE]) that should do the trick.
> HTH, Jens Suessmeyer.
>
problem copying database from sql server 2000 to sql server 2005
Hi,
I have right now sql server 2000 instance installed on a server (serverone) which is accessed on all nodes through an ODBC connection.
Now we have upgraded our server to windows server 2005 which will be on other machine servertwo. I have right now installed sql server 2005 in servertwo. Wish to copy the database from serverone to servertwo. Once it is copied and everything is working fine. I want to uninstal sql server 2000 from serverone.
What is the best way to do this? I am trying different things like tried running code Advisor on serverone which is not recognising the sql server 2005 instance of servertwo. It's giving message as since the sql server is installed on default settings it can't be accessed remotely. I see that SQL server 2000 database is accessible on all nodes including servertwo. Why is it that servertwo sql server is no where accessible?
What it is that I am missing?
Kindly help me.
Thanks, regards.
Shobha
You're really asking two questions, I think.
1. Why is servertwo not accessible?
With SQL Server 2005, we disabled a number of features by default to provide better security. You can easily adjust the settings at any time by using the Surface Area Configuration tool, available from the Start menu.
2. How do I move my databases over to servertwo?
There are several ways to do this. One way is to use sp_detach_db on serverone, then copy mdf and ldf files to servertwo and use sp_attach_db.
Paul
|||Thanks for the reply Paul.
Is it that the datatbase should be exclusively available when doing sp_detach_db?
thanks
Shobha
|||Hmm, good question, and a glance at BOL didn't pop the answer.
I don't believe you need exclusive access; I assume the operation is transactional so it'll wait for open transactions to complete before executing. But as I said above, the documentation of sp_detach_db doesn't make this clear. The documentation does list a few other requirements (e.g., if the database is replicated, it must be unpublished).
|||Thanks Paul for that answer.
If you don't mind can I have sample code or instruction list on how to use the sp_deattach_db and sp_attach_db commands, this will be the first time that I will be using these commands since earlier I was working only with MSAccess database.
will be greatful if you can give me directions.
thanks
Problem copying data from column in same table
I have a column that I would like to copy data to. The column definitions a
re exactly the same. I tried creating an Insert Into query and changed the
type to Insert Results. Everytime I run this query, all the values in my co
lumn remain to be null. Below is a sample of what I did:
INSERT INTO Customers
([CustomerID1])
SELECT [CustomerID]
FROM Customers AS Customers_1
I got a message back '(116873 row(s) affected)'. I don't know what it affec
ted.
Thanks, IrisYour SQL, if I read it correctly, is creating a new row for every existing
row in your table, with only one column (CustomerID1) populated. It is not
discerning between null values and non null values. So, if you start with
the following data:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
Run the SQL once and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
Run the SQL again and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, NULL, NULL
NULL, NULL, NULL
NULL, NULL, NULL
If your SQL is doing this, then you don't have a key on your table, which is
your first problem.
I assume that you want to update the CustomerID1 field in the existing rows,
not insert a new row for every exiusting one. The following SQL should do
this.
Update Customers set CustomerID1 = CustomerID
However, I cant think of a reason why you would want to do this. You
probably have some database design issues to work out. Once you get the
design down, you probably wont have a need to do this at all.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:eVU%23k3dHGHA.1288@.TK2MSFTNGP09.phx.gbl...
I have a column that I would like to copy data to. The column definitions
are exactly the same. I tried creating an Insert Into query and changed the
type to Insert Results. Everytime I run this query, all the values in my
column remain to be null. Below is a sample of what I did:
INSERT INTO Customers
([CustomerID1])
SELECT [CustomerID]
FROM Customers AS Customers_1
I got a message back '(116873 row(s) affected)'. I don't know what it
affected.
Thanks, Iris|||This is normal as you're not "copying" the values, you're inserting new rows
,
where only the new column is populated with data.
You need to UPDATE the new column with the values in the old column.
update <table>
set <new column> = <old column>
ML
http://milambda.blogspot.com/|||Hello, Iris
I guess that you want to use something like this:
UPDATE Customers SET CustomerID1=CustomerID
However, if the INSERT that you wrote was executed sucessfully, I
suspect that you got a lot of rows with invalid or missing
informations, because the number of rows in the table was doubled (i.e.
you now may have 233566 rows in the table).
Razvan|||That's correct, I'd rather update. I'll have to start back over since my te
st table now has all those null values in it like Jim mentioned. Thanks.
This is normal as you're not "copying" the values, you're inserting new rows
,
where only the new column is populated with data.
You need to UPDATE the new column with the values in the old column.
update <table>
set <new column> = <old column>
ML
http://milambda.blogspot.com/|||That's correct :)
Hello, Iris
I guess that you want to use something like this:
UPDATE Customers SET CustomerID1=CustomerID
However, if the INSERT that you wrote was executed sucessfully, I
suspect that you got a lot of rows with invalid or missing
informations, because the number of rows in the table was doubled (i.e.
you now may have 233566 rows in the table).
Razvan|||Jim,
There is no key on this table because it is a table with historical records.
I pulled this data from a mainframe database.
Your SQL, if I read it correctly, is creating a new row for every existing
row in your table, with only one column (CustomerID1) populated. It is not
discerning between null values and non null values. So, if you start with
the following data:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
Run the SQL once and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
Run the SQL again and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, NULL, NULL
NULL, NULL, NULL
NULL, NULL, NULL
If your SQL is doing this, then you don't have a key on your table, which is
your first problem.
I assume that you want to update the CustomerID1 field in the existing rows,
not insert a new row for every exiusting one. The following SQL should do
this.
Update Customers set CustomerID1 = CustomerID
However, I cant think of a reason why you would want to do this. You
probably have some database design issues to work out. Once you get the
design down, you probably wont have a need to do this at all.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:eVU%23k3dHGHA.1288@.TK2MSFTNGP09.phx.gbl...
I have a column that I would like to copy data to. The column definitions
are exactly the same. I tried creating an Insert Into query and changed the
type to Insert Results. Everytime I run this query, all the values in my
column remain to be null. Below is a sample of what I did:
INSERT INTO Customers
([CustomerID1])
SELECT [CustomerID]
FROM Customers AS Customers_1
I got a message back '(116873 row(s) affected)'. I don't know what it
affected.
Thanks, Iris|||I did this and it shows (116873 row(s) affected). What else could I possibl
y be doing wrong?
Iris
This is normal as you're not "copying" the values, you're inserting new rows
,
where only the new column is populated with data.
You need to UPDATE the new column with the values in the old column.
update <table>
set <new column> = <old column>
ML
http://milambda.blogspot.com/|||Thats what it should show. You are updating every existing row, and populat
ing CustomerID1 with the value from CustomerID.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message news:ut408SeHGHA.
3408@.TK2MSFTNGP12.phx.gbl...
I did this and it shows (116873 row(s) affected). What else could I possibl
y be doing wrong?
Iris
This is normal as you're not "copying" the values, you're inserting new rows
,
where only the new column is populated with data.
You need to UPDATE the new column with the values in the old column.
update <table>
set <new column> = <old column>
ML
http://milambda.blogspot.com/|||At a minimum you should have certain fields defined as not null (CustomerID
for example). There should also be some way of identifying unique records,
even in a history table (although not in an audit table). Usually when I ha
ve worked with history tables they would have a date field that would be par
t of the primary key. When multiple entries were allowed on the same date,
some sort of sequence field was used to insure uniqueness.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message news:OYAFhQeHGHA.
528@.TK2MSFTNGP12.phx.gbl...
Jim,
There is no key on this table because it is a table with historical records.
I pulled this data from a mainframe database.
Your SQL, if I read it correctly, is creating a new row for every existing
row in your table, with only one column (CustomerID1) populated. It is not
discerning between null values and non null values. So, if you start with
the following data:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
Run the SQL once and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
Run the SQL again and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, NULL, NULL
NULL, NULL, NULL
NULL, NULL, NULL
If your SQL is doing this, then you don't have a key on your table, which is
your first problem.
I assume that you want to update the CustomerID1 field in the existing rows,
not insert a new row for every exiusting one. The following SQL should do
this.
Update Customers set CustomerID1 = CustomerID
However, I cant think of a reason why you would want to do this. You
probably have some database design issues to work out. Once you get the
design down, you probably wont have a need to do this at all.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:eVU%23k3dHGHA.1288@.TK2MSFTNGP09.phx.gbl...
I have a column that I would like to copy data to. The column definitions
are exactly the same. I tried creating an Insert Into query and changed the
type to Insert Results. Everytime I run this query, all the values in my
column remain to be null. Below is a sample of what I did:
INSERT INTO Customers
([CustomerID1])
SELECT [CustomerID]
FROM Customers AS Customers_1
I got a message back '(116873 row(s) affected)'. I don't know what it
affected.
Thanks, Iris
re exactly the same. I tried creating an Insert Into query and changed the
type to Insert Results. Everytime I run this query, all the values in my co
lumn remain to be null. Below is a sample of what I did:
INSERT INTO Customers
([CustomerID1])
SELECT [CustomerID]
FROM Customers AS Customers_1
I got a message back '(116873 row(s) affected)'. I don't know what it affec
ted.
Thanks, IrisYour SQL, if I read it correctly, is creating a new row for every existing
row in your table, with only one column (CustomerID1) populated. It is not
discerning between null values and non null values. So, if you start with
the following data:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
Run the SQL once and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
Run the SQL again and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, NULL, NULL
NULL, NULL, NULL
NULL, NULL, NULL
If your SQL is doing this, then you don't have a key on your table, which is
your first problem.
I assume that you want to update the CustomerID1 field in the existing rows,
not insert a new row for every exiusting one. The following SQL should do
this.
Update Customers set CustomerID1 = CustomerID
However, I cant think of a reason why you would want to do this. You
probably have some database design issues to work out. Once you get the
design down, you probably wont have a need to do this at all.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:eVU%23k3dHGHA.1288@.TK2MSFTNGP09.phx.gbl...
I have a column that I would like to copy data to. The column definitions
are exactly the same. I tried creating an Insert Into query and changed the
type to Insert Results. Everytime I run this query, all the values in my
column remain to be null. Below is a sample of what I did:
INSERT INTO Customers
([CustomerID1])
SELECT [CustomerID]
FROM Customers AS Customers_1
I got a message back '(116873 row(s) affected)'. I don't know what it
affected.
Thanks, Iris|||This is normal as you're not "copying" the values, you're inserting new rows
,
where only the new column is populated with data.
You need to UPDATE the new column with the values in the old column.
update <table>
set <new column> = <old column>
ML
http://milambda.blogspot.com/|||Hello, Iris
I guess that you want to use something like this:
UPDATE Customers SET CustomerID1=CustomerID
However, if the INSERT that you wrote was executed sucessfully, I
suspect that you got a lot of rows with invalid or missing
informations, because the number of rows in the table was doubled (i.e.
you now may have 233566 rows in the table).
Razvan|||That's correct, I'd rather update. I'll have to start back over since my te
st table now has all those null values in it like Jim mentioned. Thanks.
This is normal as you're not "copying" the values, you're inserting new rows
,
where only the new column is populated with data.
You need to UPDATE the new column with the values in the old column.
update <table>
set <new column> = <old column>
ML
http://milambda.blogspot.com/|||That's correct :)
Hello, Iris
I guess that you want to use something like this:
UPDATE Customers SET CustomerID1=CustomerID
However, if the INSERT that you wrote was executed sucessfully, I
suspect that you got a lot of rows with invalid or missing
informations, because the number of rows in the table was doubled (i.e.
you now may have 233566 rows in the table).
Razvan|||Jim,
There is no key on this table because it is a table with historical records.
I pulled this data from a mainframe database.
Your SQL, if I read it correctly, is creating a new row for every existing
row in your table, with only one column (CustomerID1) populated. It is not
discerning between null values and non null values. So, if you start with
the following data:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
Run the SQL once and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
Run the SQL again and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, NULL, NULL
NULL, NULL, NULL
NULL, NULL, NULL
If your SQL is doing this, then you don't have a key on your table, which is
your first problem.
I assume that you want to update the CustomerID1 field in the existing rows,
not insert a new row for every exiusting one. The following SQL should do
this.
Update Customers set CustomerID1 = CustomerID
However, I cant think of a reason why you would want to do this. You
probably have some database design issues to work out. Once you get the
design down, you probably wont have a need to do this at all.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:eVU%23k3dHGHA.1288@.TK2MSFTNGP09.phx.gbl...
I have a column that I would like to copy data to. The column definitions
are exactly the same. I tried creating an Insert Into query and changed the
type to Insert Results. Everytime I run this query, all the values in my
column remain to be null. Below is a sample of what I did:
INSERT INTO Customers
([CustomerID1])
SELECT [CustomerID]
FROM Customers AS Customers_1
I got a message back '(116873 row(s) affected)'. I don't know what it
affected.
Thanks, Iris|||I did this and it shows (116873 row(s) affected). What else could I possibl
y be doing wrong?
Iris
This is normal as you're not "copying" the values, you're inserting new rows
,
where only the new column is populated with data.
You need to UPDATE the new column with the values in the old column.
update <table>
set <new column> = <old column>
ML
http://milambda.blogspot.com/|||Thats what it should show. You are updating every existing row, and populat
ing CustomerID1 with the value from CustomerID.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message news:ut408SeHGHA.
3408@.TK2MSFTNGP12.phx.gbl...
I did this and it shows (116873 row(s) affected). What else could I possibl
y be doing wrong?
Iris
This is normal as you're not "copying" the values, you're inserting new rows
,
where only the new column is populated with data.
You need to UPDATE the new column with the values in the old column.
update <table>
set <new column> = <old column>
ML
http://milambda.blogspot.com/|||At a minimum you should have certain fields defined as not null (CustomerID
for example). There should also be some way of identifying unique records,
even in a history table (although not in an audit table). Usually when I ha
ve worked with history tables they would have a date field that would be par
t of the primary key. When multiple entries were allowed on the same date,
some sort of sequence field was used to insure uniqueness.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message news:OYAFhQeHGHA.
528@.TK2MSFTNGP12.phx.gbl...
Jim,
There is no key on this table because it is a table with historical records.
I pulled this data from a mainframe database.
Your SQL, if I read it correctly, is creating a new row for every existing
row in your table, with only one column (CustomerID1) populated. It is not
discerning between null values and non null values. So, if you start with
the following data:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
Run the SQL once and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
Run the SQL again and you will get this data in the table:
CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, NULL, NULL
NULL, NULL, NULL
NULL, NULL, NULL
If your SQL is doing this, then you don't have a key on your table, which is
your first problem.
I assume that you want to update the CustomerID1 field in the existing rows,
not insert a new row for every exiusting one. The following SQL should do
this.
Update Customers set CustomerID1 = CustomerID
However, I cant think of a reason why you would want to do this. You
probably have some database design issues to work out. Once you get the
design down, you probably wont have a need to do this at all.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:eVU%23k3dHGHA.1288@.TK2MSFTNGP09.phx.gbl...
I have a column that I would like to copy data to. The column definitions
are exactly the same. I tried creating an Insert Into query and changed the
type to Insert Results. Everytime I run this query, all the values in my
column remain to be null. Below is a sample of what I did:
INSERT INTO Customers
([CustomerID1])
SELECT [CustomerID]
FROM Customers AS Customers_1
I got a message back '(116873 row(s) affected)'. I don't know what it
affected.
Thanks, Iris
Problem copying a database for testing purposes
I want to make a database copy on the same computer for testing purposes.
In BOL I found in the Transact-SQL Reference:
It may be necessary to create a copy of an existing database on the same
computer for testing purposes. In this case, the database files for the
original database already exist, so different file names need to be
specified when the database copy is created during the restore operation.
Make a copy of a database using BACKUP and RESTORE:
This example uses both the BACKUP and RESTORE statements to make a copy of
the Northwind database (I changed the destination path)
BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.mdf',
MOVE 'Northwind_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.ldf'
GO
This works fine, using SQLserver 2000 Developer with the Query analyzer.
I deleted the testdb database (in the Enterprise manager, testdb.mdf and
.ldf also disapeared)
Then I tried the production database:
BACKUP DATABASE Vineadb
TO DISK = 'C:\Vineadb.bak'
RESTORE FILELISTONLY
FROM DISK = 'C:\Vineadb.bak'
RESTORE DATABASE TestDB
FROM DISK = 'C:\Vineadb.bak'
WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.mdf',
MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.ldf'
GO
This didn't work, giving the message:
Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on file 1.
Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file 1.
BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds (1.612
MB/sec).
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is not
part of database 'TestDB'. Use RESTORE FILELISTONLY to list the logical
file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE DATABASE
is terminating abnormally
What could be the reason?
Is there a solution?
I am a Newbe on sqlserver
Vineadb.bak is created (15MB)
testdb.ldf and testdb.mdf are not created.What is the output of
RESTORE FILELISTONLY
FROM DISK = 'C:\Vineadb.bak'
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Henk Schreij" <henk@.schreijDOTnl> wrote in message
news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
1.
RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
I get a Grid with:
. LogicalName
. Physical Name
. Type
. FileGroupName
. Size
1.
VineaDb_Data
C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
D
PRIMARY
16449536
2.
VineaDb_Log
C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
L
NULL
104595456
This is the path to the production database,
not to the just made C:\VineaDb.bak backup.
Could this be the reason?
Then I ask myself, why does it take these files, not the backup.
Remember, ... I am a newbie on SQLServer.
Henk.
-- Original Message --
From: "Jasper Smith" <jasper_smith9@.hotmail.com>
Newsgroups: microsoft.public.sqlserver.server
Sent: Wednesday, January 14, 2004 1:07 AM
Subject: Re: Problem copying a database for testing purposes
[QUOTE]
> What is the output of
> RESTORE FILELISTONLY
> FROM DISK = 'C:\Vineadb.bak'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
purposes.[QUOTE]
of[QUOTE]
> 1.
(1.612[QUOTE]
not[QUOTE]
logical[QUOTE]
DATABASE[QUOTE]|||Its simply a case of not having the correct logical filename for the data
file
RESTORE DATABASE TestDB
FROM DISK = 'C:\Vineadb.bak'
WITH MOVE 'VineaDb_Data' TO 'C:\Program Files\Microsoft
SQLServer\MSSQL\Data\testdb.mdf',
MOVE 'VineaDb_Log' TO 'C:\Program Files\Microsoft
SQLServer\MSSQL\Data\testdb.ldf'
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Henk Schreij" <henk@.schreijDOTnl> wrote in message
news:%23JmoL7n2DHA.1924@.TK2MSFTNGP10.phx.gbl...
Thank you,
This is what I needed.
Now it workes fine.
I'm so glad you told me that I was making a simple mistake.
I couldn't find out (being a newbie) what I was doing wrong.
Henk
"Jasper Smith" <jasper_smith9@.hotmail.com> schreef in bericht
news:e1EA65o2DHA.1744@.TK2MSFTNGP12.phx.gbl...
Thank you,
This is what I needed.
Now it workes fine.
I'm so glad you told me that I was making a simple mistake.
I couldn't find out (being a newbie) what I was doing wrong.
Henk
"Jasper Smith" <jasper_smith9@.hotmail.com> schreef in bericht
news:e1EA65o2DHA.1744@.TK2MSFTNGP12.phx.gbl...[QUOTE]
> Its simply a case of not having the correct logical filename for the data
> file
> RESTORE DATABASE TestDB
> FROM DISK = 'C:\Vineadb.bak'
> WITH MOVE 'VineaDb_Data' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.mdf',
> MOVE 'VineaDb_Log' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.ldf'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:%23JmoL7n2DHA.1924@.TK2MSFTNGP10.phx.gbl...
1.[QUOTE]
In BOL I found in the Transact-SQL Reference:
It may be necessary to create a copy of an existing database on the same
computer for testing purposes. In this case, the database files for the
original database already exist, so different file names need to be
specified when the database copy is created during the restore operation.
Make a copy of a database using BACKUP and RESTORE:
This example uses both the BACKUP and RESTORE statements to make a copy of
the Northwind database (I changed the destination path)
BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.mdf',
MOVE 'Northwind_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.ldf'
GO
This works fine, using SQLserver 2000 Developer with the Query analyzer.
I deleted the testdb database (in the Enterprise manager, testdb.mdf and
.ldf also disapeared)
Then I tried the production database:
BACKUP DATABASE Vineadb
TO DISK = 'C:\Vineadb.bak'
RESTORE FILELISTONLY
FROM DISK = 'C:\Vineadb.bak'
RESTORE DATABASE TestDB
FROM DISK = 'C:\Vineadb.bak'
WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.mdf',
MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.ldf'
GO
This didn't work, giving the message:
Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on file 1.
Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file 1.
BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds (1.612
MB/sec).
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is not
part of database 'TestDB'. Use RESTORE FILELISTONLY to list the logical
file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE DATABASE
is terminating abnormally
What could be the reason?
Is there a solution?
I am a Newbe on sqlserver
Vineadb.bak is created (15MB)
testdb.ldf and testdb.mdf are not created.What is the output of
RESTORE FILELISTONLY
FROM DISK = 'C:\Vineadb.bak'
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Henk Schreij" <henk@.schreijDOTnl> wrote in message
news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
quote:
> I want to make a database copy on the same computer for testing purposes.
> In BOL I found in the Transact-SQL Reference:
> It may be necessary to create a copy of an existing database on the same
> computer for testing purposes. In this case, the database files for the
> original database already exist, so different file names need to be
> specified when the database copy is created during the restore operation.
> Make a copy of a database using BACKUP and RESTORE:
> This example uses both the BACKUP and RESTORE statements to make a copy of
> the Northwind database (I changed the destination path)
> BACKUP DATABASE Northwind
> TO DISK = 'c:\Northwind.bak'
> RESTORE FILELISTONLY FROM DISK = 'c:\Northwind.bak'
> RESTORE DATABASE TestDB
> FROM DISK = 'c:\Northwind.bak'
> WITH MOVE 'Northwind' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.mdf',
> MOVE 'Northwind_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.ldf'
> GO
> This works fine, using SQLserver 2000 Developer with the Query analyzer.
> I deleted the testdb database (in the Enterprise manager, testdb.mdf and
> .ldf also disapeared)
> Then I tried the production database:
> BACKUP DATABASE Vineadb
> TO DISK = 'C:\Vineadb.bak'
> RESTORE FILELISTONLY
> FROM DISK = 'C:\Vineadb.bak'
> RESTORE DATABASE TestDB
> FROM DISK = 'C:\Vineadb.bak'
> WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.mdf',
> MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.ldf'
> GO
> This didn't work, giving the message:
> Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on file
1.
quote:|||Jasper, using
> Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file 1.
> BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds (1.612
> MB/sec).
> (2 row(s) affected)
> Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is not
> part of database 'TestDB'. Use RESTORE FILELISTONLY to list the logical
> file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE DATABASE
> is terminating abnormally
> What could be the reason?
> Is there a solution?
> I am a Newbe on sqlserver
> Vineadb.bak is created (15MB)
> testdb.ldf and testdb.mdf are not created.
>
>
RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
I get a Grid with:
. LogicalName
. Physical Name
. Type
. FileGroupName
. Size
1.
VineaDb_Data
C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
D
PRIMARY
16449536
2.
VineaDb_Log
C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
L
NULL
104595456
This is the path to the production database,
not to the just made C:\VineaDb.bak backup.
Could this be the reason?
Then I ask myself, why does it take these files, not the backup.
Remember, ... I am a newbie on SQLServer.
Henk.
-- Original Message --
From: "Jasper Smith" <jasper_smith9@.hotmail.com>
Newsgroups: microsoft.public.sqlserver.server
Sent: Wednesday, January 14, 2004 1:07 AM
Subject: Re: Problem copying a database for testing purposes
[QUOTE]
> What is the output of
> RESTORE FILELISTONLY
> FROM DISK = 'C:\Vineadb.bak'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
purposes.[QUOTE]
of[QUOTE]
> 1.
(1.612[QUOTE]
not[QUOTE]
logical[QUOTE]
DATABASE[QUOTE]|||Its simply a case of not having the correct logical filename for the data
file
RESTORE DATABASE TestDB
FROM DISK = 'C:\Vineadb.bak'
WITH MOVE 'VineaDb_Data' TO 'C:\Program Files\Microsoft
SQLServer\MSSQL\Data\testdb.mdf',
MOVE 'VineaDb_Log' TO 'C:\Program Files\Microsoft
SQLServer\MSSQL\Data\testdb.ldf'
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Henk Schreij" <henk@.schreijDOTnl> wrote in message
news:%23JmoL7n2DHA.1924@.TK2MSFTNGP10.phx.gbl...
quote:|||Jasper,
> Jasper, using
> RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
> I get a Grid with:
> . LogicalName
> . Physical Name
> . Type
> . FileGroupName
> . Size
> 1.
> VineaDb_Data
> C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
> D
> PRIMARY
> 16449536
> 2.
> VineaDb_Log
> C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
> L
> NULL
> 104595456
> This is the path to the production database,
> not to the just made C:\VineaDb.bak backup.
> Could this be the reason?
> Then I ask myself, why does it take these files, not the backup.
> Remember, ... I am a newbie on SQLServer.
> Henk.
> -- Original Message --
> From: "Jasper Smith" <jasper_smith9@.hotmail.com>
> Newsgroups: microsoft.public.sqlserver.server
> Sent: Wednesday, January 14, 2004 1:07 AM
> Subject: Re: Problem copying a database for testing purposes
>
> purposes.
copy[QUOTE]
> of
analyzer.[QUOTE]
and[QUOTE]
file[QUOTE]
1.[QUOTE]
> (1.612
> not
> logical
> DATABASE
>
>
Thank you,
This is what I needed.
Now it workes fine.
I'm so glad you told me that I was making a simple mistake.
I couldn't find out (being a newbie) what I was doing wrong.
Henk
"Jasper Smith" <jasper_smith9@.hotmail.com> schreef in bericht
news:e1EA65o2DHA.1744@.TK2MSFTNGP12.phx.gbl...
quote:|||Jasper,
> Its simply a case of not having the correct logical filename for the data
> file
> RESTORE DATABASE TestDB
> FROM DISK = 'C:\Vineadb.bak'
> WITH MOVE 'VineaDb_Data' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.mdf',
> MOVE 'VineaDb_Log' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.ldf'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:%23JmoL7n2DHA.1924@.TK2MSFTNGP10.phx.gbl...
> copy
> analyzer.
> and
> file
> 1.
is[QUOTE]
>
Thank you,
This is what I needed.
Now it workes fine.
I'm so glad you told me that I was making a simple mistake.
I couldn't find out (being a newbie) what I was doing wrong.
Henk
"Jasper Smith" <jasper_smith9@.hotmail.com> schreef in bericht
news:e1EA65o2DHA.1744@.TK2MSFTNGP12.phx.gbl...[QUOTE]
> Its simply a case of not having the correct logical filename for the data
> file
> RESTORE DATABASE TestDB
> FROM DISK = 'C:\Vineadb.bak'
> WITH MOVE 'VineaDb_Data' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.mdf',
> MOVE 'VineaDb_Log' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.ldf'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:%23JmoL7n2DHA.1924@.TK2MSFTNGP10.phx.gbl...
1.[QUOTE]
Problem copying a database for testing purposes
I want to make a database copy on the same computer for testing purposes.
In BOL I found in the Transact-SQL Reference:
It may be necessary to create a copy of an existing database on the same
computer for testing purposes. In this case, the database files for the
original database already exist, so different file names need to be
specified when the database copy is created during the restore operation.
Make a copy of a database using BACKUP and RESTORE:
This example uses both the BACKUP and RESTORE statements to make a copy of
the Northwind database (I changed the destination path)
BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.mdf',
MOVE 'Northwind_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.ldf'
GO
This works fine, using SQLserver 2000 Developer with the Query analyzer.
I deleted the testdb database (in the Enterprise manager, testdb.mdf and
.ldf also disapeared)
Then I tried the production database:
BACKUP DATABASE Vineadb
TO DISK = 'C:\Vineadb.bak'
RESTORE FILELISTONLY
FROM DISK = 'C:\Vineadb.bak'
RESTORE DATABASE TestDB
FROM DISK = 'C:\Vineadb.bak'
WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.mdf',
MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.ldf'
GO
This didn't work, giving the message:
Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on file 1.
Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file 1.
BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds (1.612
MB/sec).
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is not
part of database 'TestDB'. Use RESTORE FILELISTONLY to list the logical
file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE DATABASE
is terminating abnormally
What could be the reason?
Is there a solution?
I am a Newbe on sqlserver
Vineadb.bak is created (15MB)
testdb.ldf and testdb.mdf are not created.What is the output of
RESTORE FILELISTONLY
FROM DISK = 'C:\Vineadb.bak'
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Henk Schreij" <henk@.schreijDOTnl> wrote in message
news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> I want to make a database copy on the same computer for testing purposes.
> In BOL I found in the Transact-SQL Reference:
> It may be necessary to create a copy of an existing database on the same
> computer for testing purposes. In this case, the database files for the
> original database already exist, so different file names need to be
> specified when the database copy is created during the restore operation.
> Make a copy of a database using BACKUP and RESTORE:
> This example uses both the BACKUP and RESTORE statements to make a copy of
> the Northwind database (I changed the destination path)
> BACKUP DATABASE Northwind
> TO DISK = 'c:\Northwind.bak'
> RESTORE FILELISTONLY FROM DISK = 'c:\Northwind.bak'
> RESTORE DATABASE TestDB
> FROM DISK = 'c:\Northwind.bak'
> WITH MOVE 'Northwind' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.mdf',
> MOVE 'Northwind_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.ldf'
> GO
> This works fine, using SQLserver 2000 Developer with the Query analyzer.
> I deleted the testdb database (in the Enterprise manager, testdb.mdf and
> .ldf also disapeared)
> Then I tried the production database:
> BACKUP DATABASE Vineadb
> TO DISK = 'C:\Vineadb.bak'
> RESTORE FILELISTONLY
> FROM DISK = 'C:\Vineadb.bak'
> RESTORE DATABASE TestDB
> FROM DISK = 'C:\Vineadb.bak'
> WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.mdf',
> MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.ldf'
> GO
> This didn't work, giving the message:
> Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on file
1.
> Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file 1.
> BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds (1.612
> MB/sec).
> (2 row(s) affected)
> Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is not
> part of database 'TestDB'. Use RESTORE FILELISTONLY to list the logical
> file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE DATABASE
> is terminating abnormally
> What could be the reason?
> Is there a solution?
> I am a Newbe on sqlserver
> Vineadb.bak is created (15MB)
> testdb.ldf and testdb.mdf are not created.
>
>|||Jasper, using
RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
I get a Grid with:
. LogicalName
. Physical Name
. Type
. FileGroupName
. Size
1.
VineaDb_Data
C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
D
PRIMARY
16449536
2.
VineaDb_Log
C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
L
NULL
104595456
This is the path to the production database,
not to the just made C:\VineaDb.bak backup.
Could this be the reason?
Then I ask myself, why does it take these files, not the backup.
Remember, ... I am a newbie on SQLServer.
Henk.
-- Original Message --
From: "Jasper Smith" <jasper_smith9@.hotmail.com>
Newsgroups: microsoft.public.sqlserver.server
Sent: Wednesday, January 14, 2004 1:07 AM
Subject: Re: Problem copying a database for testing purposes
> What is the output of
> RESTORE FILELISTONLY
> FROM DISK = 'C:\Vineadb.bak'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> > I want to make a database copy on the same computer for testing
purposes.
> >
> > In BOL I found in the Transact-SQL Reference:
> > ( . . . )
> > This example uses both the BACKUP and RESTORE statements to make a copy
of
> > the Northwind database (I changed the destination path)
> > ( . . . )
> > This works fine, using SQLserver 2000 Developer with the Query analyzer.
> > I deleted the testdb database (in the Enterprise manager, testdb.mdf and
> > .ldf also disapeared)
> >
> > Then I tried the production database:
> > BACKUP DATABASE Vineadb
> > TO DISK = 'C:\Vineadb.bak'
> > RESTORE FILELISTONLY
> > FROM DISK = 'C:\Vineadb.bak'
> > RESTORE DATABASE TestDB
> > FROM DISK = 'C:\Vineadb.bak'
> > WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\testdb.mdf',
> > MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\testdb.ldf'
> > GO
> >
> > This didn't work, giving the message:
> >
> > Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on file
> 1.
> > Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file 1.
> > BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds
(1.612
> > MB/sec).
> >
> > (2 row(s) affected)
> >
> > Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is
not
> > part of database 'TestDB'. Use RESTORE FILELISTONLY to list the
logical
> > file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE
DATABASE
> > is terminating abnormally|||Its simply a case of not having the correct logical filename for the data
file
RESTORE DATABASE TestDB
FROM DISK = 'C:\Vineadb.bak'
WITH MOVE 'VineaDb_Data' TO 'C:\Program Files\Microsoft
SQLServer\MSSQL\Data\testdb.mdf',
MOVE 'VineaDb_Log' TO 'C:\Program Files\Microsoft
SQLServer\MSSQL\Data\testdb.ldf'
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Henk Schreij" <henk@.schreijDOTnl> wrote in message
news:%23JmoL7n2DHA.1924@.TK2MSFTNGP10.phx.gbl...
> Jasper, using
> RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
> I get a Grid with:
> . LogicalName
> . Physical Name
> . Type
> . FileGroupName
> . Size
> 1.
> VineaDb_Data
> C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
> D
> PRIMARY
> 16449536
> 2.
> VineaDb_Log
> C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
> L
> NULL
> 104595456
> This is the path to the production database,
> not to the just made C:\VineaDb.bak backup.
> Could this be the reason?
> Then I ask myself, why does it take these files, not the backup.
> Remember, ... I am a newbie on SQLServer.
> Henk.
> -- Original Message --
> From: "Jasper Smith" <jasper_smith9@.hotmail.com>
> Newsgroups: microsoft.public.sqlserver.server
> Sent: Wednesday, January 14, 2004 1:07 AM
> Subject: Re: Problem copying a database for testing purposes
>
> > What is the output of
> >
> > RESTORE FILELISTONLY
> > FROM DISK = 'C:\Vineadb.bak'
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> >
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> >
> > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> > > I want to make a database copy on the same computer for testing
> purposes.
> > >
> > > In BOL I found in the Transact-SQL Reference:
> > > ( . . . )
> > > This example uses both the BACKUP and RESTORE statements to make a
copy
> of
> > > the Northwind database (I changed the destination path)
> > > ( . . . )
> > > This works fine, using SQLserver 2000 Developer with the Query
analyzer.
> > > I deleted the testdb database (in the Enterprise manager, testdb.mdf
and
> > > .ldf also disapeared)
> > >
> > > Then I tried the production database:
> > > BACKUP DATABASE Vineadb
> > > TO DISK = 'C:\Vineadb.bak'
> > > RESTORE FILELISTONLY
> > > FROM DISK = 'C:\Vineadb.bak'
> > > RESTORE DATABASE TestDB
> > > FROM DISK = 'C:\Vineadb.bak'
> > > WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> > > Server\MSSQL\Data\testdb.mdf',
> > > MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> > > Server\MSSQL\Data\testdb.ldf'
> > > GO
> > >
> > > This didn't work, giving the message:
> > >
> > > Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on
file
> > 1.
> > > Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file
1.
> > > BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds
> (1.612
> > > MB/sec).
> > >
> > > (2 row(s) affected)
> > >
> > > Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is
> not
> > > part of database 'TestDB'. Use RESTORE FILELISTONLY to list the
> logical
> > > file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE
> DATABASE
> > > is terminating abnormally
>
>|||Jasper,
Thank you,
This is what I needed.
Now it workes fine.
I'm so glad you told me that I was making a simple mistake.
I couldn't find out (being a newbie) what I was doing wrong.
Henk
"Jasper Smith" <jasper_smith9@.hotmail.com> schreef in bericht
news:e1EA65o2DHA.1744@.TK2MSFTNGP12.phx.gbl...
> Its simply a case of not having the correct logical filename for the data
> file
> RESTORE DATABASE TestDB
> FROM DISK = 'C:\Vineadb.bak'
> WITH MOVE 'VineaDb_Data' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.mdf',
> MOVE 'VineaDb_Log' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.ldf'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:%23JmoL7n2DHA.1924@.TK2MSFTNGP10.phx.gbl...
> >
> > Jasper, using
> > RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
> > I get a Grid with:
> > . LogicalName
> > . Physical Name
> > . Type
> > . FileGroupName
> > . Size
> > 1.
> > VineaDb_Data
> > C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
> > D
> > PRIMARY
> > 16449536
> > 2.
> > VineaDb_Log
> > C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
> > L
> > NULL
> > 104595456
> >
> > This is the path to the production database,
> > not to the just made C:\VineaDb.bak backup.
> > Could this be the reason?
> > Then I ask myself, why does it take these files, not the backup.
> > Remember, ... I am a newbie on SQLServer.
> >
> > Henk.
> >
> > -- Original Message --
> > From: "Jasper Smith" <jasper_smith9@.hotmail.com>
> > Newsgroups: microsoft.public.sqlserver.server
> > Sent: Wednesday, January 14, 2004 1:07 AM
> > Subject: Re: Problem copying a database for testing purposes
> >
> >
> > > What is the output of
> > >
> > > RESTORE FILELISTONLY
> > > FROM DISK = 'C:\Vineadb.bak'
> > >
> > > --
> > > HTH
> > >
> > > Jasper Smith (SQL Server MVP)
> > >
> > > I support PASS - the definitive, global
> > > community for SQL Server professionals -
> > > http://www.sqlpass.org
> > >
> > >
> > > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > > news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> > > > I want to make a database copy on the same computer for testing
> > purposes.
> > > >
> > > > In BOL I found in the Transact-SQL Reference:
> > > > ( . . . )
> > > > This example uses both the BACKUP and RESTORE statements to make a
> copy
> > of
> > > > the Northwind database (I changed the destination path)
> > > > ( . . . )
> > > > This works fine, using SQLserver 2000 Developer with the Query
> analyzer.
> > > > I deleted the testdb database (in the Enterprise manager, testdb.mdf
> and
> > > > .ldf also disapeared)
> > > >
> > > > Then I tried the production database:
> > > > BACKUP DATABASE Vineadb
> > > > TO DISK = 'C:\Vineadb.bak'
> > > > RESTORE FILELISTONLY
> > > > FROM DISK = 'C:\Vineadb.bak'
> > > > RESTORE DATABASE TestDB
> > > > FROM DISK = 'C:\Vineadb.bak'
> > > > WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL\Data\testdb.mdf',
> > > > MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL\Data\testdb.ldf'
> > > > GO
> > > >
> > > > This didn't work, giving the message:
> > > >
> > > > Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on
> file
> > > 1.
> > > > Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file
> 1.
> > > > BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds
> > (1.612
> > > > MB/sec).
> > > >
> > > > (2 row(s) affected)
> > > >
> > > > Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb'
is
> > not
> > > > part of database 'TestDB'. Use RESTORE FILELISTONLY to list the
> > logical
> > > > file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE
> > DATABASE
> > > > is terminating abnormally
> >
> >
> >
> >
>|||Jasper,
Thank you,
This is what I needed.
Now it workes fine.
I'm so glad you told me that I was making a simple mistake.
I couldn't find out (being a newbie) what I was doing wrong.
Henk
"Jasper Smith" <jasper_smith9@.hotmail.com> schreef in bericht
news:e1EA65o2DHA.1744@.TK2MSFTNGP12.phx.gbl...
> Its simply a case of not having the correct logical filename for the data
> file
> RESTORE DATABASE TestDB
> FROM DISK = 'C:\Vineadb.bak'
> WITH MOVE 'VineaDb_Data' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.mdf',
> MOVE 'VineaDb_Log' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.ldf'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:%23JmoL7n2DHA.1924@.TK2MSFTNGP10.phx.gbl...
> >
> > Jasper, using
> > RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
> > I get a Grid with:
> > . LogicalName
> > . Physical Name
> > . Type
> > . FileGroupName
> > . Size
> > 1.
> > VineaDb_Data
> > C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
> > D
> > PRIMARY
> > 16449536
> > 2.
> > VineaDb_Log
> > C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
> > L
> > NULL
> > 104595456
> >
> > This is the path to the production database,
> > not to the just made C:\VineaDb.bak backup.
> > Could this be the reason?
> > Then I ask myself, why does it take these files, not the backup.
> > Remember, ... I am a newbie on SQLServer.
> >
> > Henk.
> >
> > -- Original Message --
> > From: "Jasper Smith" <jasper_smith9@.hotmail.com>
> > Newsgroups: microsoft.public.sqlserver.server
> > Sent: Wednesday, January 14, 2004 1:07 AM
> > Subject: Re: Problem copying a database for testing purposes
> >
> >
> > > What is the output of
> > >
> > > RESTORE FILELISTONLY
> > > FROM DISK = 'C:\Vineadb.bak'
> > >
> > > --
> > > HTH
> > >
> > > Jasper Smith (SQL Server MVP)
> > >
> > > I support PASS - the definitive, global
> > > community for SQL Server professionals -
> > > http://www.sqlpass.org
> > >
> > >
> > > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > > news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> > > > I want to make a database copy on the same computer for testing
> > purposes.
> > > >
> > > > In BOL I found in the Transact-SQL Reference:
> > > > ( . . . )
> > > > This example uses both the BACKUP and RESTORE statements to make
> > > > copy of the Northwind database (I changed the destination path)
> > > > ( . . . )
> > > > This works fine, using SQLserver 2000 Developer with Query analyzer.
> > > > I deleted the testdb database (in the Enterprise manager)
> > > >
> > > > Then I tried the production database:
> > > > BACKUP DATABASE Vineadb
> > > > TO DISK = 'C:\Vineadb.bak'
> > > > RESTORE FILELISTONLY
> > > > FROM DISK = 'C:\Vineadb.bak'
> > > > RESTORE DATABASE TestDB
> > > > FROM DISK = 'C:\Vineadb.bak'
> > > > WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL\Data\testdb.mdf',
> > > > MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL\Data\testdb.ldf'
> > > > GO
> > > >
> > > > This didn't work, giving the message:
> > > >
> > > > Processed 1848 pages for db 'Vineadb', file 'VineaDb_Data' on file
1.
> > > > Processed 1 pages for db 'Vineadb', file 'VineaDb_Log' on file 1.
> > > > BACKUP DATABASE successfully processed 1849 pages in 9.388 sec
> > > > (2 row(s) affected)
> > > >
> > > > Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb'
> > > > is not part of database 'TestDB'.
> > > > Use RESTORE FILELISTONLY to list the logical
> > > > file names. Server: Msg 3013, Level 16, State 1, Line 5
> > > > RESTORE DATABASE is terminating abnormally
In BOL I found in the Transact-SQL Reference:
It may be necessary to create a copy of an existing database on the same
computer for testing purposes. In this case, the database files for the
original database already exist, so different file names need to be
specified when the database copy is created during the restore operation.
Make a copy of a database using BACKUP and RESTORE:
This example uses both the BACKUP and RESTORE statements to make a copy of
the Northwind database (I changed the destination path)
BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.mdf',
MOVE 'Northwind_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.ldf'
GO
This works fine, using SQLserver 2000 Developer with the Query analyzer.
I deleted the testdb database (in the Enterprise manager, testdb.mdf and
.ldf also disapeared)
Then I tried the production database:
BACKUP DATABASE Vineadb
TO DISK = 'C:\Vineadb.bak'
RESTORE FILELISTONLY
FROM DISK = 'C:\Vineadb.bak'
RESTORE DATABASE TestDB
FROM DISK = 'C:\Vineadb.bak'
WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.mdf',
MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.ldf'
GO
This didn't work, giving the message:
Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on file 1.
Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file 1.
BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds (1.612
MB/sec).
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is not
part of database 'TestDB'. Use RESTORE FILELISTONLY to list the logical
file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE DATABASE
is terminating abnormally
What could be the reason?
Is there a solution?
I am a Newbe on sqlserver
Vineadb.bak is created (15MB)
testdb.ldf and testdb.mdf are not created.What is the output of
RESTORE FILELISTONLY
FROM DISK = 'C:\Vineadb.bak'
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Henk Schreij" <henk@.schreijDOTnl> wrote in message
news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> I want to make a database copy on the same computer for testing purposes.
> In BOL I found in the Transact-SQL Reference:
> It may be necessary to create a copy of an existing database on the same
> computer for testing purposes. In this case, the database files for the
> original database already exist, so different file names need to be
> specified when the database copy is created during the restore operation.
> Make a copy of a database using BACKUP and RESTORE:
> This example uses both the BACKUP and RESTORE statements to make a copy of
> the Northwind database (I changed the destination path)
> BACKUP DATABASE Northwind
> TO DISK = 'c:\Northwind.bak'
> RESTORE FILELISTONLY FROM DISK = 'c:\Northwind.bak'
> RESTORE DATABASE TestDB
> FROM DISK = 'c:\Northwind.bak'
> WITH MOVE 'Northwind' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.mdf',
> MOVE 'Northwind_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.ldf'
> GO
> This works fine, using SQLserver 2000 Developer with the Query analyzer.
> I deleted the testdb database (in the Enterprise manager, testdb.mdf and
> .ldf also disapeared)
> Then I tried the production database:
> BACKUP DATABASE Vineadb
> TO DISK = 'C:\Vineadb.bak'
> RESTORE FILELISTONLY
> FROM DISK = 'C:\Vineadb.bak'
> RESTORE DATABASE TestDB
> FROM DISK = 'C:\Vineadb.bak'
> WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.mdf',
> MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.ldf'
> GO
> This didn't work, giving the message:
> Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on file
1.
> Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file 1.
> BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds (1.612
> MB/sec).
> (2 row(s) affected)
> Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is not
> part of database 'TestDB'. Use RESTORE FILELISTONLY to list the logical
> file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE DATABASE
> is terminating abnormally
> What could be the reason?
> Is there a solution?
> I am a Newbe on sqlserver
> Vineadb.bak is created (15MB)
> testdb.ldf and testdb.mdf are not created.
>
>|||Jasper, using
RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
I get a Grid with:
. LogicalName
. Physical Name
. Type
. FileGroupName
. Size
1.
VineaDb_Data
C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
D
PRIMARY
16449536
2.
VineaDb_Log
C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
L
NULL
104595456
This is the path to the production database,
not to the just made C:\VineaDb.bak backup.
Could this be the reason?
Then I ask myself, why does it take these files, not the backup.
Remember, ... I am a newbie on SQLServer.
Henk.
-- Original Message --
From: "Jasper Smith" <jasper_smith9@.hotmail.com>
Newsgroups: microsoft.public.sqlserver.server
Sent: Wednesday, January 14, 2004 1:07 AM
Subject: Re: Problem copying a database for testing purposes
> What is the output of
> RESTORE FILELISTONLY
> FROM DISK = 'C:\Vineadb.bak'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> > I want to make a database copy on the same computer for testing
purposes.
> >
> > In BOL I found in the Transact-SQL Reference:
> > ( . . . )
> > This example uses both the BACKUP and RESTORE statements to make a copy
of
> > the Northwind database (I changed the destination path)
> > ( . . . )
> > This works fine, using SQLserver 2000 Developer with the Query analyzer.
> > I deleted the testdb database (in the Enterprise manager, testdb.mdf and
> > .ldf also disapeared)
> >
> > Then I tried the production database:
> > BACKUP DATABASE Vineadb
> > TO DISK = 'C:\Vineadb.bak'
> > RESTORE FILELISTONLY
> > FROM DISK = 'C:\Vineadb.bak'
> > RESTORE DATABASE TestDB
> > FROM DISK = 'C:\Vineadb.bak'
> > WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\testdb.mdf',
> > MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\testdb.ldf'
> > GO
> >
> > This didn't work, giving the message:
> >
> > Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on file
> 1.
> > Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file 1.
> > BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds
(1.612
> > MB/sec).
> >
> > (2 row(s) affected)
> >
> > Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is
not
> > part of database 'TestDB'. Use RESTORE FILELISTONLY to list the
logical
> > file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE
DATABASE
> > is terminating abnormally|||Its simply a case of not having the correct logical filename for the data
file
RESTORE DATABASE TestDB
FROM DISK = 'C:\Vineadb.bak'
WITH MOVE 'VineaDb_Data' TO 'C:\Program Files\Microsoft
SQLServer\MSSQL\Data\testdb.mdf',
MOVE 'VineaDb_Log' TO 'C:\Program Files\Microsoft
SQLServer\MSSQL\Data\testdb.ldf'
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Henk Schreij" <henk@.schreijDOTnl> wrote in message
news:%23JmoL7n2DHA.1924@.TK2MSFTNGP10.phx.gbl...
> Jasper, using
> RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
> I get a Grid with:
> . LogicalName
> . Physical Name
> . Type
> . FileGroupName
> . Size
> 1.
> VineaDb_Data
> C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
> D
> PRIMARY
> 16449536
> 2.
> VineaDb_Log
> C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
> L
> NULL
> 104595456
> This is the path to the production database,
> not to the just made C:\VineaDb.bak backup.
> Could this be the reason?
> Then I ask myself, why does it take these files, not the backup.
> Remember, ... I am a newbie on SQLServer.
> Henk.
> -- Original Message --
> From: "Jasper Smith" <jasper_smith9@.hotmail.com>
> Newsgroups: microsoft.public.sqlserver.server
> Sent: Wednesday, January 14, 2004 1:07 AM
> Subject: Re: Problem copying a database for testing purposes
>
> > What is the output of
> >
> > RESTORE FILELISTONLY
> > FROM DISK = 'C:\Vineadb.bak'
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> >
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> >
> > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> > > I want to make a database copy on the same computer for testing
> purposes.
> > >
> > > In BOL I found in the Transact-SQL Reference:
> > > ( . . . )
> > > This example uses both the BACKUP and RESTORE statements to make a
copy
> of
> > > the Northwind database (I changed the destination path)
> > > ( . . . )
> > > This works fine, using SQLserver 2000 Developer with the Query
analyzer.
> > > I deleted the testdb database (in the Enterprise manager, testdb.mdf
and
> > > .ldf also disapeared)
> > >
> > > Then I tried the production database:
> > > BACKUP DATABASE Vineadb
> > > TO DISK = 'C:\Vineadb.bak'
> > > RESTORE FILELISTONLY
> > > FROM DISK = 'C:\Vineadb.bak'
> > > RESTORE DATABASE TestDB
> > > FROM DISK = 'C:\Vineadb.bak'
> > > WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> > > Server\MSSQL\Data\testdb.mdf',
> > > MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> > > Server\MSSQL\Data\testdb.ldf'
> > > GO
> > >
> > > This didn't work, giving the message:
> > >
> > > Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on
file
> > 1.
> > > Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file
1.
> > > BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds
> (1.612
> > > MB/sec).
> > >
> > > (2 row(s) affected)
> > >
> > > Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is
> not
> > > part of database 'TestDB'. Use RESTORE FILELISTONLY to list the
> logical
> > > file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE
> DATABASE
> > > is terminating abnormally
>
>|||Jasper,
Thank you,
This is what I needed.
Now it workes fine.
I'm so glad you told me that I was making a simple mistake.
I couldn't find out (being a newbie) what I was doing wrong.
Henk
"Jasper Smith" <jasper_smith9@.hotmail.com> schreef in bericht
news:e1EA65o2DHA.1744@.TK2MSFTNGP12.phx.gbl...
> Its simply a case of not having the correct logical filename for the data
> file
> RESTORE DATABASE TestDB
> FROM DISK = 'C:\Vineadb.bak'
> WITH MOVE 'VineaDb_Data' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.mdf',
> MOVE 'VineaDb_Log' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.ldf'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:%23JmoL7n2DHA.1924@.TK2MSFTNGP10.phx.gbl...
> >
> > Jasper, using
> > RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
> > I get a Grid with:
> > . LogicalName
> > . Physical Name
> > . Type
> > . FileGroupName
> > . Size
> > 1.
> > VineaDb_Data
> > C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
> > D
> > PRIMARY
> > 16449536
> > 2.
> > VineaDb_Log
> > C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
> > L
> > NULL
> > 104595456
> >
> > This is the path to the production database,
> > not to the just made C:\VineaDb.bak backup.
> > Could this be the reason?
> > Then I ask myself, why does it take these files, not the backup.
> > Remember, ... I am a newbie on SQLServer.
> >
> > Henk.
> >
> > -- Original Message --
> > From: "Jasper Smith" <jasper_smith9@.hotmail.com>
> > Newsgroups: microsoft.public.sqlserver.server
> > Sent: Wednesday, January 14, 2004 1:07 AM
> > Subject: Re: Problem copying a database for testing purposes
> >
> >
> > > What is the output of
> > >
> > > RESTORE FILELISTONLY
> > > FROM DISK = 'C:\Vineadb.bak'
> > >
> > > --
> > > HTH
> > >
> > > Jasper Smith (SQL Server MVP)
> > >
> > > I support PASS - the definitive, global
> > > community for SQL Server professionals -
> > > http://www.sqlpass.org
> > >
> > >
> > > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > > news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> > > > I want to make a database copy on the same computer for testing
> > purposes.
> > > >
> > > > In BOL I found in the Transact-SQL Reference:
> > > > ( . . . )
> > > > This example uses both the BACKUP and RESTORE statements to make a
> copy
> > of
> > > > the Northwind database (I changed the destination path)
> > > > ( . . . )
> > > > This works fine, using SQLserver 2000 Developer with the Query
> analyzer.
> > > > I deleted the testdb database (in the Enterprise manager, testdb.mdf
> and
> > > > .ldf also disapeared)
> > > >
> > > > Then I tried the production database:
> > > > BACKUP DATABASE Vineadb
> > > > TO DISK = 'C:\Vineadb.bak'
> > > > RESTORE FILELISTONLY
> > > > FROM DISK = 'C:\Vineadb.bak'
> > > > RESTORE DATABASE TestDB
> > > > FROM DISK = 'C:\Vineadb.bak'
> > > > WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL\Data\testdb.mdf',
> > > > MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL\Data\testdb.ldf'
> > > > GO
> > > >
> > > > This didn't work, giving the message:
> > > >
> > > > Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on
> file
> > > 1.
> > > > Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file
> 1.
> > > > BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds
> > (1.612
> > > > MB/sec).
> > > >
> > > > (2 row(s) affected)
> > > >
> > > > Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb'
is
> > not
> > > > part of database 'TestDB'. Use RESTORE FILELISTONLY to list the
> > logical
> > > > file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE
> > DATABASE
> > > > is terminating abnormally
> >
> >
> >
> >
>|||Jasper,
Thank you,
This is what I needed.
Now it workes fine.
I'm so glad you told me that I was making a simple mistake.
I couldn't find out (being a newbie) what I was doing wrong.
Henk
"Jasper Smith" <jasper_smith9@.hotmail.com> schreef in bericht
news:e1EA65o2DHA.1744@.TK2MSFTNGP12.phx.gbl...
> Its simply a case of not having the correct logical filename for the data
> file
> RESTORE DATABASE TestDB
> FROM DISK = 'C:\Vineadb.bak'
> WITH MOVE 'VineaDb_Data' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.mdf',
> MOVE 'VineaDb_Log' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.ldf'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:%23JmoL7n2DHA.1924@.TK2MSFTNGP10.phx.gbl...
> >
> > Jasper, using
> > RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
> > I get a Grid with:
> > . LogicalName
> > . Physical Name
> > . Type
> > . FileGroupName
> > . Size
> > 1.
> > VineaDb_Data
> > C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
> > D
> > PRIMARY
> > 16449536
> > 2.
> > VineaDb_Log
> > C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
> > L
> > NULL
> > 104595456
> >
> > This is the path to the production database,
> > not to the just made C:\VineaDb.bak backup.
> > Could this be the reason?
> > Then I ask myself, why does it take these files, not the backup.
> > Remember, ... I am a newbie on SQLServer.
> >
> > Henk.
> >
> > -- Original Message --
> > From: "Jasper Smith" <jasper_smith9@.hotmail.com>
> > Newsgroups: microsoft.public.sqlserver.server
> > Sent: Wednesday, January 14, 2004 1:07 AM
> > Subject: Re: Problem copying a database for testing purposes
> >
> >
> > > What is the output of
> > >
> > > RESTORE FILELISTONLY
> > > FROM DISK = 'C:\Vineadb.bak'
> > >
> > > --
> > > HTH
> > >
> > > Jasper Smith (SQL Server MVP)
> > >
> > > I support PASS - the definitive, global
> > > community for SQL Server professionals -
> > > http://www.sqlpass.org
> > >
> > >
> > > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > > news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> > > > I want to make a database copy on the same computer for testing
> > purposes.
> > > >
> > > > In BOL I found in the Transact-SQL Reference:
> > > > ( . . . )
> > > > This example uses both the BACKUP and RESTORE statements to make
> > > > copy of the Northwind database (I changed the destination path)
> > > > ( . . . )
> > > > This works fine, using SQLserver 2000 Developer with Query analyzer.
> > > > I deleted the testdb database (in the Enterprise manager)
> > > >
> > > > Then I tried the production database:
> > > > BACKUP DATABASE Vineadb
> > > > TO DISK = 'C:\Vineadb.bak'
> > > > RESTORE FILELISTONLY
> > > > FROM DISK = 'C:\Vineadb.bak'
> > > > RESTORE DATABASE TestDB
> > > > FROM DISK = 'C:\Vineadb.bak'
> > > > WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL\Data\testdb.mdf',
> > > > MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL\Data\testdb.ldf'
> > > > GO
> > > >
> > > > This didn't work, giving the message:
> > > >
> > > > Processed 1848 pages for db 'Vineadb', file 'VineaDb_Data' on file
1.
> > > > Processed 1 pages for db 'Vineadb', file 'VineaDb_Log' on file 1.
> > > > BACKUP DATABASE successfully processed 1849 pages in 9.388 sec
> > > > (2 row(s) affected)
> > > >
> > > > Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb'
> > > > is not part of database 'TestDB'.
> > > > Use RESTORE FILELISTONLY to list the logical
> > > > file names. Server: Msg 3013, Level 16, State 1, Line 5
> > > > RESTORE DATABASE is terminating abnormally
Subscribe to:
Posts (Atom)