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] .....
SET NOCOUNT ON
Declare @daysOld int,@deletedate nvarchar(19) ,@strDir varchar(250)
declare @cmd11 nvarchar(2000)
declare @mainBackupDir varchar(2000),
@result1 nvarchar(max);
[Code] ....
Hi
I am almost ashamed to ask but I cannot find the setting
Is there a way to set the default backup location in sql 2005? I dont want to change the folder every time I specify a new media.
Thanks
Alex
Hi
I want to change the default backup location of MSSQL. I know we can configure it while installing. But I want to change the default backup location of a particular database, running on a database server.
The default location is like.
Program FilesMicrosoft SQL ServerMssqlBackup
Thanks in advance,
Prasanna.
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.
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
}
}
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"'
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.
I have just installed SQL Server 2005. I was not given a choice during installation (that I remember seeing anyway) about which drive I wanted to place the default SQL instance on.
It ended up on C: and I needed it on D:.
So my questions are:
Is there a way to move it to D: that is easier than the way you had to go about it in SQL 2000?
Is there a way to do it during setup so I can avoid this in the future?
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 RelatedScript to find the details of creation date and modified date of all files located in a path?
I wanna write few custom messages before I delete some files from a path.
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?
View 5 Replies View RelatedI 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?
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
It is very intersting to find out that when we are backuping a database we are not able to specify a shared folder .Why?Is it because security concern or it is idenfied as bug or is there any work around.Only thing we are able to specify is that it points to the local drive of the particular server. Help would be more appericated.
View 7 Replies View RelatedHi again,I am able to schedule a backup and default it to the server. However, isthere an option or a way to locate the backup somewhere else on the network?regards,gilbert
View 2 Replies View RelatedHi all,
I have a db with 15 tables and I want to keep records that have been deleted.
Now I don't know where to start:
Must I keep the same structure as the main db or can I also dump all the data in one table?
what are the advantages and disatvantages of the named possibilities.
If someone knows anything else please help me out.
Thnx in advance
i backed up the database d1 and deleted it off my sql server.
when i innitiate the sql server instante is supposed to run the d1 DB. But since i deleted it, it pops up an error. How can i fix it?
Is the name of the most recent backup file for each database stored anywhere in SQL2K? I want execute a SQA job periodically that takes the BAK from database A and restores it over database B (using the T-SQL RESTORE DATABASE procedure), but I need to know the exact name of the .BAK file; i.e. I need to know the yyyymmddhhmm value at the end of that file.
TIA,
Al
I need to back up about 50 databases that are currently sitting on MARS. Every saturday they will be backuped so that they can be moved to HOMER
Pls help as I don't know where to start
BTW..I have also set up the SQL Maintenance wizared Backup procedure which copies to the local server but I want to be able to move the database files to HOMER (another server) so eventually they are backed up by TAPE
Any ideas
Emer
i have a copy of a SQL Server 2000 database on an external hardrive.I have SQL Enterprise Manager 2005 on my laptop and was wondering ifit is possible to view the backup on my laptop when external harddrive is plugged in.What other software/tools/procedures do i need to do to make thebackup "viewable"RegardsSteve F
View 1 Replies View RelatedHi,
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
Hi,
I had SQL Server 2000 installed like below:
Installation Directory = C:Program FilesMicrosoft SQL Server
Default data directory = D:
Default log directory = D:
I found out that the data is stored in D:Program FilesMicrosoft SQL ServerMSSQLdata and backup is done in D:Program FilesMicrosoft SQL ServerMSSQLackup
However, Microsoft says we should never put the ..ackup and ..data folders in the same partition.
My question is: How can I move the path of the backup directory?
I checked the registy but this is what I found:
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerMSSQLServer
..BackupDirectory=C:Program FilesMicrosoft SQL ServerMSSQLackup (<-- note it is pointing to C: not D:)
..DefaultData=D:
Can somebody help please?
SQL Server service accounts; the default (and recommended) service account is NT ServiceMSSQLServer virtual service account - which is shown as MSSQLSERVER locally - if you want to assign permissions to a local location. Fine, that's OK, works.
For network resources, as far as I understand it (or think I do, anyway), a virtual service on a box is made visible to the network using the <Domain><Computer>$ credential. Eg for box Bimble on Domain Wimble - if I want to give access to a directory on backupServer1, I simply assign rights to WimbleBimble$ and SQL Server should get those rights.
This, or so I thought, should allow SQL Server access to that location. The thing is, when trying to access that location, it's throwing an access denied error. But it works fine with LocalSystem.
Do I need to do some additional configuration to allow the Virtual Service account to be shown via <Domain><Computer>$ ?
When attempting to backup a database using Management Studio, I receive a ""Cannot verify the esistence of the backup file location. Do you want to use the backup file location anyway?" yes/no messsage afer entering a file directory which exists. When entering a new directory, when I select "yes," a file locaiton will not be created, however, the backup operation continues to success. Originally, I had configured the sql services to execute under a user account with domain admin and administrative privledges, and I vverified the folder's security config permited write access. I reinstalled and now al SQL services are executing as "Local System" (or "Network Service") with same problem.
Any recommendations as to how to correct what I am doing wrong? (Thanks).
Jamie
__________________________
SQL Server Enterprise 2005 running w/all patches on Windows Server 2003 Server.
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?
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.
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.
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?
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
Hello,
I created a test stored procedure in MS SQL 2000. When the problem in my app was fixed, I deleted this test stored procedure. But the backup job thinks this procedure still exist and so backup job would failed. How can I fix this problem?
Thank in advance for your assistance.
I've an emergency requirement to copy Source server database backup files to destination Server through xcopy command. Backup job on source server runs daily, so once this job get completes all databases backups needs to be moved to destination server. But here the main concern is "the backup files on destination server shouldn't be overwritten, they should be placed separately as Source server job runs daily".
We've a command which overwrites backups on destination server. But we need to keep backups on destination at-least for 4 weeks (means : retention should be 4 Weeks).