0
votes
1answer
42 views

How do I backup/restore database encryption key in order to restore on new server

I am currently in the process of restoring a TDE encrypted database to another instance. I have backed up master key and certificate, and have restored them to the new server fine, but I am still ...
1
vote
2answers
53 views

SQL Server Backup and Query Execution

Is SQL Server Backup affects the query execution performance? Is there an effect while running the SQL Server backup in remote query execution? Thanks.
0
votes
0answers
68 views

Transaction log backups during index maintenance

We have a database with large and complex files and fragmented indexes. The environment is SQL 2008 Full recovery model and Mirrored which limits me to reorganizing. The backup strategy is Full ...
2
votes
2answers
730 views

Deploy SQL Server Backup Maintenance Plan in multi-tenant environment

We have a multi-tenant environment in which I've been trying to deploy a standardised maintenance plan across all servers. I've been running into issues importing the maintenance plan properly, as ...
1
vote
1answer
384 views

can I Run a Stored Procedure across all SQL servers and return 1 html report

I have the following set up as a sp and it runs daily. It gives me a report for each SQL cluster in my environment but what I'd like to do is configure it to run across all clusters and return 1 ...
3
votes
1answer
78 views

Error with restoring from a split .bak file with password

I am trying to restore a backup from a split .bak file[.bak_1/.bak_2/.bak_3]. The backup is password protected. So far this is what I have and I cannot seem to format in a way that does not throw me ...
2
votes
3answers
5k views

SQL Server 2008 Database Restore Error - Cannot open backup device 15105

I am trying to restore from a backup and getting the following error Msg 3201, Level 16, State 2, Line 1 Cannot open backup device 'Z:\FULL_BACKUP.bak'. Operating system error 3(failed to ...
0
votes
3answers
5k views

Restoring .bak file SQL Server 2005 in SQL Server 2008

I don't have enough knowledge in SQL Server back and restore. I just want to know is it possible to restore an SQL Server 2005 .bak file in SQL Server 2008? If yes, how can I do it?
2
votes
2answers
56 views

Viewing the SQL of a view from a backup set

I have a full backup of my database from 4 days ago. It is a standard SQL 2008 .bak file. What I'd like to know is if its possible to view the SQL to a View that is in the database I backed up without ...
5
votes
3answers
257 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
513 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
605 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
478 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
1k 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
2k 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
1k 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
699 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
756 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
557 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 ...
3
votes
3answers
3k 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 ...
5
votes
1answer
806 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 ...
3
votes
2answers
1k 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
1k 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
271 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
4k 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
266 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
4answers
1k 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
143 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
176 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
608 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
754 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
253 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
77 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
1k 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
297 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 ...
2
votes
3answers
1k 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
2k 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
277 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
66 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
740 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
143 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
420 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
201 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 ...
8
votes
7answers
705 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
509 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
582 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
336 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
6k 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:- ...
7
votes
5answers
18k 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 ...