Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
fralo
Posting Yak Master
161 Posts |
 Posted - 2011-01-19 : 08:44:30
|
| Hi friends,I have a question connected to a post I had yesterday. I need to automate copying a database from one server to another. I finally discovered the 'RESTORE DATABASE' command that I can use to accomplish this. I ran it manually this morning but received an error saying 'the tail of the log for the database has not been backed up'. Only until after I ran a 'BACKUP LOG' command could I restore the database.My question is if I set this up a job step to run daily, will I have to run the 'backup log' first?In other words, I envision my job step having to be 'backup log' followed by 'restore database' because unless I do the backup it may fail at runtime with the same error.Thanks for your help. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-01-19 : 09:11:04
|
So you are using backup/restore to take a copy of a database.For this SQL Server came up with Copy-Only-Backup.A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups.Have a look in BOL or google.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2011-01-19 : 09:24:16
|
| I don't understand most of your post. I am using backup/restore. I've set up a job step to do this on a daily basis. My question is do I have to always include the backup statement or can I simply run only the restore database command?declare @today varchar(10);declare @path varchar(50);declare @restore varchar(80);begin set @today = CONVERT(varchar(8), getdate(),112) set @path = 'c:\' + @today + '.bak' backup log empmast to disk = @path with norecovery set @restore = '\\<path\e$\backups\backup_' + @today + '.bak' restore database test from disk = @restoreend |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-01-19 : 09:26:14
|
| If you're restoring over an existing database that's in full or bulk-logged recovery you need to either back the log of that DB up first or restore WITH REPLACEThe error is because SQL's notifying you that you may be overwriting a database that has transactions that haven't been backed up, transactions that you'll lose if you restore over it.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
Backup & Restore