Hi,
I need to delete all backups that are more than a day old . When I set-up a new maintenance plan using "Maintenance Plan Wizard", I don't get an option to retain backups only for "1" day... can you tell me where I should be setting this up? In a SQL 2000, this option was available... Thanks!
I am trying to setup the following and was hoping if someone could tell me a more efficient way to set this up. I am not using the maintenance plan or wizard cause Ive had nothing but issues with them:
1. Create a backup device for each day of the week (Sunday - Saturday)
2. Create a job using T-SQL to run maintenance (Rebuild Indexes) and then at the conclusion of the maintenance run a full backup every night at midnight. I would need once job for each day of the week and store them on the proper backup device.
BACKUP DATABASE [DBS1] TO [Sunday Backup] WITH NOFORMAT, NOINIT, NAME = N'DBS1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'DBS1' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DBS1' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''DBS1'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM [Sunday Backup] WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
3. Create a job using T-SQL for transaction log backups to run every hour and store them in the proper backup device. I would need one job for each day of the week.
BACKUP LOG [DBS1 [Sunday Backup] WITH NOFORMAT, NOINIT,
NAME = N'DBS1-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'DBS1' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DBS1' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''DBS1"' not found.', 16, 1) end
RESTORE VERIFYONLY FROM [Sunday Backup] WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
This method would give me 7 backups devices and 14 jobs but at least things would be organized nice and neat. Im sure there is a better way to do this so I am looking for some help. Any help would be much appreciated.
Thanks
I'm setting up my first pair of Sql 2012 servers using AlwaysOn. I set up backups to run on the primary and I understand that you can set up backups to run on both the primary and secondary servers but the secondary will fail. Is there a way I can stop the secondary server from sending out error messages about failed backups? Is it possible to script it so that the server looks at whether it's primary or secondary and turns on or off alerts based on that?
View 8 Replies View RelatedHi All,
Is anyone can show me how to setup sql server to do backup daily automatically?
TIA
We are told that we cannot get remote access on our DB servers , can access them only via SQL management studio. But we are the one's who will be restoring all our DB's from current setup to new servers.
My Question:
1. Can we restore the DB's directly via SQL Management studio(from other server where management studio is installed) without logging into the DB server.
2. How do we set the mdf/ldf file paths, assuming we have elevated access on the DB. Is this possible?.
Any other blockers we can face?
I want to restore my SQL Server 2000 database backup to SQL Server 2014.
View 4 Replies View RelatedHi,
I'm currently working on a project which uses a SQL Express 2005 database. I want to be able to setup SQL Express from my C# program so that it can perform backups to a specified path location at the requested interval itself. This would free up my program from having to manage the backups. Just wanted to know if this is possible?
Also, when the current database becomes corrupt will SQL Express perform the restore for the user automatically, instead of them having to manually request a restore? I realize that it might not be able to handle the restores in the same way as backups, but I figured I'd at least ask.
Thanks,
KingyNL
I need to list the current DB Backup Set up list in PIVOT STYLE.
I need following way:
Database_NameFULL - DDIFF - ILOG - L
DB1DL
DB2D
modelDL
DB3DIL
msdbD
I was trying an edition upgrade from SQL 2005 standard x64 to SQL 2005 enterprise x64 using the "setup SKUUPGRADE=1" from the command line. The SO is W2003 server R2 enterprise and I have a SQL clustered environment with 2 nodes (active/passive).
I select to upgrade the SQL module and I mark the checkbox for the cluster installation. The installation seems to work properly untill setup is doing the task "removing backup files". Then 2 "unexpected error" message appears as follows. The 2 above windows appear when you click on the help icon.
Link to screen capture
The following is the end of the SQL setup log file:
________________________________________________________________________
Property(S): OutOfDiskSpace = 0
Property(S): OutOfNoRbDiskSpace = 0
Property(S): PrimaryVolumeSpaceAvailable = 0
Property(S): PrimaryVolumeSpaceRequired = 0
Property(S): PrimaryVolumeSpaceRemaining = 0
Property(S): RSVirtualDirectoryManager = Reports
Property(S): RSVirtualDirectoryServer = ReportServer
Property(S): SqlActionManaged = 3
Property(S): SqlStateManaged = 2
Property(S): Sql_sqlSqlUpgradeSequence = 387709900
Property(S): SOURCEDIR = I:Setup
Property(S): SourcedirProduct = {79F1B65E-8FC0-4D03-954D-F9E71C85AEC7}
Property(S): HotfixScript = 1
Property(S): DebugClsid.B2156BC9_6CE8_422F_A957_E99A65256B1D = {A42D1020-AA4C-401D-B7BD-26C333C0D627}
Property(S): SQLBROWSERACCOUNT = HEREISTHEDOMAINheretheclusteraccount
Property(S): ProductToBeRegistered = 1
MSI (s) (84:F8) [00:32:10:598]: Product: Microsoft SQL Server 2005 (64-bit) - Update 'Hotfix 3024 for SQL Server Database Services 2005 (64-bit) ENU (KB000000)' could not be installed. Error code 1603. Additional information is available in the log file C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0024_SV00010-2_SQL.log.
MSI (s) (84:F8) [00:32:10:613]: Note: 1: 1708
MSI (s) (84:F8) [00:32:10:613]: Product: Microsoft SQL Server 2005 (64-bit) -- Installation failed.
MSI (s) (84:F8) [00:32:10:613]: Attempting to delete file C:WINDOWSInstaller75d5c.msp
MSI (s) (84:F8) [00:32:10:613]: Unable to delete the file. LastError = 32
MSI (s) (84:F8) [00:32:10:645]: Cleaning up uninstalled install packages, if any exist
MSI (s) (84:F8) [00:32:10:645]: MainEngineThread is returning 1603
MSI (s) (84:CC) [00:32:10:754]: Destroying RemoteAPI object.
MSI (s) (84:74) [00:32:10:754]: Custom Action Manager thread ending.
=== Logging stopped: 18/12/2007 0:32:10 ===
MSI (c) (34:B4) [00:32:10:832]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied. Counter after decrement: -1
MSI (c) (34:B4) [00:32:10:832]: MainEngineThread is returning 1603
=== Verbose logging stopped: 18/12/2007 0:32:10 ===
_______________________________________________________________________
Does anyone have any clue or idea about the possible problem?
We successfully restore 2005 backup into SQL 2014 , so does that mean , does it mean all are ok ? or we have to do something next ?
View 6 Replies View RelatedI am trying to set up log shipping in a clustered server environment. I am pretty confused about the location of the shared folder to be created to put backup created by log shipping job. Which drive should I use either local or clustered shared drive to store the backups in primary server and to copy the same in secondary Server?
View 2 Replies View RelatedI currently have a SQL Server cluster setup with a Primary DB Server SERVER1 and the Standby server SERVER2. SERVER1 has been failing more than normal is the past few weeks and its takes upto 5 mins for SERVER2 realize that SERVER1 is down. I am looking for a better way to implement a backup server on production with minimum downtime. Please adivse..
View 1 Replies View RelatedI have XP Pro SP2 with MDAC 2.8.1022. It had a problem so I tried to reinstall MDAC and got a Fatal Setup Error. This setup does not support installing on this operating system. I downloaded MDAC 2.8 1177 and get the same error.
I thought of uninstalling/reinstalling SP2, but this is a 2 month old Dell Latitude 610 with factory installed XP. There is no Windows Service Pack 2 option listed in the Control Panel > Add/Remove Programs.
There's some other strange things, so I wonder if they are related.
1) I have Paul set up as an administrator account. Some folders like MSSQL show that account with no permissions. I grant all the permissions to Paul for that folder. I come back later and the permissions are gone.
2) I deleted 20 files in Explorer, but 7 of them did not go away. I deleted those 7 again and they instatnly reappeared. I deleted those 7 again and then they finally went away.
3) I get a slow reaction time for things like Windows Explorer and opening and closing programs. This is suprising since it has 2 gig of RAM and 2.3 Gig processor. Could it be a memory handling problem that's causing OS problems. Probably, the memory didn't handle the OS installation well and the whole system is compromised now.
We're doing upgrades from SQL 2008 R2 to SQL 2014. This is blocked due to RS is installed but not configured. Our desired action is to uninstall RS and proceed with the upgrade. But when setuparp.exe is raised, it does not list all the features on the 'Select Features' page. In fact, it only lists the last 2 shared features (SQ Client Connectivity SDK and Microsoft Sync Framework). However, all items appear to be listed on the 'Select Instance' page including RS. I've seen this issue on 2 of our SQL 2008 R2 Servers already.
View 3 Replies View RelatedI'm trying to install SQL Server Management studio 2012 on my Windows 7 (x64) standalone laptop. When I click "New SQL stand-alone installation..." it runs a Setup Support Rules check and always fails "Setup Account Privileges". I've looked into the error and I keep getting that I need to change security rules but I don't have that option in window 7. How do I get around this without having to resort to a computer running Windows Server?
I have Visual Studio 2013 premium installed along with Localdb v11. I just want to connect and manage my database engine through SSMS when developing any application.
This forum is intended for users who are new to SQL Server, and have basic usage questions. If you have setup or installation issues or questions, you should check out the Setup forum.
Thanks
I am running a number of SQL instances on my PC. Within the network, I have think server with various System Center components. For compatibility reasons, some features of System Center 2012 R2 had to be delegated to different SQL databases. My question is, because there is now more than one IP address on my system, and each instance of SQL is assigned to its own IP, is there a way to setup DNS and SQL so the namespace points to the desired IP address? For Instance:
MSSQL2008 instance is set to run on = 11.12.13.1
MSSQL2012 is set to run on = 11.12.13.2
IN DNS:
A Record: Mike-PC = 11.12.13.1
A Record: Mike-PC = 11.12.13.2
If I want to use MSSQL2008 by specifying Mike-PC as the DNS name, how would I do that with 100% accuracy? If there is another way to get the job done, I am more than willing to approach this differently.
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
On the SQL Server the Event Viewer shows the same messages and errors every evening between 22:05:00 and 22:08:00. The following information messages are shown for every database:
"I/O is frozen on database <database name>. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup."
"I/O was resumed on database <database name>. No user action is required."
"Database backed up. Database: <database name>, creation date(time): 2003/04/08(09:13:36), pages dumped: 306, first LSN: 44:148:37, last LSN: 44:165:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{A79410F7-4AC5-47CE-9E9B-F91660F1072B}4'}). This is an informational message only. No user action is required."
After the 3 messages the following error message is shown for every database:
"BACKUP failed to complete the command BACKUP LOG <database name>. Check the backup application log for detailed messages."
I have added a Maintenance Plan but these jobs run after 02:00:00 at night.
Where can I find the command or setup which will backup all databases and log files at 22:00:00 in the evening?
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.
Hi,
I use the Transact-SQL BACKUP statement in Visual Basic to backup my local MSSQL Database. It give me this error
Error 3041
BACKUP failed to complete the command BACKUP DATABASE [BCFPC] to BCFPCBKP
I already created a backup device called BCFPCBKP and it is backup to the disk.
I tried to run the same BACKUP statement in SQL Query Analyzer and it worked fine. I tried to run my VB application in another PC. It worked fine when i use this command remotely. Can anyone tell me what's the problem?
Thanks in advance
regards,
M.Y. Yap
Using Ola Hallengren's scripts I do a full backup of a database on a Sunday. Then differential backups every 6 hours and log backups every hour. I would like to keep a full week of backups based off the full backup done on Sunday. Is there a way for me to clear out the diff and log folders after the successful full backup on Sunday nights?
View 2 Replies View RelatedNeed to restore database,here's the scenario:
Data got deleted on Friday evening, need to have database restored to FRiday afternoon and also some data has been entered on Monday, which needs to be there.
Windows 2003 backup utility uses the shadow copy option that allows it to copy open files.
Therefore, can I use this utility to backup the .mdf and .ldf files for my SQL 2000 database?
I can then attach the .mdf files if I need to restore the database to another server.
Can anyone tell me if this is safe? I've tried it and it worked but I'm worried there maybe some lurking danger in using this approach.
We take a full backup in the early morning and hourly transaction log back during the working hours for one database in the production server. The application team made certain changes to the design of the said database in their development server. The backup from the development server was restored to the production server during working hours. After the restoration should we take a full backup before next transactional logbackup? Would the transactional log backup with out a full backup after the restoration of a database be valid?
View 5 Replies View RelatedI'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] .....
Using SQL Server 2005 Server Management Studio, I attempted to back up a database, and received this error:
Backup failed: System.Data.SqlClient.SqlError: Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the satement after the current backup or file manipulation is completed (Microsoft.SqlServer.Smo)
Program location:
at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)
at Microsoft.SqlServer.Management.SqlManagerUI.BackupPropOptions.OnRunNow(Object sender)
Backup Options were set to:
Back up to the existing media set
Overwrite all existing backup sets
I am fairly new to SQL 2005. Can someone help me get past this issue? What other information do I need to provide?
I'm looking to schedule a maintenance plan for my databases which I have done.I'd like this database to be copied to another folder and the name altered to include the file name and the current date time stamp.Is this possible in the scheduled maintenance plan?
View 4 Replies View RelatedWhat is the difference between "Files aned File Groups" backup and Partial Backup?
Looks like both are same.. Please comment.
I have a database that is just over 1.5GB and the Full backup that is 13GB not sure how this is since we have compression on for full backups and my other full backups are much smaller than there respective databases...Now my full backup is taken every Sunday night and the differentials are taken every 6 hours after the full backup. Now I have been thrown into this DBA role with little to no experience just what I have picked up and read. So my understanding of backups are limited but what I think I understand is that we take a full backup and the differential only captures what changes in the database so my question is why is my database 1.5GB but my differential is 15.4GB? I have others database that are on the same instance and don't seem to have this problem. I also just noticed that we do not rebuild the index before a full backup like we do on other instances...
View 13 Replies View RelatedThis is probably a simple question but I have to ask it anyway. When backing up I can backup to a file on my local drive but I also can create a device to the same location on my local drive. Is this doing the same thing. If I so desire to backup to the local drive(bear with me) what is the difference between creating a device and a file called mybackup or just choosing to backup to a file called mybackup? Should I always create a device? I know these are dumb question but....
Jason
How do we know the list of all the backups that are present in a particualr backup device like file# and time of backup taken etc for the purose of restore?
When I run :
RESTORE HEADERONLY FROM BackupDeviceName
it doesn't give that info. Can anyone tell me the exact command to list all the files of a backup device so that I can restore the right one??
Thanks.
Shalini.
If my backup starts at 8PM and take 1 hour to complete, will the changes made to the database during that hour be captured in the full backup?
Stated another way, will my backup be a snapshot of:
a) 8PM when the backup started
b) 8PM with some of the changes made between the hour
c) 9PM when the backup finished?
Anybody know the exact way SQL Server handles that logic?
Thanks,
Marc