Copying Backup Files To Different Locations
Oct 25, 2007
I have a maintenace plan that copies production backup files in DR server. The production is mission critical and runs 24/7. We have a round the clock backups. Backups include full backup, differential backups and log backups.
To secure these backup files, I was asked to keep these files in different server at the same time as it goes to DR Server. As I am using maintenace plan for scheduled backups that go to DR server and I had no idea how to incorparate copying these files to another server in the same maintenence plan. I came up with idea using xp_cmdshell stored proc that I attached on the maintenace plan in executeT-SQL statement task. The statement looks like
Exec master..xp_cmdshell 'copy e:DRbackup*.bak \fgh-sql16ackup$'
Above statement copy all backup files on DR server and takes these to remote server called fgh-sql16.
My question here is ....are there any other effective methods to copy backup files simultaneously to different servers via maintenace plan.
Thanks all
View 6 Replies
ADVERTISEMENT
Mar 12, 2008
Hi.
What I want to know is what the issues/scenarios are of only using copies of mdf/ldf files as backups.
TIA.
View 3 Replies
View Related
Dec 14, 2005
Hi,
I'm fairly new to SQL, waiting on a course on how to use it, but wanting to get stuck it.
I was wondering how I can get SQL to copy a file, say in Access, from one location to another.
This would be part of my job with delivering data extracts users.
Many thanks in advance
View 5 Replies
View Related
Oct 23, 2007
What is the recommendation for having multiple data files ? I've read 1 per CPU etc... some say keep it all in one data file.
How do multiple sql data files help when they are located in the same raid group/single volume ?
This is the setup that I have:
4 CPU box with 2x P400 array controller to 2x MSA10 exclosure (for data only, tempdb,logs,backups are on other arrays)
one array controller per enclosure. single big RAID 10 volume on each ( or should I do 4-4-2 separation).
(In single big raid 10, All disks would help for performance of each query... I don't want to create separate raid groups, mostly due to growth constraints in the future, am I wrong ?)
How should I split database across these two enclosures ? one,two data files on each or more ?
How would this look in OLAP vs OLTP scenarios ?
Thank you.
View 1 Replies
View Related
Mar 23, 2004
I am a newbie in SQL Server Administration. I decided to make the move within my company that no one else wanted.
I have SQL Server 2000 running at this time; users are connecting and using it. My question I guess pertains to the backups.
I have two drives setup for data and transactions logs (D: E:)
My backups are running but backups are being sent to the d:systems datamssqlackup folder. once backups are comlete I normally copy the backups to a network share that is being backed up nightly to tape.
My question is this:
How do i create a device location under backup properties to point to a network share? i would like for the backups to go straight to a network drive so i do not have to copy the backups every morning when i come in.
View 8 Replies
View Related
May 7, 2008
Hello there,
I've some really big problems with SQL Server 2005 Express. I Recently, I had 2 instances on my machine, one was a SQL Server 2000 Developer and the other a 2005 Express Version. The 2000 version was not necessary anymore, so I unstalled it, since then, the Express version keeps having probems.
Under the Server Properties -> Database settings -> Database Locations I've changed the path to D:sqldatamssql, but now, the programm always takes the "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData" to store and load the databases, no matter what I do.
And there are more problems see here:
Restoring does not work=======================
I wanted to restore a database called "fw40_admin" from a backup file (.bak), but that didn't work at all, it always says this as an error:
"System.Data.SqlClient.SqlError: The operating system returned the error '5(error not found)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'D:sqldatamssqlfw40_admin.mdf'. (Microsoft.SqlServer.Express.Smo)"
But as soon as I type in the installation path "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData" inside the "Restore As" under "Options", it works.
Why does the backup only work in "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData", BUT the database locations under the server properties is indicated with "D:sqldatamssql"????
Attaching does not work=======================
Attaching an existing database does not work either. Most databases (mdf) are located on the path D:sqldatamssql, but as soon as I press
Attach Databases -> Add
The dialog window appears with the "D:sqldatamssql" path, but it is unable to find any *.mdfs altough THERE ARE .mdfs in this directory. But Express can find databases in the selected installation: "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData".
Why can't Express see the directory on the D: partition???
What is wrong? Here? Can anybody help me?
View 5 Replies
View Related
Mar 30, 2000
I have an 8 GB SQL 6.5 database that I need to move to another server. I know I can create the devices and the database on the new server exactly as they were created on the old server then replace the new DAT files with the old (after shuting down SQL Server). The issue I am running into is that the sort order and character sets on the two servers are different. Will this have an effect if I copy the DAT files? Am I better off using BCP? I'd hate to use BCP because of the time that it will take to move the files.
Thanks for you help
View 4 Replies
View Related
Dec 1, 2005
Hi,
I would like to know where do I copy .mdf and .ldf files, for it to show up on the enterprise manager. I havent created the db using the enterprise manager, but am copying it from another location. So can you tell me what I need to do for this.
thanks
View 5 Replies
View Related
Apr 8, 2004
I want to copy the database backup files to another server as a part of logshipping implementation. tried using xcopy it doesn't work.
it says "invalid drive specification"
also tried using a dll, the dll does the copying i just had to call the dll through an sp, while trying this the QA hanged, tried killing the process but the process didn't kill, i had to restart the service.
the network in not a domain its workgroup.
with sql server 2000 on win2k server.
any inputs are welcome.
thanks in advance
regards,
harshal.
View 5 Replies
View Related
May 18, 2006
I'm trying to copy files between 2 servers on a local network from within aSQL Job (and Query Analyzer) using xp_cmdshell.xcopy but get an accessdenied message returned.I'm able to successfully do the copy from within a command window so thinkthe problem has something to do with using the default SQL Server accountbut as yet I don't know how to resolve.Any help/suggestions would be much appreciated.
View 1 Replies
View Related
Dec 11, 2000
hello all,
Does anyone know know which files Enterprise Manager uses to save the settings (i.e. registered computers and groups)?
I would like to reinstall enterprise manager, but I do not want to reconfigure all of the server registrations.
Thanks,
Matt
View 1 Replies
View Related
Aug 25, 2015
I am copying files from one server to another and I have specific format for all jpg files. which is in 3 format
filename_reg.jpg,
filename_kat,
filename_pag
and I want to copy _reg files only using file system task.I have already created file sytem task using foreach loop and it is copying files but I want to copy only _reg files.
View 5 Replies
View Related
Apr 8, 2008
I am executing xcopy with xp_cmdshell in a stored procedure and it is not copying all the files. There are about 600 files in the source directory and only around 190 are copied to the destination.
The command in the stored procedure is this N'xcopy c:source*.* c:dest'
Help please!!!!
Thanks,
Saied
View 14 Replies
View Related
Feb 24, 2015
I have the need to delete old backup files via TSQL job. Found this solution online:
PushD "
emoteservershareDIFF" &&(
forfiles -m *DIFF*.sqb -d -1 -c "cmd /c del /q @path"
) & PopD
It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?
View 6 Replies
View Related
Sep 10, 2001
Hi all,
I got a situation here.....
From a source table (in SERVER1) I get ids of candidates and from another source (in SERVER2) I get their CVs (text files stored in various Folders). My destination table (in SERVER3) has two fields, CandidateId & CandidateCV.
I have to transfer the data in above fashion for nearly 1 million records.
How can I write a DTS package which picks up the text file from SERVER2 based on the CandidateId which comes from SERVER1? Probably I need some kind of looping mechanism which changes the candidate id & his CV file.
Can anyone help???
Thanks...
View 2 Replies
View Related
Nov 6, 2003
This is a pretty basic question, but I've generated a backup file for one of my SQL 2000 databases, and I need to copy the backup file to another computer so I can burn it on CD. Whenever I try to copy the file, I get an error message saying that the file is in use by another process and can't be copied. I have tried detaching the database, shutting down Enterprise Manager, stopping the MSSQL service, but all to no avail. There must be a way to do this, but I can't get it to work. Does anyone have any ideas?
View 3 Replies
View Related
Mar 13, 2015
I've written a custom script to delete backup files from location. But unable to modify now to count the number of files are deleted. How to modify the script...
/* Script to delete older than N days backup from a specific directory */
USE [db_admin]
GO
IF OBJECT_ID('usp_DeleteBackup', 'P') IS NOT NULL
DROP PROC usp_DeleteBackup
GO
[Code] .....
View 2 Replies
View Related
Apr 20, 2007
I have to copy files from a sharepoint or extranet location (basically https://.....) location to my local server using SSIS.
Any kind of early help would be really great.
View 1 Replies
View Related
Mar 27, 2014
I'm working on a project where I need to build a small database and then copy it to a server at the client's site. I can't connect directly, so I have to use a VPN connection and use Remote Desktop, copy the database backup from my machine to the cloud, then download it to the client machine. The project is still in the early stages, and the client is still sending me data in CSV files and Excel spreadsheets. I'm periodically needing to do a complete refresh of the database at the client. I've hacked my way through it a couple of times, but I need to know the proper way to do it. I get errors on the restore step, telling me the file is in use.
View 8 Replies
View Related
Feb 27, 2007
Currently we use a SQL maintenance plan to do a full backup of all our databases daily (about 40 databases on our production server). As you can imagine, this eats up disk space quickly so currently we manually zip the backup files and/or move them to an archive drive. I considered writing an application to walk through the backup folder structure and zip any .bak file it finds, but I know there are some third party tools out there that will backup/restore a MS SQL database.
I was wondering if any of these also zip the backups once they are created. Any recommendations or suggestions are welcome.
View 1 Replies
View Related
Nov 3, 2014
I scheduled automatic backup process but its only showing backup of the only one .sql file in the backup folder. Other created .sql files are not backed up. Why is it so?
View 5 Replies
View Related
Apr 12, 2007
Hi everyone,
I want to do sql db backup.But how can I backup db to split backup files? The reason I want to split the backup file is becasue single file size is too big and I want to write to dvd.
any idea or scripts?
cheers
View 1 Replies
View Related
Apr 19, 2006
hello! can you take a look at my question?
We use ARCServe to backup exchange server file (priv1.stm and priv1.edb) every night. When I select both of the files and start to back up using ARCServe. It showed me that Backup Operation Successful.
When I check the files, it didn’t backup both of the files. I don’t know why. Do you know how to backup these two files?
Many thanks.
View 1 Replies
View Related
May 6, 2006
Hi,We are about to install MSSQL Server 2000, on a Windows XP HomeMachine. However, we have servers we could set routine backups of filesto be done to. What what be the best way of doing this?Is there functionality in SQL Server 2000, where we can say dump alldata definitions, accounts, and data to files on this drive at regularintervals?What other suggestions do you have apart from obviously the usual RAID,and Tape Drive stuff?ThanksDavid
View 2 Replies
View Related
Nov 20, 2007
I am going to move to a different host. Can I get back up files of my SQLServer database (from the host) and use those to set up on the new host? Andif so, is that what I should be asking for - backup files? Someone told methere should be 2 files.Thanks
View 1 Replies
View Related
Sep 28, 2006
Hi All,I searched the archives but I could not find any useful stuff for me.I can backup a database on SQLExpress like USE masterEXEC sp_addumpdevice 'disk', 'Store_1', 'c:Store_1.dat'BACKUP DATABASE Store TO Store_1Here is my problem: If I use logins,roles etc. in an application automatically a mdf file is created. Or I can create a new sql database. SQLExpress dynamically use the mdf file when connection string defined. This mdf file is not defined as a database on SQLExpress. Because SQLExpress has not a gui, I cannot see the registered database. How can I backup any sqlexpress mdf file without deattaching or attaching?Thanks
View 2 Replies
View Related
Nov 1, 2006
HiWhat is the easiest option for Exporting ALL of my SQL Server tables into a CSV file (either separate CSV files for each table or one big file with all the table columns and data) ???I just want to Backup my SQL Server database like we backup MySQL database using phpMyAdmin. Unfortunately, my SQL hosting company does not allow backups for free.Thanks for help
View 1 Replies
View Related
Apr 12, 2006
Hi,
I am developing a tool that takes snapshot of a particular database.
By snapshot I mean, attaching the .LDF & .MDF files.
Would you please let me know how to access these files & take a backup of the same.
And also how to restore them back. (using c# programming)
Thanks,
Archana A.A.
View 1 Replies
View Related
Jun 25, 1999
I need to know how big SQL Server 7 backup files are in comparison to the database size. For example if I have a database that is 300 Mb and I do a complete backup to disk with SQL Server 7 will the backup file be about 300 Mb?
Thanks,
Mike
View 2 Replies
View Related
Jul 27, 2000
Hi Everybody,
I hv worked in 6.5. There we take backup through the command 'Dump Database'(e.g---> "Dump database master to masdump WITH INIT." ). As for as I know, in 7.0 we are taking backup using the command "Backup Database". Is it possible to use "Dump Database" command in 7.0.
Can anyone give explanation on that please.
thanks in advance
Srinivasan
View 1 Replies
View Related
Jul 22, 2003
Hello everybody.
I have SQL2000 sp3 standard
with 50 db's
All db set for full recovery
and autoshrink
Backup done with Tivoli
full backup once a week
log backup done every 12 hrs
Problem .. shrinking logs
every 20 min I run job
DBCC SHRINKFILE (My_db_logFile) for every db
70% of the time I am getting
message similar to
-------------
Cannot shrink log file 2 (Wholesale_Log) because all logical log files are in use.
--------------
1. I checked with sp_who2
The is no activity on db 'Wholesale_Log' or any other db returning "Cannot shrink..."
Why i getting "Cannot shrink ..." ?
even if job runs right after backup of the log files ,I still have messages.
Thank you
Alex
View 6 Replies
View Related
Jun 5, 2001
hi,
i want to delete the backup files older than 15 days. how can i do this?
Actually i backup the db and log using the TSQL, and the backup files are in d:ackup folder. I tried sqlmaint,xp_sqlmaint but its not running .
Any idea? thanks in adv.
kumar
View 2 Replies
View Related
Aug 10, 2004
I am but a lowly DBA unworthy of this task...
I have a large (200+ GB) database with many (100+) files. Please don't ask me why I did it this way; I inherited this database -- really, it wasn't my idea.
My predecessor also seemd to think that backups were unnecessary; there have been no backups of this database -- ever.
While we cast about for a good long term solution, I am trying various short-term options. One I want to explore is to back the database up in chunks -- ie, by backing up individual files. I created a test database with five files (there is only one filegroup on the production server). Here is the DDL:
-- =============================================
-- Create database on mulitple file groups
-- =============================================
IF EXISTS (SELECT *
FROM master..sysdatabases
WHERE name = N'MultiFile')
DROP DATABASE MultiFile
GO
CREATE DATABASE MultiFile
ON PRIMARY
( NAME = MultiFile,
FILENAME = N'e:MSSQLDataMultiFile.mdf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = MultiFile2,
FILENAME = N'e:MSSQLDataMultiFile2.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = MultiFile3,
FILENAME = N'e:MSSQLDataMultiFile3.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = MultiFile4,
FILENAME = N'e:MSSQLDataMultiFile4.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = MultiFile5,
FILENAME = N'e:MSSQLDataMultiFile5.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%)
LOG ON
( NAME = MultiFile_Log,
FILENAME = N'e:MSSQLDataMultiFile_Log.ldf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%)
GO
I have tried the following backup script:
BACKUP DATABASE MultiFile
FILE = 'MultiFile',
FILE = 'MultiFile2'
TO Backup01
WITH
INIT
BACKUP DATABASE MultiFile
FILE = 'MultiFile3',
FILE = 'MultiFile4'
TO Backup02
WITH
INIT
BACKUP DATABASE MultiFile
FILE = 'MultiFile5'
TO Backup03
WITH
INIT
And here is the restore script:
RESTORE DATABASE MultiFile2
FILE = 'MultiFile',
FILE = 'MultiFile2',
FILE = 'MultiFile3',
FILE = 'MultiFile4',
FILE = 'MultiFile5'
FROM Backup01, Backup02, Backup03
WITH MOVE 'MultiFile' TO 'E:MSSQLDataaMultfile.mdf',
MOVE 'MultiFile2' TO 'E:MSSQLDataaMultifile2.mdf',
MOVE 'MultiFile2' TO 'E:MSSQLDataaMultifile3.mdf',
MOVE 'MultiFile2' TO 'E:MSSQLDataaMultifile4.mdf',
MOVE 'MultiFile2' TO 'E:MSSQLDataaMultifile5.mdf',
MOVE 'MultiFile_log' TO 'E:MSSQLaMultFile_Log.ldf'
However, running the Restore script generates the following error:
Server: Msg 3259, Level 16, State 1, Line 1
The volume on device 'Backup02' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I'm not sure what to make of this. What do I need to alter in either the backup script or the restore script to make this work?
I am trying this because my objectives are to:
1. Limit the amount of work that the server is performing during any one given backup session. The idea that I have is to backup the database in chunks using a rolling 3-5 day window.
2. The database must be up and operational 7x24x365 (except for one 4 hour window each month)
3. This is not the long-term solution; but I need something to tide us over until we can purchase additional storage capacity.
I appreciate any thoughts and or guidance you can provide.
Regards,
hmscott
View 2 Replies
View Related