Why Is Simply Copying The Mdf/ldf Files NOT A Good Way To Backup?
Mar 12, 2008Hi.
What I want to know is what the issues/scenarios are of only using copies of mdf/ldf files as backups.
TIA.
ADVERTISEMENT
Copying Backup Files To Different Locations
Oct 25, 2007I 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
Is It A Good To Replace SQL Script Files With XML Files?
Jul 23, 2005I am thinking about replacing the INSERT data scriptfiles that I have with XML files. This way I can open the XMLfile using an XML Editor and see the values in a GRID andmake changes easier.Do you see any problem with this approach?I managed to put together some code that is exportinga SQL table with its data to an XML file and also a codethat reads the XML file's data and inserts it into a table.Now I am researching on XSD, td:datatype, DTD...(I am new to XML) in order to figure out how I canuse a single xml file that will hold both the sql serverfields, the datatypes and their values.If you have links to some sample code that has anythingto do with the datatype export and import I am workingon, can you please share them with me?Most importantly what do you think about the idea of usingXML files vs sql scripts?Thank you
View 4 Replies View RelatedIs It Good Idea To Replicate Sql Server Db Files?
Jul 20, 2005Hi.I am wondering if it is a good idea to replicate sql server db filesusing frs.I don't really know how the frs works, sodoes frs replicates the whole database from time to time or just theportion that is changed?Also if the db is expected to change very often, and wouldn't it makethe whole system down?I wonder if it's a good idea just to make a backup of the database andcopy it.What's the usual practice?
View 3 Replies View RelatedMultiple Database Files: Good Or Neutral?
Sep 12, 2007
I have inherited some responsibilities for which I'm not really qualified, so I'll push on through and maybe not totally fall down.
Assume 10 50GB databases, each in a single MDF file. All these MDF files reside on the C drive (the only drive on the system), running SQL 2005 in a 32-bit Windows 2003 or later, 8GB RAM.
The C drive is 6 physical disks in RAID 5, say about 1.0 TB or so. We have 4 dual-core processors on the box.
We have limited simultaneous users, initally about 8 users doing very heavy write on all tables in any one database. Later, we have about 15 users connecting via Web interface, and doing very heavy read and light writing. Each of the 10 or so database has this lifecycle: Heavy write for about 2 weeks (load data) then heavy read for about 1 month (research and search data), then nothing ever again (db is taken offline).
Of course, this is not enough information to go on, but let's just go on it anyway.
My TempDB, Log (simple recovery), Index etc is all on the same RAID 5 drive (C).
I have two basic questions I'd love to hear feedback on:
1. Is there any real advantage to creating 8 Data files for my database (one per processor core)?
2. Given that the hardware people here REALLY don't want to change anything, what should I fight for first:
a. Separate drive for LOG files?
b. Separate drive for TempDB?
c. Something else
Thanks in advance.
What Will Be A Good Way To Backup Master Db?
Mar 9, 2005Currently, I have set up maintenance plan to backup all the databases and I am only the most recent copies. This works very good for me on most of the databases. However, I am having a potential problem with backing up master database.
Based on what I know, I cannot perform a restore on master database. So I am wondering if anyone is able to advise me on a good method to backup the master database and restore it when it needs to be.
Thank you.
Mutiple Backup Is Good Choice?
Feb 9, 2008
Hi,
I am using a stored procedure to take backup of my database from the Visual Basic Programming.
Before i posted one of my thread with the same thing, so i was recommended to go through with DMOSQL do deal with SQL server with Visual Basic Programming. For me, this takes some time to understand the complete concept.
Because of urgent i am using stored procedure to take backup with the following:
---------------------------------------------------------------------------------------
ALTER PROCEDURE dbo.BackUPBLMSDB
(
@RP nvarchar(200)
)
AS
declare @backupfilename nvarchar(200)
set @backupfilename=@RP
BACKUP DATABASE [BLMSDB] TO DISK = @backupfilename WITH NOFORMAT, NOINIT, NAME = N'BLMSDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
---------------------------------------------------------------------------------------
Example @RP="D:ackupBackup-1 09-02-2008 11-24"
Every time i am passing a parameter with somename and dateandtime(System).
Example
Backup-1 09-02-2008 11-24 2900KB
Backup-1 09-02-2008 12-30 2900KB
Backup-1 09-02-2008 18-10 5400KB
Backup-1 09-02-2008 22-00 2900KB
Backup-1 09-02-2008 22-00 2900KB
I would like to clear my doubt, is it a good practice to take backup with different names. The above one i store four backups . If the system crashes and i created the new database with the same schema without any data present in the tables, can i restore previous backup database to the newly created database.
Moreover, The first two backups contains "2900KB", the third one is "5400KB" after the data is being modified. Look at the fourth one it is "2900KB". Why the size is being reduced to "2900KB" after taking backup eventhough i didn't delete or added data into the database.
Hope you will solve my problem.
Thanks.
Best Regards,
Kashif Chotu
Copying DAT Files In 6.5
Mar 30, 2000I 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
Copying DB Files
Dec 1, 2005Hi,
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
Would You Please Suggest A Good Backup Tape Drive?
Jul 23, 2005First posting to the group. I have received a lot of valuable info from youguys. Now, an OT question:What's a good tape drive to perform unmanned weekly backups for a WindowsXP Pro box running SQL server 2000?--Joel Farris | AIM: FarrisJoel** Their Web. Your Way. http://getfirefox.com **
View 1 Replies View RelatedCopying Files Over Workgroup
Apr 8, 2004I 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.
Copying Files Between Servers
May 18, 2006I'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 RelatedSaving Or Copying Enterprise Manager Files
Dec 11, 2000hello 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
Integration Services :: Copying Files From One Server To Another
Aug 25, 2015I 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.
Xcopy Not Copying All Files In Stored Procedure
Apr 8, 2008I 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
SQL 2012 :: FOR FILES Command To Delete Old Backup Files On Remote Server?
Feb 24, 2015I 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?
DTS: Copying Data From Text Files To A SQL Server Table
Sep 10, 2001Hi 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...
Copying Database Backup File
Nov 6, 2003This 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 RelatedSQL Server 2008 :: Count Number Of Backup Files Deleted From A Default Backup Location
Mar 13, 2015I'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] .....
Copying Files From A Sharepoint Location To Local Machine Using SSIS
Apr 20, 2007I 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.
SQL 2012 :: Backup / Restore And Copying A Database
Mar 27, 2014I'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 RelatedIs There A MS SQL Backup Tool That Will Zip Or Compress The Backup Files?
Feb 27, 2007Currently 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.
SQL 2012 :: Missing Other Backup Files Backup
Nov 3, 2014I 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 RelatedBackup Db To Split Backup Files
Apr 12, 2007Hi 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
ODBC, ADP, Or Simply The VB Route.
Jun 8, 2004Hello All,
I have an application running off an Access database. Im trying to convert the application to a server-client architecture and thus moving everything to SQL Server 2000.
I have read alot of articles about ways to accomplish that but I've still yet to decide on which approach is the best (best as in robust and scalable).
I pretty much eliminated the ODBC route due to all the layers of translation a request has to go through to reach SQL Server, although this route seems to be the quickes to accomplish.
Mind you I have plenty of time on hand and am willing to re-write the whole thing from scratch if it means a better app.
Now should I go the ADP route and keep Access as the user interface or should I rebuild the whole front End in pure Visual Basic that interacts with SQL Server? Im leaning towards the latter solution.
I haven't read any articles talking about rebuilding the whole app. using VB and SQL Server instead of just using ADP. Why so and which solution do you think is a better solution for a client-server architecture??
Thanks in advance for any replies to my questions.
Simply Link From Two Tables
Jun 26, 2006Hi all,
I would like to simply link two tables that are in two different databases in the SAME server.
I know that I could use the replication method (snapshot or merge) but I need a simpler method like the Access link table method.
Any suggestions?
Thanks
Alessandro
Help Simply The Update Statement
Aug 3, 2007Hello all,
I need some help in simplyfying the following update statement -
update table <table_a> set <col_1> = NULL where <col_1> = 'N/A'
update table <table_a> set <col_2> = NULL where <col_2> = 'N/A'
update table <table_a> set <col_3> = NULL where <col_3> = 'N/A'
update table <table_a> set <col_4> = NULL where <col_4> = 'N/A'
update table <table_a> set <col_5> = NULL where <col_5> = 'N/A'
update table <table_a> set <col_6> = NULL where <col_6> = 'N/A'
update table <table_a> set <col_7> = NULL where <col_7> = 'N/A'
....and there are 73 columns
Anyway I can create a loop or array and store the column name as a parameter and then pass it to the update statement?
Thanks in advance,
Saurav
How To Simply Join Cols Together?
Jun 5, 2007I have about 30 tabs with same struture.
I want to simple put column 2 in all 30 table in a new table without joining on anything. Is there a way to do that?
Thanks
JP
Simply Updating A Count In A SQL DB
Jul 20, 2005Hi guys,I have a simple field in a table in my sql server DB. All i need to dois update a count on it, from 5 to 6, from 6 to 7, so on. A simple counter.Do I have to SELECT the count field once, get the value, do the addingin my program, then do another command to update the count field? Ordoes SQL syntax allow a simple increment function?Thanks!Buck
View 2 Replies View RelatedSimply Execute A Stored Procedure
Dec 20, 2005Hello, I'm having trouble trying to execute a simple stored procedure. Could someone please take a look at this and let me know where I went wrong.
Dim cn As SqlConnection = New SqlConnection("Data Source=localhost;Initial Catalog=TEST;Persist Security Info=True;User ID=sa;Password=test")
Dim cmd As SqlCommand = cn.CreateCommand
cn.Open()
cmd.CommandText = "cssp_family"
cmd.CommandType = CommandType.StoredProcedure
cmd.ExecuteNonQuery()
cn.closed
Newbie Question About Simply Lookup Tranform
Nov 28, 2007I'm just getting started with SSIS and need pointing in the right direction with my first attempt to create a simple transform lookup.
The objective is to copy some columns from a table in one SQL Server 2005 db into another table in a different db on the same server. There are some simple column transforms involved but one column in the target table needs populating from a lookup as a result of a SQL Query.
So far, I have one OLE DB Source object linked to one OLE DB Destination in 'Data Flow'. I've configured the column transforms and these appear ok. However, the problem is that I can't see how to setup the Lookup. I have added a lookup transform object to the Data Flow space, linked from the appropriate OLE DB Source. The simple sql query returns the correct value when previewed. If I try to connect the output of this lookup object with the same OLE DB Destination that is the output from the first column transform, I get a warning message : -
"Cannot create connector. The destination component does not have any available inputs for use in creating a path"
There is column in the destination table available but I don't know how to direct the lookup output to it. I can see this available column by looking at the OLE Destination object properties and clicking 'Mappings'. The other columns are showing the links in the transformation with the destination column intended for the lookup output is not showing any links.
How do I linkup the output from the lookup object to the OLE Destination object? Is it ok to have two inputs into the OLE Destination object (ie. 1. the output from the OLE DB Source; and 2. The output from the lookup object)?
Thanks in advance,
Clive
Is There A Way To Simply Programticly Create A SQL Server Table And All The Fields?
Mar 26, 2008I am developing a asp.net app and I have a long, very long list of data fields.
The values of the data fields have to be saved to a SQL database. The list is so long that it a pain in the ass to have to write all the code even to declare an object and all its properties by hand. So...
I made a list of all the field names and types and load it all up in an array. Then I wrote a bunch of macros to write all the properties one by one with the corredt type for the object I needed to create instead of doing it all one by one by hand.
I want to do the same thing with the Database. I dont want to have to hand define all the fiedls 1 by 1 by hand in the IDE. I want to write a simple macro, a loop theat loops thru my array and creates a field with the field name and type that it says in the array.
Simple enough.. Now I have the table defined already since that was simple enough. It is the 200 ++ fields that I dont wanna do.
So, please what is the code I need (in VB) to create 1 simple field in an existing table. Say the table is called "Table1" in the database "Database1" and the field I want is to be called "Field1" and to be 250 chr string..... ???
Thank you Marc
Simply Create A Table In A MsSQL Express Database Through ADO.net
May 27, 2008Hello there,Now I'm really down, how do I simply create a Table in a database?It must be something likeCreate Table TableName
(
column_name data_type
)
But first how do I execute that string, so it create the table..And if we get that far, how do you then set a table to primarykey?
Hope really for help, because this is a importen thing, and I cant find the answer? :S