I'm used the Copy Database Wizard to copy a database from one server to
another. While in the copy operation the destination machine was hanging and
I had to reset it.
Now when I want to attach the source database again, I get the following
error:
"CREATE FILE encountered operating system error 5 (Access is denied.)..."
What is going on here? Why was the source machine affected in this
destructive manner?
What do I need to do in order to attach my source again?
OlavI generally find that it's 10x easier to shoot yourself in the foot and get
into serious trouble with SQL Server 2005 than it was with SQL Server 2000.
Olav
"Olav" <x@.y.com> wrote in message
news:O0Fj8YZXGHA.4212@.TK2MSFTNGP02.phx.gbl...
> I'm used the Copy Database Wizard to copy a database from one server to
> another. While in the copy operation the destination machine was hanging
> and I had to reset it.
> Now when I want to attach the source database again, I get the following
> error:
> "CREATE FILE encountered operating system error 5 (Access is denied.)..."
> What is going on here? Why was the source machine affected in this
> destructive manner?
> What do I need to do in order to attach my source again?
> Olav
>|||I read somewhere detaching databases is very risky, and can infact
corrupt the entire database if something goes wrong... Good luck to
you.|||?
Nice if that is the default option the Copy Database Wizard is using then?
Olav
"KBuser" <Kyle.Buser@.gmail.com> wrote in message
news:1144783193.978615.83980@.t31g2000cwb.googlegroups.com...
>I read somewhere detaching databases is very risky, and can infact
> corrupt the entire database if something goes wrong... Good luck to
> you.
>|||Stop me if im boring you ...
Basically a database created using created database Test consists of 2 files
mdf for tables
ldf for transaction logs
In effect detaching a database is what happens when you stop sql server
attaching is what happens when you start sql server
so dont be afraid of this process
Be aware though that ...
Any transactions not written down to the mdf file and still in the ldf
transaction logs will need to be recovered by SQL server.
to limit this complication we
stop all users
run checkpoint 3 times to for those transactions down into mdf
then detach
at this point the logfile is emptied and can be ditched on attach
Ever wanted to shink you log file. then this method can be employed.
Note I would not used this method on prodction systems crital to business..
Lifes too short and its a hassel geting another job.
If you detached thdatabase then you basicall have 2 files
If a file gets chopped on a copy you would normally tidy up and resend file.
hope this helps
"Olav" wrote:
> ?
> Nice if that is the default option the Copy Database Wizard is using then?
> Olav
> "KBuser" <Kyle.Buser@.gmail.com> wrote in message
> news:1144783193.978615.83980@.t31g2000cwb.googlegroups.com...
>
>|||"KBuser" <Kyle.Buser@.gmail.com> wrote in message
news:1144783193.978615.83980@.t31g2000cwb.googlegroups.com...
> I read somewhere detaching databases is very risky, and can infact
> corrupt the entire database if something goes wrong... Good luck to
> you.
No, detaching a database should be perfectly fine.
But you need to copy both the MDF and LDF files and attach both.
>
Showing posts with label cant. Show all posts
Showing posts with label cant. Show all posts
Thursday, March 22, 2012
Copy Database, can't attach it?
I'm used the Copy Database Wizard to copy a database from one server to
another. While in the copy operation the destination machine was hanging and
I had to reset it.
Now when I want to attach the source database again, I get the following
error:
"CREATE FILE encountered operating system error 5 (Access is denied.)..."
What is going on here? Why was the source machine affected in this
destructive manner?
What do I need to do in order to attach my source again?
OlavI generally find that it's 10x easier to shoot yourself in the foot and get
into serious trouble with SQL Server 2005 than it was with SQL Server 2000.
Olav
"Olav" <x@.y.com> wrote in message
news:O0Fj8YZXGHA.4212@.TK2MSFTNGP02.phx.gbl...
> I'm used the Copy Database Wizard to copy a database from one server to
> another. While in the copy operation the destination machine was hanging
> and I had to reset it.
> Now when I want to attach the source database again, I get the following
> error:
> "CREATE FILE encountered operating system error 5 (Access is denied.)..."
> What is going on here? Why was the source machine affected in this
> destructive manner?
> What do I need to do in order to attach my source again?
> Olav
>|||I read somewhere detaching databases is very risky, and can infact
corrupt the entire database if something goes wrong... Good luck to
you.|||?
Nice if that is the default option the Copy Database Wizard is using then?
Olav
"KBuser" <Kyle.Buser@.gmail.com> wrote in message
news:1144783193.978615.83980@.t31g2000cwb.googlegroups.com...
>I read somewhere detaching databases is very risky, and can infact
> corrupt the entire database if something goes wrong... Good luck to
> you.
>|||Stop me if im boring you ...
Basically a database created using created database Test consists of 2 files
mdf for tables
ldf for transaction logs
In effect detaching a database is what happens when you stop sql server
attaching is what happens when you start sql server
so dont be afraid of this process
Be aware though that ...
Any transactions not written down to the mdf file and still in the ldf
transaction logs will need to be recovered by SQL server.
to limit this complication we
stop all users
run checkpoint 3 times to for those transactions down into mdf
then detach
at this point the logfile is emptied and can be ditched on attach
Ever wanted to shink you log file. then this method can be employed.
Note I would not used this method on prodction systems crital to business..
Lifes too short and its a hassel geting another job.
If you detached thdatabase then you basicall have 2 files
If a file gets chopped on a copy you would normally tidy up and resend file.
hope this helps
"Olav" wrote:
> ?
> Nice if that is the default option the Copy Database Wizard is using then?
> Olav
> "KBuser" <Kyle.Buser@.gmail.com> wrote in message
> news:1144783193.978615.83980@.t31g2000cwb.googlegroups.com...
> >I read somewhere detaching databases is very risky, and can infact
> > corrupt the entire database if something goes wrong... Good luck to
> > you.
> >
>
>|||"KBuser" <Kyle.Buser@.gmail.com> wrote in message
news:1144783193.978615.83980@.t31g2000cwb.googlegroups.com...
> I read somewhere detaching databases is very risky, and can infact
> corrupt the entire database if something goes wrong... Good luck to
> you.
No, detaching a database should be perfectly fine.
But you need to copy both the MDF and LDF files and attach both.
>
another. While in the copy operation the destination machine was hanging and
I had to reset it.
Now when I want to attach the source database again, I get the following
error:
"CREATE FILE encountered operating system error 5 (Access is denied.)..."
What is going on here? Why was the source machine affected in this
destructive manner?
What do I need to do in order to attach my source again?
OlavI generally find that it's 10x easier to shoot yourself in the foot and get
into serious trouble with SQL Server 2005 than it was with SQL Server 2000.
Olav
"Olav" <x@.y.com> wrote in message
news:O0Fj8YZXGHA.4212@.TK2MSFTNGP02.phx.gbl...
> I'm used the Copy Database Wizard to copy a database from one server to
> another. While in the copy operation the destination machine was hanging
> and I had to reset it.
> Now when I want to attach the source database again, I get the following
> error:
> "CREATE FILE encountered operating system error 5 (Access is denied.)..."
> What is going on here? Why was the source machine affected in this
> destructive manner?
> What do I need to do in order to attach my source again?
> Olav
>|||I read somewhere detaching databases is very risky, and can infact
corrupt the entire database if something goes wrong... Good luck to
you.|||?
Nice if that is the default option the Copy Database Wizard is using then?
Olav
"KBuser" <Kyle.Buser@.gmail.com> wrote in message
news:1144783193.978615.83980@.t31g2000cwb.googlegroups.com...
>I read somewhere detaching databases is very risky, and can infact
> corrupt the entire database if something goes wrong... Good luck to
> you.
>|||Stop me if im boring you ...
Basically a database created using created database Test consists of 2 files
mdf for tables
ldf for transaction logs
In effect detaching a database is what happens when you stop sql server
attaching is what happens when you start sql server
so dont be afraid of this process
Be aware though that ...
Any transactions not written down to the mdf file and still in the ldf
transaction logs will need to be recovered by SQL server.
to limit this complication we
stop all users
run checkpoint 3 times to for those transactions down into mdf
then detach
at this point the logfile is emptied and can be ditched on attach
Ever wanted to shink you log file. then this method can be employed.
Note I would not used this method on prodction systems crital to business..
Lifes too short and its a hassel geting another job.
If you detached thdatabase then you basicall have 2 files
If a file gets chopped on a copy you would normally tidy up and resend file.
hope this helps
"Olav" wrote:
> ?
> Nice if that is the default option the Copy Database Wizard is using then?
> Olav
> "KBuser" <Kyle.Buser@.gmail.com> wrote in message
> news:1144783193.978615.83980@.t31g2000cwb.googlegroups.com...
> >I read somewhere detaching databases is very risky, and can infact
> > corrupt the entire database if something goes wrong... Good luck to
> > you.
> >
>
>|||"KBuser" <Kyle.Buser@.gmail.com> wrote in message
news:1144783193.978615.83980@.t31g2000cwb.googlegroups.com...
> I read somewhere detaching databases is very risky, and can infact
> corrupt the entire database if something goes wrong... Good luck to
> you.
No, detaching a database should be perfectly fine.
But you need to copy both the MDF and LDF files and attach both.
>
Tuesday, March 20, 2012
Copy database to another server
Hi;
I asked this once before but can't find the info now. I need to copy a
database from one server and put it on a second server. It is Sql Server 2000
and both servers are running Windows 2003 in a workgroup (not a domain).
I want to get all of the data and meta-data across. How can I do this? (All
I remember is it is very complex because the systems are not in a domain.)
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Have a look at this list compiled by Andrew Kelly:
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map logins to users
http://www.dbmaint.com/SyncSql Logins.asp
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Restoring a .mdf
http://www.sqlservercentral.com/scri...p?scriptid=599
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Thielen" <david@.bogus.windward.net> wrote in message
news:C8DE23AA-9CF6-46E8-8358-A998B00470EA@.microsoft.com...
> Hi;
> I asked this once before but can't find the info now. I need to copy a
> database from one server and put it on a second server. It is Sql Server 2000
> and both servers are running Windows 2003 in a workgroup (not a domain).
> I want to get all of the data and meta-data across. How can I do this? (All
> I remember is it is very complex because the systems are not in a domain.)
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>
|||Hi;
The post http://www.support.microsoft.com/kb/304692/ is for Sql Server 7
only. Is there a post somewhere for Sql Server 2000?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
|||Also, this post says how to restore a database - but not how to back it up.
How do I back it up?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
"David Thielen" wrote:
> Hi;
> The post http://www.support.microsoft.com/kb/304692/ is for Sql Server 7
> only. Is there a post somewhere for Sql Server 2000?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>
|||A quick look suggests that this should also work for 2000, but test in a test environment first. As
for how to backup, read in Books Online about the BACKUP DATABASE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Thielen" <david@.bogus.windward.net> wrote in message
news:949DC479-E52A-44BE-970F-AB64E05459AD@.microsoft.com...[vbcol=seagreen]
> Also, this post says how to restore a database - but not how to back it up.
> How do I back it up?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>
> "David Thielen" wrote:
sqlsql
I asked this once before but can't find the info now. I need to copy a
database from one server and put it on a second server. It is Sql Server 2000
and both servers are running Windows 2003 in a workgroup (not a domain).
I want to get all of the data and meta-data across. How can I do this? (All
I remember is it is very complex because the systems are not in a domain.)
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Have a look at this list compiled by Andrew Kelly:
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map logins to users
http://www.dbmaint.com/SyncSql Logins.asp
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Restoring a .mdf
http://www.sqlservercentral.com/scri...p?scriptid=599
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Thielen" <david@.bogus.windward.net> wrote in message
news:C8DE23AA-9CF6-46E8-8358-A998B00470EA@.microsoft.com...
> Hi;
> I asked this once before but can't find the info now. I need to copy a
> database from one server and put it on a second server. It is Sql Server 2000
> and both servers are running Windows 2003 in a workgroup (not a domain).
> I want to get all of the data and meta-data across. How can I do this? (All
> I remember is it is very complex because the systems are not in a domain.)
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>
|||Hi;
The post http://www.support.microsoft.com/kb/304692/ is for Sql Server 7
only. Is there a post somewhere for Sql Server 2000?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
|||Also, this post says how to restore a database - but not how to back it up.
How do I back it up?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
"David Thielen" wrote:
> Hi;
> The post http://www.support.microsoft.com/kb/304692/ is for Sql Server 7
> only. Is there a post somewhere for Sql Server 2000?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>
|||A quick look suggests that this should also work for 2000, but test in a test environment first. As
for how to backup, read in Books Online about the BACKUP DATABASE command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Thielen" <david@.bogus.windward.net> wrote in message
news:949DC479-E52A-44BE-970F-AB64E05459AD@.microsoft.com...[vbcol=seagreen]
> Also, this post says how to restore a database - but not how to back it up.
> How do I back it up?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>
> "David Thielen" wrote:
sqlsql
Thursday, March 8, 2012
Copy data from tables with script!
Hello!
I cant figure out how to copy data from tables in my default database to my new automate-generated database.
I can generate Store Procedures, I can generate the Tables but are there anyone one who knows how to copy the data from tables and put it into my new automate-generated shell. The automate-generate funktion i have built just creating a shell/empty database. How to fill it with a sql-script or similar?
Anyone have any tip?
Regards
If you mean you want to script out the data into the same script with the DDL for the tables try the Database Publishing Wizard:
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=29B4FFD8-AC3A-4481-B352-9B185619A901
|||
It worked perfectly!
Thanks!
Subscribe to:
Posts (Atom)