I have a local copy of SQL Server 2000 installed with full admin
rights. I am working with a database on a remote server and I only have
permissions to that DB. I cannot setup
backup/synchro/replication/push/pull/etc due to not having the proper
permissions.
I have been using DTS to transfer the data to my local DB. But, is there
a good way to automate this process? Basically I just want to copy the
data locally everyday as a backup in case the remote server dies. Would
it be possible to create local job that transfers the data or am I stuck
doing this manually?
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot comSchedule your DTS package to run every day.
I believe you just right-click on the package to scedule it.|||Thanks. I will settle with doing that. I was also considering a 3rd
party tool if this does not do the job.
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 4/24/2005 3:13 PM, Greg Gilman wrote:
> Schedule your DTS package to run every day.
> I believe you just right-click on the package to scedule it.
>|||So far it is working fine. But, I remember the reason I did not use the
schedule was because sometimes tables would be added and skipped by the
DTS since I am not copying all objects. I find that copying tables is
more reliable then objects/data. If only DTS could also add any new
table created. But, to make a long story short, this not seems to be the
best option considering it is the only option I have to transfer data.
Thanks
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 4/24/2005 3:13 PM, Greg Gilman wrote:
> Schedule your DTS package to run every day.
> I believe you just right-click on the package to scedule it.
>|||Well I have to copy objects anyway, just realized copying table data
fails when the table object already exists in the source database...
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 4/24/2005 3:13 PM, Greg Gilman wrote:
> Schedule your DTS package to run every day.
> I believe you just right-click on the package to scedule it.
>|||You may consider using DB Ghost (http://www.dbghost.com) which can be
scheduled and as it does a comparison - it will only get the data that it
needs where as DTS will have to get all the data. In fact I syncrhonize my
local database with the production system every day using this script:
http://www.innovartis.co.uk/downloa...J
ob.zip
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Steve Lewis - Website Nation" wrote:
> Thanks. I will settle with doing that. I was also considering a 3rd
> party tool if this does not do the job.
> ____________________________
> Steven K. Lewis
> Website Nation, LLC
> Website Development Services
> www dot Website Nation dot com
>
> On 4/24/2005 3:13 PM, Greg Gilman wrote:
>|||Thanks for the script Mark. I will also checkout your software. So far
the DTS object/data transfer is working fine.
____________________________
Steven K. Lewis
Website Nation, LLC
Website Development Services
www dot Website Nation dot com
On 4/25/2005 3:58 AM, mark baekdal wrote:
> You may consider using DB Ghost (http://www.dbghost.com) which can be
> scheduled and as it does a comparison - it will only get the data that it
> needs where as DTS will have to get all the data. In fact I syncrhonize my
> local database with the production system every day using this script:
> http://www.innovartis.co.uk/downloa...br />
rJob.zip
>
> regards,
> Mark Baekdal
> http://www.dbghost.com
> http://www.innovartis.co.uk
> +44 (0)208 241 1762
> Build, Comparison and Synchronization from Source Control = Database chang
e
> management for SQL Server
>
> "Steve Lewis - Website Nation" wrote:
>
Showing posts with label copy. Show all posts
Showing posts with label copy. Show all posts
Friday, February 24, 2012
Sunday, February 19, 2012
Best way to send email from a C# stored procedure?
I thought I could just copy over some asp.net code like:
System.Web.Mail.MailMessage mailMessage =new System.Web.Mail.MailMessage();
But VS2005 doesn't seem to want me touching System.Web.Mail.
Any ideas?
Thanks,
Allen
OK, I figured out that I need to useSystem.Net.Mail, but here is the next problem- when I create an instance of the SmtpClient object like this:
SmtpClient
client =newSmtpClient("localhost",25);I get this exception:System.Security.SecurityException: Requestfor the permissionof type'System.Net.Mail.SmtpPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.System.Security.SecurityException: at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.CodeAccessPermission.Demand() at System.Net.Mail.SmtpClient.Initialize() at System.Net.Mail.SmtpClient..ctor(String host, Int32 port)|||
has anyone managed to solve this one?
am getting the same error...
|||I was able to get around that error (and a few others) by doing the following:
ALTER DATABASE [PUBS]SET TRUSTWORTHYONGOALTER ASSEMBLY [TaskScheduler]WITH PERMISSION_SET = UNSAFE
I hope this helps.
-Allen Cryer
|||I was able to get around that error (and a few others) by doing the following:
ALTER DATABASE [PUBS]SET TRUSTWORTHYONGOALTER ASSEMBLY [TaskScheduler]WITH PERMISSION_SET = UNSAFE
I hope this helps.
-Allen Cryer
|||mate,you're a champ.I've looked for the last 24 hours all over.Thank you very much. Works like a charm now.Thursday, February 16, 2012
Best way to make a copy of an existing sql server database to another (physical) server
Hello!
What is the best way to make a copy of an existing sql server database to another (physical) server?
Plan to make a full backup of another sql server database to another server. I've read about detach and attach
and copying the datafiles and log files but some say it is prone to data loss? Is this true?
And another thing, what if the existing sql server can't affor downtime for me to copy db files etc.?
Anybody here who have done the same?
Any help is very much appreciated.
Thanks!
(a) if u don't have down time then the best method is Backup/restore...
(b) If you have down time both Detach/attach and backup/restore can be used. ..
(c) You can also use COPY Database Wizards
(d) You have to copy all the server level components to the new server. by copying use database Jobs/Packages/operator etc are not copied
(e) If you have to transfer the login also refer http://support.microsoft.com/kb/246133
I would always prefer Backup/restore method
Madhu
|||Thank you very much!
Tuesday, February 14, 2012
best way to keep updated database copy on same server
Hi,
I was hoping to get some advise on my SQL 2000 server: I would like an
automatically updated copy of the main database kept on the same server. Any
changes made to this copy would NOT be synchronized to the main database,
however, changes made to the main database need to be immediately synched to
the copy. This would give me an up-to-date copy that can be worked with and
manipulated in ways that would be inappropriate for the actual database. Any
thoughts on the easiest solution would be greately appreciated.
thx,
AF
AF,
transactional replication and log shipping are often used for reporting
databases. However you say you want to actually manipulate the data - if
this means data changes are required, then the above 2 methods will not work
(log shipping forces the database into read-only mode and tr will probably
have synchronization errors) so a different solution is required. In this
case I'd use either snapshot replication or ship the backups and restore -
obviously both methods will have a sizeable delay in data transfer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I checked in to transactional replicaiton, which looks promising. If I could
have the main database update/overwrite the copy every 30 minutes or so, that
should work. Since I am not worried about replicating to a different
physical server, do you know if there is a simpler method for such updates?
I appreciate the help,
AF
"Paul Ibison" wrote:
> AF,
> transactional replication and log shipping are often used for reporting
> databases. However you say you want to actually manipulate the data - if
> this means data changes are required, then the above 2 methods will not work
> (log shipping forces the database into read-only mode and tr will probably
> have synchronization errors) so a different solution is required. In this
> case I'd use either snapshot replication or ship the backups and restore -
> obviously both methods will have a sizeable delay in data transfer.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||I have concerns about using transactional replication for your purposes. You
mention that there will be manipulation of the data on the subscriber. If by
this you mean data changes, this'll likely cause errors and a failure of the
distribution agent. If you're just doing reads then it'll be ok.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I was hoping to get some advise on my SQL 2000 server: I would like an
automatically updated copy of the main database kept on the same server. Any
changes made to this copy would NOT be synchronized to the main database,
however, changes made to the main database need to be immediately synched to
the copy. This would give me an up-to-date copy that can be worked with and
manipulated in ways that would be inappropriate for the actual database. Any
thoughts on the easiest solution would be greately appreciated.
thx,
AF
AF,
transactional replication and log shipping are often used for reporting
databases. However you say you want to actually manipulate the data - if
this means data changes are required, then the above 2 methods will not work
(log shipping forces the database into read-only mode and tr will probably
have synchronization errors) so a different solution is required. In this
case I'd use either snapshot replication or ship the backups and restore -
obviously both methods will have a sizeable delay in data transfer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I checked in to transactional replicaiton, which looks promising. If I could
have the main database update/overwrite the copy every 30 minutes or so, that
should work. Since I am not worried about replicating to a different
physical server, do you know if there is a simpler method for such updates?
I appreciate the help,
AF
"Paul Ibison" wrote:
> AF,
> transactional replication and log shipping are often used for reporting
> databases. However you say you want to actually manipulate the data - if
> this means data changes are required, then the above 2 methods will not work
> (log shipping forces the database into read-only mode and tr will probably
> have synchronization errors) so a different solution is required. In this
> case I'd use either snapshot replication or ship the backups and restore -
> obviously both methods will have a sizeable delay in data transfer.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||I have concerns about using transactional replication for your purposes. You
mention that there will be manipulation of the data on the subscriber. If by
this you mean data changes, this'll likely cause errors and a failure of the
distribution agent. If you're just doing reads then it'll be ok.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Sunday, February 12, 2012
Best way to generate script
Hi!
I have to make a program that copy a database structure. Right now i'm using SMO but it take a lot of time just to copy all tables. Here is what i'm doing:
ServeurLocal.SetDefaultInitFields(GetType(Table), "IsSystemObject")
For Each uneTable In BDConfig.Tables
If Not uneTable.IsSystemObject Then
NouvelleBD.ExecuteNonQuery(uneTable.Script)
End If
Next
We have over 700 tables and it take me more than 5 min to copy all the tables and i haven't copy all the view and stored proc yet. The problem seems that i execute one by one each script. So i was wondering is there a way to stock all the script in some kind of object and then when the FOR is done execute this big script. Is it possible to create an object script which we can add all the script for each table ?
Thank and sorry about my bad English ^_^
I found how do to it. I have to use an object Transfer. Then i generate a script for all my object and execute this script.|||I know you've found another way to address the problem, but here's a bit of code that can create the script you were actually attempting to create, and will put the tables first, the views second and the stored procedures last in the script:
Dim scrDBScript As Scripter
Dim objSMOObjects(1) As SqlSmoObject
Dim intObjCount As Integer
intObjCount = 0
Dim srv As Server
srv = New Server("MyServer")
'Define a Database object variable by supplying the server and the database name arguments in the constructor.
Dim db As Database
Dim tbColl As TableCollection
Dim tb As Table
Dim vwColl As ViewCollection
Dim vw As View
Dim spColl As StoredProcedureCollection
Dim sp As StoredProcedure
db = New Database(srv, "AdventureWorks")
tbColl = db.Tables
For Each tb In tbColl
objSMOObjects(intObjCount) = tb 'Script each table
intObjCount += 1
Next
vwColl = db.Views
For Each vw In vwColl
objSMOObjects(intObjCount) = vw 'Script each view
intObjCount += 1
Next
spColl = db.StoredProcedures
For Each sp In spColl
objSMOObjects(intObjCount) = sp 'Script each stored procedure
intObjCount += 1
Next
scrDBScript = New Scripter(srv)
scrDBScript.Options.FileName = "c:\DBScript.sql"
scrDBScript.Options.IncludeHeaders = True
scrDBScript.Options.AppendToFile = True
ReDim Preserve objSMOObjects(intObjCount - 1)
scrDBScript.Script(objSMOObjects)
This may be something you would want to try another time, but shouldn't perform as badly as you're currently experiencing.
|||Thanks for your answer. Actually i have some problem trying to copy my DB. I have tried using a Tranfer Object and also like your method. My problem is that we have a lot of views where the query is using others views. I have the same problem with stored proc that call other stored proc. The problem is when i'm trying to execute the script for example for the views. I've got error because some views can't be created because some views are missing. In fact they are not missing they are just not created yet.
Maybe i'm not using the best way to do what i need to do. I have web application and in some page the user can create a copy of the DB that he is using. So all i want i to copy all the DB in a new one. Maybe i shouldn't use SMO to do that.....i don't really know what is the best way to do it.
|||I just played with some code and got a successful copy of AdventureWorks using the Transfer object. Here's my demo code - see if it accomplishes what you're trying to do.
Dim srv As Server
srv = New Server("TestServer")
'Define a Database object variable by supplying the server and the database name arguments in the constructor.
Dim db As Database
db = srv.Databases("AdventureWorks")
Dim strDBName As String
strDBName = "TestDatabase"
Dim dbCopy As Database
Dim dbFG As FileGroup
Dim dbFile As DataFile
Dim dbLogFile As LogFile
dbCopy = New Database(srv, strDBName) 'Instantiate the new database
dbFG = New FileGroup(dbCopy, "PRIMARY") 'Instantiate the PRIMARY filegroup
dbCopy.FileGroups.Add(dbFG) 'Add the FileGroup
dbFile = New DataFile(dbFG, strDBName + "_Data") 'Instantiate the data file within the filegroup
dbFG.Files.Add(dbFile) 'Add the File
dbFile.FileName = "D:\MSSQL.1\MSSQL\Data\" + strDBName + "_Data.mdf" 'Define the actual file system name
dbFile.Size = 25.0 * 1024.0 'Define the size of the file
dbFile.GrowthType = FileGrowthType.Percent 'Define the Growth Type
dbFile.Growth = 25.0 'Define the Growth Percent
dbFile.MaxSize = 100.0 * 1024.0 'Define the Max database size
dbLogFile = New LogFile(dbCopy, strDBName + "_Log") 'Instantiate the log file (no filegroup for log files)
dbCopy.LogFiles.Add(dbLogFile) 'Add the log file
dbLogFile.FileName = "D:\MSSQL.1\MSSQL\Data\" + strDBName + "_Log.ldf" 'Define the log file system name
dbLogFile.Size = 10.0 * 1024.0 'Define the size of the log file
dbLogFile.GrowthType = FileGrowthType.Percent 'Define the Growth Type
dbLogFile.Growth = 25.0 'Define the Growth Percent
dbCopy.Create() 'Create the database
'Define a Transfer object and set the required options and properties.
Dim xfr As Transfer
xfr = New Transfer(db)
xfr.CopyAllObjects = True
xfr.CopyAllUsers = True
xfr.Options.WithDependencies = True
xfr.Options.ContinueScriptingOnError = True
xfr.DestinationDatabase = strDBName
xfr.DestinationServer = srv.Name
xfr.DestinationLoginSecure = True
xfr.CopySchema = True
'Script the transfer. Alternatively perform immediate data transfer with TransferData method.
xfr.TransferData()
I've found another way to make my copy and it's much faster than using the Transfer object. Using the Transfert Object it took me 30sec to copy all the tables (715 tables). Now it took me 15sec to copy all the BD(715 tables + 315 view, 236 stored proc, 32 function). Insteand of doing a copy i've created a Backup. Using the backup Object i save the backup on my drive. Then i create a the New DB and then i restore the backup on this new DB. It's a lot faster and i have no error when i'm using this method.|||
Backup/restore is the most trouble-free way to get a copy of an existing database because nothing is recompiled or verified by SQL Server.
For example, if a view or stored procedure references a column that no longer exists, you cannot generate and execute a script because it will fail. The object(s) in question is invalid in the source database and will continue to be invalid in the new database. This method provides an exact copy of what you have.
I don't have experience with the scripting that you are referring to but it would seem to me that if the scripting method supports dependencies, then your dependency information is missing or corrupt in the database. For example, if MyProc1 executes MyProc2, SQL Server will only have dependency information IF MyProc2 was created first followed by MyProc1. If that is true, when you generate a SQL Script (at least through Enterprise Manager's Generate SQL Script wizard, dependency information will be used to properly sequence the objects in the script.
Best way to generate script
Hi!
I have to make a program that copy a database structure. Right now i'm using SMO but it take a lot of time just to copy all tables. Here is what i'm doing:
ServeurLocal.SetDefaultInitFields(GetType(Table), "IsSystemObject")
For Each uneTable In BDConfig.Tables
If Not uneTable.IsSystemObject Then
NouvelleBD.ExecuteNonQuery(uneTable.Script)
End If
Next
We have over 700 tables and it take me more than 5 min to copy all the tables and i haven't copy all the view and stored proc yet. The problem seems that i execute one by one each script. So i was wondering is there a way to stock all the script in some kind of object and then when the FOR is done execute this big script. Is it possible to create an object script which we can add all the script for each table ?
Thank and sorry about my bad English ^_^
I found how do to it. I have to use an object Transfer. Then i generate a script for all my object and execute this script.|||I know you've found another way to address the problem, but here's a bit of code that can create the script you were actually attempting to create, and will put the tables first, the views second and the stored procedures last in the script:
Dim scrDBScript As Scripter
Dim objSMOObjects(1) As SqlSmoObject
Dim intObjCount As Integer
intObjCount = 0
Dim srv As Server
srv = New Server("MyServer")
'Define a Database object variable by supplying the server and the database name arguments in the constructor.
Dim db As Database
Dim tbColl As TableCollection
Dim tb As Table
Dim vwColl As ViewCollection
Dim vw As View
Dim spColl As StoredProcedureCollection
Dim sp As StoredProcedure
db = New Database(srv, "AdventureWorks")
tbColl = db.Tables
For Each tb In tbColl
objSMOObjects(intObjCount) = tb 'Script each table
intObjCount += 1
Next
vwColl = db.Views
For Each vw In vwColl
objSMOObjects(intObjCount) = vw 'Script each view
intObjCount += 1
Next
spColl = db.StoredProcedures
For Each sp In spColl
objSMOObjects(intObjCount) = sp 'Script each stored procedure
intObjCount += 1
Next
scrDBScript = New Scripter(srv)
scrDBScript.Options.FileName = "c:\DBScript.sql"
scrDBScript.Options.IncludeHeaders = True
scrDBScript.Options.AppendToFile = True
ReDim Preserve objSMOObjects(intObjCount - 1)
scrDBScript.Script(objSMOObjects)
This may be something you would want to try another time, but shouldn't perform as badly as you're currently experiencing.
|||Thanks for your answer. Actually i have some problem trying to copy my DB. I have tried using a Tranfer Object and also like your method. My problem is that we have a lot of views where the query is using others views. I have the same problem with stored proc that call other stored proc. The problem is when i'm trying to execute the script for example for the views. I've got error because some views can't be created because some views are missing. In fact they are not missing they are just not created yet.
Maybe i'm not using the best way to do what i need to do. I have web application and in some page the user can create a copy of the DB that he is using. So all i want i to copy all the DB in a new one. Maybe i shouldn't use SMO to do that.....i don't really know what is the best way to do it.
|||I just played with some code and got a successful copy of AdventureWorks using the Transfer object. Here's my demo code - see if it accomplishes what you're trying to do.
Dim srv As Server
srv = New Server("TestServer")
'Define a Database object variable by supplying the server and the database name arguments in the constructor.
Dim db As Database
db = srv.Databases("AdventureWorks")
Dim strDBName As String
strDBName = "TestDatabase"
Dim dbCopy As Database
Dim dbFG As FileGroup
Dim dbFile As DataFile
Dim dbLogFile As LogFile
dbCopy = New Database(srv, strDBName) 'Instantiate the new database
dbFG = New FileGroup(dbCopy, "PRIMARY") 'Instantiate the PRIMARY filegroup
dbCopy.FileGroups.Add(dbFG) 'Add the FileGroup
dbFile = New DataFile(dbFG, strDBName + "_Data") 'Instantiate the data file within the filegroup
dbFG.Files.Add(dbFile) 'Add the File
dbFile.FileName = "D:\MSSQL.1\MSSQL\Data\" + strDBName + "_Data.mdf" 'Define the actual file system name
dbFile.Size = 25.0 * 1024.0 'Define the size of the file
dbFile.GrowthType = FileGrowthType.Percent 'Define the Growth Type
dbFile.Growth = 25.0 'Define the Growth Percent
dbFile.MaxSize = 100.0 * 1024.0 'Define the Max database size
dbLogFile = New LogFile(dbCopy, strDBName + "_Log") 'Instantiate the log file (no filegroup for log files)
dbCopy.LogFiles.Add(dbLogFile) 'Add the log file
dbLogFile.FileName = "D:\MSSQL.1\MSSQL\Data\" + strDBName + "_Log.ldf" 'Define the log file system name
dbLogFile.Size = 10.0 * 1024.0 'Define the size of the log file
dbLogFile.GrowthType = FileGrowthType.Percent 'Define the Growth Type
dbLogFile.Growth = 25.0 'Define the Growth Percent
dbCopy.Create() 'Create the database
'Define a Transfer object and set the required options and properties.
Dim xfr As Transfer
xfr = New Transfer(db)
xfr.CopyAllObjects = True
xfr.CopyAllUsers = True
xfr.Options.WithDependencies = True
xfr.Options.ContinueScriptingOnError = True
xfr.DestinationDatabase = strDBName
xfr.DestinationServer = srv.Name
xfr.DestinationLoginSecure = True
xfr.CopySchema = True
'Script the transfer. Alternatively perform immediate data transfer with TransferData method.
xfr.TransferData()
I've found another way to make my copy and it's much faster than using the Transfer object. Using the Transfert Object it took me 30sec to copy all the tables (715 tables). Now it took me 15sec to copy all the BD(715 tables + 315 view, 236 stored proc, 32 function). Insteand of doing a copy i've created a Backup. Using the backup Object i save the backup on my drive. Then i create a the New DB and then i restore the backup on this new DB. It's a lot faster and i have no error when i'm using this method.|||
Backup/restore is the most trouble-free way to get a copy of an existing database because nothing is recompiled or verified by SQL Server.
For example, if a view or stored procedure references a column that no longer exists, you cannot generate and execute a script because it will fail. The object(s) in question is invalid in the source database and will continue to be invalid in the new database. This method provides an exact copy of what you have.
I don't have experience with the scripting that you are referring to but it would seem to me that if the scripting method supports dependencies, then your dependency information is missing or corrupt in the database. For example, if MyProc1 executes MyProc2, SQL Server will only have dependency information IF MyProc2 was created first followed by MyProc1. If that is true, when you generate a SQL Script (at least through Enterprise Manager's Generate SQL Script wizard, dependency information will be used to properly sequence the objects in the script.
Best way to copy large number of databases to new server?
We're in the process of migrating to a new database server, and have a large
number of databases to move across (approx 100). I found the 'copy database
wizard' which seemed to be exactly what I needed, only it didn't work
because the servers aren't on the same domain (or even the same network).
Is there any similar solution, or am I going to have to use backup/restore
on every single database individually?
Thanks in advance!
ChrisIf they are on the same network and you can´t reach them fromthe one SQL
Server, you should make a backup. You can make a hot backup or depending on
uptime of your database and the size of the databasefiles do a service
shutdown, copy the files and restart the service. This "clone" can be
attached to the other server.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> schrieb im Newsbeitrag
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>|||Thats odd, I have done the same thing and it works on mine ok.
Couple of other solutions for you
Detach the database and copy the data and log file over (you will need to be
careful on Server permissions for the files for this)
Create the structure and copy the data over using DTS.
Have fun
Peter
"Chris Ashley" wrote:
> We're in the process of migrating to a new database server, and have a large
> number of databases to move across (approx 100). I found the 'copy database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
>|||Hi,
Easy method to copy all the databases (including system DB) is :-
1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
Tape.
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as existing server)
3. Stop the SQL server
4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
same folders (Same as existing active server).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
Thanks
Hari
SQL Server MVP
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
large
> number of databases to move across (approx 100). I found the 'copy
database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>|||In article <OsLpXcaUFHA.3176@.TK2MSFTNGP12.phx.gbl>,
hari_prasad_k@.hotmail.com says...
> Hi,
> Easy method to copy all the databases (including system DB) is :-
> 1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
> Tape.
> 2. Install SQL server and same Service packs (as old) in the identical
> folder (Same as existing server)
> 3. Stop the SQL server
> 4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
> same folders (Same as existing active server).
> 5. Start SQL server
>
> Now login to query analyzer or enterprise manager and confirm all the
> databases are online.
Don't forget about account maintenance - you will have to check all the
logon/permissions - which can be done in a script.
--
--
spam999free@.rrohio.com
remove 999 in order to email me|||Can't you simply write a T-SQL query that lists all databases and backs each
one up, so that on the other end you can restore it in a similar fashion?
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
number of databases to move across (approx 100). I found the 'copy database
wizard' which seemed to be exactly what I needed, only it didn't work
because the servers aren't on the same domain (or even the same network).
Is there any similar solution, or am I going to have to use backup/restore
on every single database individually?
Thanks in advance!
ChrisIf they are on the same network and you can´t reach them fromthe one SQL
Server, you should make a backup. You can make a hot backup or depending on
uptime of your database and the size of the databasefiles do a service
shutdown, copy the files and restart the service. This "clone" can be
attached to the other server.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> schrieb im Newsbeitrag
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>|||Thats odd, I have done the same thing and it works on mine ok.
Couple of other solutions for you
Detach the database and copy the data and log file over (you will need to be
careful on Server permissions for the files for this)
Create the structure and copy the data over using DTS.
Have fun
Peter
"Chris Ashley" wrote:
> We're in the process of migrating to a new database server, and have a large
> number of databases to move across (approx 100). I found the 'copy database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
>|||Hi,
Easy method to copy all the databases (including system DB) is :-
1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
Tape.
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as existing server)
3. Stop the SQL server
4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
same folders (Same as existing active server).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
Thanks
Hari
SQL Server MVP
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
large
> number of databases to move across (approx 100). I found the 'copy
database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>|||In article <OsLpXcaUFHA.3176@.TK2MSFTNGP12.phx.gbl>,
hari_prasad_k@.hotmail.com says...
> Hi,
> Easy method to copy all the databases (including system DB) is :-
> 1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
> Tape.
> 2. Install SQL server and same Service packs (as old) in the identical
> folder (Same as existing server)
> 3. Stop the SQL server
> 4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
> same folders (Same as existing active server).
> 5. Start SQL server
>
> Now login to query analyzer or enterprise manager and confirm all the
> databases are online.
Don't forget about account maintenance - you will have to check all the
logon/permissions - which can be done in a script.
--
--
spam999free@.rrohio.com
remove 999 in order to email me|||Can't you simply write a T-SQL query that lists all databases and backs each
one up, so that on the other end you can restore it in a similar fashion?
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
Best way to copy large number of databases to new server?
We're in the process of migrating to a new database server, and have a large
number of databases to move across (approx 100). I found the 'copy database
wizard' which seemed to be exactly what I needed, only it didn't work
because the servers aren't on the same domain (or even the same network).
Is there any similar solution, or am I going to have to use backup/restore
on every single database individually?
Thanks in advance!
Chris
If they are on the same network and you cant reach them fromthe one SQL
Server, you should make a backup. You can make a hot backup or depending on
uptime of your database and the size of the databasefiles do a service
shutdown, copy the files and restart the service. This "clone" can be
attached to the other server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> schrieb im Newsbeitrag
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
|||Thats odd, I have done the same thing and it works on mine ok.
Couple of other solutions for you
Detach the database and copy the data and log file over (you will need to be
careful on Server permissions for the files for this)
Create the structure and copy the data over using DTS.
Have fun
Peter
"Chris Ashley" wrote:
> We're in the process of migrating to a new database server, and have a large
> number of databases to move across (approx 100). I found the 'copy database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
>
|||Hi,
Easy method to copy all the databases (including system DB) is :-
1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
Tape.
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as existing server)
3. Stop the SQL server
4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
same folders (Same as existing active server).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
Thanks
Hari
SQL Server MVP
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
large
> number of databases to move across (approx 100). I found the 'copy
database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
|||In article <OsLpXcaUFHA.3176@.TK2MSFTNGP12.phx.gbl>,
hari_prasad_k@.hotmail.com says...
> Hi,
> Easy method to copy all the databases (including system DB) is :-
> 1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
> Tape.
> 2. Install SQL server and same Service packs (as old) in the identical
> folder (Same as existing server)
> 3. Stop the SQL server
> 4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
> same folders (Same as existing active server).
> 5. Start SQL server
>
> Now login to query analyzer or enterprise manager and confirm all the
> databases are online.
Don't forget about account maintenance - you will have to check all the
logon/permissions - which can be done in a script.
--
spam999free@.rrohio.com
remove 999 in order to email me
|||Can't you simply write a T-SQL query that lists all databases and backs each
one up, so that on the other end you can restore it in a similar fashion?
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
number of databases to move across (approx 100). I found the 'copy database
wizard' which seemed to be exactly what I needed, only it didn't work
because the servers aren't on the same domain (or even the same network).
Is there any similar solution, or am I going to have to use backup/restore
on every single database individually?
Thanks in advance!
Chris
If they are on the same network and you cant reach them fromthe one SQL
Server, you should make a backup. You can make a hot backup or depending on
uptime of your database and the size of the databasefiles do a service
shutdown, copy the files and restart the service. This "clone" can be
attached to the other server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> schrieb im Newsbeitrag
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
|||Thats odd, I have done the same thing and it works on mine ok.
Couple of other solutions for you
Detach the database and copy the data and log file over (you will need to be
careful on Server permissions for the files for this)
Create the structure and copy the data over using DTS.
Have fun
Peter
"Chris Ashley" wrote:
> We're in the process of migrating to a new database server, and have a large
> number of databases to move across (approx 100). I found the 'copy database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
>
|||Hi,
Easy method to copy all the databases (including system DB) is :-
1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
Tape.
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as existing server)
3. Stop the SQL server
4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
same folders (Same as existing active server).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
Thanks
Hari
SQL Server MVP
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
large
> number of databases to move across (approx 100). I found the 'copy
database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
|||In article <OsLpXcaUFHA.3176@.TK2MSFTNGP12.phx.gbl>,
hari_prasad_k@.hotmail.com says...
> Hi,
> Easy method to copy all the databases (including system DB) is :-
> 1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
> Tape.
> 2. Install SQL server and same Service packs (as old) in the identical
> folder (Same as existing server)
> 3. Stop the SQL server
> 4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
> same folders (Same as existing active server).
> 5. Start SQL server
>
> Now login to query analyzer or enterprise manager and confirm all the
> databases are online.
Don't forget about account maintenance - you will have to check all the
logon/permissions - which can be done in a script.
--
spam999free@.rrohio.com
remove 999 in order to email me
|||Can't you simply write a T-SQL query that lists all databases and backs each
one up, so that on the other end you can restore it in a similar fashion?
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
Best way to copy large number of databases to new server?
We're in the process of migrating to a new database server, and have a large
number of databases to move across (approx 100). I found the 'copy database
wizard' which seemed to be exactly what I needed, only it didn't work
because the servers aren't on the same domain (or even the same network).
Is there any similar solution, or am I going to have to use backup/restore
on every single database individually?
Thanks in advance!
ChrisIf they are on the same network and you cant reach them fromthe one SQL
Server, you should make a backup. You can make a hot backup or depending on
uptime of your database and the size of the databasefiles do a service
shutdown, copy the files and restart the service. This "clone" can be
attached to the other server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> schrieb im Newsbeitrag
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>|||Thats odd, I have done the same thing and it works on mine ok.
Couple of other solutions for you
Detach the database and copy the data and log file over (you will need to be
careful on Server permissions for the files for this)
Create the structure and copy the data over using DTS.
Have fun
Peter
"Chris Ashley" wrote:
> We're in the process of migrating to a new database server, and have a lar
ge
> number of databases to move across (approx 100). I found the 'copy databas
e
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
>|||Hi,
Easy method to copy all the databases (including system DB) is :-
1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
Tape.
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as existing server)
3. Stop the SQL server
4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
same folders (Same as existing active server).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
Thanks
Hari
SQL Server MVP
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
large
> number of databases to move across (approx 100). I found the 'copy
database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>|||In article <OsLpXcaUFHA.3176@.TK2MSFTNGP12.phx.gbl>,
hari_prasad_k@.hotmail.com says...
> Hi,
> Easy method to copy all the databases (including system DB) is :-
> 1. Stop active SQL Server service and copy all the MDF , NDF and LDF into
a
> Tape.
> 2. Install SQL server and same Service packs (as old) in the identical
> folder (Same as existing server)
> 3. Stop the SQL server
> 4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
> same folders (Same as existing active server).
> 5. Start SQL server
>
> Now login to query analyzer or enterprise manager and confirm all the
> databases are online.
Don't forget about account maintenance - you will have to check all the
logon/permissions - which can be done in a script.
--
spam999free@.rrohio.com
remove 999 in order to email me|||Can't you simply write a T-SQL query that lists all databases and backs each
one up, so that on the other end you can restore it in a similar fashion?
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
number of databases to move across (approx 100). I found the 'copy database
wizard' which seemed to be exactly what I needed, only it didn't work
because the servers aren't on the same domain (or even the same network).
Is there any similar solution, or am I going to have to use backup/restore
on every single database individually?
Thanks in advance!
ChrisIf they are on the same network and you cant reach them fromthe one SQL
Server, you should make a backup. You can make a hot backup or depending on
uptime of your database and the size of the databasefiles do a service
shutdown, copy the files and restart the service. This "clone" can be
attached to the other server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> schrieb im Newsbeitrag
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>|||Thats odd, I have done the same thing and it works on mine ok.
Couple of other solutions for you
Detach the database and copy the data and log file over (you will need to be
careful on Server permissions for the files for this)
Create the structure and copy the data over using DTS.
Have fun
Peter
"Chris Ashley" wrote:
> We're in the process of migrating to a new database server, and have a lar
ge
> number of databases to move across (approx 100). I found the 'copy databas
e
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
>|||Hi,
Easy method to copy all the databases (including system DB) is :-
1. Stop active SQL Server service and copy all the MDF , NDF and LDF into a
Tape.
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as existing server)
3. Stop the SQL server
4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
same folders (Same as existing active server).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
Thanks
Hari
SQL Server MVP
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
large
> number of databases to move across (approx 100). I found the 'copy
database
> wizard' which seemed to be exactly what I needed, only it didn't work
> because the servers aren't on the same domain (or even the same network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>|||In article <OsLpXcaUFHA.3176@.TK2MSFTNGP12.phx.gbl>,
hari_prasad_k@.hotmail.com says...
> Hi,
> Easy method to copy all the databases (including system DB) is :-
> 1. Stop active SQL Server service and copy all the MDF , NDF and LDF into
a
> Tape.
> 2. Install SQL server and same Service packs (as old) in the identical
> folder (Same as existing server)
> 3. Stop the SQL server
> 4. Copy the .MDF , NDF and .LDF files (took in step 1 ) from Tape to the
> same folders (Same as existing active server).
> 5. Start SQL server
>
> Now login to query analyzer or enterprise manager and confirm all the
> databases are online.
Don't forget about account maintenance - you will have to check all the
logon/permissions - which can be done in a script.
--
spam999free@.rrohio.com
remove 999 in order to email me|||Can't you simply write a T-SQL query that lists all databases and backs each
one up, so that on the other end you can restore it in a similar fashion?
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:427a3eb6.0@.entanet...
> We're in the process of migrating to a new database server, and have a
> large number of databases to move across (approx 100). I found the 'copy
> database wizard' which seemed to be exactly what I needed, only it didn't
> work because the servers aren't on the same domain (or even the same
> network).
> Is there any similar solution, or am I going to have to use backup/restore
> on every single database individually?
> Thanks in advance!
> Chris
>
best way to copy database
I need to move a database from sql 2000 to sql 2005. My research suggests that I can backup a sql 2000 db from enterprise manager and restore it with sql 2005 management studio. Are there step by step directions for this or can someone tell me how to do this?
what about logins? Are they transfered in this process. My research also suggests the I use copy database wizard by this failed on me and I can't find anything in the event log or anywhere else to tell me why. Please help. ThanksIMO, I would probably suggest backup and restore tactic for user databases. As for logins, either you dump the data into a file by doing a BCP out (bulk copy program) on master..syslogins table of the old server and BCP IN to the same table of the new server, however, since I've no experience on SQL2005, please check the structure of master..syslogins table if they're similar on both versions. As for the sysusers of user databases, you can make sp_adduser statements by doing a SELECT 'sp_adduser ' + NAME + ', ' + NAME + ' FROM MASTER..SYSLOGINS
As for syntax reference, try BOL. :)
HTH|||The BCP of syslogins trick quit working in SQL server 7.0. SQL 2005 has an all new system table structure, so I greatly doubt it would work here. For the logins, look up the CREATE LOGIN command. You will want to pay especial attention to the SID parameter. If you keep the SID for each user the same for SQL 2005 as it was on SQL 2000, they will match up with no problems. If you do not supply a SID for the CREATE USER command, a new random SID will be generated, and you will have to drop and recreate the user in the database (or recreate the login with the right SID).
Any jobs/maintenance plans that will have to come over?|||I got it over with backup and restore and all the logins are there. No job or maintenance plans need to be restored. Thanks
what about logins? Are they transfered in this process. My research also suggests the I use copy database wizard by this failed on me and I can't find anything in the event log or anywhere else to tell me why. Please help. ThanksIMO, I would probably suggest backup and restore tactic for user databases. As for logins, either you dump the data into a file by doing a BCP out (bulk copy program) on master..syslogins table of the old server and BCP IN to the same table of the new server, however, since I've no experience on SQL2005, please check the structure of master..syslogins table if they're similar on both versions. As for the sysusers of user databases, you can make sp_adduser statements by doing a SELECT 'sp_adduser ' + NAME + ', ' + NAME + ' FROM MASTER..SYSLOGINS
As for syntax reference, try BOL. :)
HTH|||The BCP of syslogins trick quit working in SQL server 7.0. SQL 2005 has an all new system table structure, so I greatly doubt it would work here. For the logins, look up the CREATE LOGIN command. You will want to pay especial attention to the SID parameter. If you keep the SID for each user the same for SQL 2005 as it was on SQL 2000, they will match up with no problems. If you do not supply a SID for the CREATE USER command, a new random SID will be generated, and you will have to drop and recreate the user in the database (or recreate the login with the right SID).
Any jobs/maintenance plans that will have to come over?|||I got it over with backup and restore and all the logins are there. No job or maintenance plans need to be restored. Thanks
Best way to copy database
I set up a test SQL server that I want to be exactly like the live server.
The test server is only available over the internet connected via TCP with
no network sharing or anything like that. What is the best way to copy the
data [all databases, tables & stored procedures] from my live server to the
test server? I tried using the backup wizard but it gives me an error
saying can not create share. When I use the export data function I get
errors like user account does not exists and various other errors.
Can someone suggest the best way to do this?
Bill
You should be able to use DTS.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
|||When I use the Copy database wizard to create the DTS package I get an
error, "Failed to create the share OMWWIZC." I am trying to copy from a
server over the internet using TCP/IP with SQL login only. It sounds like
DTS would require windows authentication since it create shares. Is there
another way or am I wrong?
Thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:opso2q8prxrj9kur@.hcottter-lap...
> You should be able to use DTS.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
|||don't use the copy database wizard, use export data (right click on your
database and select all tasks to find this). I believe this option does not
require a file share.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bill" <msgdev@.hotmail.com> wrote in message
news:#kJDTcrPFHA.3292@.TK2MSFTNGP12.phx.gbl...
> When I use the Copy database wizard to create the DTS package I get an
> error, "Failed to create the share OMWWIZC." I am trying to copy from a
> server over the internet using TCP/IP with SQL login only. It sounds like
> DTS would require windows authentication since it create shares. Is there
> another way or am I wrong?
> Thanks.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:opso2q8prxrj9kur@.hcottter-lap...
>
|||I managed to get most of the data over using export data but I am having a
problem with one database. It looks like there is a NT account added as a
login "<Domain>\Doman users." The error I get now is related to this user
not being on the new server. The servers are part of two different domains
so there is no way, that I know of, to add this user to the new server. I
am not the one who originally set up the server so I don't want to make any
changes to the live server if possible. Is there anyway to get around this
error?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uSRXjnrPFHA.3144@.tk2msftngp13.phx.gbl...
> don't use the copy database wizard, use export data (right click on your
> database and select all tasks to find this). I believe this option does
> not
> require a file share.
>
> --
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Bill" <msgdev@.hotmail.com> wrote in message
> news:#kJDTcrPFHA.3292@.TK2MSFTNGP12.phx.gbl...
>
The test server is only available over the internet connected via TCP with
no network sharing or anything like that. What is the best way to copy the
data [all databases, tables & stored procedures] from my live server to the
test server? I tried using the backup wizard but it gives me an error
saying can not create share. When I use the export data function I get
errors like user account does not exists and various other errors.
Can someone suggest the best way to do this?
Bill
You should be able to use DTS.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
|||When I use the Copy database wizard to create the DTS package I get an
error, "Failed to create the share OMWWIZC." I am trying to copy from a
server over the internet using TCP/IP with SQL login only. It sounds like
DTS would require windows authentication since it create shares. Is there
another way or am I wrong?
Thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:opso2q8prxrj9kur@.hcottter-lap...
> You should be able to use DTS.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
|||don't use the copy database wizard, use export data (right click on your
database and select all tasks to find this). I believe this option does not
require a file share.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Bill" <msgdev@.hotmail.com> wrote in message
news:#kJDTcrPFHA.3292@.TK2MSFTNGP12.phx.gbl...
> When I use the Copy database wizard to create the DTS package I get an
> error, "Failed to create the share OMWWIZC." I am trying to copy from a
> server over the internet using TCP/IP with SQL login only. It sounds like
> DTS would require windows authentication since it create shares. Is there
> another way or am I wrong?
> Thanks.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:opso2q8prxrj9kur@.hcottter-lap...
>
|||I managed to get most of the data over using export data but I am having a
problem with one database. It looks like there is a NT account added as a
login "<Domain>\Doman users." The error I get now is related to this user
not being on the new server. The servers are part of two different domains
so there is no way, that I know of, to add this user to the new server. I
am not the one who originally set up the server so I don't want to make any
changes to the live server if possible. Is there anyway to get around this
error?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uSRXjnrPFHA.3144@.tk2msftngp13.phx.gbl...
> don't use the copy database wizard, use export data (right click on your
> database and select all tasks to find this). I believe this option does
> not
> require a file share.
>
> --
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Bill" <msgdev@.hotmail.com> wrote in message
> news:#kJDTcrPFHA.3292@.TK2MSFTNGP12.phx.gbl...
>
Best way to copy a table
What is the best way to copy a table, with the foriegn keys, primary keys and indexes, from one database to another using SQL Server 2005?
In sql server 2000 I used the DTS, but in SQL server 2005 it does'nt bring over the foriegn keys, primary keys and indexes.
Thanks
Right click database, "Generate Script..."
|||Run an INSERT INTO with Column list it gives you control of the insert order. Run a search for INSERT INTO with Column list in SQL Server BOL (books online). Hope this helps.Best way to copy a sql db between machines?
SQL server 2005 developer version
I have about a dozen db's scattered on 3 machines and want to
consolidate all of them on my desktop with the data intact.
What's the easiest, best way to accomplish this?
Can I backup on one and restore on another?
Yes you can RESTORE from one machine to another. You cannot combine databases via RESTORE, though. You would have 12 databases on one machine.
|||No, I do not want to consolidate them , just copy them from one machine to another so the data is the same everywhere.I tried the restore and it errs out because the pc name is different
from the pc name of the db I backed up.
Seems rather limiting.
I also tried the export/import with flat file delimited but it errs out as well. I double checked the table entry details and the db's are identical. The target db is empty of data.
These are small db's but not so small I want to renter the data.
|||
Check out one of the RESTORE command options - [ WITH MOVE ...]
Refer to Books Online, Topic: RESTORE
|||I got the restore to finally work by creating the dir that it wanted.My db's were in
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
and sql server insisted upon :
C:\Program Files\Microsoft SQL Server\MSSQL\Data
Once I created that dir the restore worked just fine.
No where I could see to change the path for the restore either.
Pretty stupid if you ask me but that's what wizards are for.
Thanks for trying guys
|||A few days ago someone pinged me to do the same, this is what I sent him over:
-If you restore the files using the RESTORE DATABASE command, use with WITH MOVE options to place the files somewhere else on your computer. For more information about the WITH MOVE option, look in the BOL of SQL Server.
-Prefering the GUI to restore the files ? Then point to the backup device and navigate to the Options page within the restore dialog. Change the settings of Restore database files as (which actually means database files and logfiles) to point to the new directory. (It will contain by default the directories which were used during the backup process, by changing the directories within the dialog, it will create the script behind the scenes which uses the WITH MOVE option)
Jens k. Suessmeyer.
http://www.sqlserver005.de
Best way to copy a replicated database?
I need to copy an installation to another server without breaking merge
replication on the existing database. Is the best approach to simply script
out the database, do an export of the data, then recreate on the other end
and set up a new publication?
Earl,
if it is the publisher or the subscriber, as long as the machine name is
different in practice you'll need to reinitialize. You caould do a nsync
one, provided you could ensure the data is synchronized. This can be
achieved by stopping all activity during the process, or by using
datacompare after the restore. After that, you'll need to recreate the
publication (if publisher) and initialize.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul. These are PocketPCs, so it sounds like the goal should be to
gather up one last round of data, then create a new database, load the data,
then create a new publication and initialize?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:untCSjg2FHA.2492@.TK2MSFTNGP09.phx.gbl...
> Earl,
> if it is the publisher or the subscriber, as long as the machine name is
> different in practice you'll need to reinitialize. You caould do a nsync
> one, provided you could ensure the data is synchronized. This can be
> achieved by stopping all activity during the process, or by using
> datacompare after the restore. After that, you'll need to recreate the
> publication (if publisher) and initialize.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Earl,
that sounds like a pretty clean way of doing it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
replication on the existing database. Is the best approach to simply script
out the database, do an export of the data, then recreate on the other end
and set up a new publication?
Earl,
if it is the publisher or the subscriber, as long as the machine name is
different in practice you'll need to reinitialize. You caould do a nsync
one, provided you could ensure the data is synchronized. This can be
achieved by stopping all activity during the process, or by using
datacompare after the restore. After that, you'll need to recreate the
publication (if publisher) and initialize.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul. These are PocketPCs, so it sounds like the goal should be to
gather up one last round of data, then create a new database, load the data,
then create a new publication and initialize?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:untCSjg2FHA.2492@.TK2MSFTNGP09.phx.gbl...
> Earl,
> if it is the publisher or the subscriber, as long as the machine name is
> different in practice you'll need to reinitialize. You caould do a nsync
> one, provided you could ensure the data is synchronized. This can be
> achieved by stopping all activity during the process, or by using
> datacompare after the restore. After that, you'll need to recreate the
> publication (if publisher) and initialize.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Earl,
that sounds like a pretty clean way of doing it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Best way to Copy a Replicated Database?
Hi.
We have a production database that is set up to be replicated to a fail-over
server, with two-day synchronization.
Periodically, our developers would like to make copies of the production
database to their development box. Obviously, we would just like to take a
copy of the database, without any of the replication settings. However, the
Copy Database Wizard does not allow copying of replicated databases.
How would you approach this problem?
Thanks
Jon,
you could use backup and restore.
HTH,
Paul Ibison
|||Hi. Thanks.
This will work to some extent. But I was hoping for something like Copy
Database Wizard, because of all other dependent information it can copy at
the same time.
Do I need to look for and copy anything manually after backup/restore to a
different server?
"Paul Ibison" wrote:
> Jon,
> you could use backup and restore.
> HTH,
> Paul Ibison
>
>
|||Jon,
essentially they do the same thing, however the way they do it is different.
The Copy database wizard does a detach and attach (sp_detach_db and
sp_attach_db) which causes your problems. Backup and restore will not cause
such problems but will achieve the same aims.
HTH,
Paul Ibison
|||Thank you very much. It worked just fine: it copied all relevant users and
Full-Text Catalog settings as well, which was pretty handy. I suppose the
only thing that did not get copied were the replication settings and some
jobs, which we mostly did not want copied to our development instance, anyhow.
"Paul Ibison" wrote:
> Jon,
> essentially they do the same thing, however the way they do it is different.
> The Copy database wizard does a detach and attach (sp_detach_db and
> sp_attach_db) which causes your problems. Backup and restore will not cause
> such problems but will achieve the same aims.
> HTH,
> Paul Ibison
>
>
We have a production database that is set up to be replicated to a fail-over
server, with two-day synchronization.
Periodically, our developers would like to make copies of the production
database to their development box. Obviously, we would just like to take a
copy of the database, without any of the replication settings. However, the
Copy Database Wizard does not allow copying of replicated databases.
How would you approach this problem?
Thanks
Jon,
you could use backup and restore.
HTH,
Paul Ibison
|||Hi. Thanks.
This will work to some extent. But I was hoping for something like Copy
Database Wizard, because of all other dependent information it can copy at
the same time.
Do I need to look for and copy anything manually after backup/restore to a
different server?
"Paul Ibison" wrote:
> Jon,
> you could use backup and restore.
> HTH,
> Paul Ibison
>
>
|||Jon,
essentially they do the same thing, however the way they do it is different.
The Copy database wizard does a detach and attach (sp_detach_db and
sp_attach_db) which causes your problems. Backup and restore will not cause
such problems but will achieve the same aims.
HTH,
Paul Ibison
|||Thank you very much. It worked just fine: it copied all relevant users and
Full-Text Catalog settings as well, which was pretty handy. I suppose the
only thing that did not get copied were the replication settings and some
jobs, which we mostly did not want copied to our development instance, anyhow.
"Paul Ibison" wrote:
> Jon,
> essentially they do the same thing, however the way they do it is different.
> The Copy database wizard does a detach and attach (sp_detach_db and
> sp_attach_db) which causes your problems. Backup and restore will not cause
> such problems but will achieve the same aims.
> HTH,
> Paul Ibison
>
>
Subscribe to:
Posts (Atom)