{"id":2052,"date":"2011-08-05T13:24:23","date_gmt":"2011-08-05T11:24:23","guid":{"rendered":"http:\/\/rephlex.de\/blog\/?p=2052"},"modified":"2011-08-05T13:24:23","modified_gmt":"2011-08-05T11:24:23","slug":"regelmasiges-backup-der-kompletten-ms-sql-server-2005-datenbank","status":"publish","type":"post","link":"https:\/\/rephlex.de\/blog\/2011\/08\/05\/regelmasiges-backup-der-kompletten-ms-sql-server-2005-datenbank\/","title":{"rendered":"Regelm\u00e4\u00dfiges Backup der KOMPLETTEN MS SQL Server 2005 Datenbank"},"content":{"rendered":"

Weil Datenbanken, die in Benutzung sind, aus normalen Filebackups nicht immer wiederhergestellt werden k\u00f6nnen, empfiehlt es sich, t\u00e4glich \/ regelm\u00e4\u00dfig Dumps der Datenbanken in Dateien zu erstellen.<\/p>\n

Weil ich das \u00f6fter mal brauche und du sicher auch (sonst h\u00e4ttest du diesen Beitrag nicht gegooglet), habe ich dokumentiert wie ich das l\u00f6se.<\/p>\n

Schritt 1)<\/strong> Ordner C:\\sqlbackup anlegen<\/p>\n

Schritt 2)<\/strong> Datei C:\\sqlbackup\\dobackup.sql erstellen<\/p>\n

DECLARE @DBName varchar(255)<\/p>\n

DECLARE @DATABASES_Fetch int<\/p>\n

DECLARE DATABASES_CURSOR CURSOR FOR
\nselect
\nDATABASE_NAME\u00a0\u00a0 = db_name(s_mf.database_id)
\nfrom
\nsys.master_files s_mf
\nwhere
\n— ONLINE
\ns_mf.state = 0<\/p>\n

— Only look at databases to which we have access
\nand has_dbaccess(db_name(s_mf.database_id)) = 1<\/p>\n

— Not master, tempdb or model
\nand db_name(s_mf.database_id) not in (‚Master‘,’tempdb‘,’model‘)
\ngroup by s_mf.database_id
\norder by 1<\/p>\n

OPEN DATABASES_CURSOR<\/p>\n

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName<\/p>\n

WHILE @@FETCH_STATUS = 0
\nBEGIN
\ndeclare @DBFileName varchar(256)
\nset @DBFileName = datename(dw, getdate()) + ‚ – ‚ +
\nreplace(replace(@DBName,‘:‘,’_‘),’\\‘,’_‘)<\/p>\n

exec (‚BACKUP DATABASE [‚ + @DBName + ‚] TO\u00a0 DISK = N“c:\\db backup\\‘ +
\n@DBFileName + “‘ WITH NOFORMAT, INIT,\u00a0 NAME = N“‘ +
\n@DBName + ‚-Full Database Backup“, SKIP, NOREWIND, NOUNLOAD,\u00a0 STATS = 100‘)<\/p>\n

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
\nEND<\/p>\n

CLOSE DATABASES_CURSOR
\nDEALLOCATE DATABASES_CURSOR<\/p><\/blockquote>\n

Schritt 3)<\/strong> Datei C:\\sqlbackup\\dobackup.bat erstellen<\/p>\n

osql -Usa -Ppassword -i C:\\sqlbackup\\dobackup.sql<\/p><\/blockquote>\n

Anmerkung: in diesem Beispiel lautet der SQL-User „sa“ (Standard!) und das Passwort „manager“.<\/p>\n

Schritt 4)<\/strong> Batch-Script C:\\sqlbackup\\dobackup.bat per Taskplaner t\u00e4glich starten<\/p>\n

Resultat:<\/strong> Es werden in unserem angelegten Ordner f\u00fct jede Datenbank und jeden Wochentag je ein Dump erstellt.\u00a0 z.B. „Freitag – myDatabase“, „Freitag – sdshared“, „Freitag – msdb“ und so weiter.<\/p>\n

Diese Dumps lassen sich \u00fcbrigens auch mit dem gleichen osql-Befehl wieder in eine Datenbank einlesen (z.B. osql -Usa -Pmanager -i „C:\\sqlbackup\\Freitag-myDatabase.sql“)<\/p>\n

Bei Fragen oder Problemen dazu<\/strong> einfach kurz ne Mail schreiben oder gerne auch anrufen (nur keine Hemmungen, ich helfe gerne!). Kontaktdaten unter http:\/\/www.feins-it.de<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

Weil Datenbanken, die in Benutzung sind, aus normalen Filebackups nicht immer wiederhergestellt werden k\u00f6nnen, empfiehlt es sich, t\u00e4glich \/ regelm\u00e4\u00dfig Dumps der Datenbanken in Dateien zu erstellen. Weil ich das \u00f6fter mal brauche und du sicher auch (sonst h\u00e4ttest du diesen Beitrag nicht gegooglet), habe ich dokumentiert wie ich das l\u00f6se. Schritt 1) Ordner C:\\sqlbackup […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[4],"tags":[],"class_list":["post-2052","post","type-post","status-publish","format-standard","hentry","category-techtalk"],"_links":{"self":[{"href":"https:\/\/rephlex.de\/blog\/wp-json\/wp\/v2\/posts\/2052","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/rephlex.de\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/rephlex.de\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/rephlex.de\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/rephlex.de\/blog\/wp-json\/wp\/v2\/comments?post=2052"}],"version-history":[{"count":1,"href":"https:\/\/rephlex.de\/blog\/wp-json\/wp\/v2\/posts\/2052\/revisions"}],"predecessor-version":[{"id":2053,"href":"https:\/\/rephlex.de\/blog\/wp-json\/wp\/v2\/posts\/2052\/revisions\/2053"}],"wp:attachment":[{"href":"https:\/\/rephlex.de\/blog\/wp-json\/wp\/v2\/media?parent=2052"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/rephlex.de\/blog\/wp-json\/wp\/v2\/categories?post=2052"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/rephlex.de\/blog\/wp-json\/wp\/v2\/tags?post=2052"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}