SQL Server 2008 - nightly backup to another server

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.

All Forums

General SQL Server Forums

New to SQL Server Administration

SQL Server 2008 - nightly backup to another server

Author

Topic

EricSmith
Starting Member

3 Posts

Posted - 2010-04-22 : 11:38:20

I have a SQL Server 2008 database that I want to nightly backup to another SQL Server 2008 Express database.

What is the best way to do this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-22 : 14:36:03

You can backup to a remote server by passing the UNC path in the "FROM DISK" portion of BACKUP DATABASE command.

Here's my custom code for backups: http://weblogs.sqlteam.com/tarad/archive/2009/12/29/Backup-SQL-Server-2005-and-2008-DatabasesAgain.aspx

Just pass it the UNC path rather than a local path.

But the better solution is to backup to the local disk first and then just copy the file over to the other system using xcopy or any tool that can copy files. We use a tool called Fast Copy: http://weblogs.sqlteam.com/tarad/archive/2009/03/03/FastCopy---fastest-Windows-copy-product.aspx

If you intend to refresh the Express system automatically, check out this: http://weblogs.sqlteam.com/tarad/archive/2009/02/25/How-to-refresh-a-SQL-Server-database-automatically.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

EricSmith
Starting Member

3 Posts

Posted - 2010-04-26 : 07:08:57

I found a better way, using replication in SQL Server 2008. It works brilliantly!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-26 : 12:14:26

If replication is the answer, then your original post did not provide enough details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

EricSmith
Starting Member

3 Posts

Posted - 2010-04-26 : 12:18:46

Well, you can setup replication to run on nightly basis (once per day) with minimal effort so I believe that is exactly what I asked for in my original post?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-26 : 12:26:17

No, your post says you want to back it up to another server. It does not mention wanting that database restored or available on another server.

Replication doesn't cover everything, so it shouldn't be used as a solution to "mirror" a database. To get the exact database copied to another server, I use a method like this: http://weblogs.sqlteam.com/tarad/archive/2009/02/25/How-to-refresh-a-SQL-Server-database-automatically.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources