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
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.
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 ?
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.
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???
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.
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
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.
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.
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.
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'
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?
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.
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?
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
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.
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.
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?
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.
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?
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
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
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
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
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.
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?
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.
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.
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 .
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
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.
