Backup / Restore a MS SQL Server Database (Using SQL)

<span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">(</span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">on</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> source server</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">...)</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">
</span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">BACKUP</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">DATABASE</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> Northwind
  </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">TO</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">DISK</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">=</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(128, 0, 0);">&#39;c:\Northwind.bak&#39;</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">

</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">(</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">target server</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">...)</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">
</span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">RESTORE</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> FILELISTONLY
  </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">FROM</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">DISK</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">=</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(128, 0, 0);">&#39;c:\Northwind.bak&#39;</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">

</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">(</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">look at the device names</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">...</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">and</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> determine </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">where</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> you want the mdf </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">and</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">
ldf files </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">to</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> go </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">on</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> this target server</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">)</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">

</span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">RESTORE</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">DATABASE</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> TestDB
  </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">FROM</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">DISK</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">=</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(128, 0, 0);">&#39;c:\Northwind.bak&#39;</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">
  </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">WITH</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> MOVE </span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(128, 0, 0);">&#39;Northwind&#39;</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">TO</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(128, 0, 0);">&#39;c:\test\testdb.mdf&#39;</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">,</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">
  MOVE </span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(128, 0, 0);">&#39;Northwind_log&#39;</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(0, 0, 139);">TO</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;"> </span><span class="str" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent; color: rgb(128, 0, 0);">&#39;c:\test\testdb.ldf&#39;</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; background-color: transparent;">
GO</span>
(on source server...)
BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'
FROM DISK = 'c:\Northwind.bak'
(look at the device names... and determine where you want the mdf and
ldf files to go on this target server)
RESTORE DATABASE TestDB
WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'
GO
M	T	W	T	F	S	S
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28
Rex's Personal Site

This site is built on WordPress and contains personal notes, photos and videos

Backup / Restore a MS SQL Server Database (Using SQL)
