SQLServerPedia

Here’s the question in its entirety, so you get to see all of the details:

I have two computers running sql server, sqlserver01 and sqlserver01. On sqlserver01 I backup the database locally to C:\SQLBackup\backupfile.bak (db size: 150GB) then I copy and paste the *.bak file to sqlserver02 via the network. It finishes copying but when I try to restore it on sqlserver02 it fails. I have used tools like Eseutil from exchange but no luck. It seems like the bak file corrupts on the transfer.

What is the best way to copy over a 150GB *.bak file to another server then restore the *.bak file in a timely manner?
-end of question-
OK, to answer this I’ll need to actually throw out some other questions about this scenario.

Since this is to be timely, I would argue that this situation would benefit greatly from a tool that gives offers compressed backups. Compression can reduce the 150GB backup file down to 15-30GB, greatly improving copy speed.

But that does not address the real issue here. In fact, there should be no problem copying the 150GB file across the network to another location. It may be time consuming copying the file, depending on network speed, but this should work fine.

One question I have is if this is occurring while performing a normal Windows COPY or are if something like FTP is being used. If FTP, make sure a binary transfer is being performed.

Specific error messages weren’t given, so I would wonder if any are received or if the two instances are running the same version of SQL Server. Verifying that you can restore the database to the same instance from where it was created would be good to find out.  You can restore it to a new database name to avoid overwriting your original.

The other option available, again depending on network speed, is to back up the database to the network rather than to the local drive. A compressed backup would be a lot faster here as well.

If you want to, please provide some additional details of the error and your environments – Operation System versions and SQL Server versions.

Thanks.

–
David

The full original question is:  How do you change the default location of a mdb file? Currently when I detach it is set for C: Drive path and I need to change it to D: Drive path. How can I change it?

First, thanks for the question submission!  You can detach the database, move the file(s), and reattach using the CREATE DATABASE… FOR ATTACH command.

This is a SQL Server 2005/2008 example. For SQL Server 2000, you would use sp_attach_db/sp_attach_single_file_db to perform the attach.

For example:

Detach the database during a maintenance window – make sure you have a backup just in case something goes wrong:

USE master;
GO
EXEC sp_detach_db <db_name>;
GO

Now move the database file to the D: drive and reattach:

– Execute CREATE DATABASE FOR ATTACH statement
CREATE DATABASE
ON (FILENAME = ‘D:\\’) FOR ATTACH;
GO

Keep the questions coming!  We get a large volume of submissions and love seeing what the community is thinking about. 

David

Hi all, here’s a vblog entry that covers a bit more the topic of clock drift in virtualized environments.  This vblog entry corresponds to my blog post earlier this week. 

Enjoy and I look forward to your feedback – Kevin

[youtube=http://www.youtube.com/watch?v=BYoL7UPSNvs]

Hello all – here’s my latest vblog entry where I discuss the online resources that I use when trying to research various SQL Server topics.  I hope you find these resources as helpful as I do!

Thanks,

Kevin

[youtube=http://www.youtube.com/watch?v=8d2TmSEGu7E]

Here’s the actual question in its entirety:

We are trying to implement log shipping… We implement a transactional backup hourly. If our nightly full backup takes 1 hour and 45 minutes, what is our recovery process? Is it a bad thing to have a transactional backup in the middle of a full backup? 

Now, your question, whether you realize it or not, is pretty involved and has three major elements.  First, you want to know about implementing log shipping.  Second, you want to know about the recovery process considering your current backup process of hourly transaction log dumps and nightly full database dumps.  And finally, you want to know if a transaction log backup can cause problems if it occurs while a database backup is already processing. 

FULL DISCLOSURE - I’ll go ahead and say now that I’m going to include verbiage around a Quest product in this response – not a blatant advert, but I will be mentioning one of our products.

So, to your first question – remember that log shipping is a high-availability technique used to speed recovery of a database should you ever have a crash.  You don’t say which version of SQL Server you’re running.  But I assume that you must be running on SQL Server 2000, otherwise you would have mentioned other (and better, IMO) alternatives such as database mirroring.  Assuming you’re on SQL2000, then log shipping is not a big deal to implement and is widely discussed elsewhere on the internet, such as Microsoft’s TechNet (www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx) and MSDN (msdn.microsoft.com/en-us/library/ms187103.aspx).  I won’t rehash an elementary concept of SQL Server when others have already done an excellent job of telling you how to implement it.  Since you might still be in the planning stages of your high-availability solution, here are two caveats that are often overlooked by newbies.  One, don’t forget that the database that is the target of log shipping is going to be in recovery mode all the time until it becomes the primary database.  You pretty much cannot use it unless the source database fails.  Two, log shipping works great with LiteSpeed.  So, if you’re a Quest Software customer using LiteSpeed, then you can enjoy all the benefits of speed, reduced disk consumption, and encryption that LiteSpeed offers.

To your second question, your current backup process, in which you backup the transaction logs every hour and the database nightly, is pretty sound.  Keep in mind that you could potentially lose up to 59 minutes of data with any given database failure.  If 59 minutes does not represent a significant amount of work on the database in question, then great!  However, I rarely had a production database where I was comfortable making users re-key up to 59 minutes of work.  Fifteen minute intervals for a transaction log dump were more in my range of comfort.  Additionally, consider that most applications do NOT have users working around the clock.  Because of that very natural business cycle, you can often lessen or even completely discontinue transaction log backups during the wee hours.  For example, you might run the transaction log backups every 15 minutes from 6:00 AM EST to 8:00 PM EST every day, since you have few if any users after that time.  Alternately, you might run the log dumps ever 15 minutes from 6:00 AM to 8:00 PM EST, run them hourly from 8:00 – 10:00 PM EST, and then only do one more full database backup until the next morning.  Frequent log backups are important also because that’s the primary means by which you keep the transaction log from growing too large.

Finally, with your last question, starting a transaction log backup while a full database backup is still running will behave differently on different versions of SQL Server.  For SQL2000, SQL Server doesn’t allow you to run the transaction log backup while the full database backup is running (at least that’s what my fragile memory is telling me).  For SQL2005 and later, transaction log backups and full database backups can run concurrently though the transaction log backup will certainly be slower because of it and the database backup will likely be slower too.  The full database backup will contain all data in the state of the database at the time it completed, while the transaction log backup will contain all of the transactions that have run since the last full database backup OR transaction log backup.  Since the transaction log backup will probably finish before the 90 minute long database backup, you should think of that transaction log backup as the last log backup before the new full database backup starts the transaction log backup process all over again.  Thus, in an emergency restore situation, you could apply the previous day’s full database backup plus all transaction logs up to the time that the transaction log ran concurrently with tonight’s full database backup to get a recovery that included all transactions up to the most recent point in time.  Once the full database backup completes, however, the clock is started over and you would have to start applying transaction log backups that were subsequent to the completion of the full database backup.

Hope this helps,

-Kevin

Hello, this is Kevin Kline – join me tomorrow, along with Quest Domain Expert Brent Ozar and SQL Server MVP Ron Talmage, for a roundtable discussion on SQL Server consolidation and virtualization.  We’ll discuss a variety of issues that seem to come up constantly in the discussion forums – How are your consolidated / virtualized SQL Servers going to be used?  Are they running production environments with strict SLAs and heavy workloads?  Do SQL Server licensing costs make a big difference for your organization?  What about SQL Server management costs?  Will performance troubleshooting be an issue?  

The webcast is tomorrow, Thursday July 30th, at 8:00 AM PST / 11:00 AM EST.  Register for the webcast here.

I hope to see you there tomorrow!

Hi everybody, I just wanted to go ahead and post the third part of the four part “screencast” series that I did through TechTarget.  Again, if you’ve ever struggled with knowing which PerfMon counters to track for SQL Server performance, then check out this series and get answers to questions including:

I hope it’s helpful to you and would welcome your feedback, so feel free to leave your comments.

 Thanks alot and you can find this third part of the series here titled “Monitor SQL Server disk I/O with PerfMon counters“.

Q: I need a query that will tell me all objects that a user account has access to in each database on the server. How can I find out what roles a user belongs to and what objects they have permissions to?

Kevin Kline says: You need to get familiar with the free SQL Server Script Library that is available at http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/default.mspx?mfr=true  

However, since you specifically are looking for permission information be sure to check www.sqlsecurity.com for great (and free) scripts related to all things security.

Q: I need to do an INSERT INTO in one table from a SELECT, but one of the columns of the inserted table is a field that needs to be incremented by one – depending of a key of a “father” related file. For example, I have master-detail files, in each master register I need to begin counting from 1 up to the number of records inside of a MASTER file, when the KEY of master file is changed the child file needs to restart counting from 1 again, how I do the SELECT of this column?

Bryan Oliver says:

You should probably put an IDENTITY attribute on the column of the inserted table. Then you can simple ignore that column in your INSERT statement.

If, on the other hand, you are not allowed to do this for some reason, try this:

UPDATE my_table

SET a = my_col1,

SET b = my_col2,

SET c = ((SELECT MAX(my_val) FROM master_table) + 1) ,

SET d = my_col3

FROM master_table

JOIN my_table.my_val = master_table.my_val

If you are inserting data from some other source to a table with an identity column and you need to ensure you retain the indentity values, you can temporarily allow inserts to the indentity column. Without doing so explicitly you will receive an error if you attempt to insert a value into the indentity column. For example, if I have a table named MYTABLE and I want to allow inserts into it’s identity column, I can execute the following:

set identity_insert mytable on

Once you execute the command you will be able to insert values into the table’s identity column. This will stay in effect in until you turn it off by executing the following:

set identity_insert mytable off

Be aware that at any time, only a single table in a session can have the identity_insert set to on. If you attempt to enable this for a table and another table already has this enabled, you will receive an error and will not be able to do so until you first turn this off for the other table. Also, if the value used for the indentity is larger than the current identity value then the new value will be used for the identity seed for the column.
WHERE ….

I hope that helps.

I was able to post this on my blog on SQLBlog.com as well.  Rod Colledge, out of Oz (that would be Australia), put together a great aggregator for SQL Server related news, whitepapers, articles, etc.  If you want to check it out, go to his site at www.sqlcrunch.com – pretty cool.