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

Where Are Detached Databases Kept?

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

Cannot Execute Xp_cmdshell. Access Is Denied While Copying Data Over The Network

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

Cannot Execute Xp_cmdshell. Access Is Denied While Copying Data Over The Network

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

SQL Server 2005 Agent - Error Copying A File Onto The Network...

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

Copying Across Databases On Different Networks

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

Copying 7.0 Databases To Different 7.0 Server

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

Copying Databases To New Server

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

Copying Data Between Databases...

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

COPYING Data Between Databases.

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

Copying Databases Between Databases

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

Synchronize Databases Over Network

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

Synchronize Databases Over Network

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

Creating Databases On Network Drives?

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

Listing SQL Server Databases On A Network

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

Trouble Backing-up All Databases To Network Folder Using A Maintenance Plan

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

SQL Server 2005: Copying Tables And Stored Procedures Between Databases On Same Server

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

HELP... Need To MDF Only, But It Was Not Detached

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

Why Can I Not See My Detached Database?

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

After Reboot, Some Db Were Detached, Some Not.

Aug 22, 2007

After reboot, some db were detached, some not.
How come?

View 3 Replies View Related

Detached - Now Cannot Attach!

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

Using Detached Database...

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

How To Attach The Detached Database

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

Attached Vs Detached Database

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

Manage Detached Mdf File

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

Detached Vs Attached Database Files

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

Detached NAS Mdf/ndf Files Locked And Won't Reattach

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

Physical Files Can Not Be Deleted After Database Detached

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

How Do I Recover Database From A Detached Backup Device?

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

Transact SQL :: Moving MDF And NDF Files After Database Is Detached

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

User Instance Db File Not Get Detached After Connection Has Been Closed

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

Cannot Connect To Sql Express Via ASP.NET After I Attached And Detached My Db To Sql Management Studio

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

[DBNETLIB][ConnectionRead (recv()).]General Network Error. Check Your Network Documen

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







Copyrights 2005-15 www.BigResource.com, All rights reserved