Has anyone ever had a need to backup to multiple directories? I am building a general purpose stored procedure to do various types of backups, and I was wondering if the need to do this is common enough to make it worth the effort.
Example of backup to multiple directories:
backup database [MyBigDatabase]
to
disk= 'D:BACKUPMyBigDatabase_db_200709131454_001.BAK' ,
disk= 'E:BACKUPMyBigDatabase_db_200709131454_002.BAK' ,
disk= 'F:BACKUPMyBigDatabase_db_200709131454_003.BAK' ,
disk= 'G:BACKUPMyBigDatabase_db_200709131454_004.BAK' ,
disk= 'H:BACKUPMyBigDatabase_db_200709131454_005.BAK'
with
init,
stats = 5
we have snapshot replication set up between 2 servers. works like a charm... :-)
the problem is this: several agents are set to run every half hour (snapshot & distribution). every time they run, the agents create a new directory entry under MSSQL7REPLDATA (naming convetion: YYYYMMDDHHMMSS) and load it with files....
needless to say, after several days there are a LOT of directories. right now, we remove them manually, which is time consuming and a pain in the ass...
how do i configure SQL server to only make 1 or 2 dozen directory entries before starting to recycle them?
if this is a really simple fix everyone here knows, my apologies for clutering the board with a 'newbie' post. i'm not really a DBA, i was just 'volunteered'!... :-)
I've been trying to look for information about using an SQL 2005 database with ASP.NET 2.0 - while there are loads of different articles coving configuring a database to use with asp.net, I cannot find anything on securing a sub-folder in my asp.net application. At the moment I am successfully connecting to, and validating users via a login page in my asp.net application, however, this is all pointless since I can still browse to the pages without logging in! I have tried putting in the usual <location> tags in my root web.config file and asp.net throws an error referring to my connection string, I have also tried adding a web.config file to the sub-directory, but that just uses the windows login, and I can't find anything that'll allow me to tie my own login page to it... Arrrgh! Any help will be greatly appreciated! Thanks in advance.
I have a machine where the SQL "data" directory was installed on the smaller drive instead of the larger drive. What do I have to do to move the existing databases? (Backup/restore, or detach - move it physically, then attach?)
Hi I'm pulling my hair out on this one..... Installed SQLServer 2005 Express and upscaled my website and intranet site that runs my photo image library.
All my thumbnails and images are in a local D drive, while my site root is in C drive. I.e. <img src="file:///D|/Images-WMASTER......etc
Images will now not display. I've checked folder & sharing permissions, IIS permissions, all the usual stuff, but still with no luck after 2 weeks #@%$$@!!
If I change to a networked folder, such as <img src="file:///S|/Images-WMASTER...etc then they show fine.
All I can think is that there is some permissions issue with SQLServer that I'm unaware of.
Your erudite advice would be greatly appreciated! Thanks, Rob
I'm tring to figure out the best way to organize my reports.
When I create a new Reporting Service Project throught the 2005 .NET IDE, I'm allowed to have 1 folder in the project to deploy to. I've tried adding folders under the default Reports folder, but I haven't found a way to do this. I'd like to be able to add more directories so I can deploy them right to the server, and they will be in the proper folders, with the correct access rights.
A developer here has offered the approach of One Report - One Project. I don't want to do this because the reports have so much in common, like datasources, that I don't want to be flipping back an forth from one project to the next.
I did also look to see if there was a way to assign a dircetory to each report, rather than the project level, but I didn't see way to do that. I also, tried to add folder through the Windows Explorer and see if I could get around this restriction, but no luck there either.
Anyway, How is everyone else organizing they projects?
Hi, I recently upgraded my Standard Edition SQL2005 to Developer Edition SQL2005 for the default instance (MSSQLSERVER) only. I used the following command line setup.exe commands to do the job -
I then re-installed the SP2 patches. Everything seemed fine afterwards, until I tried to process a recent cube project in Visual studio. The DATEDIFF() function failed. So I found the problem (in this forum) that the VBAMDX assembly for my SSAS session was referencing an empty bin folder, and therefore could not process VBA functions in MDX. Now I can fix this by editing the relevant XML file in the DATA folder and restarting my SSAS service but I am concerned about all the other stuff that could be referencing old folders.
Some of the settings e.g. DATA is still in C:Program FilesMicrosoft SQL ServerMSSQL.2OLAPData but the bin programs are now in C:Program FilesMicrosoft SQL ServerMSSQL.6OLAPData
It seems that I now have 3 new installation folders - MSSQL.5, MSSQL.6, MSSQL.7 as well as the usual .1,.2,.3 since upgrading my Edition. Why did it make these? What else may be out of wack on the SQLServer services? How can I make sure everything is properly setup?
I guess the problem could be that there is another instance called OFFICESERVERS on the computer (a WIN2003 server) which was created by the SharePoint installation by someother guy..
I'm curious if there's a "best practice" for setting up the data directories MS SQL will use for each operation? I've allocated independent disks for things like C: (OS), E: (DATA), etc etc etc but I'm not familiar w/ MS SQL to understand how DBA's commonly configure the folders under each unique disk for things like DATA, LOGS, BACKUP, INDEXES, and TEMPDB. Should I have an identically name folder as show below in my example?
You can see I've just mirrored the drive name to a new folder under the partition so data is being written to: F:DATA and E:LOGS. Is this considered correct / good practice? I assume naming the folder in each mount point to whatever I logically called the drive is correct but if I should change how I configure my drive paths above. I'm trying to learn common good SQL Server practices and while I work on properly installing SQL Server 2012/2014, I want to make sure I configure my partition names SQL will utilize correctly.
What I am trying to achieve currently with SSIS is to view the content in one folder for example 'New' and if there is files in this then move it to 'Archived'.
Any nice liinks or tutorial or general advice you guys could give me ?
I have a table that has company id, attachment file name, folderexists columns.
First what I need to do is create a series of folder or directories on a networked server using the company id as the folder name where the folder name does not already exist.
Second I need to move files based on attachment file name and company id to the proper folder.
For those who want to know, this is a remediation project because of a bug in our application.
The application is supposed to created the folder based on company id and then put the attachment in that folder.
I have uninstalled and reinstalled several times and I can't get Reporting Services to create the virtual directories in Default Web Site, instead it creates a new web site called Report Server with the virtual directories under that, this Report Server website points to C:Program FilesMicrosoft SQL ServerInetPubwwwroot. Any ideas as to why this happens? My Default Web Site is called Default Website from the initial IIS install , I am on XP Pro SP 2 with IIS 5.1. This is an issue for me since I don't want to have on another site that doesn't have the aspnet mapings.
I am dumbfounded and can't figure out what to do to make it not do that with the default configuration option checked during install. Should I uninstall and reinstall again and not use the default configuration?
I contact you because we installed Report Services 2012 and after configuring werb service URL, database, Report Manager URLs... the URLs linked to the Report server + Report Manager does not work.After searching in IIS, it seems that even if virtual directories are created, they are linked to directories which does not exist :- c:Program FilesMicrosoft SQL ServerMSRS11.ReportReporting Services does contain only LogFiles and RSTempFiles directories.
I am trying to backup a VLDB (800+gig) to multiple devices. I am using 4 disk drives. Assuming all devices have the same performance, should the 4 devices fill equally? (IE 200gig per drive) If so, what would might happen if one of the 4 drives only has 100 gig free with the other 3 having 300 gig free each?
I have to created a Maintenance Plan in SQL 2005 to take the backup of 5 different DB at 2AM everyday. Each DB has its own sub-directory and backup file The total DB size is 35GB. I need to overwrite the exsisting backup instead of having new backup everytime.
I have a backup that contains multiple databases. I am restoring it to a different server. How do I restore each database? Tried to restore under SQL Server Management Studio, but can only restore one of many from the backup (it appears it has only 1 logical file name in the backup) If you can refer me to a web page that would be great. I tried googling the topic but nothing seems to come up.
Alright, here's the deal. I'm testing some backup/restore strategies, and hitting a (slight) sticking point.
We've got collections of database and log backups created by the usual maintenance plans on a 2000 Enterprise machine. I'm trying to run through a restore onto a new 2005 machine (Developer Edition on my test workstation) using the collection of .bak and .trn files copied from the 2000 server. When I try to restore to a new database on 2005 via SSMS, and select all the .bak and .trn files for the restore, I get the ol' "The volume on device '[trimmed]' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set." error.
I'm assuming this just means that SQL Server can't verify that these log backups are in fact part of a functional "set", even if they aren't part of a traditional backup media set. Is there any way to tell SSMS, "It's okay man, just restore the database from these files, in this order - trust me," or is the only solution restoring every individual log file one at a time? (Which seems to work fine, though is a tremendous pain with any more than a few log backups.) Seems like there ought to be a good one-shot method to restore a bunch of backups to a different server, and I'm just not finding it.
I am new to SQL 2005. I have setup and new maintanaince plan of making backup on to different paths but i encountered an error saying Executing the query "BACKUP DATABASE [promis_05] TO DISK = N'E:\ERP Database\ERP Backup\Promis_05', DISK = N'\\backupsrv\ERP Backup\Promis_05' WITH NOFORMAT, INIT, NAME = N'promis_05_backup_20061111181236', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "The volume on device 'E:\ERP Database\ERP Backup\Promis_05' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set. BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
We did not know what to make of this. 1) Is it bcoz i am backing up the database on 2 locations same time.
2) What is BACKUP WITH FORMAT?
3) Why won't it let me add a new file that is not part of the 'family' ?
4) How does it get to be part of the family? Thought and ideas are highly appreciated!
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?
I am looking for a SQL Backup/Restore tools which can restore multiple environments. Here is high level requirements.
1. We have 4 DBs, range from 1 TB - 1.5 TB Each Database. When we restore to QA, DEV, or Staging, we usually restore 4 of them. 2. I am looking for the speed to complete restoring between 1 - 2 hours for 4 DBs.
I am evaluating the Dephix Software but the setup is very complex and its given us a lot of issues with Windows Authentions, and failure in the middle of the backup. I used Guess Software many years ago but can't find it on the web site any more. Speed is very important for us mean complete restoring as fast as possible. We are on SQL 2012 and SQL 2008 R2.We are currently using NETAPP Technology and I have Redgate Backup Tool but I am mainly looking for fast Restore Process.
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.
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?
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?
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?
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
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?
