Copying Detached Databases Over The Network With T-SQL
Jul 25, 2006
I have a T-SQL script which deataches and attaches the database. Now here is what I want to do:
I
want to create a SQL Server Job and schedule it to run at a particular
time of the month to Detach all the databases on my local machine and
*Copy all the deatched databases over a network machine* and then
Attach those Database.
All this should be done in T-SQL. I
already have the T-SQLs for Attaching and Detaching. All I need is to
know the T-SQL (example) which will copy the *Detached Databases* from
mu local computer to my Network Computer. How do I achieve that?
Thanks
View 7 Replies
ADVERTISEMENT
Mar 9, 2008
I had to detach a databse for a project. I am now having trouble trying to find this database. It is no longer listed in the Sql Server management studio with the other databases that haven't been detached.
Is the database moved to some other folder when it is detached? Where is this database now?
thanks
View 19 Replies
View Related
Aug 7, 2006
Hi,I am having
some trouble copying data over my workgroup network from my Windows
2003 Server Machine (machineA with SQL SERVER 2005) to one of my
network Machine's drive(MachineB).Here is the T-SQL code that I am trying to execute:EXEC xp_cmdshell 'copy D:Datafile.txt \MachineBDocuments'Whenever
I tried to execute the above piece of code, I get the error message
"Access is denied", but if I try to copy the file from the Command
Prompt (cmd.exe) with the copy command, the file copies fine over the
network.I have already searched over the internet and I found
out that loads of people have the same issue, and they were suggested
something like this:"Check in Services and make sure that the MSSQLServer service is run as
a domain user and that domain user has rights to these network
resources."Well
it sounds plausible, but I don't know what are the exact steps to do
this. How do I know which user is running the MSSQL Server service? Are
they referring to the user which I use to connect to my SQL Server
Database engine throuhg the SQL Server Management Studio?Also they are suggesting 'domain user', and as I said before I do not have domain network just regular simple workgroup network.Here
are some details of the user that I use to login. I generally login
into my Windows 2003 Server machine with user called 'User1' and I use
the same 'User1' to connect to SQL Server through the Management Studio
Screen.Should I create a user called 'User1' on my MachineB(Destination Machine)?I would really appreciate, if someone can give me detailed steps explaning how to solve this problem.Thank you very much once again.
View 11 Replies
View Related
Aug 7, 2006
Hi,
I am having some trouble copying data over my workgroup network from my Windows 2003 Server Machine (machineA with SQL SERVER 2005) to one of my network Machine's drive(MachineB).
Here is the T-SQL code that I am trying to execute:
EXEC xp_cmdshell 'copy D:Datafile.txt \MachineBDocuments'
Whenever I tried to execute the above piece of code, I get the error message "Access is denied", but if I try to copy the file from the Command Prompt (cmd.exe) with the copy command, the file copies fine over the network.
I have already searched over the internet and I found out that loads of people have the same issue, and they were suggested something like this:
"Check in Services and make sure that the MSSQLServer service is run as
a domain user and that domain user has rights to these network
resources."
Well it sounds plausible, but I don't know what are the exact steps to do this. How do I know which user is running the MSSQL Server service? Are they referring to the user which I use to connect to my SQL Server Database engine throuhg the SQL Server Management Studio?
Also they are suggesting 'domain user', and as I said before I do not have domain network just regular simple workgroup network.
Here are some details of the user that I use to login. I generally login into my Windows 2003 Server machine with user called 'User1' and I use the same 'User1' to connect to SQL Server through the Management Studio Screen.
Should I create a user called 'User1' on my MachineB(Destination Machine)?
I would really appreciate, if someone can give me detailed steps explaning how to solve this problem.
Thank you very much once again.
View 18 Replies
View Related
Jan 10, 2008
Hi,
I have an SSIS package designed to copy across a file from local machine to a location on the network.
The SSIS package works fine when I run it directly run it in the Designer, ie, it copies over the file to the network.
But when I run the package through the Job server agent, it fails giving me a validation error on the network path..
Does it have to do with the permissions of the User account used here? Could anyone please provide any information on this....
Thanks
View 14 Replies
View Related
Apr 8, 2001
How can I copy a database from one server to another when they are not on the same network! I have tried to copy across the backup file from one and attempted a restore but i keep getting an error message (Abnormal execution).
Is there a way to do this! HELP!
View 2 Replies
View Related
Apr 18, 2001
Sages,
I have been trying to do this using both DTS and BACKUP/RESTORE. With the former my logins and users get transferred but no one can login. It's as if they are not linked to each other.
With the latter there are no logins to link to the db users. In Sybase we used to bcp the master..syslogins table. Does this work with MSSQL 7.0? Any other suggestions? TIA.
View 5 Replies
View Related
Oct 10, 2002
My production server has 8 databases connected to SAN where all the data resides.
This also has the system databases. There are two internal drives where I store the log files
We will be buying additional internal drives for the server and want to move all data to these drives
and free up SAN for other use.
Do I need to start building this server from scratch or is there an easy way to do this.
I can use sp_attach_db and sp_detach_db too but I prefer backup and restore
Is this the best option:
backup all the databases
uninstall sql server (since system databases are on SAN)
Reinstall sql server
Restore eveything on these new drives
Any ideas will be greatly appreciated
View 1 Replies
View Related
Sep 2, 2002
If I have to copy data from one database to a temporary database on the same server, which transaction log will be written to as a result? Will it be the DB that the command is run from, the source DB, or possibly tempdb's log?
Thanks
Derek
View 1 Replies
View Related
Oct 16, 2007
Hi All,
I wish to copy between 2 tables in different databases.
( The source and destiantion tables already exist)
The source table has duplicate entries which i want to avoid from being copied to destination table.
How can i achieve this?
Thanks in advance..!!
Vishu
View 20 Replies
View Related
Feb 8, 2000
hi from France !!!
i would like how to duplicate a database to another server with all datas, constraints, keys, indexes...
should i use sp_attach_db, dts, backup/restore, sql scripts... ???
thanks to all, nico
View 1 Replies
View Related
May 8, 2008
Hi,
I have the following scenario:
There are 3 web applications in a local network which all have a local copy of the same database. Each of them can alter their local database (just the data, not the structure), and every time that happens the databases of the other applications should be updated immediately.
Are there any solutions out there for that? I found that most tools like slony only mirror one master database to multiple slaves, but I want every database to be able to be the starting point.
There could also be a central sync server, like in subversion..
Greets,
Patrick
View 3 Replies
View Related
May 13, 2008
Hi,
I have a scenario which I need to implement at my work.
I need to maintain one Central SQL Server DB and multiple non central SQL Server DBs at different locations. Here the same DB schema is maintained at Center & non Central SQL Servers. The requirement is: each non central DB has to synchronize with the central DB and has to update the Central DB with the changes made locally.
Here the challenge is the intermittent connectivity between the DBs. The connection is not guaranteed to be alive 24 hours a day. It may go down frequently also. I would like to know the possible ways to handle my situation (i.e. synchronization). And also please suggest me if there is any support with the SQL Server DB itself.
Thanks,
Kishore
View 3 Replies
View Related
Jul 11, 2001
Is this possible? I seem to remember doing it with SQL Server 7 a long time ago. The Microsoft Knowledgebase says it's not possible with 4.2 through 6.5, but nothing about 7.0 and up.
View 2 Replies
View Related
Dec 7, 2005
I would like to provide users of a client program a list of databases on thenetwork that match a certain pattern, like "%frp%". I have seen a list ofdatabases in the Windows XP ODBC configuration tool. What I would like toknow is can I get that information through a query to one of the databases,or is there a control or application on the client machine that I can callto get the list?Does the thread "Databases not showing up in Enterprise Manager" refer towhat I am trying to do?Best regards,Steve Caldwell
View 5 Replies
View Related
Oct 10, 2006
I have set up a Database Maintenance Plan that does a nightly backup of all of my databases (about 12 of them) to a network folder. The plan works for about 95% of the job, but most nights there will be at least 1 database which will fail with the following error...
BackupDiskFile::RequestDurableMedia: failure on backup device '\myfileserverBackupSQLDatabaseDatabaseNameD atabaseName_db_200610081749.BAK'. Operating system error 64(The specified network name is no longer available.).
I know that this is not a permission or storage problem, because it works for most of the job. And a database that fails one night, may work fine the next night only to have a DIFFERENT database fail that night, and sometimes all databases work 100%.
Is there a way to fix this problem? And if not, is there a way to be notified of which specific database in the maintenance plan is failing. The message on the job itsself is very non-descriptive and I have to manually search the logs to find out which databases were successful & which where not. It is very time consuming. Any help with this would be greatly appreciated.
View 5 Replies
View Related
Mar 5, 2008
This question is about SQL Server 2005:
I have been trying to figure out how to copy tables and stored procedures between 2 databases (on the same server) using SQL Server Management Studio. I have tried right clicking on the table name, "script table as", "drop to", "clipboard", then I click on the 2nd database, and then click on the "tables" . I change the name of the database and click "execute". This creates the table but does not copy the data. I have also tried "create to" "clipboard" and "insert to" "clipboard" and cannot seem to be able to figure out how to get the results that I want. I am new at this but need to get the tables with the data copied along with the stored procedures, even if I have to do them one at a time. When I was using SQL Server 2000, I was able to use DTS to copy objects to other databases easily. Can someone please tell me a way to accomplish what I need to do? I have gotten information here before that was very useful and was hoping that someone can help me again.Thank you so much. Carol Quinn
View 9 Replies
View Related
May 18, 2001
Today we turned off SQL Server 7, copied the MDF file (a five-month, 245MB project) to the network, reformatted and installed Windows 2000 SBS with SQL Server 2000.
Lo and behold, SQL Server 2000 refuses to attach the MDF file, claiming it cannot find the accompanying LDF (no longer in existence). Using sp_attach_single_file_db did not help, nor did creating a database of the same name on the server, detaching it, and trying to swap the old MDF file for it.
Please help... if I have to get out a hex editor to get this database attached, I'm ready, but nothing I've tried works so far, and this is very important data--I can't believe the log file is THAT important, especially after committing anything left while shutting down the service.
View 1 Replies
View Related
Mar 5, 2008
I feel that I have done something stupid here.I used SSMSE to detach my database so that I could create a copy - no problem here.But then in SSMSE I right-clicked Databases, chose attach and expected to see my original database. But there are no databases shown in the attach window!I have tried to find the original database in SSMSE but nothing I do allows me to see it. However, Windows Explorer shows me that the files are still where they were when I detached them.Please help - this is driving me crazy.ThanksChris
View 2 Replies
View Related
Aug 22, 2007
After reboot, some db were detached, some not.
How come?
View 3 Replies
View Related
Mar 5, 2008
I feel that I have done something stupid here.
I used SSMSE to detach my database so that I could create a copy - no problem here.
But then in SSMSE I right-clicked Databases, chose attach and expected to see my original database. But there are no databases shown in the attach window!
I have tried to find the original database in SSMSE but nothing I do allows me to see it. However, Windows Explorer shows me that the files are still where they were when I detached them.
Please help - this is driving me crazy.
Thanks
Chris
View 1 Replies
View Related
Nov 1, 2006
Hello,
I currently have an application that uses a Access/JET database for a desktop application. The application is single-user and outside of a few corrupt MDBs occasionally, the setup is working fine.
I am thinking about switching out from using the Access database to using a SQL Server Express detached database. I'm not sure if this is a "proper" usage of a detached database, but I've verified that I can access and query a detached one...
Is this something that is recommended? Are there any performance issues associated with it (I believe the detached would be faster than the JET database.) What kind of maintainence would be needed for the SQL Server file? (I currently have a "Repair and Compact" option for the Access file...anything along those lines needed?) Is there anything else I should be aware of?
Thank in advance!
View 4 Replies
View Related
Oct 1, 2007
Dear All,
i've detached my database. how to attache thesame again?
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 10 Replies
View Related
Nov 11, 2007
Hello all,
I'm using SQL 2005 Express edition. I'm currently connecting to the database file directrly (from ASP.NET) and that db file is not attached to the server.
My question is this:
What is the difference ? Will the performance improve if the db is attached and I connect to it throught the server ? I'm really not sure what the difference is.
Thanks
View 5 Replies
View Related
Oct 13, 2006
I want to manage my sql express .mdf database file (e.g. set Roles) but I seem to only be working with a temporary instance of my database when I attach to .sqlexpress and manage in SQL Server Management Studio Express (SSMSE). The Role I set is gone when I detach and re-attach.
If I try to manage my file in Visual Studio under Server Explorer, I do not see the "Roles" folder to modify roles.
Any help is appreciated.
View 3 Replies
View Related
Nov 11, 2007
Hello all,
I'm using SQL 2005 Express edition. I'm currently connecting to the database file directrly (from ASP.NET) and that db file is not attached to the server.
My question is this:
What is the difference ? Will the performance improve if the db is attached and I connect to it throught the server ? I'm really not sure what the difference is.
Thanks
View 3 Replies
View Related
Oct 22, 2007
Hi
I have a database consisting of multiple files on a remote server.
Both servers have the same user (bob) which is in the administrator group.
The directory that the mdf/ndf files exist on is shared as "nas" with full rights given to the user bob.
They are both in the same workgroup.
SQL server is running as this user (not ideal I know but server and services will be locked down later)
The script I used to create the database uses the following line to enable remote database files:
-- Enable the use of network drive for DB files
DBCC TRACEON(1807)
GO
I used SSMS to detatch the database and then modified my create script by removing database sizes and adding "FOR ATTACH" at the end. (I have also tried using sp_attach_db but no use)
So the script looks like this:
CREATE DATABASE [dbname] ON PRIMARY
( NAME = N'DBName', FILENAME = N'\123.45.67.89asDBName_FG.mdf' , MAXSIZE = UNLIMITED, FILEGROWTH = 10GB ),
-- Filegroups for the Alerts Table and Indexes.
FILEGROUP [FG_DBFile2]
( NAME = N'DBFile2_FG', FILENAME = N'\123.45.67.89asDBFile2_FG.ndf' , MAXSIZE = UNLIMITED, FILEGROWTH = 5GB ),
FILEGROUP [FG_AlertsIndexes]
( NAME = N'DBFile3_FG', FILENAME = N'\123.45.67.89asDBFile3_FG.ndf' , MAXSIZE = UNLIMITED, FILEGROWTH = 5GB )
LOG ON
( NAME = N'DBName_log', FILENAME = N'\123.45.67.89asDBName_log.ldf' ,MAXSIZE = 13GB , FILEGROWTH = 0%)
FOR ATTACH
GO
When I run the script I get the following error:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5120, Level 16, State 101, Line 5
Unable to open the physical file "\123.45.67.89asDBName_FG.mdf". Operating system error 5: "5(error not found)".
I know the permissions are ok as I can map a drive without being prompted.
SQL server is running as the user bob which is the same user I log onto the server as.
I am running Windows 2003 Server R2 Standard Edition on server with SQL Server 2005 SP2.
The NAS box is running SQL Server R2 Storage Edition.
One thing I noticed was that the mdf/ndf files on the NAS box are locked as if SQL is still using them, i.e. I cannot rename them, move them, alter any properties etc. I just get "Access Denied".
I've tried rebooting both servers but still no luck. I've also tried stopping SQL server then renaming the files, but again "Access Denied".
I know there has been a problem in the past detaching and attaching db files on a NAS, but the Hotfix is included in SP2, which I am using.
I hope I have included enough info but if I've missed anything, it can be provided.
(Event Log basically replicates what the error message says.)
Any help or ideas would be greatly appreciated.
Thanks
Steve
View 2 Replies
View Related
Oct 10, 2007
I have detached the database via sp_detach_db in a job, (I was trying to use a job to detach the database, then make copies of the mdf file, then attach the databases as different copies, somehow the job failed due to file locked) I tried to delete the physical files, but they were still locked. I tried several times, two of them I was able to delete the mdf files, but not the ldf file. The other one both mdf file and ldf file can not be deleted. I was not able to see the database via Management studio.
Can anyone help?
Thanks
View 3 Replies
View Related
Nov 29, 2007
I ran into a problem today where I had to pull a backup file from an older tape. The file was originally is a backup that was stored in a device like :
EXEC sp_addumpdevice
'disk', --type
'c2000_BackupDevice', --logical name
'\UNCPathackupFile.bak' --physical location
I put the file from the tape on a File Server and then tried to create a new backup device pointing to the file. That's easy enough, but when I try to restore from it I get an device error saying it's offline.
How can I get that device attached to my server and then extract the backup files stored inside it?
Please advise b/c this is an interesting problem I hadn't anticipated and it argues that I should change my backup strategy .
Thanks for any advice/experience you can provide,
alex8675
View 2 Replies
View Related
Aug 19, 2015
I am using SQL 2012 SE. I am trying to move .mdf and .ndf files after a database is detached. Here is my code that is just to copy the mdf file. I am testing it against this file now and if it worked then I would do the move ldf file the same way.
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
DECLARE @cmd nvarchar(4000)
[Code] ...
The only message is I see while the query is executing is :Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
If I manually copy over the file it takes 30 seconds since the file is only 2GB and the script takes 45 minutes and still executing.
View 7 Replies
View Related
Aug 21, 2007
Hi experts,
I have sql express installed on my machine and i'm using visual studio to create database files. After i've build a connection to my database file thru Server Explorer (choose Add attach files) i opened Management Studio Express and found that the new database has been attached to sqlexpress database engine----technically speaking it's under the user instance which i use to run my visual studio. right?? that's totally fine.My question is, after i'v closed the connection in visual studio, that attached database file still exist.I want it to be detached immediately after i've closed the connection in visual studio.How can i do that?
I've searched through internet and was told that sp_config can solve this problem by setting the user instance time out. I ran sp_config against my database but did not find that item. Could anybody give me some directions? thanks in advance.
View 2 Replies
View Related
Jun 6, 2006
Hello everybody,
I have an ASP.NET 2.0 website running on a windows server 2003 - sql express management studio installed. Today I had to make some changes in my database, add some columns, and so on...
So I put the app_offline.htm into the root of the web application, so the IIS unloaded the application domain, attached it in the sql management studio, made the changes I had to do and detached the db. Ok - then I removed the app_offline.html from the root of the application and looked at my internet explorer telling me that the user NT_network... cannot login...
What did I do wrong? I attached it, detached it, and at once something went wrong...
I tried everything, at least I copied the whole application to another directory and.... It worked fine again!
So, can anybody help me, I don`t know what I did wrong or rather how to make changes in my sql express for the future, not having trouble with any security issues...
regards
Simon
View 5 Replies
View Related
Sep 30, 2004
Hello all,
We have a Windows 2003 Web Edition server serving a site through IIS. It connects to a Windows 2003 Standard Edition server running SQL 2000 SP3a.
This site receives the following error as seen in the topic:
[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.
We've configured the server network utility to only allow TCP/IP connections. The connection string for the site is as follows:
<%
' FileName="Connection_ado_conn_string.htm"
' Type="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
MM_connSpankMSSQL_STRING = "Provider=sqloledb;Data Source=SERVER_IP,1433;Network Library=DBMSSOCN;Initial Catalog=ourmaindb_1;User ID=ourmaindb_1;Password=hotcookies;"
%>
Any ideas or tips on solving this issue? We've noticed it is due to larger queries as smaller ones do work with no problems.
The servers are behind a BSD box running iptables, has 1433,1434 along with standard web ports wide open. Anything outbound is allowed.
Some further testing via ODBC on the IIS server improved things. No more connection pooling for the SQL Server driver allows for 1/3 of the query to run. Still 2/3s of it doesn't show up, and that general network error message appears.
Should have 330 rows if it works right.
View 2 Replies
View Related