SQL Server 2008 :: Easy Way To Compress Backup File?

Jul 15, 2015

easy way to compress backup file. I use SQL Server 2008 R2

View 4 Replies


ADVERTISEMENT

SQL 2008 Backup Split/compress/speed

Apr 18, 2008



Is there any improvments in SQL 2008 backup methods such as spliting backup files in manageable size(s), compress the backups and/or improving the speed of backup?

Though there are "commercial" tools available, it would be nicer if Microsoft SQL team can incorporate some core needed features in these areas for Small/Medium size businesses.

This is not a question but a suggesstion.

Thanks

View 2 Replies View Related

How To Compress Backup File

Jul 15, 2015

Any easy way to compress backup file. I use SQL Server 2008 R2

View 3 Replies View Related

SQL 2012 :: Compress Existing Backup File?

Jan 22, 2015

I have a SQL 2012 enterprise server, and I'm using Commvault as my backups. So commvault can restore a .bak file to my server, but it cannot use sql compression on the file apparently. So what would be a 150GB .bak backup file is now 600GB. I have to manually upload these files to an auditing firm on an sftp server and the transfer times are now huge.

Is there a way to use something in sql to compress this already existing .bak file down?

View 5 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 Server 2008 :: How To Find Which Backup File Belongs To Which LSN

Mar 18, 2015

I am getting following error:

"The log in this backup set terminates at LSN 9566000024284900001, which is too early to apply to the database. A more recent log backup that includes LSN 986000002731000001 can be restored."

If I am missing to restore previous log backup file, how to find which LSN mapped to which log backup file (.trn file name)? Another word looking at LSN can we tell which log backup file is missing?

View 2 Replies View Related

SQL Server 2008 :: Finding Backup File Size In Advance?

Mar 8, 2015

My backups are failing sometimes.My db size is around 400 GB and we are taking backup to the remote server. Free size available on the disk is showing 600 GB but my database full backup run more than 10 hrs and failed. The failure reason is there is not enough space on the disk.

What could be the possible failure reasons? It has more than the the database size on the backup server but why it is showed that msg on the job failures. I noticed same thing occurred couple of times in the past.

Is there any way to find how much the backup file will be generate before we run the backup job?

i.e. If we run the full backup of test1 database now, it will generate ....bak file for that test1 db

currently we are using maintenance plan and with compression (2008R2) and the database has TDE enabled

View 3 Replies View Related

SQL Server 2008 :: Backup To Device / UNC File / Generates OS Error

Aug 11, 2009

It's OS / SQL related. I backup to a UNC device, and frequently get a network related error - but not always. And the error is also OS related...

The scene: 64 bit Windows Server 2008 hosts 64 bit SQL 2008 Standard, and devices are created to point to 64 Server 2008 Server B (ServerBSQL_backupsdvc_DB.bak). The database is backed up daily, on a job, and when run the following error is returned:

Date2009/08/08 06:43:30 AM
LogJob History (Backup_AssetData_2009-08-08)

Step ID3
ServerSQL01
Job NameBackup_AssetData_2009-08-08
Step NameBackup Database - Second Attempt
Duration00:05:33
Sql Severity16
Sql Message ID3013
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0

Message
Executed as user: COMPANYSERVICE_USER. Processed 94096 pages for database 'AssetData', file 'AssetData' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1 pages for database 'AssetData', file 'AssetData_log' on file 1. [SQLSTATE 01000] (Message 4035) The operating system returned the error '64(failed to retrieve text for this error. Reason: 15105)' while attempting 'FlushFileBuffers' on 'dvc_AssetData(
iscsrv-dcm04RISCSRV-SQL01_Backupsdvc_AssetData.bak)'. [SQLSTATE 42000] (Error 3634) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

The file is generated, the same size as a successful backup, but the job terminates unsuccessful.I have tried to delete the devices' file, and then the backup usually succeeds. This may point to a permissions' type error, but the user the job is run under is a domain admin. The destination server is not unavailable during this time, and the network shares also remain active throughout the excersize - although I haven't got a way to "prove" this.

I have a few databases that backup to the UNC described above (different files) that don't fail..My SQL2000 server backs up it's databases to the SQL2008 server, and those backups don't fail with this error.

My research has led me to understand that the 64bit OS tries to buffer the files it receives from another server, and I was wondering if this could be influencing the backups as the destination server is also 64 bit Windows Server 2008.

##20090812## UPDATE

I tried another way to do the backup; with the same results:

Backup database Infovest to disk = '
iscsrv-dcm04
iscsrv-sql01_backupsdvc_Infovest_Z.bak' with INIT,STATS=1

[code]....

View 9 Replies View Related

SQL Server 2008 :: Creation Of Random Password Which Is Easy To Remember

Jun 2, 2015

I have written a script to GENERATE Random Password. But I want to modify that to a some kind of password which is little easy to remember!

I have seen some of the online sites where they generate passwords which are easy to remember and having some way to remember them,

my script:-
declare @db_pswd nvarchar(4000);
declare @len1 int = 16,
@min tinyint = 40,
@range tinyint = 74,
@exclude varchar(50) = '11cdtyuXR#0:;<=>?@O[]`^/abfty#$%^&1234567890*',
@output varchar(500)

[Code] .....

View 4 Replies View Related

SQL Server 2008 :: Easy Way To Compare Contents Of Objects Between 2 Different Databases?

Jul 20, 2015

Is there an easy way to compare the contents of objects between 2 different databases? For example, say I had 2 databases, My_DB_1 and My_DB_2, each with a SEC_User table. Say I wanted to do an object-to-object comparison between databases to see if there were any differences. Here's some sample SQL:

use My_DB_1
select * from sys.dm_sql_referencing_entities('dbo.sec_user', 'object')

use My_DB_2
select * from sys.dm_sql_referencing_entities('dbo.sec_user', 'object')

Say that the result sets above both returned a SEC_GetUser sproc object ref. Is there a way to write SQL that will compare the SEC_GetUser sprocs (and other objects in the above rowsets) from both databases? For example, if My_DB_1.SEC_GetUser returns an extra column in the result set than My_DB_2.SEC_GetUser then I'd like my comparison SQL to return a single column "IsDifferent" with SEC_GetUser as a row....

View 1 Replies View Related

SQL Server 2008 :: Backup Running Long And Backup Threads Show Suspended

Feb 18, 2015

SQL Server 2008 r2 - 6 GB memory...I attempted a backup on a 500GB database but it was taking way too long. I checked the resources on the box and saw the CPU at 100%. I checked the SQL Server activity log and saw a hung query (user was not even logged on) that had multiple threads so I killed it and now the CPU utilization is back to normal.

Trouble is, now all of the threads in the activity monitor for the backup show 'suspended' and the backup appears to be not doing anything.

View 3 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 Easy Way To Backup By DTSs?

Jul 26, 2004

Hi,

In order to backup by DTS packages, currently I individually open each DTS package in Design mode and then save them as Structured Storage File. Since I have a # of DTS packages and due to the current nature of the work these are changed frequently therefore this backing activity takes time.

Is there a simpler solution via which I canbackup all my DTS packages?

Many TIA.

View 4 Replies View Related

SQL Server 2012 :: Compress Table Timelines

Jul 8, 2014

Here is data I am working with:

CREATE TABLE HISTORY(CUSTOMER VARCHAR(10), PLANNBR VARCHAR(10), SUBPLAN VARCHAR(3),
STARTDATE DATETIME, ENDDATE DATETIME, HISTORYMONTH VARCHAR(6))
INSERT INTO HISTORY(CUSTOMER, PLANNBR, SUBPLAN, STARTDATE, ENDDATE) VALUES('9111111', 'H1111LAC', '006', '2014-01-01', '2014-05-31', '201401')
INSERT INTO HISTORY(CUSTOMER, PLANNBR, SUBPLAN, STARTDATE, ENDDATE) VALUES('9111111','H1111LAC', '006', '2014-01-01', '2014-05-31', '201402')

[code]...

I need to compress these segments into records which will look like this. The HistoryMonth column is not used to make the final determination of records to keep.

CUSTOMERPLANNBRSUBPLANSTARTDATEENDDATE

9111111H1111LAC0062014-01-012014-05-31
9111111H1111OC0102014-06-012999-12-31
9111112H1111LAC0062014-01-012014-05-31
9111112H1111LAC0182014-06-012999-12-31
9111113H1111LAC0062014-01-012999-12-31
9111114H1111LAC0062014-01-012014-02-28
9111114H1111LAC0062014-04-012999-12-31

Customer 9111111 shows a change in both PlanNbr and SubPlan 2014-06-01 Need to show both records
Customer 9111112 shows a change in SubPlan 2014-06-01 - Need to show both records
Customer 9111113 shows no change in PlanNbr or SubPlan - Need to show 1 record
Customer 9111114 show a break between and enddate and next start > 1 day - need to show both records

View 5 Replies View Related

SQL Server 2008 :: SSIS Copy File From SharePoint Library Using File System Task Permissions?

Jun 19, 2015

Historically I've always written a VB script to copy a file from a sharepoint library. I don't like this method because I have to input a username & password in the script and maintain a config file.

Yesterday I was playing around with using a file system task. The sharepoint file has a UNC path so why not? I created a simple test package with a single file system task that copies the sharepoint file (addressed via UNC) to another network location. Package runs fine locally.

I try running on our utility server but am getting a "The file name [SHAREPOINT UNC PATH] specified in the connection was not valid" error. Package is running with a proxy on the server and the proxy account has the same permissions to the sharepoint site (so far as I can tell) as me.

View 0 Replies View Related

SQL Server 2008 :: Log File Space Is 5 Times The Data File

Mar 16, 2015

one of my database data file is 100 GB and the log file is 500 GB.DB is in full recovery model and the transaction logs happen once in 6 hours.Even then, the Database log file isn't reducing in size.

View 9 Replies View Related

SQL Server 2008 :: Restore From A Backup With Wildcard?

Jan 30, 2015

I have a backup that comes to me nightly in the format of XXXX_YY_MM_DD.bak where the date is incremented each night the previous night backup is deleted when the next days is added so in general . I have only one in that folder. I wanted to setup a restore to run nightly

RESTORE DATABASE [XXXData] FROM DISK = 'C:folderExtractedDataapp_XXX_backup_15_01_28.bak' --location of .bak file
WITH REPLACE

I would like to do something like

RESTORE DATABASE [XXXData] FROM DISK = 'C:folderExtractedDataapp_XXX_backup*.bak' --location of .bak file
WITH REPLACE

While I'm asking.. In case there is an older one left behind(which shouldn't happen) I saw something about "LATESTFULL" but think its a redgate command?

View 5 Replies View Related

SQL Server 2008 :: Why Does Backup Cause Trigger To Fire

Mar 2, 2015

A job runs every morning at 3.00 am to back up a database. Many of the tables have triggers on to write updates, deletes and inserts to audit tables.A typical trigger looks like this.

USE [ProjectDB_Live]
GO
/****** Object: Trigger [dbo].[trgStakeHolders] Script Date: 03/02/2015 10:23:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[code]....

I added a trigger to a table over the weekend - structured the same as the one above and, when the back up ran last night, it copied every row in the table into the audit table as if every row in the table had been updated.

View 4 Replies View Related

SQL Server 2008 :: Replication Backup And Restore

Aug 5, 2015

SQL Transaction replication, specifically SQL backup and restore Transaction replication. So Scenario,

S1 = Primary Server 1
R1 = T - Replication Server 1
R2 = T - Replication Server 2

So we have S1 replicating to R1, and we want to build another subscriber which is R2.

Can I take the Replicated Database from R1, backup it up, then restore it to R2, and create the publication/subscription?

Will that work? if not, is there an easier way to avoid the snapshot? the reason i ask this is because we do have replication snapshot, but takes long. One of my Colleagues stated he tried this, however replication made duplicate rows on R2, which is why he had to use replication snapshot.

View 0 Replies View Related

SQL Server 2008 :: Locating MDF / Log And Backup Files?

Aug 21, 2015

I am trying to locate the mdf,log and bak files but I am not seeing folder (MSSQL10_50.MSSQLSERVER) the only folder I see there are 90,100,110 and Report builder.

I located the properties of one of the databases below right clicking the properties on SSMS, but when I physically go there, I don't find that folder.

C:Program Files (x86)Microsoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATA

I gave myself all the permissions under the security tab fro the folder Microsoft SQL Server but still nothing.

View 9 Replies View Related

SQL Server 2008 :: Mysterious Backup Entries In Log

Sep 17, 2015

2008 R2 Instance

SQL Server Logs show that a full backup is occurring on each database at 10:30pm every night.

We have a Maintenance Plan the does a full backup on Friday at 8pm and another Plan that does a differential backup on every other day at 8pm.

There are no other Maintenance Plans or Agent Jobs.

Nothing related shows in the Windows Task Scheduler.

There are no related files that have a 10:30ish time stamp (including no backup files).

Where I should look next to find these backups and/or the script/process that is creating them?

View 5 Replies View Related

SQL Server 2008 :: Restore / Backup Minimal Permissions

Nov 3, 2010

We use Netbackup for our SQL servers to backup and restore databases. I would like the service account used by Netbackup to have as limited permissions as possible. The account should be able to backup and restore a db without being able to read any of the content. Right now the account jobs fail if the service account is not in the sysadmin role.

I removed the account from sysadmin and limited it to dbcreator and public but the job fail.

How to setup an account so that people who know the service account password can't log in with that account and read db information?

View 9 Replies View Related

SQL Server 2008 :: Powershell Script To Move Backup Files

Feb 11, 2015

I am trying to create a job using power-shell script to move the backup files from one folder to another. I am using Ola Hallengren script for backups. Ola hallengren created a common backup folder with sub-folders for databases and even more sub folders for Full and Log backups. My goal is to move full backups, which are older than a month and save them in a different drive along with the same folder structure. I was able to move the first set of backups without any problem, but I can't move anymore files and keep getting this error even when I try to overwrite the previous file with the force statement:

Move-Item : Cannot create a file when that file already exists.

At line:5 char:9
+ Move-Item $i.FullName C:Test -force
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : WriteError: (C:BackupVALIDATIONgcommon:DirectoryInfo) [Move-Item], I
+ FullyQualifiedErrorId : MoveDirectoryItemIOError,Microsoft.PowerShell.Commands.MoveItemCommand

Here's the script that I used to move the first set of files:

foreach ($i in Get-ChildItem C:BackupVALIDATION)
{
if ($i.CreationTime -lt ($(Get-Date).AddMonths(-1)))
{
Move-Item $i.FullName C:Test
}
}

View 0 Replies View Related

SQL Server 2008 :: Restore To Point In Time DURING Differential Backup

Feb 25, 2015

We have a 1TB Database. Our backup strategy looks like this.

Weekly Full backups - Saturday 10pm. (Takes anywhere between 5 - 9 hours)
twice Nightly Diffs (8:30p and 2:30am - Mid week this takes roughly 1.5 hours)
Hourly Log backups starting at 4:00am until 11pm.

I have an issue where I need to restore to 3:00 today. If my (2:30a)Diff is still running at that recovery point.

I am getting an error when trying to restore Full/2:30a Diff/4a Log with a stopat 3:00a.

Error telling me my log backup is incorrectly formed.

Can I, in fact, restore to this point at all? Do I need to go back to my previous Diff (8:30p) and restore the logs, if so, which ones?

My nighttime Diff ran from 8:30 - 10p.

I have logs at 9p, 10p, 11p, 4a

View 8 Replies View Related

SQL Server 2008 :: Can Suppress Messages After The Backup Database Statement Is Run

Apr 22, 2015

Wanted to check if there's any option to suppress message after the database backup statement is run like we have in dbcc checkdb by using NO_INFOMSGS?

Processed 448 pages for database 'master', file 'master' on file 1.
Processed 2 pages for database 'master', file 'mastlog' on file 1.
BACKUP DATABASE successfully processed 450 pages in 0.100 seconds (35.151 MB/sec).
Processed 280 pages for database 'model', file 'modeldev' on file 1.
Processed 2 pages for database 'model', file 'modellog' on file 1.
BACKUP DATABASE successfully processed 282 pages in 0.036 seconds (60.994 MB/sec).
Processed 1992 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.

View 0 Replies View Related

SQL Server 2008 :: Backup Device Creating Backups With A New Transaction Log For Each Day

Jun 19, 2015

Having a lot of problems with backup device creating backups with a new transaction log for each day. This is causing the backups to grow way to fast. Seems to be random with our clients. Created new device backups but getting same problem. A manual backup selecting overwrite all existing backup sets will fix it. But starts the cycle all over again.

View 9 Replies View Related

SQL Server 2008 :: Backup Of Distribution Database Hangs On Stored Procedure

Nov 29, 2014

Have a server running 14 production databases, 12 of which are replicated (transactional replication) to a second server for reporting. Distributor on same machine as publishers. All set to 'SyncWithBackup' = true, distribution set to Full recovery mode, log backups every 30 minutes, full backup nightly. This generally runs just fine.

Occasionally, the process 'hangs' indefinitely (has gone 12 hours or more before being caught) and I need to stop the backup job, stop one or more of the log reader agents, and restart everything, and it proceeds just fine. Annoying, but not fatal, and not very often.

This time, no matter what, the backup job hangs when it runs. This is true whether it is the FULL backup or just a Transaction Log backup. It hangs on the stored procedure sp_msrepl_backup_start, at the point where it is attempting to update the table 'MSrepl_backup_lsns'. When it is hung like this, all of the log reader agent jobs are also hung, blocked by this stored proc. I've tried stopping ALL of the log reader agents prior to starting the backup, but the backup process still hangs up at the same spot and never ends.

I can run the select statement in that SP that gathers the data for the databases 'manually' in a query window and it finishes in about 10 seconds. It actually seems to be hung up on the 'UPDATE' statement. When it is hung, I cannot SELECT from MSrepl_backup_lsns unless I append WITH (NOLOCK) to the statement. Nothing else I can find indicates that there is anything else locking that table. DBCC OPENTRAN shows that there is a lock on that table held by that stored proc -- but I can't see any reason why it won't update the table (17 total records) and move on.

As I said, normally this runs just fine. Totally baffled by what may be causing this at this time.

View 1 Replies View Related

SQL Server 2008 :: Initialize Transactional Publication From Backup Loss Of Data

Mar 18, 2015

I have automated process, which synchronizes a transactional publication using initialize from backup approach. It drops subscriptions and puts them back again once the restore on the subscriber is completed.

Dropping the subscriptions causes a lot of blocking and deadlocking. I've decided to remove those steps, but it causes loss of data on the subscriber.

Is it a must to drop and re-create the subscriptions during such process? If not, how can I avoid the loss of data?

View 0 Replies View Related

Format File Easy One (not For Me)!

Jul 20, 2005

I have a format file which is working but not correctly. It is, forsome reason, dropping the first line of the input .csv file. Theproblem is something with the second coulumn of data having quotes init. Any ideas? Below is some info.Format file (I use firstrow=2 in Bulk Import command:8.061 SQLCHAR 0 3000 ","" 1 Provider_Raw_ID Latin1_General_CI_AS2 SQLCHAR 0 3000 ""," 0 none_name Latin1_General_CI_AS3 SQLCHAR 0 3000 "," 0 none_Spec_orig Latin1_General_CI_AS4 SQLCHAR 0 3000 "," 3 SpecialtyCode Latin1_General_CI_AS5 SQLCHAR 0 3000 "," 2 Category Latin1_General_CI_AS6 SQLCHAR 0 3000 "
" 4 NetworkComparedTo Latin1_General_CI_ASSample input file:ID,NAME,SPEC_ORIGINAL,SPEC,CATEGORY,NetworkCompare dTo1,"Aaron, Arnold H, DO",Family Practice General Practice,FP,PCP,netcomp12,"Aaron, Arnold H, DO",Family Practice General Practice,FP,PCP,netcomp13,"Aaron, Arnold H, DO",General Practice,GP,PCP,netcomp14,"Abae, Mick, MD",Reproductive Endocrinology,OBEN,OB,netcomp15,"Abanilla, Fernando M, MD",Nephrology,IMNE,SPEC,netcomp16,"Abaunza, Ramiro J, MD",Obstetrics/Gynecology,OBGY,OB,netcomp17,"Abaunza-Fiallos, Yanina J, MD",Pediatrics,PD,PED,netcomp18,"Abbas, Rahat, MD",Internal Medicine,IM,PCP,netcomp1Thanks a lot!!!Andrew*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!

View 5 Replies View Related

SQL Server 2008 :: Test Backup Restores (full And Log) - Restore Databases Automatically?

Apr 4, 2015

I am working towards automating the process of testing our backups. For the meantime, I do it all manually - I copy the backup files (full + transaction logs) to our test server and then run the restore script. Once database restored I run the DBCC CheckDB. The results of checkdb I manually upload to our Sharepoint portal as proof that the backup file is intact with no errors.

here are some ideas I have but have not yet tested:

Create a maintenance plan with each 3 jobs:

--> Powershell script to copy the files from Prod server to Test server - add this scrip to Job1
--> Powershell script to restore databases files - add this script to Job2
--> Run the DBCC in powershell (yet to find if possible in PS) - add this script to Job3

I would like to use seperate jobs as to get a report on the duration and status of each job

Would also like to get the results of the DBCC Checkdb as proof that no errors were found for upload to our Sharepoint portal. Dont know if possible via the job.

View 8 Replies View Related

SQL Server 2008 :: Query To Delete All Backup Files Older Than A Specific Date?

Jul 29, 2015

I want to delete all backup files from a folder older than a specific date. But if I use the beklow query, I need to pass how many days of older backup files I need to delete whereas in my case, I dont know how many days/month/syears of old backup files are there in the backup folder.

EXEC xp_cmdshell 'FORFILES /p c:BACKUP /s /m *.sql /d -30 /c "CMD /C del /Q /F @FILE"'

View 2 Replies View Related

Easy Question: Copy Image To A File

Mar 27, 2004

How I can copy an image to a file?

I want to use SP

I use MSDE, textcopy is not installed

I can use bcp, but I don't understand the syntax for image filed

Something like this

bcp "SELECT [ImageFiled] FROM MyDatabase..MYTable WHERE [ID]=1111" queryout c: est.BMP -c -Sservername -Usa -Ppassword

This statement don’t work.
Please I need an example.


Thanks,
Sorry for my English

View 1 Replies View Related

SQL Server 2008 :: MDF And NDF File Sizes

Feb 26, 2015

I have one .mdf and two .ndf files on the same drive. The .mdf file size =275GB, one .ndf file size = 300GB and other .ndf file size = 135GB. Is this normal to have 3 different file size? if not what can I do to fix this? I don't have option to make all files to initial size equal to 300GB as a .ndf.If I have to add a .ndf file (in case of running out the above drive), what initial file size should I set up for new file on new drive? And how data gets distributed across all 4 files (including new .ndf on different drive)?

View 2 Replies View Related







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