How To Copy A Backup File Between Two Servers?
Sep 14, 2004
Hi,
I am creating a job that runs this command:
EXEC xp_cmdshell 'copy f:dados_sqlmssqlackup
ecom_tb_basico.bak \stalingrado_2c$ emp',
The result of this job is:
"Access is denied.
0 file(s) copied.
NULL"
But I am running this command with sa user.....
Wich kind of permission is missing to execute this copy?
When I execute the same command to copy the backup from the server to itself, it works fine!!!!
Does someone have an idea to solve this problem?????
View 3 Replies
ADVERTISEMENT
Apr 26, 2008
hi
i am using isp_backup store procedure to get the daily backup of database and this is work fine but i have copy the database file in network sharing folder to use
following cmd
EXEC master..xp_cmdshell 'D:filename M:foldernam'
M: is network folder to window2003
but their no copy this folder
thank
View 2 Replies
View Related
Jan 4, 2000
What is the syntax for using xp_smdshell to copy a file from 1 server to another?
Our Report server is restored from our production server and I want to copy the .dat
file from the production server to a folder on the report server.
View 1 Replies
View Related
Jun 10, 2006
Hello,
i would like to copy the SQL Server Express database .mdf and .ldf files for backup. Is this ok?
Autoclose = true and recovery model = simple.
Must i detach the database before copy the 2 files or can i copy the 2 files without detach at any time? When connections are open (also remote connections).
Can i copy at any time even when transactions are active?
I would like to write a copy programm which copies the 2 files every 30 minuutes. Only 30 minutes of work could be lost.
This would be enough for me and i don't have to care for the the BACKUP and RESTORE stuff. In the past i used BACKUP and when i needed this BACKUP it did not run. Returns some error message..
Is copy ok? When is it possible? At any time or must all transactions be comitted? Must all connections (remotes too) be closed? Must the database be detached?
Is this enough to have a valid backup? Backup would be an attach of the .mdf file.
Or must i use the BACKUP and RESTORE stuff? Why?
If so, for what reason is the AUTO CLOSE property there?
Regards,
Markus
View 11 Replies
View Related
Nov 6, 2015
I need to copy a just-created bak file to another drive after the backup task has completed. I don't see anything in the job toolbox which works with file system operations like this. But still it must be a common need..There are ways to script this or use third part tools but I am looking for something native to the sql server 2012 SSMS toolset, if possible.
An alternate approach would be to run the backup job again, after the main backup, and change the destination to the alternate location. But I was thinking that another backup job would probably invoke more overhead on the server than a simple file copy operation. If I do end up taking this approach I could also use the cleanup task to toss older bak files in the alt dir.
View 7 Replies
View Related
Dec 29, 2014
I'm looking to schedule a maintenance plan for my databases which I have done.I'd like this database to be copied to another folder and the name altered to include the file name and the current date time stamp.Is this possible in the scheduled maintenance plan?
View 4 Replies
View Related
Nov 14, 2000
hi, I do have over 30 scheduled jobs in one sql server in which I want to have the same jobs in another sql server. What is the best way to copy those jobs?
I thought of backing up the msdb from one sql server then restoring msdb into another sql server..... someone advice me NOT to do so due to potential compatibility problems that he did not explicitly state.
I am hoping to get the answer here.
Thanks
Ahmed
View 5 Replies
View Related
Sep 9, 2005
Hi, How can you copy a table from one server to another server using SELECT statement?
View 1 Replies
View Related
Dec 9, 2007
Hi,
I've got two servers with sql 2005 express on, the first is a shared server at our isp, we rent one database from them. The other server is our own and we have full sa access.
Is there an easy way to copy the database from the shared server onto our own server. We're unable to use the backup.
Thanks
View 3 Replies
View Related
Jun 26, 2007
Hi,
Once a report is deployed to a specific server (server A), is there a way to "copy" it to a different server (servers B, C), without having to actually deploy it server by server?
In my case, I am trying to have the developers deploy the reports on the development server first (server A), and then manually or automatically copy them to a Preview server (server B), and later to a production server (server C). I cannot grant the developers full permissions on the production server, so a "copy" is what I'm thinking about....
Thanks in advance for any ideas.
Edwin.
View 4 Replies
View Related
May 6, 2015
I need to backup a single DB with "Copy-only Backup" option on. I Want to place the backup on a different location, we have a media set which is used for daily backups. I want this backup placed on a different
server 192.168.250.xxx
DepartmentsITBackup2015xxx.bak
I have added this location to "back up to" The old one is still there. I I then hit OK th backup hangs for 2-3 minutes and gives me an error which points to the old media set. SO I want to create a new media set and call it "onlyonce" but what I am not sure about is what this Means "back up to a new media set and erase all existing backup sets" Does that mean all old backups will be deleted?
View 3 Replies
View Related
Jul 31, 2007
Hello. I need to copy all of the rows in a table from a database on one server, to another existing table of the same name in a different database on a different server. I'm trying to use a SELECT INTO statement. Any idea how to do this?I've tried SELECT * INTO DestinationServer.dbo.DestinationDB.DestinationTableFROM SourceTable AS SourceTable_1 But this doesn't work, saying there are too many prefixes. Any idea how to do this?
View 5 Replies
View Related
Aug 10, 1999
I connect to remote servers using PPTP connection. There are 4 web servers and 1 production server on the other side of the firewall. From my staging server, I am supposed to archive the NT event logs(Application and system) for each of the servers.
To solve this problem, I have mapped the C drives of each of the remote servers to my local staging server. I then created a batch file, webevnt.bat containing code,
G:
Cd winnt
Cd system32
Cd config
Copy sysevent.evt c:eventarcweb01sysevent.evt
Copy appevent.evt c:eventarcweb01appevent.evt
Then I used SQL Agent to create a job of type, Operating system command(cmdexec) and typed the following command :
C:atch_~1eventl~1webevnt.bat
When I run the command, C:atch_~1eventl~1webevnt.bat through the command prompt, the files are copied with the correct modified date.
The output is,
1 file(s) copied
1 file(s) copied
But, when I use SQL Agent and start the job containing the same command, the job runs successfully but, the files have a wrong modified date. Moreover, when I view the job history for this job, it says, specified drive not found. 1 file(s) copied. 1 file(s) copied.
Please let me know why this happens.
Thank you.
Praveena
View 1 Replies
View Related
Oct 20, 2005
I need to move SQL Agent scheduled jobs from one server to another..
Here are the details:
Source Server
SQL 2000 SP3 (upgraded from SQL 7)
Data stored in MsSQL7Data
target
SQL Server 2000 SP4
Data Stored in DATA
DTS Packages have already been resaved on Target Server, how can I copy the scheduled jobs over?
Thanks
View 2 Replies
View Related
Feb 10, 2008
I have a SSIS package which is used to update the OLAP database daily. It includes various processes from the initial dropping of the raw data source tables, rebuilding it, dropping of the OLAP datamart tables (dimension tables and fact tables), re-building it, updating the OLAP dimensions and finally updating the OLAP cubes. I wrote a series of the Sql scripts to perform the tasks except the updating of the OLAP dimensions and cubes, and assigned them to the SSIS package. Everything is working great and it was scheduled with the sql agent to execute at the early hour everyday. We have a single server enviroment for the "Production/Processing" i.e. Database engine, SSIS, SSAS, SSRS are all installed on a single server. By the way we are using Sql 2005.
Recently we split the "Production/Processing" enviroment into two seperated servers. We purchase another server "Production" server and use the existing server as the "Processing" server. I want to copy the success updated OLAP database from the current server "Processing" server to the new "Production" server. How to copy an OLAP database between servers?
Idealy, I believe that the following 5 processes should be assigned after the completion of the OLAP cubes updated at the "Processing" server:
Stop the SSAS at the "Production" server.........I know how to do this in SSIS
Re-name the existing OLAP database at the "Production" server.......not sure how to do this
Copy the newly updated OLAP database from "Processing" server to the "Production" server. If the copy process is succeed, drop the OLAP database that has just been re-named at the "Production" server. If the copy process is failed, re-named the OLAP database that has just been re-named back to the original name.....not sure how to do this. Should it be a Sql script?
Start the SSAS at the "Production" server........I know how to do this in SSIS
Thanks
View 1 Replies
View Related
Mar 23, 2006
I have recently moved from a Microsoft SQL Server 2000 to a SQL Server
2005.
In the good old Enterprise Manager, when I imported a table from a SQL
server to another, I could choose "Copy objects and data between SQL
Server
databases". When choosen, all primary keys, and default values was
copied.
My question is:
Is it possible to do the same in Microsoft SQL Server Management Studio
with
SQL Server 2005? And how do I do it?
I have tried using the "SELECT * INTO NewTable FROM OldTable"
statement, but
it just drops all information about primary keys and default values.
Best Regards
View 1 Replies
View Related
Apr 27, 2006
This is a question of whether or not to use SSIS to solve a problem.
I need to copy SQL Server database backup files from a server in the DMZ to a fileserver inside the firewall. The SQL Server is not allowed to write it's backup files directly to the fileserver, so they are written to local disk. A connection can be made from inside the firewall to the SQL Server to copy the files off.
So, I'm considering SSIS for the job. Is it possible to use SSIS to perform the file copies from one remote server to another? If so, is the FTP task required, or can File System tasks be used?
An alternative would be Windows scripting, xcopy, robocopy, etc. but I like the features of SSIS and would like to take advantage of it's flow control, error handling, database scheduling, etc.
Any tips, sample code, etc. would be appreciated.
Thanks,
david
View 4 Replies
View Related
Feb 21, 2008
I want to migrate from one machine to another is it wise to use SSIS "Copy Objects and Data Between SQL Servers" task to accomplish this....migration both data and metadata (in master,model,and msdb ) databases?
View 1 Replies
View Related
May 27, 2015
I have migrated databases and logins from SQL server 2008 R2->SQL Server 2014. Now I also want to migrate/copy the "linked servers". Do you have a step-by-step for this, so that i will copy everything that is necessary regarding logins etc ?
View 2 Replies
View Related
Nov 13, 2006
The DTS Task Copy Server Objects is PAINFULLY slow.
The Copy Table Wizard is fast but generates an unmanagable DTS and does not bring over the indexes.
Any tips or tricks to copy tables, data and indexes and a reasonable speed?
Thanks,
Carl
View 1 Replies
View Related
Nov 4, 2006
Hello,
if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.
Or is the datebase name part of the .mdf file?
Regards
Markus
View 6 Replies
View Related
Jul 26, 2006
Hi, I am trying to use SQL Server 2005 Enterprise Edition to copy tables from my local server to my hosts server at brinkster.com. In the old version you could use DTS, but everything seems to have got far more complicated now! Please can anybody guide me in the right direction? Thanks a lot.
View 2 Replies
View Related
Jun 30, 2000
I am trying to install failover support for my sql server 7.0 database on the in clustere server enviorment
Now We have operating system on two clusterd servers but just one common database disk
Now what will happen if i lose server A ( Active ) the server B will take over and use the same database . But what will
happen if database disk crashes IS it possible for me to replicate the data or use disk mirroring , or have one clustered
database server seperate
How does the database server cluster work ?
What kind of replication do i use for my backup support ?
what should be my recovery process ?
how can i use standby option on the database during recovery ?
Please try to reply as soon as possible
if you can also point to some documetation on the net that will also help
Also look at my current structure ( but this should not affect your answer )
Tables
group 1 --- 20 tables ( being replicated to server c )
group 2 ----60 tables
on cluster 1
Server A-- Holds Group 1 ( Transictional replication to server c )
on cluster 2
server B---(Production server ) Group 2 + Group 1
View 1 Replies
View Related
Aug 10, 1999
Hello!
We are planning to build Primary SQL Server 6.5 and Backup SQL Server 6.5.
In case of media failure on primary server what kind of options should
we set if we want all users automatically switch to backup server until recovery process on primary server take place?
Thank you in advance,
Alona
View 2 Replies
View Related
Apr 14, 2004
How to backup database on the other servers in the LANS.
View 8 Replies
View Related
Jul 26, 2015
I am wanting to use backup compression on a few sql servers (2008R2 and 2012). I have never touched compression before and always just gone with the default.I use Ola Hallengren's scripts to do the backups which, if not specifically specified, will use the server default.Backups (FULL and LOG) have been happening successfully on these servers for years.SQL won't care that the previous backup in the set was uncompressed or that the hourly transaction log backups previously taken were uncompressed?Restore statements (T-SQL) will be identical?From everything I am reading it is simply a case of setting the configuration and acting like nothing changed but I just wanted to be 100% certain.1 of the servers is a SharePoint backend.All of the backup files from all servers are picked up by Commvault backup system.
View 4 Replies
View Related
Sep 14, 2007
I ran the following code on one of our servers and I got some surprising results. Those results included backups for that machine but also showed entries for other servers. I don't understand it! I can understand the results being wrong because I coded something incorrectly, but why would another server be listed in bs.server_Name? It appears that the entries are "correct" in that backups were taken on the other server at the time given, but why is it there??? (I put the code together to list the most recent backup for each database on the instance. I hope it is correct.)
select @@ServerName, bs.server_Name, bs.Database_Name,
bmf.physical_Device_Name,
bs.backup_start_date, bs.backup_finish_date,
Case When bs.backup_start_date < Getdate() - 1 Then 'Stale' Else 'Recent' End as Status,
Case When bs.backup_finish_date is NULL Then 'Failed' Else 'Successful' End as Success
from msdb.dbo.backupset bs
Join msdb.dbo.backupmediafamily bmf on bmf.media_set_id = bs.media_set_id
where type = 'd'
and bs.backup_set_id = (select max (bso.backup_set_id)
from msdb.dbo.backupset bso
where bso.server_name = bs.server_Name
and bso.Database_Name = bs.Database_Name
and bso.Type = 'D')
order by database_name
View 1 Replies
View Related
Apr 10, 2000
Hello out there.
I am currently testing the transmission rates of sql server 70 backups over an ATM circuit (T3) from tampa, fl to detroit. We are evaluating what we need to do to relocate our warm backup database servers to our Disaster Recovery site in detroit. I have tried several types of transmissions and are not receiving any transfer rates that will reasonably work with a recovery plan. I have tried copying a file from source to target, I have tried backing up from source to target, I have tried FTP'ing files, and never can achieve higher transmission rate then 1 gig per hour.
I am looking to talk to anyone out there that may have implemented a long distance sql servber DR site or anyone who is thinking about it. Or anyone who has any input on how I might be able to achieve a higher transmission rate over a T3. Or anyone who is using SAN's in both their local and DR site and might be using special synchronizing software. I am currently piloting a Metastore SAN and a Compaq SAN to solve some local disk management problems and eventually transmit data from SAN to SAN for DR.
Please, if you are involved with any of these strategies and would like someone to share your knowledge with or if I can be of help to you by sharing our testing knowledge, please write asap.
Thanks.
Gail Wade
Database Administration
Raymond James Financial
gwade@it.rjf.com
View 3 Replies
View Related
Jan 10, 2005
Hi,
I have been experiencing a problem with a job I am trying to automate. My team requires two instances of a db on development. Every week, db1 is detached and reattached as db1_copy, and a new copy of db1 from production is copied over. I have used cursors for the dropusers, addlogins, grantdbaccess, addroles, and addrolemembers aspects of the restore process. Also, we use sql authentication and not windows authentication. The issue is that when the agent encounters a minor issue, ie. a login that already exists in that db, or adding a rolemember to a role that is already there, I get an error. I have ensure that at that step, the job simply proceeds because it is a minor step in the process (it happens during the script execution when done manually but I can override this because I am present and just let it run in one step instead of many). Is there any way to do this without adding a multitude of steps?
Thanks,
Maria
View 1 Replies
View Related
Jun 23, 2015
We are told that we cannot get remote access on our DB servers , can access them only via SQL management studio. But we are the one's who will be restoring all our DB's from current setup to new servers.
My Question:
1. Can we restore the DB's directly via SQL Management studio(from other server where management studio is installed) without logging into the DB server.
2. How do we set the mdf/ldf file paths, assuming we have elevated access on the DB. Is this possible?.
Any other blockers we can face?
View 1 Replies
View Related
Jun 19, 2015
Historically I've always written a VB script to copy a file from a sharepoint library. I don't like this method because I have to input a username & password in the script and maintain a config file.
Yesterday I was playing around with using a file system task. The sharepoint file has a UNC path so why not? I created a simple test package with a single file system task that copies the sharepoint file (addressed via UNC) to another network location. Package runs fine locally.
I try running on our utility server but am getting a "The file name [SHAREPOINT UNC PATH] specified in the connection was not valid" error. Package is running with a proxy on the server and the proxy account has the same permissions to the sharepoint site (so far as I can tell) as me.
View 0 Replies
View Related
Aug 31, 2001
Hi i am looking for some advice about sql databases and wondering if anybody can help me.
First the Background.
I am in the process of writing a payroll program that uses SQL as a data store. However rather then creating individual databases for each payroll that can be ran on the system i have created a core payroll table that generates a unique id for each payroll and then reference that thoughtout the system.
Now The Problem
The problem I now have is when I work out the final pay for the employees I want to make a quick copy of the various records just in case I need to undo the net pay calculations for adjustments to the data, it is effectively rolling back the transactions to the database but I want to be able to keep them till I decide that everything is correct.
I cant take a snapshot of the entrie database because the payrolls stored within may be in all sorts of states.
I need to keep the records for just the payroll I'm working on.
Any Ideas or Solutions !!!!!
Thanks
EJ Gibbons
"Where poets speak there hearts, then bleed for it" - U2
View 1 Replies
View Related
Apr 12, 2000
hello,
I was storing data on sql server with license for 60 days. before license expired I did:
in enterprise man. selected DB, right click, selected All Tasks,
selected Backup. Backed up to MSSQL7BACKUP DIR using same name.
then went to other machine installed sql server and from the enterprise manager did a restore DB with "restore on existing DB" option
the problem is that I try to give a user rights to the DB and I get error message: error 15023 user or role already in DB
any hints,
thanks
View 1 Replies
View Related
