I have some questions on backups and log shipping.
Before I get to them, though, the goal:
Back up a SQL 2000 database (~5GB in size) completely every day.
Back up its transaction log hourly.
Maintain a warm backup DB server using log shipping.
Here is what I have done so far, which has led to my questions:
I have stopped using maintenance plans for backups, feeling that
then the process would be less 'black boxy'
I first created two jobs to perform the daily db and hourly tlog backups
that saved the backups in files using the naming formats
<name>_db_<yymmdd>.bak and <name>_tlog_<yymmddnnss>.bak.
Issue 1: doing backups this way means that it's a little difficult for the
log shipping jobs to figure out the filenames (esp. the tlog ones), if log
shipping is going to use the files generated by the backup jobs.
Issue 2: I thought maybe I'd have log shipping generate its OWN backup
files, but would that cause problems with the transaction log? Say the
normal tlog backup fires, then fifteen minutes later the shipping tlog
backup fires. Would the shipping tlog backup file be missing the transactions
that were backed up during the normal log backup?
Issue 3: To try to help with the naming issue, I tried switching the backups from
creating new files each time to file devices whose names would be constant.
This worked, but since my database is about 5GB in size, that meant
that, with expiring backups after 7 days, the database backup device
would settle in at about 25GB (I skip weekends) and I'd have to copy
or cab-copy-uncab that file over to my warm server every day. That
seemed a little inefficient. Does anybody have alternative ideas?
My last question is for general info:
Where does SQL Server keep the information on the backups that exist?
Whether I was using individual files or file devices, I was able to go to
Database-All Tasks-Restore Database... in Enterprise Manager and it
would show me the backups that existed. I imagine these must be
stored in a system table somewhere, but I did not see any obvious place
to look (no 'sysbackups' table, etc.).
We have a SQL 2012 server instance that has log shipping set up to another SQL 2012 server to provide a warm standby for a forward facing application. The databases on the primary server occasionally are required to be backed up and restored to a development environment, completely different server. Is there a way to schedule full backups with log shipping enabled?
I've got log shipping set up, and everything seems to be working fine, but the log files are not being deleted from the primary server despite configuring log shipping to retain them for 3 days. I see no errors concerning the log shipping, but did not configure a monitor. What process is responsible for deleting the older log backups, and how can I look for errors. I could simply set up a jog to delete the older files, but that will only mask the issue.
I would like to know if anyone has a recommended method for having backups to tape while running log shipping. For example, is it possible to copy the transaction log backups used for log shipping to tape and apply them to a Full Database Restore from a 3rd Party Backup tool such as Veritas?
My goal is to be able to do a point-in-time restore from tape and still be able to use SQL Server 2005 Log Shipping.
I plan to use Log Shipping on SQL 2000 to have warm standby database. I understand current procedure but have a question wether I can do full normal backups of my database server without screwing up my Log Shipping process? Also can I do transaction log backups as well (separate from the ones used for log shipping)?
I have a few Log Shipped DBs that are working great.
Currently they are set to fire off every 15 minutes 24/7.
My question is this ... I need to get FULL backups of the source DBs in order to restore them on certain Dev boxes.
If I were to execute the full backup on one of these Log Shipped DBs ... how would it affect the log shipping process?
Is there a special method to accomplish this?
As a side note, what would be some concerns/issues if in being able to create the FULL backups and not interupt log shipping, I were to create the backup using a 3rd party tool like Quest LiteSpeed?
I sure wish we were on Enterprise, then I could create a mirror and then snapshot off it to create my backups BUT ... that is not the case as we stand today.
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE( ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL, FirstName VARCHAR(50) NULL, LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName) VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) SELECT FirstName, LastName, NULL FROM MYOTHERTABLE
The space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
I could not able to find Forums in regards to 'Log Shipping' thats why posting this question in here. Appriciate if someone can provide me answers depends on their experience.
Can we switch database recovery model when log shipping is turned on ?
We want to switch from Full Recovery to Bulk Logged Recovery to make sure Bulk Insert operations during the after hours load process will have some performance gain.
I 'm sure I am missing something obvious, hopefully someone could point it out. After a failover log shipping, I want to fail back to my inital Primary server database; however, my database is marked as loading. How can I mark it as normal?
I did the failover as follow:
I did a failover log shipping from the 2 server Sv1 (Primary) and Sv2 (Secondary) by doing the following
1) Stop the primary database by using sp_change_primary_role (Sv1)
2) Change the 2nd server to primary server by running sp_change_secondary_role (Sv2)
3) Change the monitor role by running sp-change_monitor_role (Sv2)
4) Resolve the log ins - (Sv2)
5) Now I want to fail back - I copy the TRN files to Sv1 - use SQL Ent to restore the database at point in time. The task is done; however, the database is still mark as loading. I could not use sp_dboption.
Hi, I'm trying to work out how to backup an ASP.NET 2.0 site which uses an SQL 2005 Express database located in the App_Data directory. It seems that the database file cannot be copied while in use so I guess that either leaves taking the site offline or maybe the database can be backed up to another location using a scheduled task? Any suggestions? Thanks, Dale
What backups options do you have if you do not have a tape drive connected to your SQL 7.0 Server ? Can you run a SQL Maintenance Plan and backup the databases to a remote server. If not, can ARCServe 2000 backup the SQL backups to another server with a tape drive ?
I've set up several backups in the past using the backup screen. I've also set up a Maintenance Plan to back up my database using MyDbName* where the * is the familiar suffix automatically generated by the system when the backup runs every night.
The trouble is that, even though I've deleted old maintenance plans and scheduled backups, they're not going away. Every morning, I see new nightly backups of old backups etc., in the backup folder. Furthermore, the maintenance plan I want to backup my database isn't creating a backup file.
How can I make the old backups stop running, and make sure the new backup runs? I've gone through everything and can't seem to find any residue of these old scheduled backups--yet they still run! Can anyone help?
We are having problems with our tape drives so we are attempting to back up our databases to disk. I have a whole server on the network I could use for this. When I use the backup that comes with SQL 7.0 it won't allow me to choose a network drive. It looks like it must be a local drive. Is there a way around this so I could back it up to this other server. I jave 32 gig available on the server which would be plenty. We don't have enough disk space on the local drives to back it up. We have been having problems with our current tape drive company Exabyte. Does anyone have any recommendations. We are thinking of switching to another company.
I currenntly backup several databases to thier own unique device. Each backup is executed as a full backup (databases are small)and they are set to expire after 7 days. I checked my devices after 7 days and they continue to grow in size - is this right? I expected the device size to hold constant once I had a rolling seven days of backup (The databases don't change in size from day to day) Any ideas?
I'm using the database maintenance plans to backup my databases. We are backing them up to tape. Is there a way within the database maintenance plan to tell it to format the tape instead of appending it to the tape.
I am trying to make sure I have my backups scheduled correctly and in the correct spot. We have a tape drive on the server which is backing up the databases each night. The logs I have them currently being backp up once an hour to disk. My question is would it better to have the logs back up to the tape drive or to disk. The server where I'm backing up the logs to has 2 drive C and D. Where D contains the data and C contains the application/program files. I'm assuming I wouldn't want it on D since if there was a crash with that drive and we lost the data we wouldn't be able to get the logs for that day so it would be better to put it on C. What are most people doing to backup there logs. At the end of the day after a full backup I clear the device that contains the log backups. It this good practice?
How do you backup to a remote drive? The book says that inorder to backup to a remote drive, you need to log on with a domain NT account with administrative privileges. I am unable to log on with a domain account and backup to a mapped drive.
For example, S1 and S2 are two servers in the domain D1. The NT domain administrative account is A1. I need to backup a database D1 on S1 to a location on S2. For this I do the following. On S1, sp_addumpdevice 'disk','dump_dev','s2sharenamefilename.bak' On executing, I get,
(1 row(s) affected) 'Disk' device added.
Then when run the command, backup database D1to dump_dev I get the following error,
Server: Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'dump_dev'. Device error or device off-line. See the SQL Server error log for more details. Server: Msg 3013, Level 16, State 1, Line 1 Backup or restore operation terminating abnormally.
The event log shows the following error message:
BackupDiskFile::CreateMedia: Backup device 's2sharenamefilename.bak' failed to create. Operating system error = 1326(Logon failure: unknown user name or bad password.).
Please let me know how to back up to a mapped drive using TSQL and Enterprise Manager.
I have a dump device configured to append the backup file each time a backup is run. This file has grown extremely large. Im looking for a way to go into the file and delete all but the last 2 or 3 backups. Is there a way to do this?
Hello, is there any backup software (archserve,ultraback) that has a sql agent for 7.0 ? I have not found one yet. Charlie Principato Dialamerica Marketing,Inc.
I have a SQL2K/SP3 database with an MDF of 4GB and an LDF of .5GB. If I do a backup using Enterprise Manager, whether manually or via a DB Maintenance Plan, I get a .BAK of approx. 2GB. If I do a backup using: USE master EXEC sp_addumpdevice 'disk', 'RAMScopyBAK', 'F:MSSQLBACKUPRAMScopyRAMScopy.BAK' GO
BACKUP DATABASE RAMScopy TO RAMScopyBAK GO
I get a .BAK of 24GB !!!
In the end I want a T-SQL step to be part of a SQA job that will back up the database, thus the use of the dumpdevice command. I could create a maintenance plan, disable the jobs it creates, and then start them via sp_start_job, but this is just an example of a systemic problem; i.e. I'd have to do individual maintenance plans for more than one database.
For a lot of smaller/medium sized systems that I administer, I tend to use SQL Server to backup to a backup device and then let the normal system backups backup the dump files to tape.
However, I am taking on a new system with approx 900Gb of data. We dont want to buy another 900Gb of disk to secure the databases ! I have found the SQL Agents in Arcserve 2000 to be unreliable to say the least. (we dont want to stop the SQL Services either)
Would anyone recommend alternative software to backup large databases.
Is there a way in SQL 2k running on win2k adv. server to map a scsi tape device from one sql server to another so database backups can run to the remote tape device??
i took database back up on 01-jan-2005 and from 2nd jan onwards im taking differential backups. if i want to restore the database to a new system, is it sufficient that i restore the back up which i took on 1st jan , and the most recent differential backup ? will the entire data till the last back up date be restored?
I'm pretty new to SQL Server. I have a ? regarding backups.
I have a database which I'm backing up everyday using BACKUP DATABASE db TO db_bak WITH INIT. This job runs everyday successfully, but I'm having an evergrowing transaction log file. I presumed a full database backup will apply transaction logs and will truncate the log files. Isnt it so, or am i missing some concepts? This database can have a data loss of 1 day which is covered by my full database backup job. I dont need a transaction log backup. But to reduce log file size should I do a log backup?
I have some backups scheduled on my sqlserver.most of them are differentail backups.But among them some have options 'Append to media' and some of them are 'Overwrite existing media '. But i dont know which of them falls into which category.How can i find that and can i cahnge 'Append to media' to 'Overwrite existing media '?If yes how can i do that?Does it effect my db if i change it? INfact which one is the best option ('Append to media','Overwrite existing media ') among these two? Thanks.
A complete database backup is very simple to execute and use in recovery. If your data doesn't change often, you might simply schedule a nightly full backup of your database. Even if you need more frequent backups, regularly scheduling a full database backup each hour might be sufficient (if your database is small enough).
Now what does a small database mean , can someone please let me the size in (GB) that a DB is considered to be small (even a range would do )
& How much time does the backup take , or should taken on a dual processor server. I mean need to get a rough idea
I am trying to restore a database to a point in time preferably a minute early before a crash. Initially i have a table called orders where i inserted new rows but it has later been dropped. If i later want to recovered that drop table along with the inserted rows before the drop would this be the correct procedure using the stopat?
RESTORE DATABASE DB1 FROM 'c:db1.bck' WITH RECOVERY, STOPAT = 'mmm dd, yyyy hh:mm AP'
the following is what i have worked out so far. Would it make sense?
1. I backed up the database. backup database db1 to disk = 'c:db1.bck' with init
2. I Added 2 rows to two tables Orders and OrderDetails table. insert into Orders(salesproductID,OrderQty,status) values(' 1','5','6') insert into orderdetails(salesproductID,OrderQty,ProductID) values('9','6','8')
3. I backed up the log backup log db1 to disk = 'c:db1.bck' with init
4. here I recorded time using the GETDATE() function. select GetDate()
5. I added Add 3 more rows to the two tables like this insert into Orders(salesproductID,OrderQty,status) values(' 2','3','5') insert into Orders(salesproductID,OrderQty,status) values(' 4','2','9') insert into Orders(salesproductID,OrderQty,status) values(' 3','2','1')
insert into orderdetails(salesproductID,OrderQty,ProductID) values('8','4','2') insert into orderdetails(salesproductID,OrderQty,ProductID) values('6','3','2') insert into orderdetails(salesproductID,OrderQty,ProductID) values('3','7','9')
6. I Performed a differential database backup. backup database db1 to disk = 'c:db1.bck' with differential, init
7. I then recorded the second time: Time 2. select getdate()
8. I Dropped the OrderDetails table. drop table orderdetails
9. I then record the third time: Time 3. select getdate()
10. I then Added 3 rows to the Orders table. insert into Orders(salesproductID,OrderQty,status) values('7','21','10') insert into Orders(salesproductID,OrderQty,status) values('14','12','19') insert into Orders(salesproductID,OrderQty,status) values(' 13','12','11')
11. I Backuped the log and record the fourth Time 4. backup log db1 to disk = 'c:db1.bck' with init select getdate()
Now when i tried to add rows to the OrderDetails table it does not exist but I like to get back the dropped table OrderDetails and not lose the three rows added to the table Orders. the only thing i would know is that the table orderdetails existed at time 2 and not at time 3
I am in search of a simple .bat file or .vbs script that I can use tostop my SQL database server. I am looking to schedule a script of sortsto initiate at a certain time every day. This script needs to be able tostop my server and/or initiate a backup. The script should be able to beran remotely from the lan. can anyone help me?*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
