Backup Order For Data And Log Files
Apr 16, 2008Hi,
Is there a preferred backup order for backing up data and log files?
Which is preferable to backup first?
Thank you,
Seb.
ADVERTISEMENT
How To Copy Data From Backup Or Data Files To New SQL Installation?
Dec 10, 2005Hi,I have(had) an old Win2k Server server with about 30 web site databases(SQL 2000) that just went under due to hardware problems. Thankfully, Ihave backups of all the databases plus the MDF and LDF files from thehard drive.I want to move all of these sites and their data to a newer server(Win2003) running SQL2000.What's the best way to copy the database from the old server hard drive(now mounted as an extrnal drive to a local machine; I'm currentlyFTPing all of the web site directories from it to the new server)?Just upload the original data to the new server and then mount the MDFand LDF files within the new SQL server? Or do I restore the backupfiles in the new SQL2000?All of my previous data migrations have been DTS operations from onelive server to another, so no experience with either of the abovescenarios. I'll certainly have a lot more experience at one of them bythe time this weekend is through.Thanks for any help you can offer.
View 1 Replies View RelatedSchedule Backup Of Data/log Files
Sep 5, 2006Is there an equivelant to the SQL Server Agent in SQL server express edition? If not, how can I schedule the backup of the data/log files?
Thanks,
SQL 2012 :: LocalDB Cannot Restore A Backup Whose Original Data And Log Files Are In Different Folders
Dec 29, 2014LocalDb cannot restore a backup whose original data and log files are in different folders
[URL]
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?
SQL 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] .....
Is 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 RelatedAnalysis :: Order Of Rows In Tabular Table Not In Same Order Data Was Retrieved?
May 19, 2015I never paid much attention to this before but I noticed this today in a new table I was creating.
For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.
I have a date table I setup and I noticed it is NOT respecting the sort order.
I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.
I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.
Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?
Deduping 20 Files In Certain Order
Sep 30, 2013I have 20 files to dedupe in a certain order, I was going to build a match key based on surname and 6 characters of address line 1 but any script to run the dedupe based on the match key and by a unique keycode for each file?
View 6 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
SQL 2012 :: Load Files In Date Order In SSIS
Apr 10, 2014I have files which has date in file name and I want to load all files in sequential order.
Here I am giving you an example of my file name.
"NetworkActivity_869_403722_01-01-2014.log"
"NetworkActivity_869_403722_01-02-2014.log"
"NetworkActivity_869_403722_01-03-2014.log"
"NetworkActivity_869_403722_01-04-2014.log"
"NetworkActivity_869_403722_01-05-2014.log"
These are my files in that I want to load files sequentially means jan 1st, 2nd like this way.
SQL Server Admin 2014 :: Separate Data Files / Log Files / TempDB / Backups
Jan 9, 2015I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.
View 2 Replies View RelatedBackup Files
Apr 19, 2006hello! 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.
Backup SQL Files
May 6, 2006Hi,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 RelatedBackup Files
Nov 20, 2007I 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 RelatedBackUp SQLEXPRESS Mdf Files
Sep 28, 2006Hi 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 RelatedBackup Database To CSV Files
Nov 1, 2006HiWhat 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 RelatedDatabase Backup Using .LDF && .MDF Files
Apr 12, 2006Hi,
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.
Size Of Backup Files
Jun 25, 1999I 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
Is It "Backup Dump" Files Available In 7.0?
Jul 27, 2000Hi 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
Shrinking Log Files After Backup
Jul 22, 2003Hello 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
Delete Old Backup Files
Jun 5, 2001hi,
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
Backup Database Using Files
Aug 10, 2004I 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
Backup Files Not Released
Oct 1, 2004hi
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
Differential Backup Files
Feb 4, 2004When 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 RelatedSQL 2012 :: Zero Out Backup Files?
Jan 28, 2015Looking 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.
Identify Backup Files
Apr 4, 2008How to identify the backup files, whether the backup files were taken using SQL 2000 or SQL 2005?
Advance thanks,
Ravi
Different Files In One Backup (MS SQL 2005)
Jan 31, 2008Hi 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
Migration Without Backup-files *.dts
Jan 10, 2007Hello All,
I have to
migrate SQL 2000 DTS-Packages to SQL 2005. My Problem is that I didn't saved my
DTS-Packages as files and the SQL 2000 Server exist no longer.
Actually I
have only the old hard disk with the SQL 2000 databases. Do you have any ideas
what can I do?
Thanks
Jonas
Log Backup Files - Appending
Sep 6, 2007Quick question regarding log backup files.
Currently I have a maintenance plan running a Full backup weekly, differential backups nightly, and log backups hourly. The log backups are all going into a single backup file - but it's hard to see what's going on behind the scenes here.
Does this file get 'reset' when the full backup is performed? Will it just keep growing indefinitely and should I be creating new files for each log backup, or manually deleting the file each week during the full backup task?
Thanks
TOP URGENT PLZ: SQL Backup Files
Jun 8, 2006
I have installed Beta 2 over SQL Express and when i wanted to install either Beta 2 or Express it dosn't allow e and i need to backup my databases urgently?
how can i do that?
is it possibe to do it with the phisyical files?
Deleting Backup Files Question
Aug 8, 2000I have created some backups where expiration dates or days to be
retained were not specified.
Using EM, how do I find the old backups.
Using EM, how do I delete the old backups (hoping this will
clean up the MSDB tables and physically delete the files
from disk).
Thanks!!!!