Tagged Questions
4
votes
4answers
79 views
Backup not creating a new file
I run a daily backup of my database through command prompt:
c:\sqlcmd -H localhost -Q "BACKUP DATABASE test TO DISK='c:\test.bak'"
But the new backup replaces the previous backup. Please tell me ...
3
votes
2answers
94 views
How large will the Transaction Log backup be?
I encountered a production database of 12 GB with a log of 90 GB. Full and differential database backups are made with a third-party backup tool, but no one is backing up the transaction log so it got ...
3
votes
1answer
81 views
Backing up file groups with restore to earlier backup
I have a task: I have created a database with a filegroup lamb that has a few tables.
I need to extract a csv into a table in a filegroup named lamb and back up that filegroup.
Then extract another ...
2
votes
1answer
96 views
Windows native backups makes SQL Server think that databases backup has been done
community.
I've Windows Server 2008 R2 which hosts few virtual machines.
One of machines is Windows 2008 R2 server with SQL server 2008 R2 Express installed.
Here's the thing:
I wrote a script that ...
0
votes
2answers
296 views
SQL Server 2008 R2 Restore COPY_ONLY full backup with transaction logs
After doing some research I cannot seem to find an answer to this question.
Background
I am attempting to setup a backup plan that fits the following three requirements:
Reliability of backups, ...
1
vote
3answers
236 views
Transaction log for SQL Server is too big
I have a database that has a 33 gb transaction log. It is caused by the fact that the database is in full recovery mode has been used for over 5 years prior to the first backup.
I want to truncate ...
1
vote
2answers
232 views
Differential backups in SQL Server 2008 R2
I created a maintenance plan to backup all user databases (full) and set them to expire after three days.
This has proved to be cost inefficient in regards to disk space so I want to switch to ...
3
votes
1answer
182 views
Filestream database backup error
We have DatabaseA that has filestream files in it. We then have an archive process which first deletes files in DatabaseB (different server) and then inserts new files from DatabaseA into DatabaseB ...
4
votes
1answer
171 views
SQL Server database backup file is double final size until backup completes
I've just completed writing a backup statement for a reasonably large database (1.3 TB), and I've been testing it out using a much smaller database (10 GB + 10GB log file). The backup writes to three ...
2
votes
1answer
174 views
Only a single WITH MOVE clause should be specified for any logical file name
I'm trying to do a copy database by scripting a backup and restore. I have the backup procedure working correctly but I keep getting the error
Conflicting file relocations have been specified ...
0
votes
1answer
63 views
A failure in SQL Server backup job [closed]
The job was invoked by schedule 16 (WIN7DB01 UserDB Backups.Subplan_1). The last step to run was step 1 (Subplan_1). I could not understand what is the cause of this. Please help me.
2
votes
2answers
999 views
Sql Server 2008 R2: Simple recovery model with transaction log backups
I'm busy reviewing my SQL Maintenance plans and I'm a bit confused with the recovery model option on the databases, specifically related to point in time recovery.
Here is my scenario:
I have two ...
3
votes
1answer
256 views
How to create a SQL Server 2008 backup without logs with mirroring
How do I create a backup of a SQL Server 2008 in a mirrored setup without any logs?
We need to do this so that when we restore our local development databases, we would have the most up to date ...
2
votes
1answer
365 views
On SQL Server Backup: Msg 3271, Level 16, State 1, Server Line 1 A nonrecoverable I/O error occurred on file
I am trying to back up a pretty big database and I keep getting this error:
Msg 3271, Level 16, State 1, Server {Server Name}, Line 1 A
nonrecoverable I/O error occurred on file "{Backup File ...
2
votes
3answers
392 views
How can you save all user-defined functions to txt files on your local hard drive?
I am running SQL Server 2008 and need to back up the UDF scripts/queries that I have created. How can this be done in a timely manner? I tried right clicking on the folder icon where the UDF are saved ...
3
votes
1answer
133 views
Transactional versus Merge Replication (Restore backup after downtime)
I have the following transactional replication setup:
Windows Server 2008 with SQL Server 2008 Enterprise
Server A: Master, has a database with a table, publisher
Server B: Slave, subscriber ...
1
vote
1answer
996 views
Full and differential restore with two backup files - SQL Server 2008
We are trying to establish a maintenance plan with 2 separate backups.
One full backup over the weekend and differential backups through the week. [the differential backup has a "_diff" appended to ...
5
votes
2answers
172 views
Performing data update operations while backing up a large SQL Server database
I have a large (in the tens of millions of records) database that I am going to perform a full database backup on.
However, the database is large enough that transactions can start before and during, ...
4
votes
3answers
718 views
RSync Database File Deltas in SQL Server 2008
Alright, I feel like this is a terrible idea, but I need some help understanding why this is bad:
I'm still working on implementing a disaster recovery/business continuity solution for our ...
2
votes
1answer
106 views
Availability Strategies in MS SQL Server 2008
I need some assistance in developing a backup strategy for my SQL Server deployments. Currently I have two classes of customer: Hosted customers, whose data is housed in our datacenter, and On ...
2
votes
1answer
121 views
SQL Server asks for 100GB for a dump with 1GB
Is it acceptable that SQL Server asks for 100GB free disk space to restore a 1GB dump file?
If no, what should be parameterized?
2
votes
2answers
274 views
Notification mail not sent on failure
Currently we are having problems with our backups, specifically disk space issues. On investigation it seems the notification mail isn't being sent when the job fails. This is a weird issue as a ...
5
votes
2answers
451 views
How to automatically stagger transaction log shipping to minimize bandwidth usage peaks?
The following question pertains to Microsoft SQL Server transactional log shipping (TLS).
We're using SQL Server 2008 R2 SP1, although the question is probably relevant for all recent editions.
...
2
votes
2answers
188 views
Can I move a system database from one version of SQL server to another?
We are moving our databases from one computer running SQL Server 2005 to a different machine running SQL Server 2008.
I am following the steps found here, in an attempt to copy over the master ...
1
vote
1answer
71 views
Where and with what should I be looking for a good hardware backup tool?
Until now, I was doing my SQL Backups using USB Hard disk, and copying that to my local computer. After that, I went zipping and burning onto DVDs.
Now I want to push that on more professional and ...
5
votes
2answers
798 views
SQL Server backup, attach to BAK file?
I have a SQL Server database with a full text index. When I created the app that uses it, I made a backup of the database and I ship the .BAK file with the app. As part of the app install it does a ...
3
votes
2answers
206 views
Archive a single giant table from a SQL 2008 database - filegroup backup?
I have an extremely large table (400GB data size, inside a 2.5TB database) that is no longer needed. Before we drop it, however, our client contract says we need to archive it to tape in case in needs ...
1
vote
3answers
251 views
Is it good idea to change Simple Recover Model into Bulk-Logged on ETL system?
We are having an ETL process which inserts lots of data into tables. This database is set to Simple Recovery Model and the transaction log is growing a lot. I was thinking that would it help to set ...
2
votes
2answers
1k views
SQL Server 2008, switching to simple recovery mode
Currently our transaction log is 50 GB; impressive huh?
I want to switch to simple recovery mode and remove the transaction log entirely.
Do I need to stop any user activity and detach the database. ...
-1
votes
1answer
226 views
Does SQL Server full restore set the differential base?
Differential backups are taken as the difference between the current state, and the last full backup called the "differential base".
Does restoring a full backup reset the differential base? If so, ...
1
vote
1answer
57 views
How do I determine if a database restore file should be restored to the database based on backupset?
I have a production database that is fully backed up nightly and incrementally backed up hourly. On our backup machine, we have the differential backups and full backups restored ion a regular basis ...
2
votes
1answer
512 views
How to Backup and Restore Filestream Data
I am trying to backup and restore my database which includes a filestream filegroup.
I backed up the database without the filestream filegroup and was interested to know what happens when you restore ...
2
votes
1answer
109 views
Aborted restoring from back up Sql Server 2008
I started restoring from backup using (WITH REPLACE, RESTORE WITH RECOVERY)
and realized that it was a mistake, so I stopped restoring.
Now this db is unavailable and has a 'Restoring' status.
Can ...
2
votes
1answer
231 views
How often do you back up the distribution database for transactional replication when sync with backup is set to true for the distribution database?
I'm trying to figure out how often to back up the distribution database when "sync with backup" is set to true. If I understand this correctly, sync with backup ensures the distribution database can ...
1
vote
1answer
140 views
Copy database schema from SQL Server 2008 to SQL Server 2005 [duplicate]
Possible Duplicate:
Downgrading from SQL Server 2008 to 2005
Is it possible to copy the database schema from SQL Server 2008 to SQL Server 2005,
I don't need data from the tables, just ...
7
votes
6answers
421 views
Scheduled backup task doesn't always backup all databases despite always saying job successful
I have a job in SQL 2008 that runs a stored proc to backup all databases. This runs daily via sql server agent job.
It quits with success every day but some days it quits with success only after ...
9
votes
5answers
414 views
SQL Server Backups - A couple of questions
We run our weekly backup job at 9pm on Fridays and we are experiencing a couple of issues with regard to disk space (which gets perilously low at times) and performance. We're looking at ...
0
votes
1answer
410 views
How to create a backup schema script including System Tables in Sql Server 2008 R2?
I need to back up my entire DB including one table that's allocated in "Tables -> System Tables". When I try to generate the sql scripts to back up my DB this table isn't mapped in this scripts. Do I ...
1
vote
2answers
259 views
Scheduled backup errors
I am trying to do the following. I have about 50 DBs and a network NAS that acts as backup machine. I have made an sql script(lets say backup.sql) with 50 lines each of these say "BACKUP DATABASE ABC ...
3
votes
2answers
3k views
How can I backup & restore a single FILEGROUP in Sql Server 2008
Previously, on ServerFault I asked a question about backing up and restoring a Sql Server 2008 Filegroup.
Today, when I tried to RESTORE one of these FILEGROUP backups, I got the following error:-
...
5
votes
4answers
10k views
Restoring a backup to an older version of SQL Server
When trying to restore a backup to a SQL Server Express 2008 database I got the following error:
Restore failed for Server '...\SQLEXPRESS'. (Microsoft.SqlServer.SmoExtended)
...
5
votes
3answers
2k views
Will the transaction log shrink automagically in SQL Server?
When SQL Server database in a SIMPLE mode, you don't have to care about the transaction log bakcups. But in a SIMPLE mode, the transaction log seems to grow as it does in FULL mode. Does is truncate ...
2
votes
4answers
2k views
SQL Server: cold backup, shutting down services?
One of our collagues told me that they are doing "cold" backups for SQL Server so that they are shutting down services and then taking a backup somehow. I think this is all wrong! Am I right? I think ...
4
votes
1answer
3k views
Restore encrypted database to another server
I'm using a product which runs on SQLServer 2008. Understandably the company supplying it don't offer SQLServer support. When I installed the product I specified a password to encrypt the database. ...
26
votes
6answers
1k views
The smallest backup possible … with SQL Server
Daily we ship our SQL Server backups across the WAN. We need to minimize the size of these backups so it does not take forever.
We don't mind if our backup process takes a bit longer; as it stands ...
5
votes
1answer
472 views
How to back up a small database in SQL Server 2008 R2 Express Edition
I will shortly be migrating a database from SQL Server 2000 (part of SBS 2003) to SQL Server 2008 R2 Express Edition
The database is small, and there are only a few hundred short transactions per day ...
2
votes
1answer
319 views
What is the best practice for backing up a database in Simple mode?
I am trying to create a SQL Job for backing up my database. I want to have:
A weekly full backup
A daily differential backup
Something that will clean up backups that are older that 14 days
I ...
9
votes
2answers
460 views
A Non-DBA Asks: How to Painlessly Copy/Move a SQL Server instance to another Server via Backup/Restore?
We've just got a new server so I need to copy the existing SQL Server 2008 instance over from the old db box.
I normally do this by copying .mdf and log files over and attaching them but I'm not able ...
4
votes
5answers
5k views
User Permissions Messed Up following a Backup -> Restore Operation
I had to move several SQL Server 2008 databases to our new db server so I backed them all up (to .bak files), copied these files over to the new box and restored them (all done using SQL Management ...
3
votes
2answers
129 views
SQL server 2005 backup statistic
I'd like to share with you this simple snippet (answering my own question) and I'm also curious how do you control your SQL server backups.
