Backup Individual Files Vs Whole Database

May 10, 2007

We have a pretty large SQL Server database, just shy of 400 GB. It is divided up into 24 different data files. The previous DBA here said it is faster to backup the backup individual data files to seperate files. So what he did was issue 6 backup statements like the one below as a step in an agent job.

BACKUP DATABASE MYDATABASE
FILE = 'Data',
FILE = 'data2',
FILE = 'data3',
FILE = 'data4'
TO Data1_4
WITH FORMAT

Next step:

BACKUP DATABASE MYDATABASE
FILE = 'data5',
FILE = 'data6',
FILE = 'data7',
FILE = 'data8'
TO Data5_8
WITH FORMAT

etc.. in sequence.

What I question is how this is faster than just issuing a backup for the whole database? Has anyone ever ran into this before?

- Eric

View 9 Replies


ADVERTISEMENT

Script SQL 2005 Database To Individual Files?

Feb 27, 2006

I am trying to script the DROP(IF EXISTS) and CREATE for all of mytables, views, stored procs, and functions to individual SQL text files(one per object). This was trivially done in SQL 2000 with EnterpriseManager, but when I try in SQL 2005 through Management Studio my only"script mode" options are:- Script to file (which is one huge file with everything)- Script to Clipboard- Script to New Query WindowFYI, I get to this screen through Management Studio by right clickingon a database and selecting Tasks > Generate Scripts... > Next (doesn'tseem to matter what combo of objects I select to script or what otheroptions). I am using SQL Server 2005 Developer (which is Microsoft SQLServer Management Studio 9.00.1399.00).Any solution to this (i.e. via Management Studio, command line, etc.)would be greatly appreciated.Thanks.Ted

View 2 Replies View Related

Different Backup Location For Individual Users

Nov 6, 2007

Hi,


My customers are having problems to restore their own db.

They use SQL Server Management Studio and when they try to restore their db from a device and add the backup file an error message comes up stating that the user does not have permission to C:Program FilesMicrosoft SQL ServerMSSQLBackup.

How can I set a different backup location for each user?


Kindest regards,


Tito Duarte

View 4 Replies View Related

Backup Database To CSV Files

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

Database Backup Using .LDF && .MDF Files

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

Backup Database Using Files

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

Backup SQL Database Files With Vb.net 2005

Mar 17, 2008

I am trying to backup the sql database files from a local computer to a server location through vb.net 2005. Is there a simple way to do this? Every time I try to detach the database, I cannot because the database is in use. Is there something I am missing with this?

Thanks

View 5 Replies View Related

I Dont Have Any Backup Of My Database, But I Have The Mdf's And Ldf's Files

Jul 23, 2005

Im trying to recover my database using the mdf and ldf files.I dont have any backup and i have recovered two of the mdf files usinga tool which "discovers" deleted files after hard drive formatting...It sounds cool, isnt it...:? :(Obviously, i get a "suspect" message when the server starts and the logfile says this kind of things:·"Full PathName.MDF is not a primary database file." (This is one ofthe files repaired using the magic tool.·Error: 823, Severity: 24, State: 6·"I/O error (torn page) detected during read at offset0000000000000000 in file 'Full PathiName2.mdf'... Name2.mdf is thesecond file·Device activation error. The physical file name 'Full PathName2.mdf'may be incorrect.When i try to execute the command "DBCC CHECKDB ('Database_Name') WITHPHYSICAL_ONLY" i get the following message :·Could not open FCB for invalid file ID 0 in database 'Logs'.Do you have any ideas? Thank you very much...:D

View 2 Replies View Related

How To Backup A Database Into A Number Of Smaller Files ?

Jul 23, 2005

To all,How to backup a database into a number of smaller files ?For example, can I can fully backup a DB of 10 MB into 10 files (each 1MB)???The problem I've met is that the DB backup file is too large, over 4GB, and even Winzip can't compress it (after compressing, around 80 %of compression rate is possible)Thanks![color=blue]>From Jason (Kusanagihk)[/color]

View 11 Replies View Related

Seagate Backup Exec Will Not Copy DAT Database Files

Feb 10, 1999

We are having a problem with trying to backup the database device and log DAT files located in the MSSQLData directory.
The Seagate Backup Exec. states that the files are busy and skips them during its backup cycle. It skips all the devices in the
directory.

Any suggestions?

View 3 Replies View Related

Need Suggestion For BACKUP And Database Files On RAID 5 Configuration....!

Sep 10, 2007

I have RAID 5 with 1 logical drive with 2 partitions with SQL Server 2000 running on SERVER.

what best model for data storage and backup would some experienced one suggest:

basically I want to put Master, Database files and T-Log files away from each other.

-- Data files
-- Log files
-- Master database.

Thanks,

Fahim.

View 1 Replies View Related

DB Engine :: Moving Database Backup Files From One Server To Another?

Sep 15, 2015

How to move the database backup files (.bak) from one server to another server using ‘XP_CMDSHELL’

View 2 Replies View Related

RESTORE DATABASE Timeout In SQL 2000 With Large Backup Files

Sep 11, 2007



Hello,

I am attempting to restore the database from within VB.NET application I am making the following 3 calls:

RESTORE FileListOnly FROM DISK = 'C:MyDatabase.dat'

USE Master RESTORE DATABASE MyDatabase FROM DISK = 'C:MyDatabase.dat' WITH NORECOVERY, MOVE 'MyDatabase' TO 'C:Program FilesMicrosoft SQL ServerMSSQLDataMyDatabase.mdf', MOVE 'MyDatabase_log' TO 'C:Program FilesMicrosoft SQL ServerMSSQLDataLDFMyDatabase.ldf', REPLACE

RESTORE DATABASE MyDatabase FROM DISK = 'C:MyDatabase.dat'


using SMO. This logic works fine with small *.dat files, however when using *.dat file of about 4Gb I get an error on the 3d restore database call:



ExecuteNonQuery failed for Database 'master'.

An exception occurred while executing a Transact-SQL statement or batch.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Operator aborted backup or restore. See the error messages returned to the console for more details.

ExecuteNonQuery failed for Database 'master'.

An exception occurred while executing a Transact-SQL statement or batch.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Operator aborted backup or restore. See the error messages returned to the console for more details.



The same program/logic also works fine when I use MS SQL 2005 and it runs fine from MS SQL 2005 Query Analyzer for both 2005 and 2000 databases. There seem to be only problem with MS SQL 2000 from within VB.NET. Anybody has any idea? I'd appreciate any response. Thanks

Eugene

View 6 Replies View Related

SQL 2012 :: FOR FILES Command To Delete Old Backup Files On Remote Server?

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

SQL Server 2008 :: Count Number Of Backup Files Deleted From A Default Backup Location

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

Is There A MS SQL Backup Tool That Will Zip Or Compress The Backup Files?

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

SQL 2012 :: Missing Other Backup Files Backup

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

The Backup Set Holds A Backup Of A Database Other Than Existing Database. Restore Database Is Terminating Abnormally

Apr 9, 2008



I have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".

I tried by using

RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF',
MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF',
REPLACE

And also i tried like


RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'

WITH REPLACE

When i use like this,

RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.

Can i anyone please help me out?

Thanks in Advance,
Anand Rajagopal

View 8 Replies View Related

Backup Db To Split Backup Files

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

BACKUP LOG Cannot Be Performed Because There Is No Current Database Backup. BACKUP LOG Is Terminating Abnormally.

Jan 31, 2008



Hi there

I'm getting this message on my third automated backup of the transaction logs of the day. Both databases are in full recovery mode, both successfully backed up at 01.00. The transaction logs backed up perfectly happily at 01:30 and 05:30, but failed at 09:30.

The only difference between 05:30 and 09:30's backups is that the log files were shrunk at 08:15 (the databases in question are the ones that sit under ILM2007, and keeping the log files small keeps the system running better).


Is it possible that shrinking the log files causes the database to think that there hasn't been a full database backup?

Thanks

Jane

View 3 Replies View Related

Backup Files

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

Backup SQL Files

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

Backup Files

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

BackUp SQLEXPRESS Mdf Files

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

Size Of Backup Files

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

Is It &#34;Backup Dump&#34; Files Available In 7.0?

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

Shrinking Log Files After Backup

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

Delete Old Backup Files

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

Backup Files Not Released

Oct 1, 2004

hi

I have had trouble with maintenance plans not deleting expired backups, & this filling the hadrdisk. i have now done an agent job using tsql backup with init/skip, so it just overwrites. problem now is that it fails saying the backup device isnt available (cant delet the file from OS either) - so this was obviously the prob with the maintence plans too - sql/os doesnt seem to be releasing the file after it is completed and a server restart is needed to release it...
anyone know how to sort this?
thanks
des

View 4 Replies View Related

Differential Backup Files

Feb 4, 2004

When a new scheduled job is created for a Differential backup, the file specified in the Destination folder is automatically created by SQL Server. After the first time the job runs, is there a way to configure SQL Server to give each Differential file a unique name, including the timestamp (i.e. similar to Full Backup jobs)? I noticed my only options are 'Append to File" and "Overwrite Existing File." If I choose to enable "Backup Set Expiration," the backup job will not run, because it wants to append/overwrite the filename specified.

View 1 Replies View Related

SQL 2012 :: Zero Out Backup Files?

Jan 28, 2015

Looking at the documentation, it would suggest that as well as data files, when a backup file's created it will also be zeroed out unless the service a/c's been given Perform Volume Maintenance.

We take our backups to dedicated backup servers, meaning backup performance should improve significantly if instant file initialization's given to the Service account logins for the source boxes if I'm right.

View 4 Replies View Related

Identify Backup Files

Apr 4, 2008

How to identify the backup files, whether the backup files were taken using SQL 2000 or SQL 2005?


Advance thanks,
Ravi

View 1 Replies View Related

Different Files In One Backup (MS SQL 2005)

Jan 31, 2008

Hi there,

I have sent a mssql backup file in .bak format to my host admin and asked them restore it. but they told me there are 3 files with 3 different dates in that backup what does it mean? should I ask them to restore all of them or just the last one?
Thanks very much in addvance
(context: MS SQL 2005 express)

Cheers
Shahram

View 3 Replies View Related







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